Using Existing Access Paths

When two or more files are based on the same physical files and the same key fields in the same order, they automatically share the same keyed sequence access path. When access paths are shared, the amount of system activity required to maintain access paths and the amount of auxiliary storage used by the files is reduced.

When a logical file with a keyed sequence access path is created, the system always tries to share an existing access path. For access path sharing to occur, an access path must exist on the system that satisfies the following conditions:

• The logical file member to be added must be based on the same physical file members that the existing access path is based on.
• The length, data type, and number of decimal positions specified for each key field must be identical in both the new file and the existing file.
• If the FIFO, LIFO, or FCFO keyword is not specified, the new file can have fewer key fields than the existing access paths. That is, a new logical file can share an existing access path if the beginning part of the key is identical. However, when a file shares a partial set of keys from an existing access path, any record updates made to fields that are part of the set of keys for the shared access path may change the record position in that access path. See Example of Implicitly Shared Access Paths for a description of such a circumstance.
• The attributes of the access path (such as UNIQUE, LIFO, FIFO, or FCFO) and the attributes of the key fields (such as DESCEND, ABSVAL, UNSIGNED, and SIGNED) must be identical.

  Exceptions:

  1. A FIFO access path can share an access path in which the UNIQUE keyword is specified if all the other requirements for access path sharing are met.
  2. A UNIQUE access path can share a FIFO access path that needs to be rebuilt (for example, has *REBLD maintenance specified), if all the other requirements for access path sharing are met.
• If the new logical file has select/omit specifications, they must be identical to the select/omit specifications of the existing access path. However, if the new logical file specifies DYNSLT, it can share an existing access path if the existing access path has either:
  • The dynamic select (DYNSLT) keyword specified
  • No select/omit keywords specified
• The alternative collating sequence (ALTSEQ keyword) and the translation table (TRNTBL keyword) of the new logical file member, if any, must be identical to the alternative collating sequence and translation table of the existing access path.

The owner of any access path is the logical file member that originally created the access path. For a shared access path, if the logical member owning the access path is deleted, the first member to share the access path becomes the new owner. The FRCACCPTH, MAINT, and RECOVER parameters on the Create Logical File (CRTLF) command need not match the same parameters on an existing access path for that access path to be shared. When an access path is shared by several logical file members, and the FRCACCPTH, MAINT, and RECOVER parameters are not identical, the system maintains the access path by the most restrictive value for each of the parameters specified by the sharing members. The following illustrates how this occurs:

Access path sharing does not depend on sharing between members; therefore, it does not restrict the order in which members can be deleted.

The Display File Description (DSPFD) and Display Database Relations (DSPDBR) commands show access path sharing relationships.

Example of Implicitly Shared Access Paths

The purpose of this example is help you fully understand implicit access path sharing.

Two logical files, LFILE1 and LFILE2, are built over the physical file PFILE. LFILE1, which was created first, has two key fields, KFD1 and KFD2. LFILE2 has three key fields, KFD1, KFD2, and KFD3. The two logical files use two of the same key fields, but no access path is shared because the logical file with three key fields was created after the file with two key fields.

Table 3. Physical and Logical Files Before Save and Restore

	Physical File (PFILE)	Logical File 1 (LFILE1)	Logical File 2 (LFILE2)
Access Path		KFD1, KFD2	KFD1, KFD2, KFD3
Fields	KFD1, KFD2, KFD3, A, B, C, D, E, F, G	KFD1, KFD2, KFD3, F, C, A	KFD1, KFD2, KFD3, D, G, F, E

An application uses LFILE1 to access the records and to change the KFD3 field to blank if it contains a C, and to a C if it is blank. This application causes the user no unexpected results because the access paths are not shared. However, after a save and restore of the physical file and both logical files, the program appears to do nothing and takes longer to process.

Unless you do something to change the restoration, the AS/400 system:

• Restores the logical file with the largest number of keys first
• Does not build unnecessary access paths

Because it has three key fields, LFILE2 is restored first. After recovery, LFILE1 implicitly shares the access path for LFILE2. Users who do not understand implicitly shared access paths do not realize that when they use LFILE1 after a recovery, they are really using the key for LFILE2.

Table 4. Physical and Logical Files After Save and Restore

Note that the only difference from before the save and restore is that the logical files now share the same access path.
	Physical File (PFILE)	Logical File 1 (LFILE1)	Logical File 2 (LFILE2)
Access Path		KFD1, KFD2, KFD3	KFD1, KFD2, KFD3
Fields	KFD1, KFD2, KFD3, A, B, C, D, E, F, G	KFD1, KFD2, KFD3, F, C, A	KFD1, KFD2, KFD3, D, G, F, E

The records to be tested and changed contain:

Relative Record	KFD1	KFD2	KFD3
001	01	01	<blank>
002	01	01	<blank>
003	01	01	<blank>
004	01	01	<blank>

The first record is read via the first key of 0101<blank> and changed to 0101C. The records now look like:

Relative Record	KFD1	KFD2	KFD3
001	01	01	C
002	01	01	<blank>
003	01	01	<blank>
004	01	01	<blank>

When the application issues a get next key, the next higher key above 0101<blank> is 0101C. This is the record that was just changed. However, this time the application changes the KFD3 field from C to blank.

Because the user does not understand implicit access path sharing, the application accesses and changes every record twice. The end result is that the application takes longer to run, and the records look like they have not changed.