DFSORT Joining records Example
Scenario - Join the below two files based on the student number.
Input File1 - MATEPK.SORT.INPUT01 - FB file of 80 length
----+----1----+----2---+---3---+---4----+---5---+----6---+---7---+---8 00001 student1 dept1 00003 student3 dept2 00004 student4 dept1 00005 student5 dept2 00002 student2 dept3 00006 student6 dept3 00008 student8 dept1 00007 student7 dept3 00009 student9 dept2 00010 student10 dept2
Input File2 - MATEPK.SORT.INPUT02 - FB file of 80 length
----+----1---+---2---+----3---+----4----+---5----+----6----+---7---+----8
00001 560
00003 520
00004 540
00005 500
00002 510
00006 550
00008 530
00007 510
00010 505
JCL -
---+----1----+----2---+---3---+----4---+----5---+
//MATEPKD JOB (123),'MTH',NOTIFY=&SYSUID
//*
//STEP01 EXEC PGM=SORT
//IN1 DD DSN=MATEPK.SORT.INPUT01,DISP=SHR
//IN2 DD DSN=MATEPK.SORT.INPUT02,DISP=SHR
//SORTOUT DD DSN=MATEPK.SORT.OUTPT01,
// SPACE=(CYL,(1,1),RLSE),DCB=*.IN1,
// DISP=(NEW,CATLG,DELETE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=IN1,FIELDS=(1,5,A)
JOINKEYS F2=IN2,FIELDS=(11,5,A)
REFORMAT FIELDS=(F1:1,40,F2:21,3)
/*Output File - MATEPK.SORT.OUTPT02 - FB file of length 80
----+----1----+----2----+---3---+---4---+---5----+---6---+---7----+----8 00001 student1 dept1 560 00002 student2 dept3 510 00003 student3 dept2 520 00004 student4 dept1 540 00005 student5 dept2 500 00006 student6 dept3 550 00007 student7 dept3 510 00008 student8 dept1 530 00009 student9 dept2 520 00010 student10 dept2 505
Explaining Example -
- JOINKEYS F1=IN1,FIELDS=(1,5,A) - Specifies the file1 join criteria.
- JOINKEYS F2=IN2,FIELDS=(11,5,A) - Specifies the file2 join criteria.
- REFORMAT FIELDS=(F1:1,40,F2:21,3) - Reformat the output record with both files data by specifying starting and ending position.