Unpaired records F2: This example illustrates how you can select only unpaired records from one of two files. In this case, we will select the F2 records that do not have a match in F1 on the specified keys (for example, key1=Molly and key2=2100). We will also omit certain records from each input file and handle denormalized keys.
Input file1 has RECFM=FB and LRECL=15. It contains the following records:
Molly 145 Molly 99999 Molly 2143 Jasmine 1292 Jasmine 5 Jasmine 28 Jasmine 99999
Input file2 has RECFM=VB and LRECL=35. It contains the following records:
Len|Data 30|Molly 145 Thursday 31|Molly 2100 Wednesday 28|Molly 18 Sunday 28|Jasmine 99999 Monday 28|Jasmine 5 Sunday 30|Jasmine 28 Saturday 29|Jasmine 103 Tuesday 31|Jasmine 99999 Wednesday
Unpaired records F2
//JKE4 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //IN1 DD DSN=FILE1.IN,DISP=SHR //IN2 DD DSN=FILE2.IN,DISP=SHR //SORTOUT DD DSN=FILE3.OUT,DISP=OLD //JNF1CNTL DD * Control statements for subtask1 (F1) OMIT COND=(10,5,UFF,EQ,99999) INREC BUILD=(1,8,9:10,5,UFF,TO=ZD,LENGTH=5) /* //JNF2CNTL DD * Control statements for subtask2 (F2) OMIT COND=(14,5,UFF,EQ,99999) INREC BUILD=(1,4,5:14,5,UFF,TO=ZD,LENGTH=5,10:5) /* //SYSIN DD * Control statements for JOINKEYS application JOINKEYS F1=IN1,FIELDS=(1,8,A,9,5,D) JOINKEYS F2=IN2,FIELDS=(10,8,A,5,5,D) JOIN UNPAIRED,F2,ONLY REFORMAT FIELDS=(F2:1,4,10) Control statement for main task OPTION COPY /*
The output file will have RECFM=VB and LRECL=35.F1 records with 99999 in positions 10-14 and F2 records with 99999 in positions 14-18 will be removed before JOIN processing. The output file will contain unpaired F2 records (that is, records from F2 that do not have a match in F1 for the specified keys) as follows:
Len|Data 29|Jasmine 103 Tuesday 31|Molly 2100 Wednesday 28|Molly 18 Sunday
The first JOINKEYS statement defines the DDNAME and keys for the F1 file. F1=IN1 tells DFSORT that the DDNAME for the F1 file is IN1.
The control statements in JNF1CNTL will be used to process the F1 file before it is sorted. The OMIT statement removes records that have 99999 in positions 10-14. We want to use the numeric field as our key. However, the numeric field is unnormalized since it is left-aligned instead of right-aligned, so sorting it as a binary key will not work. To handle this, we use the INREC statement to reformat the numeric field as ZD values in positions 9-13 (positive ZD values with the same sign can be sorted as binary). For example, the first reformatted FB record will look like this:
Molly 00145
Since we don’t need the F1 records for output, we don’t need to keep the original left-aligned numeric value.
FIELDS=(1,8,A,9,5,D) in the JOINKEYS statement (referring to the reformatted INREC positions) tells DFSORT that the first key is in positions 1-8 ascending and the second key is in positions 9-13 descending.
The second JOINKEYS statement defines the DDNAME and keys for the F2 file. F2=IN2 tells DFSORT that the DDNAME for the F2 file is IN2.
The control statements in JNF2CNTL will be used to process the F2 file before it is sorted. The OMIT statement removes records that have 99999 in positions 14-18. Again, we need a ZD version of the left-aligned numeric value to use for the binary key. But in this case, since we want the original F2 records for output, we need to keep the original numeric value as well. Using the INREC statement, we add the ZD value at positions 5-9 between the RDW and the first data field. That shifts the original data to start at position 10. For example, the first reformatted VB record will look like this:
Len|Data 35|00145Molly 145 Thursday
In this case, since the input is a VB file, we specify the RDW (1,4), then the converted field, and then the rest of the record (5 without a length) in the INREC statement.
FIELDS=(10,8,A,5,5,D) in the JOINKEYS statement (referring to the reformatted INREC positions) tells DFSORT that the first key is in positions 10-17 ascending and the second key is in positions 5-9 descending.
Note that since IN2 is a VB file, all of its starting positions must take the RDW in positions 1-4 into account.
The JOIN statement tells DFSORT that the joined records should be the F2 records that do not have a match in F1 on the specified keys.
The REFORMAT statement defines the fields to be extracted for the joined records in the order in which they are to appear. We need the RDW (1,4) and the original data which starts in position 10 of the reformatted F2 records. So we use FIELDS=(F2:1,4,10). Since the last field (10)is a position without a length, it tells DFSORT to create VB records. The joined records are created as follows from the reformatted F2 records:
Joined Record Positions Extracted from ----------------------- --------------------------------- 1-4 RDW (not extracted) 5 to end Reformatted F2 position 10 to end
Conceptually, JOINKEYS application processing proceeds as follows:
- Subtask1 performs OMIT and INREC processing for the IN1 (F1 file) records as directed by the control statements in JNF1CNTL and sorts the resulting records as directed by its JOINKEYS statement. As a result, it passes the following records to the main task:
Jasmine 01292 Jasmine 00028 Jasmine 00005 Molly 02143 Molly 00145
- Subtask2 performs OMIT and INREC processing for the IN2 (F2 file) records as directed by the control statements in JNF2CNTL and sorts the resulting records as directed by its JOINKEYS statement. As a result, it passes the following records to the main task:
Len|Data 34|00103Jasmine 103 Tuesday 35|00028Jasmine 28 Saturday 33|00005Jasmine 5 Sunday 36|02100Molly 2100 Wednesday 35|00145Molly 145 Thursday 33|00018Molly 18 Sunday
- The main task joins the records passed from subtask1 and subtask2 as directed by the specified JOINKEYS, JOIN and REFORMAT statements, resulting in the following joined records (unmatched F2 records):
Len|Data 29|Jasmine 103 Tuesday 31|Molly 2100 Wednesday 28|Molly 18 Sunday
- Finally, the main task copies the joined records and writes them to SORTOUT. Thus, SORTOUT contains these records:
Len|Data 29|Jasmine 103 Tuesday 31|Molly 2100 Wednesday 28|Molly 18 Sunday
Example 1 – Paired records without duplicates (F1/F2) Example 2 – Paired records with duplicates (F1/F2) Example 3 – Paired records (F1) Example 4 – Unpaired records (F2) Example 5 – Indicator Method : Paired and unpaired records (F1/F2) Example 6 – Fill Method : Paired and unpaired records (F1/F2)
Read JCL blogs: Click Here SYNCSORT Manual: Click Here