DFSORT JOINKEYS Instrumentation - A Practical Example
MartinPacker 11000094DH Visits (7043)
Some technologies show up “in the field” very soon after they’re announced and shipped. Others take a little longer.
Dave Betten and I have - at last - a set of data from a customer where one of the major jobs does indeed use JOINKEYS. The purpose of this post is to show you what one of these looks like - from the point of view of SMF records. I won’t claim this post highlights all the statistics available to you but I hope it gives you a flavour.
Though the job is repeated this post will concentrate on one such running. As you’ll see from the graphic below it runs from 15:25 to 16:33. There are two steps:
From The SORT Step To The JOINKEYS Step
The SORTOUT data set from the SORT step feeds directly into the JOINKEYS step as the SORTJNF1 data set. Note it’s sorted twice - once in the SORT step and again in the JOINKEYS step - which seems rather a pity. It is read by a TSO user later, so maybe the two different sort orders are needed.
What I’ve just used is our Life Of A Data Set Technique (or LOADS for short). Below is the LOADS table for this SORTOUT data set.
This is where - to me - it gets more interesting. In this case we’re joining two data sets - DDs SORTJNF1 and SORTJNF2.
both data sets are sorted on the same key fields. We know this just because they each have Sort Work File data sets - 5 used in one case and 21 in the other.
You might’ve spotted that everything I’ve said so far is based on SMF 14 and 15 (Non-VSAM CLOSE for Read and Update) records. Now let’s start to dig into the SMF 16 (DFSORT Invocation) records, restricting ourselves to the JOINKEYS step.
We have three SMF 16 records for this step:
The two sorts are necessary because the programmer told DFSORT to sort both files so the key fields for the Join are in order. As I indicated in DFSORT Does JOIN there are ways of avoiding this if the sorts are unnecessary (and terminating if the sorts are proven necessary).
For a real tuning exercise you’d try to avoid unnecessary sorts.
The following is a schematic of how the three invocations work.
Let’s look at JNF2 first. The 5 Sort Work File data sets OPEN and CLOSE within the same minute (16:11) according to our Gantt chart. Indeed there are zero EXCPs to them. But the SORTJNF2 data set is held open until the end of the JOINKEYS step (16:33).
Note there’s no output data set from this sort. We’ll come to what happens to the output data in a minute.
Turning to JNF1 the Sort Work File data sets stay open throughout the JOINKEYS step; There’s lots of I/O to them.
Again there’s no output data set from this sort.
The third SMF 16 record relates to the Copy (with an exit) that does the actual join. It has no input data sets but it does have an output data set (DD OUTFILE1).
So let’s turn to what SMF 16 tells us about records and how they flow:
One other point - from SMF 14 and 15 analysis: In this case I don’t see records for SYMNAMES or SYMNOUT DDs, so either DFSORT symbols aren’t being used or they are SYSIN or SPOOL data sets, respectively. To my mind SYMNAMES data sets are most valuable when they are permanent. I don’t expect SYMNOUT to have permanent value, beyond debugging.
There’s lots of extra detail in the SMF 14, 15, and 16 records of course. But I hope this has given you some idea of how to view the data when JOINKEYS is invoked.
And the reason it’s taken us a while to see JOINKEYS in a customer is quite straightforward: It’s not something you flip a switch to use; Rather you have to write code to use it.
And note that this post hasn’t given any real tuning advice: The prev