Re: Sorting records in Physical and Logical files

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?