The best Sort JOIN Key example to create 3rd data set

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

  1. Two JOINKEYS statements are required: one for the F1 data set and another for the F2 data set.
  2. 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.
  3. 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.
  4. 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.

Records with the same key in both data sets are joined and constructed as directed by the REFORMAT statement using F1: for fields from the F1 record and F2: for fields from the F2 record.

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).

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.