JOINKEYS joining two files using SORT

JOINKEYS feature joins transactions/records from two different files based on certain keys (i.e. fields with common information). The SORTJNF1 and SORTJNF2 DD statements were used to specify these files. Each JOINKEYS statement must specify the DDNAME of the file it applies to and the starting position, length, and sequence of the keys in that file. You can also optionally specify

1. If the file is already sorted by the keys and if sequence checking of the keys is not needed.
2. If the file has fixed-length or variable-length records.
3. To stop reading the file after n records.
4. A 2-byte id is to be used for the message and control data set for the subtask used to process the file.
5. If a subset of the records is to be processed based on a logical expression.

FILE=F1 or F1=ddname must be used to indicate that the JOINKEYS statement applies to the F1 input file. FILE=F1 associates the F1 file with a DDNAME of SORTJNF1. You can use a different DDNAME for the F1 file by specifying F1=ddname. For simplicity, we will use SORTJNF1 when referring to the DDNAME for the F1 file.

FILE=F2 or F2=ddname must be used to indicate that the JOINKEYS statement applies to the F2 input file. FILE=F2 associates the F2 file with a DDNAME of SORTJNF2. You can use a different DDNAME for the F2 file by specifying F2=ddname. For simplicity, we will use SORTJNF2 when referring to the DDNAME for the F2 file.

JOINKEYS Syntax

      JOINKEYS---+-FILE=F1---+-,--FIELDS=----(p,m,s)------->
                 +-FILES=F1--+                   
                 +-F1=ddname-+                  
                 +-FILE=F2---+              
                 +-FILES=F2--+                  
                 '-F2=ddname-'                  
       | V                                        | |   
       '---+-SORTED--+------------+-------------+-+-'   
           |         '-,--NOSEQCK-'             |       
           +-TYPE=--+-F-+-----------------------+       
           |        '-V-'                       |       
           +-STOPAFT=n--------------------------+       
           +-TASKID=id--------------------------+       
           +-INCLUDE=--+-(logical expression)-+-+       
           |           +-ALL------------------+ |       
           |           '-NONE-----------------' |       
           '-OMIT=--+-(logical expression)-+----'       
                    +-ALL------------------+      

There are primarily four basic steps involved in any JOIN operations. 

• Use the REFORMAT control statement to specify the name of fields, that need to be included in the joined record.
• Selecting or dropping records either from both files or from either of the file by using the INCLUDE/OMIT parameter of the JOINKEYS statement.
• Specify whether the input join data is already sorted as per JOINKEYS control fields by using SORTED parameters. The overall performance of JOINKEYS is better if JOIN input data is already sorted as per JOINKEYS fields.
• Use JOIN statement to specify the nature of joins i.e. (Inner join, left outer join, right outer join, and full outer join) 

JOINKEYS Example:

//SORTSTEP EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTJNF1 DD DSN=XXXXX.YYYYYY.ZZZZZZ,DISP=SHR
//SORTJNF2 DD DSN=AAAAA.BBBBBB.CCCCCC,DISP=SHR
//SORTOUT  DD SYSOUT=*
//SYSIN DD *
* Control statements for JOINKEYS operation.
  JOINKEYS FILE=F1,FIELDS=(1,15,A,20,8,A),SORTED,NOSEQCK
  JOINKEYS FILE=F2,FIELDS=(10,15,A,1,8,A),SORTED,NOSEQCK
  REFORMAT FIELDS= (F2:1,80,F1:1,80)
* Control statements for main-task (joined records)
  SORT FIELDS=COPY
/*

Keyword: JOIN

If you don’t specify a JOIN statement for a JOINKEYS application, only paired records from F1 and F2 are kept and processed. This is known as an inner join.

JOIN UNPAIRED,F1,ONLY

The joined records will be the original unpaired F1 records. If the F1 records are fixed-length, the joined records will be fixed-length. If the F1 records are variable-length, the joined records will be variable-length.

JOIN UNPAIRED,F2,ONLY

The joined records will be the original unpaired F2 records. If the F2 records are fixed-length, the joined records will be fixed-length. If the F2 records are variable-length, the joined records will be variable-length.

JOIN UNPAIRED,F1,F2,ONLY or JOIN UNPAIRED,ONLY

The joined records will be variable-length. If the F1 records are fixed-length, each unpaired F1 record will be variable-length with an RDW followed by the original F1 record. If the F1 records are variable-length, each unpaired F1 record will be the original F1record. If the F2 records are fixed-length, each unpaired F2 record will be variable-length with an RDW followed by the original F2 record. If the F2 records are variable-length, each unpaired F2 record will be the original F2 record.

You can change which records are kept and processed by the main task as the joined records by specifying a JOIN statement. You must specify the UNPAIRED operand. The F1, F2, and ONLY operands are optional. The JOIN operands you specify indicate the joined records to be kept and processed by the main task as follows:

UNPAIRED,F1,F2 or UNPAIRED

Unpaired records from F1 and F2 as well as paired records. This is known as a full outer join.

UNPAIRED,F1

Unpaired records from F1 as well as paired records. This is known as a left outer join.

UNPAIRED,F2

Unpaired records from F2 as well as paired records. This is known as a right outer join.

UNPAIRED,F1,F2,ONLY or UNPAIRED,ONLY

Unpaired records from F1 and F2.

UNPAIRED,F1,ONLY

Unpaired records from F1.

UNPAIRED,F2,ONLY

Unpaired records from F2.

Keyword: REFORMAT

REFORMAT statement specified to indicate the F1 and/or F2 fields you want in the joined records. You can optionally specify an indicator of where the key was found, and a FILL character to be used for missing bytes. If a JOIN statement with ONLY is specified, the REFORMAT statement is optional.

Keyword: SORTED with JOINKEYS

By default, DFSORT will sort the input file by the specified keys. If the records of the input file are already in sorted order by the specified keys, you can use the SORTED operand to tell DFSORT to copy the records rather than sort them. This will improve performance. DFSORT will terminate if the copied records are not in the order specified by the keys unless you specify the NOSEQCK operand.

For example, if you specify:

JOINKEYS FILE=F1,FIELDS=(22,3,A),SORTED
JOINKEYS FILE=F2,FIELDS=(15,3,A)

File F1 is copied using the DDNAME SORTJNF1 and the ascending key in positions 22-24. The SORTJNF1 records will be checked for the correct key order. File F2 is sorted using the DDNAME SORTJNF2 and the ascending key in positions 15-17.

If you use the SORTED operand, statements and options only available for a sort application, such as SUM, will be ignored for the subtask that copies the input file.

Keyword: NOSEQCK with JOINKEYS

If you specify the SORTED operand and know that the records of the input file are already in sorted order by the specified keys, you can use the NOSEQCK operand to tell DFSORT not to check the order of the records. This can improve performance.

For example, if you specify:

  JOINKEYS FILE=F1,FIELDS=(22,3,A),SORTED,NOSEQCK
  JOINKEYS FILE=F2,FIELDS=(15,3,A),SORTED 

File F1 is copied using the DDNAME SORTJNF1 and the ascending key in positions 22-24. The SORTJNF1 records will not be checked for the correct key order. File F2 is copied using the dd name SORTJNF2 and the ascending key in positions 15-17. The SORTJNF2 records will be checked for the correct key order. If the records are not actually in order by the specified keys and you use NOSEQCK, the output may be incorrect. The NOSEQCK operand is ignored if the SORTED operand is not specified.

Keyword: TYPE with JOINKEYS

TYPE=V can be used to tell DFSORT to use variable-length processing for a VSAM input file. TYPE=F (the default) can be used to tell DFSORT to use fixed-length processing for a VSAM input file.

For example, if you specify:

  JOINKEYS F1=VSAM1,FIELDS=(22,3,A),TYPE=V
  JOINKEYS F2=VSAM2,FIELDS=(15,3,A),TYPE=F 

VSAM file F1 is sorted as variable-length records using the DDNAME VSAM1 and the ascending key in positions 22-24. (Remember that for TYPE=V VSAM processing, DFSORT adds an RDW in positions 1-4 which you must account for when specifying the starting position.) VSAM file F2 is sorted as fixed-length records using the DDNAME VSAM2 and the ascending key in positions 15-17. (Remember that for TYPE=F VSAM processing, DFSORT does not add an RDW.)

Keyword: STOPAFT with JOINKEYS

Can be used to specify the maximum number of records (n) you want the subtask for the input file to accept for sorting or copying (accepted means read from the input file and not deleted by INCLUDE or OMIT).

N: can be up to 28 digits with up to 15 significant digits.

For example, if you specify:

  JOINKEYS FILE=F1,STOPAFT=5,FIELDS=(32,4,A)
  JOINKEYS FILE=F2,STOPAFT=10,FIELDS=(32,4,A) 

The first 5 input records from SORTJNF1 and the first 10 input records from SORTJNF2 will be processed.

You can use STOPAFT=n on the OPTION statement in JNF1CNTL (for the F1 file) or JNF2CNTL (for the F2 file) instead of specifying STOPAFT=n on the JOINKEYS statement. For example, instead of the STOPAFT operands in the JOINKEYS statements shown previously, you could specify:

//SYSIN DD *
   JOINKEYS FILE=F1,FIELDS=(32,4,A) 
   JOINKEYS FILE=F2,FIELDS=(32,4,A) 
 //JNF1CNTL DD * 
   OPTION STOPAFT=5 
 //JNF2CNTL DD * 
   OPTION STOPAFT=10 
   ...........
 /*

Keyword: TASKID with JOINKEYS

By default, DFSORT uses the following DDNAMEs for the subtasks:

• JNF1JMSG for the subtask1 (F1 file) message data set.
• JNF1CNTL for the subtask1 (F1 file) control data set.
• JNF1WKdd for the subtask1 (F1 file) work data sets.
• JNF2JMSG for the subtask2 (F2 file) message data set.
• JNF2CNTL for the subtask2 (F2 file) control data set.
• JNF2WKdd for the subtask2 (F2 file) work data sets.

The TASKID=id operand can be used to change the first two characters from JN to the specified id characters. The same id can be used for the F1 and F2 dd names, or a different id can be used for each.

For example, if you specify:

  JOINKEYS F1=IN1,FIELDS=(1,10,A),TASKID=C1
  JOINKEYS F2=IN2,FIELDS=(22,10,A),TASKID=C1 

C1F1JMSG, C1F1CNTL, and C1F1WKdd will be used for subtask1 (F1 file). C1F2JMSG, C1F2CNTL, and C1F2WKdd will be used for subtask2(F2 file).

If you specify:

  JOINKEYS F1=IN1,FIELDS=(1,10,A),TASKID=I1
  JOINKEYS F2=IN2,FIELDS=(22,10,A),TASKID=I2 

I1F1JMSG, I1F1CNTL, and I1F1WKdd will be used for subtask1 (F1 file). I2F2JMSG, I2F2CNTL, and I2F2WKdd will be used for subtask2(F2 file).

The TASKID=id operand can be useful when you are doing multiple JOINKEYS applications and want to separate the messages for each application and/or specify different control statements or work data sets for different subtasks.

Keyword: INCLUDE or OMIT with JOINKEYS

Can be used to specify criteria for the records you want the subtask for the input file to include or omit for sorting or copying. You can use the same logical expressions, ALL or NONE in the same way as for the INCLUDE or OMIT operand of the OUTFIL statement. See OUTFIL control statements for details.

For example, if you specify:

     JOINKEYS FILE=F1,FIELDS=(35,8,A),
     OMIT=(5,4,CH,EQ,C'ABCD') 
     JOINKEYS FILE=F2,FIELDS=(37,8,A), 
     INCLUDE=(1,20,SS,EQ,C'NO') 

Only records without ‘ABCD’ in positions 5-8 will be processed from file F1. Only records with ‘NO’ somewhere in positions 1-20 will be processed from file F2.

Although the INCLUDE and OMIT operands are available on the JOINKEYS statement, it is recommended that you specify an INCLUDE or OMIT statement in JNF1CNTL or JNF2CNTL instead for ease of use.

For example, instead of the OMIT and INCLUDE operands in the JOINKEYS statements shown previously, you could specify:

//SYSIN DD *
   JOINKEYS FILE=F1,FIELDS=(35,8,A) 
   JOINKEYS FILE=F2,FIELDS=(37,8,A) 
......
......
 //JNF1CNTL DD * 
   OMIT COND=(5,4,CH,EQ,C'ABCD') 
 //JNF2CNTL DD * 
   INCLUDE COND=(1,20,SS,EQ,C'NO') 
 //*

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