Going Native With Stored Procedures?
MartinPacker 11000094DH Visits (368)
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.1
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.2 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
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 like3. 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:
So, there’s an economic advantage to SPs being Native. Actually it’s twofold:
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:
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” - JDBC4 - 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:
So I took away
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 work5.
So, Shall We Go Native?
“Not so fast” applies - both as an exclamation and as an expectation:
Here’s how I think you should proceed:
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.