A Record Of Sorts

(Originally posted 2016-08-27.)

When looking at a batch job¹ I like to see how the data flows through the various steps.

The first step – some 23 years ago 🙂 – was to look at the Life Of A Data Set (“LOADS” for short).²

With LOADS – for VSAM and non-VSAM data sets – you can see who reads and writes the data set. You can also see the EXCP count. More on that in a bit but suffice it to say EXCP count might be enough to tell you if the data set was written or read in its entirety.

Why Record Counts Matter

Probably just out of curiosity. 🙂

Actually, really not…

I just said I can detect readers and writers and I used the words “in its entirety”. But I think it useful to go deeper. Here are two – off the top of my head – reasons to want record counts:

• Because business volumes can show up in record counts. For example, a transaction file’s record count is the number of transactions in the life of this version of the data set.

• Because it might explain some other count. More on this one in a minute.

Estimating Record Counts

I just used the word “estimating”. Under some circumstances we can do better than estimating, as we’ll see.

One of the reports our “Job Dossier” code produces is called “Job Data Set”. Basically a list of steps and the data sets each step accesses.³

For data sets accessed by QSAM we can estimate the number of records in the data set by examining the LRECL, the Block size and the EXCP count. But there are lots of problems with this:

• This is only going to work for Fixed-Blocked (FB) data sets.
• Compression complicates things. We need to fix our code to handle this – though today we print the compression ratio.
• The assumption is the processing is sequentially start-to-finish.
• You might do a small number of EXCPs not related to actual data transfer.
• It’s likely the step will read or write partially-filled blocks.

Still, where applicable it’s a good start.

But we can do better:

DFSORT’s SMF 16 tells you the overall counts of input records and output records⁴ whether SMF=FULL⁵ or not.

So in a very simple case – a single sort invocation in a step – we can use these record counts to estimate the number of records in the SORTIN and SORTOUT data sets. And we can find the SORTIN data set represented by an SMF 14 record and the SORTOUT data set by an SMF 15 record.

Record Counts And SQL Statements

Several times in a recent batch study the SMF 101 SQL counts have borne some relation to record counts. Consider the following (very realistic) scenario:

The sort step reads a data set (SORTIN DD) and writes one (SORTOUT DD). The DB2 step reads the same data set and does something with DB2 data based on the records read.

For example, in one job step the Singleton Select count matches the input record count.

So we can glean that the selects are record-driven – just with SMF.

By the way, we match SMF 101 records with SMF 30–4 Step End records by Timestamp comparison and Correlation ID matching, which I describe in gory detail in Finding The DB2 Accounting Trace Records For an IMS Batch Job Step. Ignore the “IMS” bit if you like; The preamble is the more general bit.

What My Code Does Today

So, the essential thing is that DFSORT keeps good account of the records written – overall. For output data sets it keeps good counts at the individual data set level (with SMF=FULL).

We map all this, of course.

My first toe in the water is very limited:

For the “single sort in a step with one input data set and one output data set” case I use the SMF 16 record counts as the data set sizes. These overwrite any EXCP / block size / LRECL estimate for FB data sets – as it’s more accurate.

The really nice thing is it gives me an accurate estimate for VB data sets, which I didn’t have before.

Possible Extensions

A number of quite feasible extensions are:

• I could keep the output data set’s record count once I’ve got it and use it in downstream steps. If it gets rewritten then the previous estimate could be invalidated, so that’s safe.
• It would be tricky but I could propagate backwards the input data set’s record count to previous steps that read or wrote the data set.
• I could use the OUTFIL and Output File sections in the SMF 16 record (as we query them) to handle the “multiple output data set” case.
• With multiple input data sets I could pro-rate the input record count across them using the Access Method calls count in SMF 16 Input FIle sections of the SMF 16 record. (This one is dodgy but better than “I’ve no idea”.)
• I said “single sort in a step” but there is enough timestamp instrumentation to do better than that. But where do multiple sorts in a step come from? Here are some examples:
  • DB2 Utilities – where record counts would be especially useful
  • ICETOOL
  • DFSORT JOINKEYS
  • Programs that happen to invoke DFSORT multiple times
• I don’t flag whether a record count is exact – from DFSORT – or estimated. The latter could be printed in italics.

This is quite a long list of potential extensions – but each one is fiddle. Some will get done; Some possibly won’t.

All I know is our code’s ability to estimate record counts took a leap forward, and that is proving useful straightaway. And writing this has helped me sort my thoughts out, as has explaining it to a couple of friends (with a stake in this). And I haven’t even begun to talk about VSAM yet… 🙂