Example 2 - Paired F1/F2 records with duplicates (cartesian)
//JKE2 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)
/*
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).
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
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.
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.
- 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