DB2 Version 8 z/OS Stored Procedures and Workload Manager: Realizing the Synergy and Planning for Migration
Transactions can also span multiple address spaces. Without enclaves, workload can be managed only by address space. This benefit gives great flexibility to setting application and stored procedure priorities. The priorities can be set at the level of caller or the level of stored procedure address space. Priorities can also be established for specific applications, users, or stored procedures.
Synergy and Performance: Stored
Procedures and WLM WLM-established address spaces provide multiple isolated environments for stored procedures. This is highly beneficial because failures need not affect other stored procedures, as they can in a DB2-managed Stored Procedure Address Space (SPAS). WLM-established address spaces also reduce demand for storage below the 16MB line. This removes a limitation on the number of stored procedures that can run concurrently within an address space. With WLM, the stored procedures also inherit the dispatching priority of the DB2 thread that issues the CALL statement.
This allows high-priority work to have its stored procedures execute ahead of lower-priority work and their stored procedures. In a DB2 established address space, prioritization of stored procedures is impossible and you are limited by storage in the address space. There’s also no separation of the work by dispatching priorities, so high-priority work could potentially be penalized.
Stored procedures using WLM also can have static priority assignment and dynamic workload balancing. High-priority stored procedures in WLM achieve highly consistent response times and WLM provides dynamic workload balancing and distribution. WLM routes incoming requests to the stored procedure address space that’s the least busy, or starts new address spaces, if required. Once this occurs, the actions are fully automatic and don’t require monitoring, tuning, or operator intervention.
Another benefit that WLM-managed address spaces can provide is better options for stopping runaway stored procedures. If a procedure is looping outside DB2’s control, we have different options for regaining control. If we’re using DB2- managed SPAS, then we have to cancel the address space. This isn’t a good option because we affect everything running in that address space.
With a runaway stored procedure in a WLM-managed application environment in Goal mode, we have the option to refresh the environment, which will halt all address spaces running under that environment, start a new address space, and route all new requests to the new address space. Once we’re assured that all normal work in the address space containing the runaway stored procedure has finished, we can cancel that address space. This completely isolates all other stored procedures from failure. With compatibility mode, we can only cancel the address space and start the address space.
There’s an option on the stored procedure itself that can help with runaway stored procedures: the ASUTIME parameter. Stored procedures are normally designed for high-volume online transactions and the parameter lets us limit the resources stored procedures use. The value for ASUTIME is stored in the ASUTIME column in the SYSIBM.SYSROUTINES catalog table.
By setting the ASUTIME, we let DB2 cancel stored procedures that are in a loop. This approach is helpful for handling runaway stored procedures. DB2 checks for overages on ASUTIME every 20 seconds. This isn’t a strict control on how much CPU time a stored procedure can use. This is where WLM is also beneficial. Since WLM lets us establish priorities and service goals, we have an additional mechanism for tight control of system resource usage. By using both ASUTIME and WLM priorities and goals, we gain total control of stored procedures in this environment.