The JOINKEYS File Match utility help in comparing two files. JOINKEYS File Match operation is controlled by JOINKEYS, JOIN, and REFORMAT control statements. The SORTJNF1 and SORTJNF2, are the DD statements used to specify the files which will be used to perform the join operations. During JOIN operation on two files, each record from the first file (SORTJNF1- M records) will be joined with the second file (SORTJNF2 – N records) based on key values & produces M*N records. In other words, INNER JOIN occurs. Joining can be performed in a number of ways like inner join, full outer join, left outer join, right outer join, and unpaired combinations.
There are primarily four basic steps involved in any JOIN operations.
If the JOIN statement is not specified then only paired records from F1 and F2 are kept and processed by the main task as the joined records (INNER JOIN)
If the JOIN statement is specified then below mentioned process can happen
The SORTED parameter is used to tell DFSORT to copy file records rather than sort them again.
The NOSEQCH operand is used to tell DFSORT not to check for the order of the records.
For example
JOINKEYS FILE=F1,FIELDS=(12,4,A),SORTED,NOSEQCH JOINKEYS FILE=F2,FIELDS=(25,4,A),SORTED
Explanation: File F1 is copied using the DDNAME SORTJNF1 and the ascending key in positions 12-15. The SORTJNF1 records will not be checked for the correct key order.
File F2 is copied using the dd name SORTJNF2 and the ascending key in positions 25-28. The SORTJNF2 records will be checked for the correct key order.
If the records are not actually in order by the specified keys and you use NOSEQCK, the output may be incorrect. The NOSEQCK operand is ignored if the SORTED operand is not specified.
TYPE parameter is used to specify the processing length for a VSAM input file.
For example
JOINKEYS F1=VSAM1,FIELDS=(13,3,A),TYPE=V JOINKEYS F2=VSAM2,FIELDS=(26,3,A),TYPE=F
Explanation: VSAM file F1 is sorted as variable-length records using the DDNAME VSAM1 and the ascending key in positions 13-15. (Remember that for TYPE=V VSAM processing, DFSORT adds an RDW in positions 1-4 which you must account for when specifying the starting position.)
VSAM file F2 is sorted as fixed-length records using the DDNAME VSAM2 and the ascending key in positions 26-28. (Remember that for TYPE=F VSAM processing, DFSORT does not add an RDW.)
STOPAFT is used to specify the maximum number of records (n) you want the subtask for the input file to accept for sorting or copying.
For example
JOINKEYS FILE=F1,STOPAFT=5,FIELDS=(32,4,A) JOINKEYS FILE=F2,STOPAFT=10,FIELDS=(32,4,A) OR //SYSIN DD * JOINKEYS FILE=F1,FIELDS=(32,4,A) JOINKEYS FILE=F2,FIELDS=(32,4,A) ... //JNF1CNTL DD * OPTION STOPAFT=5 //JNF2CNTL DD * OPTION STOPAFT=10
Explanation: The first 5 input records from SORTJNF1 and the first 10 input records from SORTJNF2 will be processed.
Join performance can be improved by using INCLUDE/OMIT parameters. These parameters can be specified in the JOIN statement of JONKEYS but it is recommended to specify INCLUDE/OMIT parameter in JNF1CNTL or JNF2CNTL for ease of use.
For example
//SYSIN DD * JOINKEYS FILE=F1,FIELDS=(35,8,A), OMIT=(5,4,CH,EQ,C'ABCD') JOINKEYS FILE=F2,FIELDS=(37,8,A), INCLUDE=(1,20,SS,EQ,C’NO’) //*
Recommended
//SYSIN DD * JOINKEYS FILE=F1,FIELDS=(35,8,A) JOINKEYS FILE=F2,FIELDS=(37,8,A) **** **** //* //JNF1CNTL DD * OMIT COND=(5,4,CH,EQ,C'ABCD') //JNF2CNTL DD * INCLUDE COND=(1,20,SS,EQ,C'NO') //*
Let’s take an example F1 80 bytes and F2 40 bytes file.
If the JOIN statement is not specified in the sort card, paired records from F1 and F2 are written into the output file.
//SYSIN DD * SORT FIELDS=COPY JOINKEYS FILES=F1,FIELDS=(1,10,A) JOINKEYS FILES=F2,FIELDS=(1,10,A) REFORMAT FIELDS=(F1:1,80,F2:1,40) /*
Sort card to retain unpaired records from both F1 and F2 files along with paired records. This type of join is called a FULL OUTER JOIN.
//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,80,F2:1,40) /*
Sort card to retain unpaired records from the F1 file along with paired records. This type of join is called a LEFT OUTER JOIN.
//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,80,F2:1,40) /*
Sort card to retain unpaired records from F1 file, cannot use reformat in F2.
//SYSIN DD * SORT FIELDS=COPY JOINKEYS FILES=F1,FIELDS=(1,10,A) JOINKEYS FILES=F2,FIELDS=(1,10,A) JOIN UNPAIRED,F1,ONLY REFORMAT FIELDS=(F1:1,80) /*
Sort card to retain unpaired records from the F2 file along with paired records. This type of join is called RIGHT OUTER JOIN.
//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,80,F2:1,40) /*
Sort card to retain unpaired records from F2 file, cannot use reformat in F1.
//SYSIN DD * SORT FIELDS=COPY JOINKEYS FILES=F1,FIELDS=(1,10,A) JOINKEYS FILES=F2,FIELDS=(1,10,A) JOIN UNPAIRED,F2,ONLY REFORMAT FIELDS=(F2:1,40) /*
Sort card to retain only unpaired records from F1 and F2 files.
//SYSIN DD * SORT FIELDS=COPY JOINKEYS FILES=F1,FIELDS=(1,10,A) JOINKEYS FILES=F2,FIELDS=(1,10,A) JOIN UNPAIRED,F1,F2,ONLY or JOIN UNPAIRED,ONLY REFORMAT FIELDS=(F1:1,80,F2:1,40) /*
Example: JOINKEYS File Match and Comparison Examples
//STEP010 EXEC PGM=SORT //SORTJNF1 DD DSN=FIRST.FILE,DISP=SHR //SORTJNF2 DD DSN=SECOND.FILE,DISP=SHR //BOTHF1F2 DD DSN=MYDATA.URMI.SAMPLE.MATCH,DISP=OLD //ONLYF1 DD DSN=MYDATA.URMI.SAMPLE.NOMATCH1,DISP=OLD //ONLYF2 DD DSN=MYDATA.URMI.SAMPLE.NOMATCH2,DISP=OLD //SYSOUT DD SYSOUT=* //SYSIN DD * JOINKEYS FILE=F1,FIELDS=(1,10,A) JOINKEYS FILE=F2,FIELDS=(7,10,A) JOIN UNPAIRED,F1,F2 REFORMAT FIELDS=(?,F1:1,14,F2:1,20) OPTION COPY OUTFIL FNAMES=BOTHF1F2,INCLUDE=(1,1,CH,EQ,C’B'), BUILD=(1:2,14,/,16,20) OUTFIL FNAMES=ONLYF1,INCLUDE=(1,1,CH,EQ,C'1'),BUILD=(1:2,14) OUTFIL FNAMES=ONLYF2,INCLUDE=(1,1,CH,EQ,C'2'),BUILD=(1:16,20) /*
Explanation:
You can achieve the above result by using the FILL parameter.
//SYSIN DD * JOINKEYS FILE=F1,FIELDS=(1,10,A) JOINKEYS FILE=F2,FIELDS=(7,10,A) JOIN UNPAIRED,F1,F2 REFORMAT FIELDS=(F1:1,14,F2:1,20),FILL=C'$' OPTION COPY OUTFIL FNAMES=BOTHF1F2,INCLUDE=(15,1,CH,NE,C’$’, AND,1,1,CH,NE,C'$'), BUILD=(1:1,14,/,15,20) OUTFIL FNAMES=ONLYF1,INCLUDE=(15,1,CH,EQ,C’FILL=C'$'), BUILD=(1:1,14) OUTFIL FNAMES=ONLYF2,INCLUDE=(1,1,CH,EQ,C’$'), BUILD=(1:15,20) /*
Explanation:
A well-maintained product backlog is crucial for successful product development. It serves as a single…
Incremental value to the customer refers to the gradual delivery of small, functional parts of…
A Product Market refers to the group of potential customers who might be interested in…
The Professional Agile Leadership - Evidence-Based Management (PAL-EBM) certification offered by Scrum.org is designed for…
The Professional Agile Leadership (PAL I) certification, offered by Scrum.org, is designed to equip leaders…
Choosing the right Scrum Master Certification depends on your current experience and career goals. If…