JCL - SORT JOIN Statement

We can make use of SORT to join two flat files and writes records from both files. JOINKEYS in sort utility is used to perform various join operation on matched and non-matched records can be executed based on matching fields or Keys.

Joining can be performed in number of ways like inner join, full outer join, left outer join, right outer join and unpaired combinations.

The join operation is controlled by three important control statements JOINKEYS, JOIN, and REFORMAT. The SORTJNF1 and SORTJNF2, are the DD statements used to specify the files which will be use to perform join operation.

1. JOINKEYS

2. JOIN

3. REFORMAT

JOINKEYS:

• You must specify two JOINKEYS statements; one for the F1(i.e file 1) and another for the F2(i.e 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 and if sequence checking of the keys is not needed; if the file has fixed-length or variable-length records; 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, and if a subset of the records is to be processed based on a logical expression.

JOIN:

• If you don't specify a JOIN statement, only paired records from F1 and F2 are kept and processed by the main task as the joined records (inner join).

• You can optionally specify a JOIN statement to process in below ways:

  1. Unpaired F1 records as well as paired records (left outer join)

  2. Unpaired F2 records as well as paired records (right outer join)

  3. Unpaired F1 and F2 records as well as paired records (full outer join)

  4. Only unpaired F1 records

  5. Only unpaired F2 records

  6. Only unpaired F1 and F2 records

REFORMAT:

• If a JOIN statement with ONLY is specified, REFORMAT statement is optional

• You would normally specify a REFORMAT statement 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.

Sample JCL: JOINKEYS

See sample JCL and control statements for a simple JOINKEYS application to do an inner join (also known as a cartesian join) which joins all paired records.

//STEP01   EXEC  PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=INPUT DATASET 1,DISP=SHR
//SORTJNF2 DD DSN=INPUT DATASET 2,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
* Control statements for JOINKEYS application
  JOINKEYS FILE=F1,FIELDS=(15,2,A,7,4,A)
  JOINKEYS FILE=F2,FIELDS=(21,2,A,23,4,A)
  REFORMAT FIELDS=(F2:1,70,F1:1,60)
* Control statements for main task (joined records)
  SORT FIELDS=COPY
/*

We will see each JCL DD statement in detail in upcoming section.

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