And Some More Things On The Way To DB2 Version 11

(Originally posted 2014-03-08.)

It’s only fair to warn you this gets technical straight away. And is probably only interesting to a DB2 audience anyway. And as someone who disclaims on his DB2 knowledge anyway you might not want to read this. 🙂

Still with me? Excellent!

As described in Coping With DB2 Version 10 And Preparing For DB2 Version 11 I’ve been revamping my DB2 analysis code.[1] Here are a couple of areas I think you might be interested in:

When Package And Plan Level Accounting Appear To Disagree

While I like to ensure my reporting matches that produced by IDMS products there is another kind of corroboration sensible reporting developers should try: Where possible check two data sources agree: For example DB2 Statistics Trace and DB2 Accounting Trace.[2]

This story relates to two different sections of the same record type:[3] The plan-level timings sections (QWAC and QWAX) appeared to disagree with the package-level[4] timing section (QPAC):

When you read the description of QWACAWTI it appears to be all the synchronous I/O time. A long time ago another field was introduced – QWACAWLG – which is the synchronous Log Write I/O time. This looked like a breakout of QWACAWTI and so I treated it that way.

The QPAC section also has a field like this called QPACAWTI. My DB2 Batch analysis code allows me to compare the plan- and package-level timings. So I discovered that – for specimen jobs – QWACAWTI was much less than the sum of all the QPACAWTI times. This didn’t make sense to me. Adding QWACAWLG to QWACAWTI got me the sum of the QPACAWTI times. Actually this does make sense as there is no package-level equivalent of QWACAWLG.

So now my code works properly – in this one regard.[5]

The byproduct is I know these jobs have substantial issues with logging speed: Either too much logging or too low logging bandwidth. Actually I do know which package has the logging issue in most cases as it’s the one with the big QPACAWTI value.

Row Count Statistics

This is a nicer story as it involves some newish statistics. In DB2 Version 9 Row Count statistics were introduced in the plan-level QXST section of Accounting Trace. In all likelihood you didn’t notice them. I certainly didn’t.

Let’s take one example: A basic statistic that’s been in the record forever is QXFETCH – the number of Fetch statements.[6] But one Fetch statement doesn’t necessarily equate to one row fetched. As someone who’s not in the habit of coding SQL I can immediately think of two scenarios where the two are different:

• In programs where you open a cursor you Fetch rows until there are no more rows available. The last Fetch statement doesn’t return a row.

• With the quite old now Multi-Row Fetch statement returns more than one row. This is for efficiency. Even returning two rows at a time is better than one, though changing to it is complex enough that you might want to return, say, 10.[7]

In both these cases the number of rows fetched is different from the number of Fetch statements and the new QXRWSFETCHD field could be compared to QXFETCH.

One possibility that immediately springs to mind is detecting when a program consistently finds no rows: That would seem unfortunate.

Another possible use is comparing the rows fetched by a program with the number of records written to a sequential data set (estimated from SMF Type 15 records).[8] That would help identify cases where the program is extracting records and directly writing them out.

While I’ve only talked about Fetch the same applies to Insert, Update and Delete. This doesn’t get us to omniscience with SMF but does give us some handy clues. While I haven’t finalised my design for reporting using these fields you can see it’s got me thinking.

---

So, as with all new data (or data I haven’t revisited in a while) I’m learning new tricks. And as I learn more I’ll continue to write about them.