Paired records F1 using SORT

Paired records F1 – Below example illustrates how you can select only paired records from one of two files. In this case, we will select the F1 records that have a match in F2 on the specified keys (for example, key1=Development and key2=Master). The F1 records are in comma-delimited form so we will parse them to get the binary keys we need.

//JKE3  EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//MASTER DD DSN=MASTER.FILE,DISP=SHR
//PULL DD DSN=PULL.FILE,DISP=SHR
//SORTOUT DD SYSOUT=*
//JNF1CNTL DD *
Control statement for subtask1 (F1)
INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=15),
           %=(ENDBEFR=C','),
           %02=(ENDBEFR=C',',FIXLEN=10)),
OVERLAY=(36:%01,52:%02)
/*
//DFSPARM   DD    *
Control statements for JOINKEYS application
JOINKEYS F1=MASTER,FIELDS=(36,15,A,52,10,A)
JOINKEYS F2=PULL,FIELDS=(12,15,A,1,10,A)
REFORMAT FIELDS=(F1:1,35)
Control statement for main task
OPTION COPY
/*

Input file1 (F1) has RECFM=FB and LRECL=35. It contains the following records:

Marketing,William,Master 
Development,John,Bachelor 
Manufacturing,Louis,Master 
Development,Carol,Master 
Research,Angela,Master 
Research,Anne,Doctorate
Development,Sara,Doctorate 
Marketing,Joseph,Master
Manufacturing,Donna,Bachelor 
Development,Susan,Master 
Manufacturing,Nick,Master 
Research,Howard,Doctorate

Input file2 (F2) has RECFM=FB and LRECL=30. It contains the following records:

Master     Development
Master     Manufacturing
Bachelor   Development
Doctorate  Research

The output file will have RECFM=FB and LRECL=35. It will contain the paired F1 records, that is, the records from F1 that have a match in F2 for the specified keys (the first and third fields):

Development,John,Bachelor
Development,Carol,Master
Development,Susan,Master
Manufacturing,Louis,Master
Manufacturing,Nick,Master
Research,Anne,Doctorate
Research,Howard,Doctorate

The first JOINKEYS statement defines the DDNAME and keys for the F1 file. F1=MASTER tells DFSORT that the DDNAME for the F1 file is MASTER.

The control statements in JNF1CNTL will be used to process the F1 file before it is sorted. The input records are in comma-delimited form, so to use the first and third fields as binary keys, while preserving the original data, we use an INREC statement to parse out the fields we want and add them to the end of the record. The parsed first key will be in positions 36-50 and the parsed second key will be in positions 52-61. For example, the first reformatted record will look like this:

Marketing,William,Master           Marketing       Master

FIELDS=(36,15,A,52,10,A)in the JOINKEYS statement (referring to the reformatted INREC positions) tells DFSORT that the first key is in positions 36-50 ascending and the second key is in positions 52-61 ascending.

The second JOINKEYS statement defines the DDNAME and keys for the F2 file. F2=PULL tells DFSORT that the DDNAME for the F2 file is PULL.

FIELDS=(12,15,A,1,10,A)in the JOINKEYS statement tells DFSORT that the first key is in positions 12-26 ascending and the second key is in positions 1-10 ascending.

The REFORMAT statement defines the fields to be extracted for the joined records in the order in which they are to appear. FIELDS=(F1:1,35) tells DFSORT to create the joined records from the original F1 input records (positions 1-35 of the F1 records). Since we only needed the added parsed fields for sorting, we don’t need to include the min the joined records. Of course, if we wanted the main task to “see” the parsed fields in the joined records, we could include the parsed fields in the REFORMAT FIELDS operand.

Since there is no JOIN statement, only paired records are joined by default.

The OPTION COPY statement tells DFSORT to copy the joined records.

Conceptually, JOINKEYS application processing proceeds as follows:

• Subtask1 performs INREC processing for the MASTER (F1 file) records as directed by the control statement 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:

Development,John,Bachelor          Development     Bachelor
Development,Sara,Doctorate         Development     Doctorate
Development,Carol,Master           Development     Master
Development,Susan,Master           Development     Master
Manufacturing,Donna,Bachelor       Manufacturing   Bachelor
Manufacturing,Louis,Master         Manufacturing   Master
Manufacturing,Nick,Master          Manufacturing   Master
Marketing,William,Master           Marketing       Master
Marketing,Joseph,Master            Marketing       Master
Research,Howard,Doctorate          Research        Doctorate
Research,Anne,Doctorate            Research        Doctorate
Research,Angela,Master             Research        Master

• Subtask2 sorts the PULL (F2 file) records as directed by its JOINKEYS statement. As a result, it passes the following records to the main task:

Bachelor   Development
Master     Development
Master     Manufacturing
Doctorate  Research

• The main task joins the records passed from subtask1 and subtask2 as directed by the specified JOINKEYS and REFORMAT statements, resulting in the following joined records:

Development,John,Bachelor
Development,Carol,Master
Development,Susan,Master
Manufacturing,Louis,Master
Manufacturing,Nick,Master
Research,Anne,Doctorate
Research,Howard,Doctorate

• Finally, the main task copies the joined records to SORTOUT. Thus, SORTOUT contains these records:

Development,John,Bachelor
Development,Carol,Master
Development,Susan,Master
Manufacturing,Louis,Master
Manufacturing,Nick,Master
Research,Anne,Doctorate
Research,Howard,Doctorate

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