4.7.5 Access Routes and PJFs
There are some important PJF characteristics that are defined as part of the access route definition:
- Maximum Records
- Keep Last
- Derivation - Before or After Virtual Field.
Both of these attributes apply to all predetermined join fields defined on the access route.
Maximum Records:
This value specifies the maximum number of records that are expected to be found in the Accessed File that have a key matching the Key Fields/Values specified. When the value is One then a "1:1" relationship between the files is established. If the value is More than one, then a "1:many" relationship is established. A 1:1 relationship will allow a file LOOKUP type PJF to be created and the Keep Last value can be entered. A 1:m relationship will allow TOTAL, MAXIMUM, MINIMUM, AVERAGE and COUNT type PJFs to be created.
Keep Last:
When the file relationship is 1:1 using the key defined on the access route, a single value will be retrieved into the predetermined join field. In the Personnel System example, a Department Description can be retrieved using the Department Code if an access route has been defined between the Section file and Department file. A KEEP LAST value can be nominated to reduce I/O operations. Keep last causes the last 'nnn' values retrieved from the file to be stored in memory. If a value with the same Department Code is required, it can be read from memory instead of reading from the file again.
Derivation Before/After Virtual Field:
The derivation of the PJF before or after virtual fields will affect the retrieval timing. This option nominates when the predetermined join fields are to be retrieved from the file, i.e. either before or after virtual fields which are derived. "Before" allows a predetermined join field to be used in deriving a virtual field. "After" allows a virtual field to be used as a key field to a file being accessed by the predetermined join field. Simply ask yourself, do I need the virtual field to determine the PJF? If yes, specify After. Or ask yourself, do I need the PJF to determine the virtual field? If yes, specify Before.