TSO-ISPF JCL COBOL VSAM DB2 CICS IMS-DB Tools Articles Forum Quiz Interview Q&A

JCL - JCL SORT Joinkeys Statement

JCL Joinkeys Statment:

JOINKEYS feature joins records from two files based on certain keys (i.e. matching record from both files). The SORTJNF1 and SORTJNF2 DD statements were used to specify file 1 and file 2.

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,

  • If the file is already sorted by the keys

  • If sequence checking of the keys is not needed

  • If the file has fixed-length or variable-length records

  • If you want to stop reading the file after n records

  • A 2-byte id to be used for the message and control data set for the subtask used to process the file

  • If a subset of the records is to be processed based on a logical expression.

Joinkeys Syntax:

jcl sort joinkeys syntax

Two JOINKEYS statements are required for a JOINKEYS application; one for the F1 file and the other for the F2 file.

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.

FILE=F1, FILES=F1 or F1=ddname

Must be used for the JOINKEYS statement associated with the F1 file. FILE=F1 (or FILES=F1) specifies a ddname of SORTJNF1 for the F1 file. F1=ddname can be used to specify any valid ddname for the F1 file. Do not use the same ddname for the F1 file and the F2 file.

When FILE=F1, FILES=F1 or F1=ddname is specified, the other operands of the JOINKEYS statement apply to the F1 file.

FILE=F2, FILES=F2 or F2=ddname

Must be used for the JOINKEYS statement associated with the F2 file. FILE=F2 (or FILES=F2) specifies a ddname of SORTJNF2 for the F2 file. F2=ddname can be used to specify any valid ddname for the F2 file. Do not use the same ddname for the F1 file and the F2 file.

When FILE=F2, FILES=F2 or F2=ddname is specified, the other operands of the JOINKEYS statement apply to the F2 file.

FIELDS=(p,m,s)

Where,

  • Specifies the starting position of the key. The first data byte of a fixed-length record is in position 1. The first data byte of a variable-length record is in position 5 after the 4-byte RDW. p can be 1 to 32752 but all fields must be completely contained within the first 32752 bytes of the record.

  • Specifies the length of the key. The total length of all keys must not exceed 4080 bytes. All fields must be completely contained within the first 32752 bytes of the record. The length for each pair of F1 and F2 keys must match.

  • Specifies the order of the key. Use A for ascending or D for descending. The order for each pair of F1 and F2 keys must match.

Example:
JOINKEYS F1=FILE1,FIELDS=(10,3,D,60,5,A)
JOINKEYS F2=FILE2,FIELDS=(20,3,D,40,5,A)

File F1 is processed using the ddname FILE1, the descending key in positions 10-12 and the ascending key in positions 60-64. File F2 is processed using the ddname FILE2, the descending key in positions 20-22 and the ascending key in positions 40-44.

SORTED

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 can improve performance.

Note: DFSORT will terminate if the copied records are not in the order specified by the keys unless you specify the NOSEQCK operand.

Example:
JOINKEYS FILE=F1,FIELDS=(10,5,A),SORTED
JOINKEYS FILE=F2,FIELDS=(20,5,A)

File F1 is copied using the ddname SORTJNF1 and the ascending key in positions 10-14. 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 20-24.

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.

NOSEQCK

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.

Example:
JOINKEYS FILE=F1,FIELDS=(10,5,A),SORTED,NOSEQCK
JOINKEYS FILE=F2,FIELDS=(20,5,A),SORTED

File F1 is copied using the ddname SORTJNF1 and the ascending key in positions 10-14. The SORTJNF1 records will be not be checked for the correct key order. File F2 is copied using the ddname SORTJNF2 and the ascending key in positions 20-24. 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.

TYPE

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.

JOINKEYS FILE=F1,FIELDS=(10,5,A),TYPE=V
JOINKEYS FILE=F2,FIELDS=(20,5,A),TYPE=F

Note: For TYPE=V file processing, DFSORT adds an RDW in positions 1-4 which you must account for when specifying the starting position

TYPE

It is 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).

STOPAFT=n

Where, "n" can be up to 28 digits with up to 15 significant digits.

Example:
JOINKEYS FILE=F1,STOPAFT=6,FIELDS=(10,5,A)
JOINKEYS FILE=F2,STOPAFT=9,FIELDS=(20,5,A)

The first 6 input records from SORTJNF1 and the first 9 input records from SORTJNF2 will be processed.

Note: 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=(10,5,A)
  JOINKEYS FILE=F2,FIELDS=(20,5,A)
  ...
//JNF1CNTL DD *
  OPTION STOPAFT=6
//JNF2CNTL DD *
  OPTION STOPAFT=9

TASKID

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 ddnames, or a different id can be used for each.

Example:
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.

INCLUDE/OMIT

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.

Example:
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')


If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!

Are you looking for Job Change? Job Portal