How can I keep DB2 for z/OS stored procedures address spaces (SPAS) that are managed by Workload Manager (WLM) from taking over the system?
Questions and answers are presented about stored procedures that are managed by Workload Manager (WLM).
Q1. On the Workload Manager (WLM) application environment panel, the options for the number of address spaces are: 'no limit', '1 per system', and '1 per sysplex'. I want to be able to specify a different number to keep the DB2 stored procedures workload from taking over our z/OS system with too many address spaces. Which one should I choose?
A1. There are frequent misconceptions regarding the meaning behind the term 'no limit'. IBM is changing the language on this panel to the term "managed by WLM" to more accurately reflect how it works. WLM uses the service goals of all of the work running on the z/OS® system, as well as the current utilization of key system resources, when deciding when and how many stored procedures address spaces need to start.
You should also realize that even a small number of address spaces can "take over the system" if the stored procedure applications are allowed to loop out of control. That's why there is an ASUTIME LIMIT specification on every DB2 stored procedure and UDF definition.
Q2. How does WLM decide when to start another address space? Does every concurrent stored procedure and UDF request need a WLM-SPAS task, so a sudden spike in stored procedure calls could cause many address spaces to start?
A2. No, a sudden spike in stored procedure calls would not cause many address spaces to start. WLM determines periodically whether the work for a service class is meeting the goals that you set, and whether the work queues are piling up. WLM then examines the system resource utilization as part of the algorithm to decide if another address space should be started. In many cases, a spike in incoming work is handled by existing WLM-SPAS. Since the work is queued by DB2 and the WLM-SPAS, TCBs take work off the queues. This makes the number of "concurrent" stored procedures or UDFs that are handled by a single address space significantly higher, in most cases, than the NUMTCB value that you set.
Prior to DB2 ® for z/OS Version 8, DB2 always starts the number of tasks specified by NUMTCB when a WLM-SPAS address space starts. Beginning in Version 8, the NUMTCB specified becomes a maximum value. WLM will tell DB2 how many tasks to start when the address spaces comes up, and WLM can tell DB2 to add and remove tasks in an existing WLM-SPAS. Because the time to add tasks to an existing address space is generally less than the time to start a new WLM-SPAS, this allows DB2 and WLM to be more responsive when there are spikes in a workload.
Q3. If WLM does start too many WLM-SPAS address spaces, how can I stop them and regain control of my system?
A3. First of all, this is a 'should not happen' scenario, so if you get a chance during all of this, please take a console dump of the WLM system address space and report a problem to IBM.
To shut down your workload, you can issue a DB2 -STOP PROC, a -STOP FUNC SPEC command with ACTION(REJECT), or both, either for all procedures and UDFs, or for the ones that are in the application environment that seems to have the problem. This will prevent new work from being queued to WLM. You can also cancel the threads that are queued with the DB2 -CANCEL THD command. WLM should begin shutting down the address spaces that are not required in about five minutes.
For more drastic measures, the V WLM,APPLENV=xxx,QUIESCE command will also shut down the address spaces, but please be aware that this has sysplex scope. Also, if you cancel the WLM-SPAS to get rid of them, please make sure you have the PTF for APAR PQ11902 applied, since this will help to ensure DB2 stability for that cancel. Be aware that if you cancel the WLM-SPAS instances too quickly after they are started by WLM the application environment may be put into STOPPED state and need to be resumed.
Q4. I still want a high level of control over how may WLM-SPAS can be started. How can I do that?
A4. If you really want to, you can use manual control. As stated in the WLM manual,
"Application environments can be manually controlled by the installation or automatically controlled by workload management."
All of the applications in an application environment are supported by a single JCL startup procedure. Defining the name of this startup JCL procedure to workload management indicates that workload management should control the server address spaces. This is called "automatic" control. If you omit the name of the JCL procedure in the application environment definition, then "manual" control is in effect.
Under "manual" control, the installation must create and delete, as needed, the server address spaces for each application environment. Note that the VARY WLM,APPLENV command can be used to terminate manually started server address spaces (through the quiesce or refresh options), but it will not restart them.