Joining records
Joining records
Summary
- A JOINKEYS application helps to perform various "join" applications on two data sets by one or more keys.
- Joining can be performed in number of combinations like inner join, full outer join, left outer join, right outer join and unpaired combinations.
- The two data sets can be of different types and lengths, and have keys in different locations.
- The records from the input data sets can be processed in a variety of ways before and after they are joined.
- DFSORT control statements allowed to add with JOINKEYS including SORT or COPY, INCLUDE or OMIT, INREC, OUTREC and OUTFIL.
- The first JOINKEYS statement identifies SORTJNF1 as the ddname for the F1 data set and indicates ascending keys. The second JOINKEYS statement identifies SORTJNF2 as the ddname for the F1 data set and indicates ascending keys.
Syntax -
JOINKEYS FILE=F1, FIELDS=(….),[SORTED],
JOINKEYS FILE=F2, FIELDS=(….),[SORTED],
JOIN UNPAIRED,F1,[F2,ONLY]
REFORMAT FIELDS=(….)
OPTION COPY
OUTFIL FNAME=BOTH,INCLUDE/OMIT COND=(…), BUILD=(..)
OUTFIL FNAME=F1ONLY,INCLUDE/OMIT COND=(…), BUILD=(..)
OUTFIL FNAME=F2ONLY,INCLUDE/OMIT COND=(…), BUILD=(..)
DDNAMEn | DDNAME is eight character’s name that representing the actual dataset in JCL. |
F1 | Specifies file1 |
F2 | Specifies file2 |
JOIN UNPAIRED | Specifies UNPAIRED combination JOIN UNPAIRED,F1,ONLY is used to restrict the output (SORTOUT) to the records in F1 that do not have matching keys in F2. UNPAIRED,F1,F2 to keep the unpaired joined records as well as the paired join records. UNPAIRED,F2,ONLY is used to restrict the output (SORTOUT) to the recordsin F2 that do not have matching keys in F1. |
SORTED | Specifies the records are already sorted and do a Copy only. |
REFORMAT FIELDS | Specifies to reformat the records from both files |
Example -
Scenario - Join the below two files based on the student number.
Input File1 - MTHUSER.SORT.INPUT01 - FB file of 80 length
----+----1----+----2---+---3---+---4----+---5---+----6---+---7---+---8
********************************* Top of Data *****************************
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
******************************** Bottom of Data ****************************
Input File2 - MTHUSER.SORT.INPUT02 - FB file of 80 length
----+----1---+---2---+----3---+----4----+---5----+----6----+---7---+----8
********************************* Top of Data ******************************
00001 560
00003 520
00004 540
00005 500
00002 510
00006 550
00008 530
00007 510
00009 520
00010 505
******************************** Bottom of Data ****************************
JCL -
---+----1----+----2---+---3---+----4---+----5---+---6----+---7--
***************************** Top of Data ***************************
//Job-card
//********************************************************************
//STEP01 EXEC PGM=SORT
//IN1 DD DSN=MTHUSER.SORT.INPUT01,DISP=SHR
//IN2 DD DSN=MTHUSER.SORT.INPUT02,DISP=SHR
//SORTOUT DD DSN=MTHUSER.SORT.OUTPT01,
// SPACE=(CYL,(1,1),RLSE),DCB=*.IN1,
// DISP=(NEW,CATLG,DELETE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,5,A)
JOINKEYS F2=IN2,FIELDS=(11,5,A)
REFORMAT FIELDS=(F1:1,40,F2:21,3)
OPTION COPY
/*
**************************** Bottom of Data *************************
Output File - MTHUSER.SORT.OUTPT02 - FB file of length 80
----+----1----+----2----+---3---+---4---+---5----+---6---+---7----+----8
********************************* Top of Data *******************************
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
******************************** Bottom of Data *****************************
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)- Format the output record with both files data by specifying starting and ending position.