The JOINKEYS File Match utility help in comparing two files. JOINKEYS File Match operation is controlled by JOINKEYS, JOIN, and REFORMAT control statements. The SORTJNF1 and SORTJNF2, are the DD statements used to specify the files which will be used to perform the join operations. During JOIN operation on two files, each record from the first file (SORTJNF1- M records) will be joined with the second file (SORTJNF2 – N records) based on key values & produces M*N records. In other words, INNER JOIN occurs. Joining can be performed in a number of ways like inner join, full outer join, left outer join, right outer join, and unpaired combinations.
There are primarily four basic steps involved in any JOIN operations.
- Use the REFORMAT control statement to specify the name of fields, that need to be included in the joined record.
- Selecting or dropping records either from both files or from either of the file by using the INCLUDE/OMIT parameter of the JOINKEYS statement.
- Specify whether the input join data is already sorted as per JOINKEYS control fields by using SORTED parameters.
The overall performance of join keys is better if join input data is already sorted as per JOINKEYS fields. - Use JOIN statement to specify the nature of joins i.e. (Inner join, left outer join, right outer join, and full outer join)
JOINKEYS
- Must specify two JOINKEYS statements i.e. one for file 1 (F1) and another for file 2 (F2).
- Each JOINKEYS statement must specify the DDNAME of the file it applies to and the starting position, length, and sequence of the keys in that file.
- Optionally specify if the file is already sorted by the keys and if sequence checking of the keys is not needed; if the file has fixed-length or variable-length records; to stop reading the file after n records; a 2-byte id to be used for the message and control data set for the subtask used to process the file, and if a subset of the records is to be processed based on a logical expression.
JOIN
If the JOIN statement is not specified then only paired records from F1 and F2 are kept and processed by the main task as the joined records (INNER JOIN)
If the JOIN statement is specified then below mentioned process can happen
- UNPAIRED,F1,F2 or UNPAIRED: Retain unpaired records from both F1 & F2 files along with paired records. This type of join is called a FULL OUTER JOIN.
- UNPAIRED,F1: Retain unpaired records from the F1 file along with paired records. This type of join is called a LEFT OUTER JOIN.
- UNPAIRED,F1,ONLY: Retain unpaired records from the F1 file.
- UNPAIRED,F2: Retain unpaired records from the F2 file along with paired records. This type of join is called RIGHT OUTER JOIN.
- UNPAIRED,F2,ONLY: Retain unpaired records from the F2 file.
- UNPAIRED,F1,F2,ONLY or UNPAIRED,ONLY: Retain unpaired records from F1 and F2 files.
REFORMAT
- If a JOIN statement with ONLY is specified, REFORMAT statement is optional
- Specify a REFORMAT statement to indicate the F1 and/or F2 fields you want in the joined records.
- Optionally specify an indicator of where the key was found, and a FILL character to be used for missing bytes.
JOINKEYS – SORTED/NOSEQCK Parameter
The SORTED parameter is used to tell DFSORT to copy file records rather than sort them again.
The NOSEQCH operand is used to tell DFSORT not to check for the order of the records.
For example
JOINKEYS FILE=F1,FIELDS=(12,4,A),SORTED,NOSEQCH JOINKEYS FILE=F2,FIELDS=(25,4,A),SORTED
Explanation: File F1 is copied using the DDNAME SORTJNF1 and the ascending key in positions 12-15. The SORTJNF1 records will not be checked for the correct key order.
File F2 is copied using the dd name SORTJNF2 and the ascending key in positions 25-28. The SORTJNF2 records will be checked for the correct key order.
If the records are not actually in order by the specified keys and you use NOSEQCK, the output may be incorrect. The NOSEQCK operand is ignored if the SORTED operand is not specified.
JOINKEYS – TYPE Parameter
TYPE parameter is used to specify the processing length for a VSAM input file.
- TYPE=V: DFSORT would use variable-length processing for the VSAM file.
- TYPE=F: DFSORT would use fixed-length processing for the VSAM file.
For example
JOINKEYS F1=VSAM1,FIELDS=(13,3,A),TYPE=V JOINKEYS F2=VSAM2,FIELDS=(26,3,A),TYPE=F
Explanation: VSAM file F1 is sorted as variable-length records using the DDNAME VSAM1 and the ascending key in positions 13-15. (Remember that for TYPE=V VSAM processing, DFSORT adds an RDW in positions 1-4 which you must account for when specifying the starting position.)
VSAM file F2 is sorted as fixed-length records using the DDNAME VSAM2 and the ascending key in positions 26-28. (Remember that for TYPE=F VSAM processing, DFSORT does not add an RDW.)
JOINKEYS – STOPAFT Parameter
STOPAFT is used to specify the maximum number of records (n) you want the subtask for the input file to accept for sorting or copying.
For example
JOINKEYS FILE=F1,STOPAFT=5,FIELDS=(32,4,A) JOINKEYS FILE=F2,STOPAFT=10,FIELDS=(32,4,A) OR //SYSIN DD * JOINKEYS FILE=F1,FIELDS=(32,4,A) JOINKEYS FILE=F2,FIELDS=(32,4,A) ... //JNF1CNTL DD * OPTION STOPAFT=5 //JNF2CNTL DD * OPTION STOPAFT=10
Explanation: The first 5 input records from SORTJNF1 and the first 10 input records from SORTJNF2 will be processed.
JOINKEYS – INCLUDE/OMIT Parameters
Join performance can be improved by using INCLUDE/OMIT parameters. These parameters can be specified in the JOIN statement of JONKEYS but it is recommended to specify INCLUDE/OMIT parameter in JNF1CNTL or JNF2CNTL for ease of use.
For example
//SYSIN DD * JOINKEYS FILE=F1,FIELDS=(35,8,A), OMIT=(5,4,CH,EQ,C'ABCD') JOINKEYS FILE=F2,FIELDS=(37,8,A), INCLUDE=(1,20,SS,EQ,C’NO’) //*
Recommended
//SYSIN DD * JOINKEYS FILE=F1,FIELDS=(35,8,A) JOINKEYS FILE=F2,FIELDS=(37,8,A) **** **** //* //JNF1CNTL DD * OMIT COND=(5,4,CH,EQ,C'ABCD') //JNF2CNTL DD * INCLUDE COND=(1,20,SS,EQ,C'NO') //*
Let’s take an example F1 80 bytes and F2 40 bytes file.
Matched Records(Inner Join)
If the JOIN statement is not specified in the sort card, paired records from F1 and F2 are written into the output file.
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
REFORMAT FIELDS=(F1:1,80,F2:1,40)
/*
Matched Records and Non-Matched Records from both files
Sort card to retain unpaired records from both F1 and F2 files along with paired records. This type of join is called a FULL OUTER JOIN.
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,80,F2:1,40)
/*
Matched Records and Non-Matched Records from File1
Sort card to retain unpaired records from the F1 file along with paired records. This type of join is called a LEFT OUTER JOIN.
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,F2:1,40)
/*
Non Matched from File1
Sort card to retain unpaired records from F1 file, cannot use reformat in F2.
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1:1,80)
/*
Matched Records and Non Matched from File2
Sort card to retain unpaired records from the F2 file along with paired records. This type of join is called RIGHT OUTER JOIN.
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F2
REFORMAT FIELDS=(F1:1,80,F2:1,40)
/*
Non Matched from File2
Sort card to retain unpaired records from F2 file, cannot use reformat in F1.
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F2,ONLY
REFORMAT FIELDS=(F2:1,40)
/*
Non-Matched Records from both files
Sort card to retain only unpaired records from F1 and F2 files.
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F1,F2,ONLY or JOIN UNPAIRED,ONLY
REFORMAT FIELDS=(F1:1,80,F2:1,40)
/*
Example: JOINKEYS File Match and Comparison Examples
//STEP010 EXEC PGM=SORT
//SORTJNF1 DD DSN=FIRST.FILE,DISP=SHR
//SORTJNF2 DD DSN=SECOND.FILE,DISP=SHR
//BOTHF1F2 DD DSN=MYDATA.URMI.SAMPLE.MATCH,DISP=OLD
//ONLYF1 DD DSN=MYDATA.URMI.SAMPLE.NOMATCH1,DISP=OLD
//ONLYF2 DD DSN=MYDATA.URMI.SAMPLE.NOMATCH2,DISP=OLD
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,10,A)
JOINKEYS FILE=F2,FIELDS=(7,10,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(?,F1:1,14,F2:1,20)
OPTION COPY
OUTFIL FNAMES=BOTHF1F2,INCLUDE=(1,1,CH,EQ,C’B'),
BUILD=(1:2,14,/,16,20)
OUTFIL FNAMES=ONLYF1,INCLUDE=(1,1,CH,EQ,C'1'),BUILD=(1:2,14)
OUTFIL FNAMES=ONLYF2,INCLUDE=(1,1,CH,EQ,C'2'),BUILD=(1:16,20)
/*
Explanation:
- JOINKEYS specifies the field on which the two files are compared.
- REFORMAT FIELDS=?
- places ‘B’ (matched records),
- ‘1’ (present in file1, but not in file2), or
- ‘2’ (present in file2 but not in file1) in the 1st position of the output BUILD.
- JOIN UNPAIRED does a full outer join on the two files.
You can achieve the above result by using the FILL parameter.
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,10,A)
JOINKEYS FILE=F2,FIELDS=(7,10,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,14,F2:1,20),FILL=C'$'
OPTION COPY
OUTFIL FNAMES=BOTHF1F2,INCLUDE=(15,1,CH,NE,C’$’,
AND,1,1,CH,NE,C'$'),
BUILD=(1:1,14,/,15,20)
OUTFIL FNAMES=ONLYF1,INCLUDE=(15,1,CH,EQ,C’FILL=C'$'),
BUILD=(1:1,14)
OUTFIL FNAMES=ONLYF2,INCLUDE=(1,1,CH,EQ,C’$'),
BUILD=(1:15,20)
/*
Explanation:
- JOINKEYS specifies the field on which the two files are compared.
- FILL=C’$’ in REFORMAT FIELDS will fill the spaces with $.
- $ in position 15 till 35 means (present in file1, but not in file2), or
- $ in position 1 till 14 means(present in file2 but not in file1).
- JOIN UNPAIRED does a full outer join on the two files.
JOINKEYS File Match and Comparision Examples
- Example 1 – Paired records without duplicates (F1/F2)
- Example 2 – Paired records with duplicates (F1/F2)
- Example 3 – Paired records (F1)
- Example 4 – Unpaired records (F2)
- Example 5 – Indicator Method: Paired and unpaired records (F1/F2)
- Example 6 – Fill Method: Paired and unpaired records (F1/F2)