Re: Join LF sequence

On 29 Oct 2012 10:54, M. Lazarus wrote:

Is the sequence of a join LF without keys specified, that is over
two keyed PFs, "undefined," similar to an SQL statement without an
ORDER BY clause? Or will it be sorted by the primary file's keys by
default?

As I recall, and as verified in a test... Neither. The order of the primary file data is known, and the joined secondary file row data is at least somewhat predictable [and depending on knowledge of internals and strict control of index creation and LF re-creation this can even be "known"].

By its definition as unkeyed, the collation is the retrieval order of the program requesting the non-keyed sequential READ Row Level Access method. For the sequential read, the records will be provided to the program by the database, in what is described as the "arrival" [access path] sequence. The DSPFD TYPE(*ACCPTH) information requested of the non-keyed DDS LF will show that the "Access path"="Arrival". Thus the order of the rows from the "primary" file of the join will reflect the physical order of the records. FWiW: The term "arrival" was debased by the reuse of deleted records (REUSEDLT) feature, such that having since described the access path as being "by RRN order" versus "by arrival order", might generally be considered more accurate.

So whatever is defined as the /physical/ key of the primary\first JFILE named in the DDS does not influence directly the retrieval order experienced by the program performing the sequential READ. The order of the row data retrieved by the database from the primary file [using the arrival access path] would not match the index of that primary file, except when the physical data in that file either had been placed in or reorganized in, the order that matches that key.

In its implementation the "join fields" specifications on the Join Logical File (JLF) will cause the database to define an index\key on each JFLD named from the secondary physical files. When a native\RLA sequential READ access method receives a row, the JFLD value of that primary file record is then matched using the key defined on [the JFLD of] the secondary file. The index over the physical data in the secondary file will either be implicitly created additionally for that LF or that LF will share an existing matching compatible key [owned by the PF or by another LF]. The collation across the logical rows from secondary file data can be predicted according to which existing index will be shared or instead knowing that the secondary index will be created.