Join statement in JCL

Prerequisite – Job Control Language (JCL)
In RDBMS, SQL JOIN clause is used to join tables and perform multiple operations on it. To perform operations on records in different flat files, JOINKEYS are used. With the help of JOINKEYS in SORT JCL, various join operation on matched and non-matched records can be executed based on matching fields or Keys. Joining can be performed in number of ways like inner join, full outer join, left outer join, right outer join and unpaired combinations.

The join operation is controlled by three important control statements JOINKEYS, JOIN, and REFORMAT. The SORTJNF1 and SORTJNF2, are the DD statements used to specify the files which will be use to perform join operation.

Control Statements :

1. JOINKEYS FILES=F1, FIELDS=(1, 10, A) – Specifies the file1 join criteria.
2. JOINKEYS FILES=F2, FIELDS=(1, 10, A) – Specifies the file2 join criteria.
3. REFORMAT FIELDS=(F1:1, 71, F2:1, 9) – REFORMAT fields tells the sort utility, what fields to be written into output file from the input files by specifying starting and ending position.

JCL SORT to join two files and writes records from both files :

1. Matched Records (Inner Join) –
   If join statement not specified in sort card, paired records from F1 and F2 are written into 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, 71, F2:1, 9)
   /*

2. Matched Records and Non Matched Records from File1 –
   Sort card to retain unpaired records from 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, 71, F2:1, 9)
   /*

3. 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, 71)
   /*

4. Matched Records and Non Matched from File2 –
   Sort card to retain unpaired records from 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, 71, F2:1, 9)
   /*

5. 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, 9)
   /*

6. 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 as 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, 71, F2:1, 9)
   /*

7. 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, 71, F2:1, 9)
   /*