Joining records


  • 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 -

  1. JOINKEYS F1=IN1,FIELDS=(1,5,A)- Specifies the file1 join criteria.
  2. JOINKEYS F2=IN2,FIELDS=(11,5,A) - Specifies the file2 join criteria.
  3. REFORMAT FIELDS=(F1:1,40,F2:21,3)- Format the output record with both files data by specifying starting and ending position.