Going Native With Stored Procedures?

(Originally posted 2019-08-04.)

I hope the term “Going Native” isn’t considered offensive. If it is my defence is that I didn't coin the term “Native Stored Procedures” and that’s what I’m alluding to.

This post is about realising the benefit of Native Stored Procedures for DDF callers. Most particularly, assessing the potential benefit of converting Stored Procedures to Native.

It’s not been long since l wrote about DDF. (My last DDF post was in 2018: DDF TCB Revisited. ) It’s been rather longer since I last wrote about Db2 Stored Procedures: 2007’s WLM-Managed DB2 Stored Procedure Address Spaces is the latest I can find.¹

You might ask “what on earth is a z/OS Performance guy doing talking about Db2?” In “Low Self Esteem Mode” 🙂 I might agree, but I’ve always thought I could pull off “one foot in each camp” when it comes to Db2 and z/OS. “I consider it a challenge…” 🙂

Seriously, z/OS folks have quite a bit to say that can help Db2 folks, if they try. And so we come to a recent example.

We’ll get to Stored Procedures presently.² But let me build up to it.

And, yes, this post is inspired by a recent customer case. And, no, it didn’t alert me to the topic; It just was a good excuse to talk about it.

zIIP Eligibility And DDF

In principle, a DDF workload should see roughly 60% zIIP Eligibility. As I said in DDF TCB Revisited

“The line in the graph – or rather the round blobs – shows zIIP eligibility hovering just under 60% – across all the DB2 subsystems across both customers. (One of the things I like to do – in my SMF 101 DDF Analysis Code – is to count the records with no zIIP eligibility.”

That last sentence is interesting, and I don’t think I spelt it out sufficiently: For quite a while now a DDF thread is either entirely zIIP-eligible or not at all.

I glossed over something in that post – which was the right thing to do as that set of data didn’t display an important structural feature: If your DDF thread calls a Non-Native Stored Procedure (SP) or User-Defined Function (UDF) it loses zIIP eligibility for the duration of that call.

Native Stored Procedures

You can write SPs and UDFs in pretty much any language you like, and “shell out” to anywhere you like³. For example, you can write them in REXX, Java, COBOL, PL/I. And SPs and UDFs can call each other. Lovely for reuse.

These capabilities have been around for at least 20 years. More recently (but not that recently (but progressively enhanced over recent Db2 versions)) a different set of capabilities have emerged: Native Stored Procedures. You write these in an extension to SQL called PL/SQL. The term “Native” here refers to using the built-in language, rather than a programming language.

Native SPs run in the Db2 DBM1 address space – but with the caller’s WLM attributes. Non-Native SPs run in WLM Stored Procedures Server address spaces (as described by WLM-Managed DB2 Stored Procedure Address Spaces and the Redbook it references.)

For the rest of this post I shall talk of SPs – for brevity. UDFs and Triggers are generally similar.

Why Do Native Stored Procedures Matter?

As a Performance person, Native Stored Procedures matter to me because there’s a fundamental difference in zIIP eligibility:

• When a DDF thread calls a Native SP the zIIP eligibility is preserved.
• When a DDF thread calls a Non-Native SP the CPU to run the SP is not zIIP eligible.

So, there’s an economic advantage to SPs being Native. Actually it’s twofold:

• The obvious one of zIIP CPU having lower software (etc) costs than GCP CPU.
• When work switches between zIIP eligibility and non-eligibility the work doesn’t get undispatched on one processor and redispatched on another immediately. However, if it does – for any reason – get redispatched its eligibility is checked. That could well cause redispatch on a different type of processor. Of course that’s a different processor – with the potential for cacheing loss.

In any case, we’d want to maximise zIIP eligibility, wouldn’t we?

A Case In Point

I said early on in this point I have a real customer case to discuss.

I did my standard “let’s see how much GCP and how much zIIP this Db2 subsystem uses” analysis. The results were emphatically not 60% zIIP eligible, 40% ineligible.

I examined this at three levels:

• DDF Service Class, using SMF 72-3 Workload Activity data.
• DIST Address Space, using SMF 30 Interval Independent Enclave data.
• Db2 DDF Application, using SMF 101 Db2 Accounting Trace data.

They all agreed on transaction rates, GCP CPU, and zIIP CPU.

The customer has 9 subsystems, across 3 LPARs. These are not cloned subsystems, though the same application “style” – JDBC⁴ – dominated the DDF traffic in all cases.

Of these 9 subsystems only 1 approached a 60%/40% split. The rest varied up 90% or so GCP.

Db2 Accounting Trace is helpful here – if you examine Class 2 CPU times:

• You get overall TCB time on a GCP [1]
• You get overall zIIP CPU time [2]
• You get Stored Procedures CPU time [3]
• You get UDF CPU time [4]

So I took away [3] and [4] from [1]. I compared the result to [2]. With this adjusted measure of GCP CPU Time I got extremely close to 60% being zIIP eligible – for all 9 subsystems.

As an aside, Stored Procedures Schedule Wait and UDF Schedule Wait time buckets can tell you if there are delays in starting the relevant server address spaces. In this data it was notable that the Test Db2 subsystems were more prone to this than Production. As the machines ran pretty busy it’s good to see the delays being directed to less important work⁵.

So, Shall We Go Native?

“Not so fast” applies – both as an exclamation and as an expectation:

• Because these are generally comprehensive application changes it’s going to take a while to convert.
• It might not even be worth it – at least not for 100% of Non-Native SPs.

Here’s how I think you should proceed:

1. Figure out how much of a problem this is (and you’ve seen that above).
2. Use Db2 Accounting Trace at 2 levels to establish which moving parts might be worth reworking:
   • IFCID 3 would show you which applications, including client computer.
   • IFCID 239 would show you which package burns the most CPU, and whether it’s a SP, UDF, or Trigger.
3. Assess – for each of the “high potential” SPs – which applications call them. Of course, only some of them will benefit – as SPs can be called from a wide variety of non-DDF applications as well as DDF ones.

If you carry out the above steps you should see whether it’s worth it to convert to Native.

One final word of caution: Many Non-Native SPs can’t be converted to PL/SQL so any discussion with your Db2 and Applications colleagues need to be sensitive to that.