There are some important PJC characteristics that are defined as part of the access route definition:
Both of these attributes apply to all predetermined join columns defined on the access route.
Maximum Records:
This value specifies the maximum number of records that are expected to be found in the Accessed Table that have a key matching the Key Columns/Values specified. When the value is 1, then a "1:1" relationship between the tables is established. If the value is More than one, then a "1:many" relationship is established. A 1:1 relationship will allow a table LOOKUP type PJC to be created and the Keep Last value can be entered. A 1:m relationship will allow TOTAL, MAXIMUM, MINIMUM, AVERAGE and COUNT type PJCs to be created.
Keep Last:
When the table relationship is 1:1 using the key defined on the access route, a single value will be retrieved into the predetermined join column. 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 table and Department table. A KEEP LAST value can be nominated to reduce I/O operations. Keep last causes the last 'nnn' values retrieved from the table 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 table again.
Derivation Before/After Virtual Column:
The derivation of the PJC before or after virtual columns will affect the retrieval timing. This option nominates when the predetermined join columns are to be retrieved from the table, i.e. either before or after virtual columns which are derived. "Before" allows a predetermined join column to be used in deriving a virtual column. "After" allows a virtual column to be used as a key column to a table being accessed by the predetermined join column. Simply ask yourself, do I need the virtual column to determine the PJC? If yes, specify After. Or ask yourself, do I need the PJC to determine the virtual column? If yes, specify Before.
