DFSORT Joining records

DFSORT OUTFIL: Converting VB to FB

Advertisements


A JOINKEYS application helps to perform various "join" applications on twodata sets by one or more keys.

Joining can be performed in number of combinations like inner join, full outer join, left outerjoin, right outer join and unpaired combinations.

The two data sets can be ofdifferent types and lengths, and have keys indifferent locations.

The records from the input data sets can be processed in a variety of ways beforeand after they are joined.

DFSORT control statements allowed to add with JOINKEYS including SORT or COPY, INCLUDE or OMIT, INREC, OUTRECand 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=(..) 

Name Description
DDNAMEnDDNAME 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 recordsin 
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 FIELDSSpecifies to reformat the records from both files

JOIN mostly used on 2 input files.


Example: -

Join the below two files based on the student number.


Input File: 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 ********************************

File: 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 ********************************

Job:

000100 //Jobcard 
000400 //*                                                                     
000500 //**********************************************************************
000600 //*                                                                     
001100 //STEP01   EXEC PGM=SORT                                                
001300 //IN1      DD DSN=MTHUSER.SORT.INPUT01,DISP=SHR                    
001400 //IN2      DD DSN=MTHUSER.SORT.INPUT02,DISP=SHR                    
001801 //SORTOUT  DD DSN=MTHUSER.SORT.OUTPT01,                            
001802 //             SPACE=(CYL,(1,1),RLSE),DCB=*.IN1,                        
001803 //             DISP=(NEW,CATLG,DELETE)                                  
001810 //SYSOUT   DD SYSOUT=*                                                  
002410 //SYSIN    DD *                                                         
002420      JOINKEYS F1=IN1,FIELDS=(1,5,A)                                     
002430      JOINKEYS F2=IN2,FIELDS=(11,5,A)                                    
002440      REFORMAT FIELDS=(F1:1,40,F2:21,3)                                  
002450      OPTION COPY                                                        
003500 /*                                                                      
****** **************************** 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 Solution:

  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.

DFSORT OUTFIL: Converting VB to FB

Advertisements