Paired records without duplicates – ASA (formerly ANSI)control characters appear in Position 5 for variable-length records (just after the Record Descriptor Word (RDW)). So coding REMOVECC on the OUTFIL statement suppresses the control characters.
This example illustrates how you can join paired records from two files using multiple keys. In this case, neither file has duplicates. The paired records are the records in F1 and F2 with matching keys (for example, key1=Roses and key2=Red).
Paired records without duplicates Example
//STEP001 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTJNF1 DD * Roses 03 Red Daisies 06 Orange Roses 04 Pink Daisies 02 Yellow Roses 06 Yellow Daisies 12 Lilac Roses 09 Blue /* //SORTJNF2 DD * Red Lilies InStock Red Roses InStock Orange Daisies SoldOut Pink Roses SoldOut Yellow Daisies InStock Yellow Roses Ordered Lilac Daisies SoldOut White Daisies InStock /* //SORTOUT DD SYSOUT=* //SYSIN DD * Control statements for JOINKEYS application JOINKEYS FILE=F1,FIELDS=(1,15,A,20,8,A) JOINKEYS FILE=F2,FIELDS=(10,15,A,1,8,A) REFORMAT FIELDS=(F1:20,8,1,15,F2:26,10,F1:16,2) Control statements for main task (joined records) OPTION COPY OUTFIL REMOVECC, HEADER2=(1:'Color',11:'Flower',26:'Status',36:'Per Pot',/, 1:7'-',11:14'-',26:7'-',36:7'-'), BUILD=(1:1,8,11:9,15,26:24,10, 36:34,2,ZD,M10,LENGTH=7) /*
Input file1 (F1) has RECFM=FB and LRECL=80. It contains the records shown for SORTJNF1 in the JCL shown previously in this section. Input file2 (F2) has RECFM=FB and LRECL=80. It contains the records shown for SORTJNF2 in the JCL shown previously in this section.
The output file will have RECFM=FB and LRECL=42. It will contain the paired records from the two files reformatted as follows:
Color Flower Status Per Pot ------- -------------- ------- ------- Lilac Daisies SoldOut 12 Orange Daisies SoldOut 6 Yellow Daisies InStock 2 Pink Roses SoldOut 4 Red Roses InStock 3 Yellow Roses Ordered 6
The first JOINKEYS statement defines the DDNAME and keys for the F1 file. FILE=F1 tells DFSORT that the DDNAME for the F1 file is SORTJNF1. FIELDS=(1,15,A,20,8,A) tells DFSORT that the first binary key is in positions 1-15 ascending and the second binary key is in positions 20-27 ascending. Since SORTED is not specified, DFSORT will sort the SORTJNF1 records by the specified binary keys.
The second JOINKEYS statement defines the DDNAME and keys for the F2 file. FILE=F2 tells DFSORT that the DDNAME for the F2 file is SORTJNF2. FIELDS=(10,15,A,1,8,A) tells DFSORT that the first binary key is in positions 10-24 ascending and the second binary key is in positions 1-8 ascending. Since SORTED is not specified, DFSORT will sort the SORTJNF2 records by the specified binary keys.
Note that corresponding keys for the two files match in length and order.
The REFORMAT statement defines the fields to be extracted for the joined records in the order in which they are to appear.FIELDS=(F1:20,8,1,15,F2:26,10,F1:16,2) tells DFSORT to create the joined records as follows:
Joined Record Positions Extracted from ----------------------- ------------------ 1-8 F1 positions 20-27 9-23 F1 positions 1-15 24-33 F2 positions 26-35 34-35 F1 positions 16-17
Since there is no JOIN statement, only paired records are joined by default.
The OPTION COPY statement tells DFSORT to copy the joined records. The OUTFIL statement tells DFSORT to reformat the joined records, display a header at the top of each page and remove the carriage control characters. Note that the BUILD operand of the OUTFIL statement must reference the positions of fields in the joined records.
Conceptually, JOINKEYS application processing proceeds as follows:
- Subtask1 sorts the SORTJNF1 (F1 file) records as directed by its JOINKEYS statement. As a result, it passes the following records to the main task:
Daisies 12 Lilac
Daisies 06 Orange
Daisies 02 Yellow
Roses 09 Blue
Roses 04 Pink
Roses 03 Red
Roses 06 Yellow
- Subtask2 sorts the SORTJNF2 (F2 file) records as directed by its JOINKEYS statement. As a result, it passes the following records to the main task:
Lilac Daisies SoldOut
Orange Daisies SoldOut
White Daisies InStock
Yellow Daisies InStock
Red Lilies InStock
Pink Roses SoldOut
Red Roses InStock
Yellow Roses Ordered
- 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:
Lilac Daisies SoldOut 12
Orange Daisies SoldOut 06
Yellow Daisies InStock 02
Pink Roses SoldOut 04
Red Roses InStock 03
Yellow Roses Ordered 06
- Finally, the main task copies and reformats the joined records according to the OUTFIL statement, and writes the resulting records to SORTOUT. Thus, SORTOUT contains these records:
Color Flower Status Per Pot ------- -------------- ------- ------- Lilac Daisies SoldOut 12 Orange Daisies SoldOut 6 Yellow Daisies InStock 2 Pink Roses SoldOut 4 Red Roses InStock 3 Yellow Roses Ordered 6
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)