Case 5: Random access
This example join logical file returns records for a random access read operation.
Assume that a join logical file is specified as in Example 1: Basic concepts of joining two physical files. Note that the join logical file has key fields defined.
Assume that PF1 and PF2 have the following records.
Table 1. Physical file 1 (PF1) Employee number Name 235 Anne 440 Doug 500 Mark 729 Sue 997 Tim
Table 2. Physical file 2 (PF2) Employee number Salary 235 1700.00 440 950.50 729 1400.90 984 878.25 997 331.00 997 555.00 In PF2, no record is found for record 500, record 984 exists only in PF2, and duplicate records are found for 997.
The program can get the following records.
Given a value of 235 from the program for the Nbr field in the logical file, the system supplies the following record.
Employee number Name Salary 235 Anne 1700.00 Given a value of 500 from the program for the Nbr field in the logical file and with the JDFTVAL keyword specified, the system supplies the following record.
Employee number Name Salary 500 Mark 0000.00 If the JDFTVAL keyword was not specified in the join logical file, no record is found for a value of 500 because no matching record is contained in the secondary file.
Given a value of 984 from the program for the Nbr field in the logical file, the system supplies no record and a no record found exception occurs because record 984 is not in the primary file.
Given a value of 997 from the program for the Nbr field in the logical file, the system returns one of the following records.
Employee number Name Salary 997 Tim 331.00 or
Which record is returned to the program cannot be predicted. To specify which record is returned, specify the JDUPSEQ keyword in the join logical file. Notes:
Employee number Name Salary 997 Tim 555.00
- With random access, the application programmer must be aware that duplicate records can be contained in PF2, and ensure that the program does more than one read operation for records with duplicate keys. If the program is using sequential access, a second read operation gets the second record.
- If you specify the JDUPSEQ keyword, the system can create a separate access path for the join logical file (because there is less of a chance that the system will find an existing access path that it can share). If you omit the JDUPSEQ keyword, the system can share the access path of another file. (In this case, the system can share the access path of PF2.)
Parent topic:
Example 1: Basic concepts of joining two physical files
Related reference
Example 3: Reading duplicate records in the secondary file