WLM-Managed DB2 Stored Procedure Address Spaces

(Originally posted 2007-11-09.)

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…

You can observe the starting and stopping of WLM SP server address spaces – using SMF Type 30. Job end/step end subtypes 4 and 5. And also you can count the address spaces with a given name using the subtypes 2 and 3. This gives warm fuzzies or cold spikeys 🙂 about how the WLM-starting-and-stopping business is working out.

And, further, you can see the “weight” of the address space – in terms of (non-DB2) I/O, memory and CPU from the Type 30 records. Recall the “weight” feeds into WLM decisions about whether it can afford to start another address space that services the same queue.

Here’s why I wrote what I wrote.. Given the address spaces each serve one and only one Application Environment and one and only one WLM Service Class it would be VERY nice to be able to say things like “This AE / SC had some reluctance to start another address space because the EXISTING ones servicing this queue are too darned heavy”. To do that you need to identify the queue related to the address space your looking at. Hence a good address space naming convention is the ONLY thing that will enable you to do it.

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…?” 🙂