//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).
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
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