|
DB2 stored procedure performance considerations, part two
from CCR2, Issue 4 - 2004
 |
By Ed Woods
Systems Engineer
Candle Corporation |
This article discusses the essential skills for effective stored procedure implementation. In Part One, we defined stored procedures, explained their life cycle and covered enclaves. In Part Two, we cover Workload Manager, scheduling delays, NUMTCB and language considerations, and monitoring and managing stored procedures.
DB2, WLM, and stored procedures
Workload Manager (WLM) uses service definition information specified by the systems programmer and internal monitoring feedback to check how well the system is doing in meeting specified goals. One of the strengths of WLM is its ability to dynamically measure workload performance and goal achievement. WLM performs sampling to see how well the workload is accomplishing the goals defined.
DB2 supplies several distributed thread attributes to enable WLM to classify DB2 distributed threads into different service classes, which are then associated by WLM with performance objectives. These attributes are defined through the WLM classification menus to specify the desired service classes and objectives to the WLM.
There are limitations on thread attributes, such as procedure name. Procedure name will work if the first SQL statement issued by the client, after the connect to DB2, is a call to the stored procedure. This is documented in the IBM Redbook, "OS/390 Workload Manager Implementation and Exploitation" (SG 24 5326).
WLM with OS/390 and z/OS enables the management of multiple DB2 stored procedure address spaces. This includes support for multiple JCL procedures and the ability to execute one JCL procedure multiple times. Stored procedure and user-defined function (UDF) isolation is improved by grouping related applications together in common stored procedure address spaces. Each stored procedure address space may execute one or multiple task control blocks (TCBs). The priority of the workload is set by WLM definitions.
OS/390 and z/OS offer the ability to dynamically process application workload using WLM facilities. Server address spaces may be started and stopped dynamically, as deemed necessary by WLM. As stored procedures are invoked, WLM will analyze the needs of the workload and may optionally start additional stored procedure address spaces as needed. Figure 4 shows the flow of this process. The benefit of this approach is that the priority of the DB2 workload will be connected to the priority as specified within the MVS WLM. This also means that some stored procedures may be high priority, while others may execute at a lower priority, if desired.
Figure 4
WLM manages a separate queue for each combination of service class and application environment defined to OS/390 and z/OS. To segregate workload into different server address spaces, it is necessary to define different application environments. Within each application environment, there is a separate queue for each service class. Because of this architecture, it is likely that there will often be at least as many server address spaces active as there are the combination of service classes and application environments defined. For some shops, this may add up to quite a few stored procedure address spaces running on the system.
NUMTCB considerations
Each stored procedure request must have a TCB within an available stored procedure address to execute. In addition, since stored procedures may be nested (i.e., one stored procedure may call another), each level of nested activity requires its own TCB. Stored procedure A calling stored procedure B means a second TCB is needed for execution of procedure B. This means that a given interaction may require multiple TCBs. Triggers, stored procedures and UDFs may be nested up to 16 levels deep. Nesting is not allowed between DB2-managed and WLM-managed address spaces.
As part of the setup and definition of the stored procedure address spaces, a parameter -- NUMTCB -- needs to be specified. NUMTCB literally specifies how many tasks or stored procedure requests may run simultaneously within a single stored procedure address space. NUMTCB can have a noticeable effect on stored procedure performance. Setting the number too low can result in queuing of requests and delays in the scheduling of stored procedures as the system has to start additional stored procedure address spaces. The number may not be set outrageously high, since only so many stored procedure modules may execute within an address space.
The challenge is there does not appear to be a proven scientific method to set NUMTCB effectively. Some places pick a number for NUMTCB and forget it (e.g., 30 is a popular number). The recommendation is to set NUMTCB to load balance the number of stored procedure address spaces, and to avoid address space thrashing and startup delays. Starting a new address space on z/OS is a costly process, and this is where the delays in stored procedure scheduling can become quite lengthy. The goal should be to try to balance the number of application environments and the setting of NUMTCB to allow for the necessary number of stored procedure address spaces to stay active.
Enhancements to WLM promise to help in this area, including the ability to dynamically change the setting of NUMTCB to meet the needs of the stored procedure workload. Look for DB2 V8 to exploit this enhancement to WLM.
Scheduling delays
Stored procedures may experience scheduling delays based upon the availability of stored procedure address spaces, or the availability of TCBs within these address spaces required for the procedures to execute. How many of these resources are available is specified by the DB2 system programmer using WLM facilities, Application Environments and NUMTCBs, described earlier in this article. There may be delays imparted by WLM as it queues and manages the stored procedure requests. Again, it is important to keep in mind the effect of nesting. As stored procedure requests may nest and invoke additional procedures, each request requires a TCB in a stored procedure address space to execute. Nesting increases the likelihood that there may be delays waiting for these resources for a given application invocation. Figure 5 shows a simple example of this flow and the potential scheduling delay bottleneck.
Figure 5
DB2 accounting traces provide extensive information about the elapsed time (Class 1) and IN-DB2 (Class 2) times of DB2 applications, including stored procedures. The accounting traces also include information about stored procedure scheduling delays. Typically, the stored procedure scheduling delay was reported in the DB2 accounting Class 3 wait times. However, DB2 V7 introduced a PTF, UQ77215/APAR PQ69983, which made stored procedure delay time a Class 1 counter.
While stored procedure scheduling delay can occur, the numbers shown in the accounting traces should be minimal (often in the range of 1 or 2 milliseconds). Larger numbers may indicate a potential issue. Also, look for large variations in the number, which may indicate inconsistencies in how efficiently stored procedures are being scheduled and executed.
Language considerations
The choice of language in which to write stored procedures can be one of the most important -- and most controversial -- choices a shop can make. Stored procedures may be written in a variety of languages, including COBOL, C, PL/I, Java and an SQL procedure language. The choice of language may potentially have a large effect on stored procedure resource requirements and performance. This is simply due to the inherent performance differences in different languages. For example, code written in interpreted Java will inherently require more resources than comparable code written in COBOL.
When practical, use a language that is native to the platform where the stored procedures will execute. For example, this could mean using COBOL stored procedures on the z/OS platform, even for a WebSphere-based application. The result will typically be better performance. Similarly, COBOL will probably run noticeably faster than procedures written in an SQL procedure language.
There is a trade-off when considering language choices. Newer application development methodologies, such as WebSphere, stress reusable code objects built around languages such as Java. Code reusability and multiplatform code transportability may be more important and, therefore, worth the expense of added resource usage. Sometimes compromises may need to be made.
Miscellaneous considerations
Most shops use Stayresident as an option. Stayresident means that the stored procedure application code will remain in memory, and a reload of the module from DASD may be avoided. The actual performance effect of this may vary and there may not be a significant impact in all shops. Testing and benchmarking is suggested.
Stored procedures run within the LE/370 language environment. LE provides commands and displays to show current and recommended storage settings. The RPTSTG option in Language Environment (LE) shows settings and even provides recommended settings. This option may be set as an optional parameter as part of stored procedure creation.
It is important to stay relatively current on DB2 maintenance. There have been fixes in the form of DB2 maintenance that address performance issues and also DB2 accounting data for measuring performance.
Monitoring and managing
You can use DB2 accounting and statistic traces to monitor and track important stored procedure performance metrics. You can also track information on an ongoing basis, such as the number of stored procedures called, scheduling delay times and nesting activity. Note the number of stored procedure address space starts and stops.
Also note patterns of stored procedure usage, such as counts of stored procedures called, and from what types of applications. Look for items such as repetitive stored procedure usage from batch jobs. In general, the CPU path length to call a stored procedure from batch jobs is relatively high (assume 30K instructions) vs. calling it a subroutine linked directly within an application. If the numbers appear relatively small, then the difference is not likely to be significant. However, take an example of a batch job calling a given stored procedure hundreds of thousands of times per run and the cost of repetitive stored procedure use can become quite significant.
From a DB2 accounting trace perspective, remember that stored procedures are packaged-based. Accounting Classes 7 and 8 provide application accounting information at the level of the DB2 package. Accounting Classes 7 and 8 should be on to derive the optimal level of accounting information for best performance.
Summary
Stored procedures are an important function of the DB2 DBMS. Stored procedure support has been available for quite some time, but only recently have shops begun to truly exploit their powerful capabilities. Many factors need to be considered when deploying stored procedures. WLM options, language choices, along with DB2 and SQL concerns, all may have a large impact on the performance and effectiveness of stored procedures.
|
