MF Technique 2: Advanced SORT

Batch data processing: save up to 90% of the time with the SORT utility

• Using the SORT utility efficiently to match files brings large savings in batch processing.

• A 90% saving in run time by doing separate unloads and matching the files.

In this batch processing case, using the SORT utility properly saved more than 90% of the process's elapsed run time, and also cut its CPU consumption, and therefore its cost, by more than 50%.

Below we explain the advantages of the SORT utility and walk through a practical case:

The SORT utility

Batch processing has many utilities for handling files. One of them is the SORT utility, which has many functions. One of them is to join the records of two files, as long as they share a common key that lets records be paired. The result is a Cartesian product of the records with the same key from each file.

To drastically reduce batch processing and elapsed time, when the job is a file match with no further logic and no need for complex operations or calculations, it is advisable to do it with a SORT step instead of a matching program in COBOL or PL/I.

• To match files in a JCL, the JOINKEYS parameter is required.

JOINKEYS parameters

To match files with JOINKEYS, the control statements need the following elements:

• Two mandatory JOINKEYS statements. Each one is associated with an input file (F1 and F2), defines the key fields of the “join” operation and sorts the records (if needed). The pairing happens after the two JOINKEYS statements have been processed.

• An optional JOIN statement. Through a parameter, it specifies which records are written to the output. After the JOINKEYS statements, the records are already paired.

• An optional REFORMAT statement. It reformats the output record as needed, selecting which fields of each file make up the resulting record, and in what order.

• A SORT statement, either SORT FIELDS=COPY or any other kind of sort. Bear in mind that any sort definition must refer to the positions of the reformatted records – if REFORMAT was used – or to the Cartesian product of the paired records, if we are working with it directly, without REFORMAT.

In addition, if needed, different control statements can be run on each input file before the match. They are named SORTJNF1 and SORTJNF2, referring to each of the input files.

The utilities that can be run are: INCLUDE, OMIT, INREC and SUM.

These statements can help reduce the cost of JOINKEYS by filtering the files, whenever that is possible.

Types of JOIN file matching

The JOIN statement is optional and specifies which records are sent to the output. If it is omitted, only the paired records appear in the output. However, the utility offers other possibilities through the following options.

• UNPAIRED,F1,F2 or simply UNPAIRED: paired records and unpaired records from F1 and F2.

• UNPAIRED,F1: paired records and unpaired records from F1.

• UNPAIRED,F2: paired records and unpaired records from F2.

• UNPAIRED,F1,F2,ONLY or simply UNPAIRED,ONLY: unpaired records from both files.

• UNPAIRED,F1,ONLY: unpaired records from F1.

• UNPAIRED,F2,ONLY: unpaired records from F2.

SORTED and NOSEQCK

To match files with JOINKEYS, the input files must be sorted by the key fields used for the match; so unless told otherwise, JOINKEYS sorts the files before matching them.

The SORTED keyword tells it that the inputs are already sorted. Alongside it, the NOSEQCK keyword skips the check that the records are in order.

Both are coded on the JOINKEYS statements in the control statements, and can be used on either input file, on both or on neither.

It is important to know that if SORTED is combined with NOSEQCK and one of the inputs is not sorted, the match step ends without an error, but the results are unpredictable, because with unsorted files we are not really matching anything.

On the other hand, if SORTED is not combined with NOSEQCK and one of the inputs is not sorted, the match step ends abruptly with an error.

So, when we are certain that both inputs are sorted, using SORTED and NOSEQCK improves the match step considerably, reducing its CPU consumption by up to 63% and its elapsed time by 61%.

When to use JOINKEYS

One case where JOINKEYS is recommended is when we want to retrieve records from two different database tables, joining them on fields that are not part of the clustering index of both tables, or when the clustering index has more fields than those used for the join. In this case, because the JOIN is not done on the most suitable fields, having to scan the table by non-indexed fields is costly.

Another case worth matching with JOINKEYS is a database join where, even though the join fields are the right ones in both tables, the tables are very large.

In these cases, it is advisable to unload the tables separately, sorted by the match fields, and then match them in a JOINKEYS step with SORTED and NOSEQCK.

Practical case

Below is a real example from one of our success stories, using the SORTED keyword correctly. In a process we were asked to analyse to recommend improvements that would reduce its consumption, we found that two tables were being joined inefficiently: the join fields made up the clustering index of one of them (a medium-sized table), while for the other table (with a large number of records) those fields were part of the clustering index.

Unload performed

TABLA1 contains 127,463,056 records, and these are the fields that make up its clustering index

TABLA2 contains 86,654 records, and these are the fields that make up its clustering index

Practical case – Proposed solution

In this case we recommended doing separate unloads in the process, filtering and sorting at the same time, and then matching them in a JOINKEYS step with the SORTED and NOSEQCK keywords.

CPU SAVING: -50.3% 187 seconds
ELAPSED SAVING: -91.3% 1 hour and 45 minutes