Re: Sorting records in Physical and Logical files

[CRPence <CRPbottle@xxxxxxxxx>]

  The set of rows returned by a query with no ordering specified is 
actually not "always ordered in arrival sequence" even if that may 
appear to be the situation.  The order is better described as 
*unpredictable*, because the next time(s) that same query is run, the 
rows returned may appear instead [of collated by arrival], to be 
collated either in some apparently logical order [e.g. matching the 
keyed ordering of one of the indexes created over the data] or in some 
apparently random order.

  For Row Level Access:  The _Keyed access method_ will perform 
retrieval via the index.  The _Direct access method_ performs retrieval 
via the Relative Record Number.  The _Sequential access method_ performs 
retrieval via the /Arrival/ access path.

  Regardless of the existence of keys defined on a file for which its 
index [keyed access path] is created, that keyed access path will be 
used only for the Keyed access method, for the retrieval of the records. 
 The file with the index, either logical or physical, may be accessed 
via _other than_ its keyed access path; i.e. just because the file is 
keyed, does not limit the access methods to keyed access.  How any query 
might retrieve data can not be predicted nor even implied [except with 
the generation of a database query ODP which is effectively a temporary 
logical file that enables RLA to the query result set within a HLL; e.g. 
using OPNQRYF].

Regards, Chuck

Pete Helgren wrote:
> <<SNIP>> I thought the point of a logical file was to build an
> index that was an alternate to the default sorting sequence of the
> Physical file (among other things). Yes, I could issue an ORDER BY
> using SQL or OPNQRYF by I though the file had a "natural" sorting
> order that was set by the DDS.
>
> So, there is no way to define a physical file using DDS to sort
> records in anything other than arrival sequence?

Pete Helgren wrote:
> I thought I understood this but apparently I don't.
>
> I have a physical and logical file with the following specs:
>
>                             UNIQUE
> R FASA809                   TEXT('List Workfile')     
>   SLWID          6S 0       COLHDG('Absence ID')
>   SLW#           6S 0       COLHDG('Seq. Number')
>   SLWSN          9S 0       COLHDG('Sub SSN')   
> K SLWID
> K SLW#
>
> The logical file looks like:
>
>                             UNIQUE
> R FASA809                   PFILE(PASA809)
> K SLWID
> K SLWSN
>
>
> The physical file has a randomly generated number in field SLW# and 
> since the keys are SLWID and then SLW# in that order. I would expect
> the file to be ordered that way when I open it up in Query or a
> simple select with no ordering in SQL. However, the file is always
> ordered in arrival sequence. If I read through this file, I see the
> records in the order in which they were written in the file. I
> *expected* them to be ordered by the key sequence.
>
> What did I miss or misunderstand?