Indicator method: Paired and unpaired records

Indicator method: Paired and unpaired records – This example illustrates how you can create three output files; paired F1/F2 records, unpaired F1 records, and unpaired F2 records. In this case:

• F1 records which do not have a match in F2 on the specified keys (for example, key=David) will be written to the F1ONLY output file.
• F2 records which do not have a match in F1 on the specified keys (for example, key=Karen) will be written to the F2ONLY output file.
• F1 and F2 records that have a match in F1 and F2 on the specified keys (for example, key=Carrie) will be written to the BOTH output file.

Indicator method: Paired and unpaired records Example

Input file1 has RECFM=FB and LRECL=14. It contains the following records:

Carrie    F101
David     F102
Frank     F103
Holly     F104
Vicky     F105

Input file2 has RECFM=FB and LRECL=20. It contains the following records:

No    Carrie    F201
Yes   Holly     F202
Yes   Karen     F203
No    Sri Hari  F204
Yes   Vicky     F205

//JKE5  EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTJNF1 DD DSN=FIRST.FILE,DISP=SHR
//SORTJNF2 DD DSN=SECOND.FILE,DISP=SHR
//F1ONLY DD SYSOUT=*
//F2ONLY DD SYSOUT=*
//BOTH DD SYSOUT=*
//SYSIN    DD    *
  Control statements for JOINKEYS application
  JOINKEYS FILE=F1,FIELDS=(1,10,A),SORTED,NOSEQCK
  JOINKEYS FILE=F2,FIELDS=(7,10,A),SORTED,NOSEQCK
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,14,F2:1,20,?)
  Control statements for main task (joined records)
  OPTION COPY
  OUTFIL FNAMES=F1ONLY,INCLUDE=(35,1,CH,EQ,C'1'),
  BUILD=(1,14)
  OUTFIL FNAMES=F2ONLY,INCLUDE=(35,1,CH,EQ,C'2'),
  BUILD=(15,20)
  OUTFIL FNAMES=BOTH,INCLUDE=(35,1,CH,EQ,C'B'),
  BUILD=(1,14,/,15,20)
/*

The F1ONLY file will have RECFM=FB and LRECL=14.It will contain the unpaired F1 records as follows:

David     F102
Frank     F103

The F2ONLY file will have RECFM=FB and LRECL=20.It will contain the unpaired F2 records as follows:

Yes   Karen     F203
No    Sri Hari  F204

The BOTH file will have RECFM=FB and LRECL=20. It will contain the paired F1 and F2 records as follows:

Carrie    F101
No    Carrie    F201
Holly     F104
Yes   Holly     F202
Vicky     F105
Yes   Vicky     F205

the ddname for the F1 file is SORTJNF1. FIELDS=(1,10,A) tells DFSORT that the key is in positions 1-10 ascending. Since SORTED is specified, indicating that the records are already in order by the specified binary key, DFSORT will copy the SORTJNF1 records. Since NOSEQCK is specified, DFSORT will not check that the records are in order by the key. (Only use NOSEQCK if you know for sure that the records are in order by the key.)

The second JOINKEYS statement defines the DDNAME and key for the F2 file. FILE=F2 tells DFSORT that the DDNAME for the F2 file is SORTJNF2. FIELDS=(7,10,A) tells DFSORT that the key is in positions 7-16 ascending. Since SORTED is specified, indicating that the records are already in order by the specified binary key, DFSORT will copy the SORTJNF2 records. Since NOSEQCK is specified, DFSORT will not check that the records are in order by the key. (Only use NOSEQCK if you know for sure that the records are in order by the key.)

The JOIN statement tells DFSORT that the joined records should include the unpaired F1 and F2 records as well as the paired F1/F2 records.

The REFORMAT statement defines the fields to be extracted for the joined records in the order in which they are to appear and includes an indicator in the last position that will be set to ‘1’ if the key is found only in the F1 file, ‘2’ if the key is found only in the F2 file, or ‘B’ if the key is found in the F1 file and in the F2 file.FIELDS=(F1:1,14,F2:1,20,?) tells DFSORT to create the joined records as follows:

Joined Record Positions   Extracted from
-----------------------   -----------------
1-14                      F1 positions 1-14
15-34                     F2 positions 1-20
35                        Indicator of where key was found

The OPTION COPY statement tells DFSORT to copy the joined records. The OUTFIL statements use the indicator in position 35 to determine where to find the F1 or F2 fields in the joined records and where to write the fields (F1ONLY, F2ONLY, or BOTH).

Conceptually, JOINKEYS application processing proceeds as follows:

• Subtask1 copies the SORTJNF1 (F1) records as directed by the JOINKEYS statement. As a result, it copies the unchanged SORTJNF1 records to the main task.
• Subtask2 copies the SORTJNF2 (F2) records as directed by the JOINKEYS statement. As a result, it copies the unchanged SORTJNF2 records to the main task.
• 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 (paired and unpaired):

Carrie    F101      No   Carrie    F201B
David     F102                         1
Frank     F103                         1
Holly     F104     Yes   Holly     F202B
                   Yes   Karen     F2032
                    No   Sri Hari  F2042
Vicky     F105     Yes   Vicky     F205B

For F1 records without a match in F2 (for example, the F102 record), the indicator in position 35 has a’1′. For F2 records without a match in F1 (for example, the F203 record), the indicator in position 35 has a ‘2’. For F1 records with a match in F2 (for example, the F101 and F201 records), the indicator in position 35 has a ‘B’.

• The first OUTFIL statement finds records with a ‘1’ in position 35. These are the F1 records without a match in F2. The F1 field is in positions 1-14 of the joined record, so those positions are written to the F1ONLY file. Thus, F1ONLY contains these records:

David     F102
Frank     F103

• The second OUTFIL statement finds records with a ‘2’ in position 35. These are the F2 records without a match in F1. The F2 field is in positions 15-34 of the joined record, so those positions are written to the F2ONLY file. Thus, F2ONLY contains these records:

Yes   Karen     F203
No    Sri Hari  F204

• The third OUTFIL statement finds records with ‘B’ in position 35. These are the F1 and F2 records with a match. The F1 field is in positions 1-14 of the joined record and the F2 field is in positions 15-34 of the joined record, so each joined record is split into those two records and written to the BOTH file. The shorter F1 record is padded with blanks on the right to the length of the F2 record. Thus, BOTH contains these records:

Carrie    F101
No    Carrie    F201
Holly     F104
Yes   Holly     F202
Vicky     F105
Yes   Vicky     F205

Note: If you only want one record per key in BOTH, you can have the BUILD for FNAMES=BOTH specify the positions for just that record. For example, BUILD=(1,14) for the F1 records or BUILD=(15,20) for the F2 records.

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