Use the JOINKEYS statement to enable join feature processing and to identify the fields used to select records for join processing.
The join feature joins records from two input files that are specified on the SORTJNF1 and SORTJNF2 DD statements. By default, when the JOINKEYS fields from m records in SORTJNF1 match the JOINKEYS fields from n records in SORTJNF2, all combinations of the records are joined using the REFORMAT statement, producing m*n records as input to subsequent MFX processing. (This is called an “inner join.”)
See the discussion of the REFORMAT control statement for a description of how a record is constructed from the two records that have been selected as a match.
If the optional JOIN UNPAIRED statement is specified, the unmatched records from the SORTJNF1 and/or SORTJNF2 files will also be REFORMATted and included in the input to MFX without being joined. (Including the unmatched records from SORTJNF1 is called a “left outer join,” including the unmatched records from SORTJNF2 is called a “right outer join,” and including all unmatched records is called a “full outer join.”) Optionally, only these unmatched records will become input to MFX. See the descriptions of the JOIN and REFORMAT statements for further details on their specification.
The input files do not need to be presorted or have the same record type.
Two JOINKEYS control statements are required – one for each of the two files used in the join.
The JOINKEYS control statement cannot be used with MAXSORT, PARASORT, Syncsort PipeSort, SKIPREC, checkpoint, and merge exits (except for E35), and the DB2 Query feature.
JOINKEYS Control Statement Format
FILE Parameter (Required)
The FILE parameter connects the JOINKEYS control statement with the input file to be read. The specification of F1 connects the JOINKEYS control statement with the SORTJNF1 DD statement. The specification of F2 connects the JOINKEYS control statement with the SORTJNF2 DD statement. FILE cannot be used if either F1 or F2 is used.
For large applications, if one of the two input files has many more duplicate keys for the join than the other input file, that file should be allocated as SORTJNF2 to achieve optimal performance.
F1 and F2 Parameters (Required)
The F1=ddname and F2=ddname parameters are used to specify the ddnames of the input files to be read for the JOINKEYS control statement. F1 is used in place of FILE=F1 to connect JOINKEYS to the first input file and change the ddname from SORTJNF1. F2 is used similarly to connect JOINKEYS to te second input file and change the ddname from SORTJNF2. You should use only one of F1 or F2. The FILE parameter cannot be used if either F1 or F2 has been used.
FIELDS Parameter (Required)
The FIELDS parameter is required. It describes the fields to be used to match records from the two files, SORTJNF1 and SORTJNF2.
The number of JOINKEYS fields and their sorted order (A or D) must be the same for both files, although their starting positions and lengths need not be the same.
The join files do not need to be presorted on the fields specified on the JOINKEYS statement. By default, MFX will sort the records to the proper sequence before performing the join operation. If one or both of the files are already in the JOINKEYS fields sequence, the SORTED parameter (see below) of the JOINKEYS statement can be specified. If the SORTED parameter can be used, the performance of the application will be improved since the need for MFX to preorder the records prior to join processing will be removed.
The maximum number of JOINKEYS fields is 64.
Each JOINKEYS field may be anywhere within the record through column 32750, the maximum length of a field is 4080 bytes, and the sum of all fields on a JOINKEYS statement cannot exceed 4080 bytes.
|
Valid Formats for JOINKEYS Fields
The following table lists the valid formats for JOINKEYS fields.
| ||||||||||||||||
Note that LOCALE will not be used with CH fields.
Field-to-Field Comparisons
The formats of the JOINKEYS fields for the SORTJNF1 file must be compatible with the corresponding fields for the SORTJNF2 file.
The following table shows the permissible types of format comparisons.
|
|
AQ |
BI |
CH |
FI |
PD |
ZD |
|---|---|---|---|---|---|---|
|
AQ |
X |
|
|
|
|
|
|
BI |
|
X |
X |
|
|
|
|
CH |
|
X |
X |
|
|
|
|
FI |
|
|
|
X |
|
|
|
PD |
|
|
|
|
X |
X |
|
ZD |
|
|
|
|
X |
X |
Padding of Compared Fields of Unequal Lengths
The padding characters are blanks when the shorter field is in CH or AQ format; otherwise, they are zeros of the shorter field’s own format. For negative FI fields, the padding character is X‘FF’.
Padding is on the right if the shorter field is in BI, CH or AQ format. Padding is on the left for FI, PD and ZD formats.
SORTED Parameter (Optional)
By default, MFX will presume that the records in the file are not presequenced per the JOINKEYS fields specified. If the records are already collated in the proper sequence, the SORTED parameter can be specified to improve the application's performance. Since LOCALE is not used for CH JOINKEYS fields, do not specify SORTED if the files were previously sorted with LOCALE.
MFX will sequence check each input file according to its JOINKEYS fields. If the SORTED parameter of the JOINKEYS statement was specified to indicate that the file was presorted and the sequence check fails, MFX will issue a critical error message containing the file number. The record number within the file will also be in the error message text whenever the INCLUDE/OMIT parameter of the JOINKEYS statement was not specified.
NOSEQCK Parameter (Optional)
The NOSEQCK parameter may be used when the SORTED parameter has been specified. NOSEQCK instructs MFX to bypass the sequence check that MFX performs for the sorted input file. NOSEQCK should only be used when you are certain that the input file connected to the JOINKEYS statement has already been sorted in the same collating sequence as specified in the JOINKEYS FIELDS parameter, otherwise your output may be incorrect. NOSEQCK may slightly improve the performance of your JOINKEYS application. NOSEQCK is ignored if SORTED has not been specified.
INCLUDE/OMIT Parameter (Optional)
Specify the INCLUDE or OMIT parameter to indicate which records are to be included or omitted from the SORTJNFn file specified on the JOINKEYS statement. The INCLUDE/OMIT processing occurs prior to the JOINKEYS field matching process.
See INCLUDE/OMIT Control Statement for the detailed format of a comparison. The FORMAT=f parameter, which is permitted for the INCLUDE/OMIT control statement, is not permitted for the INCLUDE/OMIT parameter. Field formats must be specified on a field-by-field basis.
TASKID Parameter (Optional)
The TASKID=xx parameter is used to change the first two bytes of the ddnames for the dynamically allocated sortwork data sets used to sort the JOINKEYS input file. This parameter should be used when invoking MFX from a program and attaching multiple join applications that will run concurrently.
TYPE Parameter (Optional)
The TYPE parameter can be used to indicate the record format. TYPE=F indicates fixed-length records; TYPE=V indicates variable-length records.
TYPE should be provided if the input file being specified is VSAM. If TYPE is not provided, TYPE=F will be assumed if the SORTJNFn file is VSAM.
For more information, see Joining Records from Multiple Files.
STOPAFT Parameter (Optional)
The STOPAFT parameter limits the number of records processed from the SORTJNFn file specified. This can be useful when testing a new join application. You can sample a subset of one or both input files and view your output without having to sort both input files in their entirety and possibly generate a very large number of joined records.
The variable n specifies the number of records to be sorted or copied from SORTJNF1 or SORTJNF2. These will be the first n records after JOINKEYS INCLUDE/OMIT processing, if specified, has completed.