Example 1: Dynamically join database files without DDS

This example shows how to dynamically join database files without DDS.

Assume that you want to join FILEA and FILEB, and the files contain the following fields:

FILEA	FILEB	JOINAB
Cust	Cust	Cust
Name	Amt	Name
Addr		Amt

The join field is Cust which exists in both files. Any record format name can be specified on the Open Query File (OPNQRYF) command for the join file. The file does not need a member. The records are not required to be in keyed sequence.

You can specify:

OVRDBF     FILE(JOINAB) TOFILE(FILEA) SHARE(*YES)
OPNQRYF    FILE(FILEA FILEB) FORMAT(JOINAB) +
              JFLD((FILEA/CUST FILEB/CUST)) +
              MAPFLD((CUST 'FILEA/CUST'))
CALL       PGM(PGME) /* Created using file JOINAB as input */
CLOF       OPNID(FILEA)
DLTOVR     FILE(JOINAB)

File JOINAB is a physical file with no data. This file contains the record format to be specified on the FORMAT parameter of the OPNQRYF command.

Notice that the TOFILE parameter on the Override with Database File (OVRDBF) command specifies the name of the primary file for the join operation (the first file specified for the FILE parameter on the OPNQRYF command). In this example, the FILE parameter on the OPNQRYF command identifies the files in the sequence they are to be joined (A to B). The format for the file is in the file JOINAB.

The JFLD parameter identifies the Cust field in FILEA to join to the Cust field in FILEB. Because the Cust field is not unique across all of the joined record formats, it must be qualified on the JFLD parameter. The system attempts to determine, in some cases, the most efficient values even if you do not specify the JFLD parameter on the OPNQRYF command. For example, using the previous example, if you specified:

OPNQRYF    FILE(FILEA FILEB) FORMAT(JOINAB) +
              QRYSLT('FILEA/CUST *EQ FILEB/CUST') +
              MAPFLD((CUST 'FILEA/CUST'))

The system joins FILEA and FILEB using the Cust field because of the values specified for the QRYSLT parameter. Notice that in this example the JFLD parameter is not specified on the command. However, if either JDFTVAL(*ONLYDFT) or JDFTVAL(*YES) is specified on the OPNQRYF command, the JFLD parameter must be specified.

The MAPFLD parameter is needed on the Open Query File (OPNQRYF) command to describe which file should be used for the data for the Cust field in the record format for file JOINAB. If a field is defined on the MAPFLD parameter, its unqualified name (the Cust field in this case without the file name identification) can be used anywhere else in the OPNQRYF command. Because the Cust field is defined on the MAPFLD parameter, the first value of the JFLD parameter need not be qualified. For example, the same result can be achieved by specifying:

JFLD((CUST FILEB/CUST)) +
MAPFLD((CUST 'FILEA/CUST'))

Any other uses of the same field name in the OPNQRYF command to indicate a field from a file other than the file defined by the MAPFLD parameter must be qualified with a file name.

Because no KEYFLD parameter is specified, the records appear in any sequence depending on how the OPNQRYF command selects the records. You can force the system to arrange the records the same as the primary file. To do this, specify *FILE on the KEYFLD parameter. You can specify this even if the primary file is in arrival sequence.

The JDFTVAL parameter (similar to the JDFTVAL keyword in DDS) can also be specified on the OPNQRYF command to describe what the system should do if one of the records is missing from the secondary file. In this example, the JDFTVAL parameter was not specified, so only the records that exist in both files are selected.

If you tell the system to improve the results of the query (through parameters on the OPNQRYF command), it generally tries to use the file with the smallest number of records selected as the primary file. However, the system also tries to avoid building a temporary file.

You can force the system to follow the file sequence of the join as you have specified it in the FILE parameter on the OPNQRYF command by specifying JORDER(*FILE). If JDFTVAL(*YES) or JDFTVAL(*ONLYDFT) is specified, the system will never change the join file sequence because a different sequence can cause different results.