(Originally posted 2017-06-20.)
I’ve written a lot about DDF and SMF 101 (Accounting Trace) over the years. It turns out my code went backwards a few years ago, and with good reason.
Let me explain.
But before I do, recall “my code” refers to a DFSORT E15 exit that “flattens” SMF 101 records, extracting the DDF-related fields into fixed positions. Each input record leads to an output record (if it qualifies). Downstream code does summarization but, crucially, records aren’t joined together.
Prior to DB2 Version 8 package-level information was recorded in the main (IFCID 3) 101 record. IFCID 239 (also 101) records contained overflow package sections only, as shown in the following diagram. My code picked up the first few packages in the IFCID 3 record.
Notice the first 10 packages were in the IFCID 3 record, with the first IFCID 239 record containing up to 10 more, and so on.
The importance of package-level information for DDF is threefold:
- The initial package says a lot about the calling (usually distributed) application.
- Quite a lot of DDF applications work by calling Stored Procedures and User-Defined Functions (UDFs). We see that fine structure in the package-level information.
- You can, as usual, see where the time and CPU is being spent – to the package level.
Generally I could do my work without needing IFCID 239 records as the first 10 packages were described in the IFCID 3 record.
Life was goodish. 
Not So Happy Days
But then Version 8 came along and the structure of SMF 101 changed.
Now the IFCID 3 records don’t contain package information. All this is in IFCID 239 records now. So I couldn’t get information about the first two, say, packages for a DDF invocation. The colour drained out of this. 😦
I wanted, for example, to know which machines access IBM Content Manager and which functions they used. I probably see something mnemonic at the plan level in the IFCID 3 record. I definitely see something mnemonic in the IFCID 3 record but now they’re separate records. Never the twain shall meet.
So, reluctantly, I ripped the package analysis stuff out of my code. A good few years ago. And I was miserable. 🙂
And you’ve seen all the things I’ve been able to do with DDF with SMF 101s – in previous blog posts.
Happy Days Are Here Again
But then along came DFSORT JOIN which allows pairs of records to be efficiently joined together.
This is great but what would the key to join on be? It couldn’t be the time stamp – as the IFCID 3 and IFCID 239 records’ timestamps would usually be slightly different – and probably no combination of other SMF 101 record fields either. Well, some bits for the IFCID 3 and 239 records are common. In particular the Standard Header (mapped by DSNDQWHS). One field in particular stands out: The Logical Unit Of Work ID (LUWID).
As you can see in each of the diagrams the LUWID ties the related records together.
So then there was hope.
So I extended my DFSORT E15 exit to emit two types of flattened record and the DFSORT invocation itself to write to an additional destination: DD IFCID239. So IFCID–3-originated records are formatted differently and go to different data sets than IFCID–239-originated records.
Now I can use join – very much in the style of Lost For Words With DDF. In that post I talked about joining Client and Server 101 (IFCID 3) records based on most of the LUWID. In this new case I can do something pretty similar.
At this stage I have thrown into production this code to write the flat files, having run some test reporting to verify my code works.
In my first set of data I see (as I mentioned above) IBM Content Manager callers, complete with nested stored procedures. I can tell they’re stored procedures because they have the appropriate flag set in the right sections.
Now to build some reporting based on these files and JOIN. Actually I can see some value in reporting on the IFCID 239 data alone.
Stay tuned for another thrilling installment. 🙂 Seriously, I fully expect to learn stuff, including some new tricks, as build on this foundation.
And as I finish this post off, sitting in my back garden 🙂 , I’ve jotted down a few notes on using DFSORT JOIN. So expect to hear more about that soon.