Join Contents of Files

Two files can be merged in JCL using JOINKEYS operand of SORT utility. This is similar to the JOIN clause in SQL DB2. 

The sample JCL is used with both files mentioned in SORTJNF1 and SORTJNF2 and REFORMAT operand. The sort card is substituted as per the requirement.

//EXEC PGM=SORT //SYSPRINT DD SYSOUT=* //SYSOUT   DD SYSOUT=* //SORTJNF1 DD DSN=<input file 1>, //        DISP=SHR //SORTJNF2 DD DSN=<input file 2>, //        DISP=SHR //SORTOUT  DD DSN=<output file>, //        DISP=(NEW,CATLG,DELETE) //SYSIN DD *   <substitute from below requirement> /*

Sample Input File 1(colours):

Orange Green Yellow White

Sample Input File 2(fruits):

Apple Orange Mango Grape Cherry

Below are the control statements used for each criteria:

1. Matched Records from Both Files (INNER JOIN)

In this case, both the matching records from file 1 and 2 will be copied.

SYSIN card:

//SYSIN DD *   SORT FIELDS=COPY   JOINKEYS FILES=F1, FIELDS(1,10,A)   JOINKEYS FILES=F2, FIELDS(1,10,A)   REFORMAT FIELDS=(F1:1,10,F2:1,10) /*

Output

Orange    Orange

2. Matched Records and Non Matched Records from File 1(LEFT OUTER JOIN)

In this case, both the matching records and non matching records from file 1 will be copied.

SYSIN card:

//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,10,F2:1,10) /*

Output

Orange    Orange Green Yellow White

3. Matched Records and Non Matched Records from File 2(RIGHT OUTER JOIN)

In this case, both the matching records and non matching records from file 2 will be copied.

SYSIN card:

//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,10,F2:1,10) /*

Output

Apple Orange    Orange           Mango           Grape           Cherry

4. Matched Records and Non Matched Records from both files(FULL OUTER JOIN)
   

In this case, both the matching records and non matching records from both files will be copied.

SYSIN card:

//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,10,F2:1,10) /*

Output

Orange    Orange Green Yellow White           Apple           Mango           Grape           Cherry