Basic Concepts of Joining Two Physical Files (Example 1)

A join logical file is a logical file that combines (in one record format) fields from two or more physical files. In the record format, not all the fields need to exist in all the physical files.

The following example illustrates a join logical file that joins two physical files. This example is used for the five cases discussed in Example 1.

In this example, employee number is common to both physical files (PF1 and PF2), but name is found only in PF1, and salary is found only in PF2.

With a join logical file, the application program does one read operation (to the record format in the join logical file) and gets all the data needed from both physical files. Without the join specification, the logical file would contain two record formats, one based on PF1 and the other based on PF2, and the application program would have to do two read operations to get all the needed data from the two physical files. Thus, join provides more flexibility in designing your database.

However, a few restrictions are placed on join logical files:

• You cannot change a physical file through a join logical file. To do update, delete, or write (add) operations, you must create a second multiple format logical file and use it to change the physical files. You can also use the physical files, directly, to do the change operations.
• You cannot use DFU to display a join logical file.
• You can specify only one record format in a join logical file.
• The record format in a join logical file cannot be shared.
• A join logical file cannot share the record format of another file.
• Key fields must be fields defined in the join record format and must be fields from the first file specified on the JFILE keyword (which is called the primary file).
• Select/omit fields must be fields defined in the join record format, but can come from any of the physical files.
• Commitment control cannot be used with join logical files.

The following shows the DDS for Example 1:

Figure 13. DDS Example for Joining Two Physical Files

JLF |...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A R JOINREC JFILE(PF1 PF2) A J JOIN(PF1 PF2) A JFLD(NBR NBR) A NBR JREF(PF1) A NAME A SALARY A K NBR A PF1 |...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A R REC1 A NBR 10 A NAME 20 A K NBR A PF2 |...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A R REC2 A NBR 10 A SALARY 7 2 A K NBR A

The following describes the DDS for the join logical file in Example 1 (see the DDS Reference for more information on the specific keywords):

The record level specification identifies the record format name used in the join logical file.

R

Identifies the record format. Only one record format can be placed in a join logical file.

JFILE

Replaces the PFILE keyword used in simple and multiple-format logical files. You must specify at least two physical files. The first file specified on the JFILE keyword is the primary file. The other files specified on the JFILE keyword are secondary files.

The join specification describes the way a pair of physical files is joined. The second file of the pair is always a secondary file, and there must be one join specification for each secondary file.

J

Identifies the start of a join specification. You must specify at least one join specification in a join logical file. A join specification ends at the first field name specified in positions 19 through 28 or at the next J specified in position 17.

JOIN

Identifies which two files are joined by the join specification. If only two physical files are joined by the join logical file, the JOIN keyword is optional. See Joining Three or More Physical Files (Example 7) later in this section for an example of how to use this keyword.

JFLD

Identifies the join fields that join records from the physical files specified on the JOIN. JFLD must be specified at least once for each join specification. The join fields are fields common to the physical files. The first join field is a field from the first file specified on the JOIN keyword, and the second join field is a field from the second file specified on the JOIN keyword.

Join fields, except character type fields, must have the same attributes (data type, length, and decimal positions). If the fields are character type fields, they do not need to have the same length. If you are joining physical file fields that do not have the same attributes, you can redefine them for use in a join logical file. See Using Join Fields Whose Attributes Are Different (Example 4) for a description and example.

The field level specification identifies the fields included in the join logical file.

Field names

Specifies which fields (in this example, Nbr, Name, and Salary) are used by the application program. At least one field name is required. You can specify any field names from the physical files used by the logical file. You can also use keywords like RENAME, CONCAT, or SST as you would in simple and multiple format logical files.

JREF

In the record format (which follows the join specification level and precedes the key field level, if any), the field names must uniquely identify which physical file the field comes from. In this example, the Nbr field occurs in both PF1 and PF2. Therefore, the JREF keyword is required to identify the file from which the Nbr field description will be used.

The key field level specification is optional, and includes the key field names for the join logical file.

K

Identifies a key field specification. The K appears in position 17. Key field specifications are optional.

Key field names

Key field names (in this example, Nbr is the only key field) are optional and make the join logical file an indexed (keyed sequence) file. Without key fields, the join logical file is an arrival sequence file. In join logical files, key fields must be fields from the primary file, and the key field name must be specified in positions 19 through 28 in the logical file record format.

The select/omit field level specification is optional, and includes select/omit field names for the join logical file.

S or O

Identifies a select or omit specification. The S or O appears in position 17. Select/omit specifications are optional.

Select/omit field names

Only those records meeting the select/omit values will be returned to the program using the logical file. Select/omit fields must be specified in positions 19 through 28 in the logical file record format.

Reading a Join Logical File

The following cases describe how the join logical file in Figure 13 presents records to an application program.

The PF1 file is specified first on the JFILE keyword, and is therefore the primary file. When the application program requests a record, the system does the following:

1. Uses the value of the first join field in the primary file (the Nbr field in PF1).
2. Finds the first record in the secondary file with a matching join field (the Nbr field in PF2 matches the Nbr field in PF1).
3. For each match, joins the fields from the physical files into one record and provides this record to your program. Depending on how many records are in the physical files, one of the following conditions could occur:
   1. For all records in the primary file, only one matching record is found in the secondary file. The resulting join logical file contains a single record for each record in the primary file. See Matching Records in Primary and Secondary Files (Case 1).
   2. For some records in the primary file, no matching record is found in the secondary file.

If you specify the JDFTVAL keyword:

• For those records in the primary file that have a matching record in the secondary file, the system joins to the secondary, or multiple secondaries. The result is one or more records for each record in the primary file.
• For those records in the primary file that do not have a matching record in the secondary file, the system adds the default value fields for the secondary file and continues the join operation. You can use the DFT keyword in the physical file to define which defaults are used. See Record Missing in Secondary File; JDFTVAL Keyword Not Specified (Case 2A) and Record Missing in Secondary File; JDFTVAL Keyword Specified (Case 2B).

  Note:

  If the DFT keyword is specified in the secondary file, the value specified for the DFT keyword is used in the join. The result would be at least one join record for each primary record.

• If a record exists in the secondary file, but the primary file has no matching value, no record is returned to your program. A second join logical file can be used that reverses the order of primary and secondary files to determine if secondary file records exist with no matching primary file records.

If you do not specify the JDFTVAL keyword:

• If a matching record in a secondary file exists, the system joins to the secondary, or multiple secondaries. The result is one or more records for each record in the primary file.
• If a matching record in a secondary file does not exist, the system does not return a record.

In the following examples, cases 1 through 4 describe sequential read operations, and case 5 describes reading by key.

Matching Records in Primary and Secondary Files (Case 1)

Assume that a join logical file is specified as in Figure 13, and that four records are contained in both PF1 and PF2, as follows:

The program does four read operations and gets the following records:

Record Missing in Secondary File; JDFTVAL Keyword Not Specified (Case 2A)

Assume that a join logical file is specified as in Figure 13, and that there are four records in PF1 and three records in PF2, as follows:

With the join logical file shown in Example 1, the program reads the join logical file and gets the following records:

If you do not specify the JDFTVAL keyword and no match is found for the join field in the secondary file, the record is not included in the join logical file.

Record Missing in Secondary File; JDFTVAL Keyword Specified (Case 2B)

Assume that a join logical file is specified as in Figure 13, except that the JDFTVAL keyword is specified, as shown in the following DDS:

JLF |...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A JDFTVAL A R JOINREC JFILE(PF1 PF2) A J JOIN(PF1 PF2) A JFLD(NBR NBR) A NBR JREF(PF1) A NAME A SALARY A K NBR A

The program reads the join logical file and gets the following records:

With JDFTVAL specified, the system returns a record for 500, even though the record is missing in PF2. Without that record, some field values can be missing in the join record (in this case, the Salary field is missing). With JDFTVAL specified, missing character fields normally use blanks; missing numeric fields use zeros. However, if the DFT keyword is specified for the field in the physical file, the default value specified on the DFT keyword is used.

Secondary File Has More Than One Match for a Record in the Primary File (Case 3)

Assume that a join logical file is specified as in Figure 13, and that four records in PF1 and five records in PF2, as follows:

The program gets five records:

For more information, see Reading Duplicate Records in Secondary Files (Example 3).

Extra Record in Secondary File (Case 4)

Assume that a join logical file is specified as in Figure 13, and that four records are contained in PF1 and five records in PF2, as follows:

The program reads the join logical file and gets only four records, which would be the same even if JDFTVAL was specified (because a record must always be contained in the primary file to get a join record):

Random Access (Case 5)

Assume that a join logical file is specified as in Figure 13. Note that the join logical file has key fields defined. This case shows which records would be returned for a random access read operation using the join logical file.

Assume that PF1 and PF2 have the following records:

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:

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:

Note:

If the JDFTVAL keyword was not specified in the join logical file, no record would be 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:

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. See Reading Duplicate Records in Secondary Files (Example 3).

Notes:

1. With random access, the application programmer must be aware that duplicate records could be contained in PF2, and ensure that the program does more than one read operation for records with duplicate keys. If the program were using sequential access, a second read operation would get the second record.

2. 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 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 would share the access path of PF2.)