WLM-Managed DB2 Stored Procedure Address Spaces
MartinPacker 11000094DH Comments (2) Visits (10053)
I was contacted by the team updating the SG24-7083 "DB2 Stored Procedures: Through The Call And Beyond" this past week. Their question was quite straightforward:
"One of the statements in the book, in the chapter on WLM address space management states:
To help analyze the use of resources by different types of stored procedures, you should name the server address spaces in such a way that it is clear which Application Environment they serve. With this naming convention SMF Type 30 Subtypes 2 and 3, Accounting Interval records can be used to determine the resource consumption by each server address space. These records include CPU time and virtual storage usage.
Do you have any details on what this might mean?"
As I wrote that chapter I guess I do. :-)
Here's the gist of my reply. I think it's worth sharing with you:
First a little background to what I was talking about...
There is obviously more detail one could add to this...
The program name a WLM DB2 Stored Procedures Server Address Space runs under is "DSN9XWLM". (Other types of WLM Server Address Space will have different names but the same basic "trick" will still work.)
One could also worry about Virtual Storage above and below the 16MB line in such an address space. It's a fact that some kinds of DB2 Stored Procedure are a bit of a challenge in virtual storage terms. Recall: You can write a DB2 Stored Procedure or UDF using almost any programming language or tools you'd ever want to.
If you had only one AE and one SC for work using DB2 Stored Procedures you'd not care about which address spaces were which. But the whole flippin' point of Stored Procedures is that they enable common application logic across DDF, CICS, Batch, Websphere, etc.
And finally, a tiny piece of background as to why I focused on this issue in 2003 - when the original Redbook was written...
A major customer whom I greatly admire had had some difficulties with getting nested Stored Procedures (and UDFs) to perform. This was largely down to WLM not wanting to start new server address spaces. I believe the problems are well and truly behind them. But it was a very interesting technical area to get into. And, as usual, I dug into the instrumentation to see if we could shed some light on the matter.
So that's how I came to write the chapter in the Redbook - given the 1.5 weeks IBM Global Services so graciously allowed me to spend on the project - on WLM DB2 Server Address Space Management.
And now some diligent people are working on a new version of the Redbook - and I'm pleased they're asking me "what on earth did you mean by...?" :-) questions.