The JOIN key is a concept used to create new data set from the given data sets by using KEY. This is possible with DFSORT, and can be used with JCL
//JN1 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //IN1 DD DSN=DSN=A123456.REGION.IN1,DISP=SHR //IN2 DD DSN=DSN=A123456.REGION.IN2,DISP=SHR //SORTOUT DD DSN=A123456.REGION.OUT,DISP=(NEW,CATLG,DELETE),UNIT=3390, // SPACE=(CYL,(5,5)) //SYSIN DD * JOINKEYS F1=IN1,FIELDS=(1,5,A) F1 has ddname IN1 and key in 1-5 JOINKEYS F2=IN2,FIELDS=(5,5,A) F2 has ddname IN2 and key in 5-9 REFORMAT FIELDS=(F2:1,4, Office from F2 F1:1,5,21,15, Region and Regional Director from F1 F2:10,4,14,10, Employees and Evaluation from F2 F1:6,15) Headquarters from F1 OPTION COPY Copy joined records /*
How to understand each step in JOIN Key JCL
- Two JOINKEYS statements are required: one for the F1 data set and another for the F2 data set.
- In this case, the first JOINKEYS statement identifies IN1 as the ddname for the F1 data set and indicates an ascending key (Region) in positions 1-5 of that data set.
- The second JOINKEYS statement identifies IN2 as the ddname for the F2 data set and indicates an ascending key (Region) is at positions 5-9 of that data set.
- Each key in the F1 data set must be of the same length and order (ascending or descending) as the corresponding key in the F2 data set, but does not have to be in the same location.
The keys are always treated as unsigned binary (INREC can be used to “normalize” the keys in each data set before the records are joined, if necessary).
The F1 data set will be sorted by the key in positions 1-5. The F2 data set will be sorted by the key in positions 5-9. If the records in a data set are already sorted by the key, you can specify SORTED on the JOINKEYS statement to tell DFSORT to copy the records of that data set rather than sorting them.
This REFORMAT statement creates joined records from the following fields:
- Output positions 1-4: Office from F2
- Output positions 5-9: Region from F1
- Output positions 10-24: Regional Director from F1
- Output positions 25-28: Employees from F2
- Output positions 29-38: Evaluation from F2
- Output positions 39-53: Headquarters from F1
The resulting joined records are 53 bytes long and are copied to the SORTOUT data set (REGION.OUT).