Paired records with duplicates using SORT

Paired records with duplicates: This example illustrates how you can join paired records from two files, both of which have duplicate records. The result will be a cartesian join. The paired records are the records in F1 and F2 with matching keys (for example, key=Cats).

Paired records with duplicates Example

//SEP002  EXEC  PGM=SORT
//SYSOUT  DD  SYSOUT=*
//VBIN DD DSN=MY.VBFILE,DISP=SHR
//FBIN DD DSN=MY.FBFILE,DISP=SHR
//SORTOUT DD DSN=MY.FB.OUTPUT,DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(5,5)),UNIT=SYSDA
//SYSIN    DD    *
Control statements for JOINKEYS application
JOINKEYS F1=VBIN,FIELDS=(18,16,A),SORTED
JOINKEYS F2=FBIN,FIELDS=(1,16,A)
REFORMAT FIELDS=(F2:22,12,F1:5,12,F2:1,16)
Control statements for main task (joined records)
OPTION EQUALS
SORT FIELDS=(13,12,CH,A)
/*

Input file1 has RECFM=VB and LRECL=50. It contains the following records:

Len|Data
 40|Eliot        Cats            Musical
 40|Lloyd-Webber Cats            Musical
 48|Hart         Pal Joey        Musical, Comedy
 48|Rodgers      Pal Joey        Musical, Comedy
 47|Hammerstein  South Pacific   Musical, Drama
 47|Rodgers      South Pacific   Musical, Drama

The output file will have RECFM=FB and LRECL=40. It will contain the paired cartesian product of the two files sorted as follows:

Start: 1982 Eliot       Cats
End:   2000 Eliot       Cats
Start: 1949 Hammerstein South Pacific
End:   1954 Hammerstein South Pacific
Start: 1940 Hart        Pal Joey
End:   1941 Hart        Pal Joey
Start: 1982 Lloyd-WebberCats
End:   2000 Lloyd-WebberCats
Start: 1940 Rodgers     Pal Joey
End:   1941 Rodgers     Pal Joey
Start: 1949 Rodgers     South Pacific
End:   1954 Rodgers     South Pacific

The first JOINKEYS statement defines the DDNAME and key for the F1 file. F1=VBIN tells DFSORT that the ddname for the F1 file is VBIN. FIELDS=(18,16,A) tells DFSORT that the key is in positions 18-33 ascending. Note that since VBIN is a VB file, the starting position of its key must take the RDW in positions 1-4 into account. Since SORTED is specified, indicating that the records are already in order by the specified binary key, DFSORT will copy the VBIN records.

The second JOINKEYS statement defines the DDNAME and binary key for the F2 file. F2=FBIN tells DFSORT that the DDNAME for the F2 file is FBIN. FIELDS=(1,16,A) tells DFSORT that the binary key is in positions 1-16 ascending. Since SORTED is not specified, DFSORT will sort the FBIN records by the specified binary key.

The REFORMAT statement defines the fields to be extracted for the joined records in the order in which they are to appear.FIELDS=(F2:22,12,F1:5,12,F2:1,16) tells DFSORT to create the joined records as follows:

Joined Record Positions     Extracted from
-----------------------     ------------------
1-12                        F2 positions 22-33
13-24                       F1 positions 5-16
25-40                       F2 positions 1-16

The first JOINKEYS statement defines the DDNAME and key for the F1 file. F1=VBIN tells DFSORT that the ddname for the F1 file is VBIN. FIELDS=(18,16,A) tells DFSORT that the key is in positions 18-33 ascending. Note that since VBIN is a VB file, the starting position of its key must take the RDW in positions 1-4 into account. Since SORTED is specified, indicating that the records are already in order by the specified binary key, DFSORT will copy the VBIN records.

The second JOINKEYS statement defines the DDNAME and binary key for the F2 file. F2=FBIN tells DFSORT that the DDNAME for the F2 file is FBIN. FIELDS=(1,16,A) tells DFSORT that the binary key is in positions 1-16 ascending. Since SORTED is not specified, DFSORT will sort the FBIN records by the specified binary key.

The REFORMAT statement defines the fields to be extracted for the joined records in the order in which they are to appear.FIELDS=(F2:22,12,F1:5,12,F2:1,16) tells DFSORT to create the joined records as follows:

Joined Record Positions     Extracted from
-----------------------     ------------------
1-12                        F2 positions 22-33
13-24                       F1 positions 5-16
25-40                       F2 positions 1-16

Note that since VBIN (F1) is a VB file, the starting position of its REFORMAT field must take the RDW in positions 1-4 into account.

Since there is no JOIN statement, only paired records are joined by default. Since there are duplicates in each input file, a cartesian join is performed.

The SORT FIELDS=(13,12,CH,A) statement tells DFSORT to sort the joined records by a different key than the one used for the join of F1 and F2 records. Note that the FIELDS operand of the SORT statement must reference the positions of fields in the joined records.

Conceptually, JOINKEYS application processing proceeds as follows:

• Subtask1 – copies the VBIN (F1 file) records as directed by its JOINKEYS statement. As a result, it passes the following records to the main task:

Len|Data
 40|Eliot        Cats            Musical
 40|Lloyd-Webber Cats            Musical
 48|Hart         Pal Joey        Musical, Comedy
 48|Rodgers      Pal Joey        Musical, Comedy
 47|Hammerstein  South Pacific   Musical, Drama
 47|Rodgers      South Pacific   Musical, Drama

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

Cats                 Start: 1982  50
Cats                 End:   2000
Pal Joey             Start: 1940  22
Pal Joey             End:   1941 
South Pacific        Start: 1949  13
South Pacific        End:   1954

• 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:

Start: 1982 Eliot       Cats
End:   2000 Eliot       Cats
Start: 1982 Lloyd-WebberCats 
End:   2000 Lloyd-WebberCats
Start: 1940 Hart        Pal Joey
End:   1941 Hart        Pal Joey
Start: 1940 Rodgers     Pal Joey
End:   1941 Rodgers     Pal Joey
Start: 1949 Hammerstein South Pacific
End:   1954 Hammerstein South Pacific
Start: 1949 Rodgers     South Pacific
End:   1954 Rodgers     South Pacific

• Finally, the main task sorts the joined records according to the SORT statement and writes the resulting records to SORTOUT. Thus, SORTOUT contains these records:

Start: 1982 Eliot       Cats
End:   2000 Eliot       Cats
Start: 1949 Hammerstein South Pacific
End:   1954 Hammerstein South Pacific
Start: 1940 Hart        Pal Joey
End:   1941 Hart        Pal Joey
Start: 1982 Lloyd-WebberCats
End:   2000 Lloyd-WebberCats
Start: 1940 Rodgers     Pal Joey
End:   1941 Rodgers     Pal Joey
Start: 1949 Rodgers     South Pacific
End:   1954 Rodgers     South Pacific

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