More On Native Stored Procedures

(Originally posted 2019-08-25.)

This follows up on Going Native With Stored Procedures?, and it contains a nice illustrative graph.

I could excuse a follow up so soon with the words "imagine how unreadably long the post would be I had written this as all as one piece”.

However, the grubby truth is I got to write some code I didn’t expect to just yet. And this post became possible as a result.

Where Were We?

The gist of the previous post is:

• Non-native stored procedures aren’t zIIP eligible, regardless of their caller’s eligibility.
• You can see the level of zIIP eligibility for DDF and note if it looks less than you might expect or desire.

And that’s where we (and my code) left it. A useful place to be but not the best that could be done.

The New Code

So, for the very same study as in Going Native With Stored Procedures?, I began to dig, and to teach my code new tricks.

I wanted to know more about the stored procedures that weren’t zIIP-eligible.

To do that would take processing of package-level Db2 Accounting Trace (SMF 101).

Package-Level SMF Records

There are two types of SMF 101:

• IFCID 3 – Plan-Level Accounting
• IFCID 239 – Package-Level Accounting

Each IFCID 239 record contains up to 10 QPAC Package Sections[1], each describing one package name. I emphasise “name” because you don’t get one each time you call a stored procedure with that name. Instead you get one section for all calls that invoke that package.

My Old Package-Level Code

To be honest, I stopped earlier with developing my package-level code than I wanted to. The code takes an IFCID 239 record and “flattens” it, with each Package Section placed in a fixed position in the output record.

So, I have records still with up to 10 package names in them.

If you’d sent me Package-Level Accounting for a study involving DDF my code flattened the records and left it at that. There was no reporting.

My New Package-Level Code

Now I have code that takes a 10-package record and turns it into 10 1-package records[2]. I also have some reporting.

The interesting bit is the reporting.

I create a CSV file, sorted by subsystem and, within that, GCP CPU. Each line is a separate Correlation ID and Package combination. There is a nice field in the QPAC section (QPACAAFG) which says which type of package it is. Here are the values QPACAAFG can take.

Code	Package Type
X’0000’	Regular package
C’01’	Non-Native Stored Procedure
C’02’	User-Defined Function (UDF)
C’03’	Trigger
C’04’	Natived Stored Procedure
C’05’	Inline UDF

Notice anything strange about the above table?

I think the X’0000’ is odd but it’s probably indicating “this field not filled”.

In any case you can readily tell what kind of package we have.

When I import the CSV file into Excel (and hold my nose) 🙂 I get a useful spreadsheet[3]. I define a field which is the concatenation of the package type and its name. That enables me to produce a nice graph. Here’s an example.

It shows CPU seconds – both GCP and zIIP – for the 20 packages with the biggest GCP CPU.

I’ve obfuscated all but one package name. We’ll get to that. But here are some observations:

1. This package is a non-native stored procedure. There is very little zIIP eligibility. (Why there is any zIIP eligibility is unclear.)
2. This package is a native stored procedure. It has about 60% zIIP eligibility.
3. This package is well known – SYSLH200 – so I’ve not obfuscated it. It also shows about 60% zIIP.
4. This is a second native stored procedure – again showing good zIIP eligibility.

Most of the rest of the packages are non-native stored procedures, with a couple of native ones. The zIIP usage is much as you would expect.

Let’s return to SYSLH200. It’s a Db2-supplied default package. You see it a lot. In a different subsystem it’s – by miles – the biggest package. This would be entirely normal for a subsystem that wasn’t a heavy user of explicit DDF packages, such as stored procedures.

Here’s a table of such package names:

Package Name	Description
SYSSHxyy	dynamic placeholders – small package WITH HOLD
SYSSNxyy	dynamic placeholders – small Package NOT WITH HOLD
SYSLHxyy	dynamic placeholders – large package WITH HOLD
SYSLNxyy	dynamic placeholders – large package NOT WITH HOLD

Now I can spot them I’ve added this table to my bank of standard slides to use in customer engagements. I’ll add other well-known package names over time.

For this subsystem it’s clear the customer has some familiarity with Native Stored Procedures, whether accidentally or intentionally. (It’s possible the native ones were supplied by a vendor and the install script set up the Db2 Catalog to enable them.) But this customer has some way to go – assuming the remaining major stored procedures can and should be converted to native.

Conclusion

This code is going into Production – Just as soon as I can find the time to convert the JCL to an ISPF File Tailoring skeleton.

But I’m already using it so it is in a sense in Production.

So if I ask you for package-level Db2 Accounting Trace this will be one of the good uses I’ll put it to.

The Journey Continues

I’m sure there’ll be “another thrilling instalment” 🙂 in the DDF saga. I just have no idea right now what what it would be.

Stay tuned. 🙂