• Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

Comments (8)

1 localhost commented Permalink

Peggy - I opened a ticket with IBM and asked a couple of questions and received some excellent answers. However, one thing that I was hoping for was a way to tell if we are going over the 512 entry limit in the PIPI table. In one of our environments we have over 2500 SP's defined to one WLM_ENVIRONMENT. The IBM LEE guys sent me a very detailed description but didn't think there was any way to get a count of what's in this table at any given time. Before I give up on this, do you have any suggestions regarding tracing or monitoring how often we overrun this table? Thanks

2 localhost commented Permalink

Hey Bill. The only way I can think of is to watch the I/O in the address space. If you see the load modules getting loaded each time, that indicates that the table is full.

If you were to create a requirement, what would you consider a successful solution? A trace you could turn on, and it would trace the first time in each address space instance, or every time it ran into the table full condition? Or a console message? What would you do if you observed it?
Another suggestion might be for you to manage which SPs are STAY RESIDENT YES, and then keep the entries in the table for the 512 invoked most often.

3 localhost commented Permalink

Thanks Peggy - I'm thinking that some sort of report that could be run off of SMF records would be the best. However, I don't know if this is possible. While DB2 uses WLM to run Stored Procedures, I don't know if WLM cuts any SMF data that could be used for this analysis. A trace might be too intrusive in a production environment but would be my second choice if we could turn it on and off at will. Console messages would be my third choice. My initial goal would be to determine if we do have a problem. If we do, we would probably create new WLM environments so that we can better distribute our Stored Procedures.

4 localhost commented Permalink

WLM does cut SMF records for the work they do in queuing requests and determining when to start another address space, but WLM isn't in this load module path at all. In this case it's really all up to DB2. I'll bring this up for consideration with our administration console folks since it's kind of a "health check" item that they might want to expose.

Thanks for the suggestion!

5 localhost commented Permalink

Hi Peggy,

What will happen to loadmodules called from within a stayresident defined stored procedure ? How long are those kept in memory or put another way what triggers a reload of them ?

6 localhost commented Permalink

Hi Will,

If you are running PROGRAM TYPE MAIN, those subprograms are removed from memory every time the SP is called.
PROGRAM TYPE SUB helps them stay in storage, but it means that the programmers have more responsibility to initialize their program variables every time. Also, I'm told that use of COBOL dynamic call may cause the subprograms to be deleted from storage after they are called.

7 localhost commented Permalink

Peggy, we have learned alot from reading your blog and appreciate you posting it. Our customer has a couple questions and we were hoping you could help.

1. What is the limit for the number of procedures defined with STAY RESIDENT YES in the same WLM environment? We assume there are virtual storage considerations for the region. Are there any mechanisms by which this can be monitored as we gradually increase the number of procedures that are STAY RESIDENT? Based on your blog, 512 is the limit.
2. The Stored Procedure Redbook says that STAY RESIDENT YES or NO have no impact on programs called by a stored procedure, which we do prevalently. Thus we are concerned that our effort to implement STAY RESIDENT YES will have an underwhelming impact on the I/O rate, if any. Are there any recommendations for techniques to allow the called modules to remain resident as well?

8 localhost commented Permalink

Hi Michele,

1. 511 is the hidden architectural limit, and DB2 will just silently stop keeping over 511 in storage. You're right, there will be virtual storage constraints, too, and of course the number of load modules you can keep in storage is going to vary with the size of the modules. I believe that RMF or other monitoring tools can be used to monitor storage usage.
2. If you see the comment above yours, PROGRAM TYPE SUB will help those called modules stay in storage.
It's also worth noting that a WLM-SPAS will come and go as needed and so you'd have to be invoking the STAY RESIDENT stored procedures during the life of the address space. All but the last one for a WLM environment will go away after 5 minutes of inactivity, and the last one will go away after an hour.

Add a Comment Add a Comment