RE:Shared accpths

Hi Henrik and the group -

>Date: Tue, 8 Feb 2000 07:20:27 -0600 (CST)
>From: "Henrik Krebs" <hkrebs@hkrebs.dk>
>Subject: Shared accpths (Was: RGZPFM mistake on a giant file at mid-day).

>Note that sharing accpths might change the order of which you read the 
>records. Example: PF has LF1 (Key FIELDA and FIELDB) and LF2 (Key FIELDA, 
>FIELDB and FIELDC). PF has multible records with the same content in 
>FIELDA, FIELDB and FIELDC. IBM says that the order records in LF1 (whithin 
>one set of FIELDA and FIELDB) are read is 'unpredictably'. In practice - 
>with no shared accpth's - they are read in arrival seq. But with accpth 
>shared with LF2 the order is determined by the content of FIELDC.

I'm going to throw in my two cents worth because I have strong feelings
about how to set up logical files ...

For master files (item master, customer master, employee master, etc.) and
batch type files that have uniquely identified records (order header unique
by order number, order detail unique by order number and line number) ...

The physical file should be keyed by the field(s) that make up the
uniqueness and should be specified UNIQUE.  Depending on circumstances it
generally should be REUSEDLT(*YES).

All logical files should include at the end of the key list all of the
field(s) that make up the uniqueness which have not already appeared in the
key list.  This will insure uniqueness on the logical and therefore a
consistent retrieval sequence regardless of the physical order of the
records in the file.  Since UNIQUE is on the physical, UNIQUE does not need
to be specified on the logical, and for performance it should not be
specified.

Example ... a logical over the order detail file by item number should not
just be by item number, it should be by item number, order number, line
number.  Since the physical is unique by order number, line number;
obviously this logical will also be unique.

For historical type files (sales history, inventory transaction history)
often there is no set of fields that insure uniqueness.  If there is, and
you want to enforce the uniqueness, it should be treated like a master file,
except that you probably wouldn't want REUSEDLT(*YES) as records should not
be being deleted except in bulk, at which point you would reorganize the
file.

Assuming that there is not uniqueness or you don't want to enforce it ...

The physical file should not be keyed and should be REUSEDLT(*NO).

Identify the set of fields that come the closest to insuring uniqueness.

All logical files should include at the end of the key list all of the
field(s) that you identified as coming the closest to insuring uniqueness.
If your program is not dependent on the order of certain fields in the
latter part of the key list, try to arrange those fields in the order that
you think will be most useful for future programs.  Specify FIFO on the
file.  This will insure consistent retrieval sequence so long as the file is
REUSEDLT(*NO) is specified and so long as KEYFILE is not used when
reorganizing the file.

If the rules specified above are followed, there will not be any access path
sharing unless you specify a logical with exactly the same key information
as an existing file, in which case you should just use the existing file.
(There are a few situations where you would want two files with exactly the
same key information, but they are rare.  In that case, access path sharing
would [and should] take place.)

Ken
Southern Wine and Spirits of Nevada, Inc.
Opinions expressed are my own and do not necessarily represent the views of
my employer or anyone else.

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---