Paired records without duplicates using SORT

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)

Read JCL blogs: Click Here SYNCSORT Manual: Click Here