Example 1: Basic concepts of joining two physical files

 

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

In this example, the join logical file (JLF) has field Employee number, Name, and Salary. Physical file 1 (PF1) has Employee number and Name, while physical file 2 (PF2) has Employee number and Salary. 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, 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 data file utility (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.

This example uses the following DDS:

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 list describes the DDS for the join logical file in example 1: 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.

JFLD

Identifies the join fields that join records from the physical files specified on the JOIN keyword. 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.

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
  These cases show how the join logical file in example 1 presents records to an application program.

• Case 1: Matching records in primary and secondary files
  This example join logical file contains a single record for each record in the primary file because each record in the primary file has a match in the secondary file.

• Case 2A: A record missing in the secondary file (JDFTVAL keyword not specified)
  This example join logical file misses one record in the primary file because it does not have a match in the secondary file and the Join Default Values (JDFTVAL) DDS keyword is not specified.

• Case 2B: A record missing in the secondary file (JDFTVAL keyword specified)
  Because the Join Default Values (JDFTVAL) DDS keyword is specified, this example join logical file contains a single record for each record in the primary file even though one record does not have a match in the secondary file.

• Case 3: Secondary file has more than one match for a record in the primary file
  This example join logical file contains a duplicate record in the primary file because it has more than one match in the secondary file.

• Case 4: An extra record in the secondary file
  This example join logical file does not include an extra record in the secondary file because the record does not have a match in the primary file.

• Case 5: Random access
  This example join logical file returns records for a random access read operation.

 

Parent topic:

Setting up a join logical file

 

Related concepts

DDS concepts

 

Related reference

Example 7: Joining three or more physical files
Example 4: Using join fields whose attributes are different