Unpaired records F2 using SORT Utility

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