Top 15 Useful Hack Tips On JOINKEYS With Examples! [JOINKEYS in JCL]

JOINKEYS in JCL

In your day-to-day task, you might have come across a situation, where you need to produce some statistics of matched or non-matched records from two separate files based on keys fields. If you are dealing with RDBMS then this task would be pretty simple, you could have written an SQL statement to generate desired statistics. 

But eventually, this task is not simple if you are dealing with flat files. In such a situation either you write some Easytrieve or COBOL program or use SORT JOINKEYS. JOINKEYS in SORT JCL is usually the preferred option despite writing a program. 

In laymen's terms, the term JOIN is used to combine rows/columns from two different files based on key fields, for example, you want to get a list of the customer from two separate files based on key i.e. employee_no. Also, you can use various subparameters like REFORMAT, OMITS, INCLUDE/COPY/SORT, etc. along with usual join operations. 

In today's tutorial, you'll learn the application of JOINKEYS in SORT JCL's. You'll also get practical exposure to various real-time SORT JOINKEY examples for better understanding. Also, you'll learn top hack tips and tricks associated with SORT JOINKEYS.   

What is JOINKEYS?

JOINKEYS is one of the most popular features of DFSORT/MFX. It enables the programmers to perform various join operations on two files. If you don't have a facility of SORT JOINKEYS in your shop then you need to write an Easytrive or a COBOL program to get desired results. Writing a simple program for small day-to-day tasks is a tedious job.  

Hence, 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. 

By default, when the JOINKEYS operation is applied to two files having M and N records respectively, then records are joined using the reformat clause, producing M*N records as input to subsequent DFSORT/MFX SORT processing. This type of JOIN is called an INNER JOIN.  

How JCL SORT JOINKEYS process work? 

The feature of JOINKEYS in sort JCL is designed to improve the productivity of the programmer by reducing the time of designing, testing, and debugging applications. The SORT JOINKEYS is an extensive data processing utility. 

The join operation is controlled by three important control statements i.e. JOINKEYS, JOIN, and REFORMAT. When you apply a join operation on two files, each record from the first file (SORTJNF1) will be joined with the second file (SORTJNF2) based on key values. Thus, if m records from the left side have a given join key value, and n from the right side have the same join key value, the join results in m*n records with that join key value. 

JOINKEYS process flow diagram is a pictorial representation of the processing performed for a JOINKEYS application.

JOINKEYS Process Flow Diagram.

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

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

JOINKEYS Syntax. 

A JCL SORT JOINKEYS join operation required two JOINKEYS statements: One for the first file i.e. F1 and the other for the second file i.e. F2. Refer to pictorial representation for more details.  

JOINKEYS Statement

JOINKEYS Sample JCL.

Here is the sample JCL with the control statements for a simple inner join key application using SORT JOINKEYS.

//TMXXAXXX JOB (99999),'MAINFRAME FORUM',CLASS=A,MSGCLASS=X, 
//              MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*
//*   SAMPLE JCL FOR JOINKEYS.
//* 
//STEP01  EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTJNF1 DD DSN=MXXP.EMP.PAY.DT001,DISP=SHR
//SORTJNF2 DD DSN=MXXP.EMP.PAY.DT002,DISP=SHR
//SORTOUT  DD SYSOUT=*
//SYSIN DD *
* Control statements for JOINKEYS operation.
  JOINKEYS FILE= F1,FIELDS=(10,2,A,17,4,A)
  JOINKEYS FILE= F2,FIELDS=(20,2,A,23,4,A)
  REFORMAT FIELDS= (F2:1,80,F1:1,80)
* Control statements for main-task (joined records)
SORT FIELDS=COPY
/*

JOIN Statement. 

JOIN control statement in SORT JOINKEYS is an important parameter and if you don't specify this parameter then by default, only paired records from both F1 and F2 files are processed by the main task as joined transaction records. This type of join operation is called an INNER JOIN.

You can use the join statement to specify which records need to be included and processed by the main task. Also, you must specify the UNPAIRED operand. However, F1, F2 and ONLY are optional parameters. 

JOIN Statement in JOINKEYS

When you specify the JOIN operands, then SORT JOINKEYS operation will retain the joined records and these records will be processed by the main task as follows:

• UNPAIRED,F1,F2 or UNPAIRED: Retain unpaired records from both F1 & F2 files along with paired records. This type of join is called as FULL OUTER JOIN.
• UNPAIRED,F1: Retain unpaired records from the F1 file along with paired records. This type of join is called a LEFT OUTER JOIN.
• UNPAIRED,F2: Retain unpaired records from the F2 file along with paired records. This type of join is called RIGHT OUTER JOIN.
• UNPAIRED,F1,F2,ONLY or UNPAIRED,ONLY: Retain unpaired records from F1 and F2 files. 
• UNPAIRED,F1,ONLY: Retain unpaired records from F1 file. 
• UNPAIRED,F2,ONLY: Retain unpaired records from F2 file. 

JOINKEYS: SORTED/NOSEQCK parameters.

By default, DFSORT/MFX will sort the input files based on the specified keys. If you know the input file records are already sorted in order, you can use the SORTED parameter to tell DFSORT/MFX to copy file records despite sorting them again. Also, use NOSEQCH operand to tell DFSORT not to check for the order of the records. For example, if you specify:

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

JOINKEYS: TYPE parameter.
Another important parameter is TYPE, which is used to specify the processing length for a VSAM input file. For example, if you specify :

• TYPE=V: DFSORT would use variable-length processing for the VSAM file. 
• TYPE=F: DFSORT would use fixed-length processing for the VSAM file.

JOINKEYS F1=VSAM1,FIELDS=(23,6,A),TYPE=V
JOINKEYS F2=VSAM2,FIELDS=(16,6,A),TYPE=F

JOINKEYS: INLUCDE/OMIT Parameters.

The performance of SORT JOINKEYS can be improved by using two key parameters i.e. INCLUDE/OMIT. These two parameters are widely used either to include or exclude records from the file during the join operations.

JOINKEYS INCLUDE/OMIT Parameters.

Both parameters can be specified at JOIN statement of JONKEYS. But you should always specify INCLUDE/OMIT parameter in JNF1CNTL or JNF2CNTL. For example:

//*
//* INCLUDE/OMIT Specified at JOIN statements.
//*  
  JOINKEYS FILE=F1,FIELDS=(35,8,A),
     OMIT=(10,15,CH,EQ,C'MAINFRAME FORUM')
  JOINKEYS FILE=F2,FIELDS=(37,8,A),
     INCLUDE=(1,20,SS,EQ,C'TRAN')

//*
//* INLCLUDE/OMIT specified at JFN1CNTL/JNF2CNTL 
//*
//SYSIN DD *
  JOINKEYS FILE=F1,FIELDS=(35,8,A)
  JOINKEYS FILE=F2,FIELDS=(37,8,A)
...
//JNF1CNTL DD *
  OMIT COND=(10,15,CH,EQ,C'MAINFRAME FORUM')
//JNF2CNTL DD *
  INCLUDE COND=(1,20,SS,EQ,C'TRAN')

Example 1: Generate output report with paired transaction records from both F1/F2 without duplicates.

//TMXXXSDX JOB (99999),'MAINFRAME FORUM',CLASS=A,MSGCLASS=X, 

//              MSGLEVEL=(1,1),NOTIFY=&SYSUID

//*

//*   SAMPLE JCL FOR JOINKEYS.

//* 

//STEP01 EXEC PGM=SORT

//SYSOUT DD SYSOUT=*

//SORTJNF1 DD *

  00100       ROGGER    $1000.00

  00200       MURPHY    $2000.00

  00300       CIARA     $3000.00

/*

//SORTJNF2 DD *

  00100       ROGGER    $1000.00  JAN

  00200       MURPHY    $2000.00  JAN

  00300       CIARA     $3000.00  JAN

  00300       CIARA     $3000.00  JAN

/*

//SORTOUT DD SYSOUT=*

//SYSIN DD *

* Control statements for JOINKEYS application

  JOINKEYS FILE=F1,FIELDS=(1,05,A)

  JOINKEYS FILE=F2,FIELDS=(1,05,A)

  REFORMAT FIELDS=(F1:1,5,13,10,F2:33,03,F1:23,8)

* Control statements for main task (joined records)

  OPTION COPY

  OUTFIL REMOVECC,

  HEADER2=(1:'EMPID',11:'F-NAME',26:'EP-SAL',36:'SL-MNTH',/,

  1:7'-',11:14'-',26:7'-',36:7'-'),

  BUILD=(1:1,5,11:13,10,26:23,08,36:33,03)

/*

Output. 

EMPID     F-NAME         EP-SAL   SL-MNTH

-------   -------------- -------  -------

00100     ROGGER         $1000.00 JAN

00200     MURPHY         $2000.00 JAN 

00300     CIARA          $3000.00 JAN

JOINKEYS in JCL Youtube Video: 

SUMMARY.

DFSORT/MFX are powerful data manipulation tools available on the mainframe platform.

These tools have the ability to sort, merge, copy, generate the report for business analysis. JOINKEYS in SORT JCL supports a various number of features such as JOINKEYS, JOIN, REFORMAT, JKFROM, TOJUL, TOGREG, WEEKDAY, DT, DTNS, MERGE operator, MERGE, etc.

►Subscribe to Topictrick & Don't forget to press THE BELL ICON to never miss any updates. Also, Please visit mention the link below to stay connected with Topictrick and the Mainframe forum on - 

► Youtube

► Follow us on Twitter  

► Facebook 

► Linkedin

► Reddit

► Mainframe Blog

► Medium Blog

Thank you for your support. 

Mainframe Forum™