JCL Tutorial — What is JOINKEYS in SORT 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 combining rows/columns from two different files based on keys 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 example 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 the 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 does 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.
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 is 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 Syntax.
A SORT JOINKEYS join operation required two JOINKEYS statements: One for the first file i.e. F1 and other for the second file i.e. F2. Refer below pictorial representation for more details.
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.
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 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),JOINKEYS FILE=F2,FIELDS=(15,3,A), SORTED,NOSEQCH
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 :
JOINKEYS F1=VSAM1,FIELDS=(23,6,A),JOINKEYS F2=VSAM2,FIELDS=(16,6,A),
- TYPE=V: DFSORT would use variable-length processing for the VSAM file.
- TYPE=F: DFSORT would use fixed-length processing for the VSAM file.
TYPE=V
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 included or exclude records from the file during the join operations.
Both parameters can be specified at JOIN statement of JONKEYS. But you should always specify INCLUDE/OMIT parameter in JNF1CNTL or JNF2CNTL. For example:
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.
00100 ROGGER $1000.00
00200 MURPHY $2000.00
00300 CIARA $3000.00
00300 CIARA $3000.00 JAN
* 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)
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)
EMPID F-NAME EP-SAL SL-MNTH
— — — — — — — — — — — — — — — — — — -
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 feature such as JOINKEYS, JOIN, REFORMAT, JKFROM, TOJUL, TOGREG, WEEKDAY, DT, DTNS, MERGE operator, MERGEIN ect.
Originally published at https://mainframe-forum.blogspot.com.
