This section provides an overview of performance, monitoring, and tuning considerations in a DB2 for zSeries environment. For detailed explanation of the topics described in this section, refer to the DB2 UDB for z/OS V8 Administration Guide.
Before you analyze DB2 for performance problems, you should look at the overall system. In general, try to see why application processes are running slowly or why a given resource is being heavily used. The resource measurement facility (RMF) of z/OS is the best tool to use for such tasks.
If your analysis suggests that the performance problem is within DB2, follow these steps to further pinpoint where the problem is:
The EDM Statement Cache
In DB2 for zSeries, the EDM Statement cache contains the skeletons of dynamic SQL. This cache is part of the EDM Pool and is equivalent to the package cache in DB2 for LUW. To turn on the EDM Statement cache, make sure to specify YES for the field CACHE DYNAMIC SQL of installation panel DSNTIP8 (Performance and Optimization Panel) or, if you need to update its value, use panel DSNTIPB, option 19.
For information about packages, plans, and DBRMs, you can review the following catalog tables:
If a plan or package is bound with the REOPT(ALWAYS) bind option, the statement will not be saved in the cache. This option ensures that a statement is optimized with the latest statistics; therefore, DB2 for zSeries will not used the cached information.
In addition to the EDM Statement cache, the Routine Authorization cache can be used to save authorization information for SQL procedures and user-defined functions. The field ROUTINE AUTH CACHE in panel DSNTIPP can be used to indicate how much storage to allocate for this cache. If you would like to update this value, use option 22 on panel DSNTIPB.
The Performance Expert Tool
The output of your traces can be analyzed through reports generated by tools such as the DB2 Performance Expert. The DB2 Performance Expert is a licensed program that integrates the function of two other tools, the DB2 Buffer Pool Analyzer and the DB2 Performance Monitor (DB2 PM). This tool provides performance monitoring, reporting, buffer pool analysis, and a performance-warehouse all-in-one tool.
The data collected from the traces can be presented in the following ways:
When you need to obtain more information about these problems, use the following Performance Expert reports:
For stored procedures in particular, the accounting report provides the following information:
Improving Response Time and Throughput
To improve response time and throughput, you should take into account the following items discussed in the following subsections.
Buffer Pool Size
Make buffer pools as large as you can afford for your workload because they will minimize I/O. You also need to monitor the different buffer pool thresholds for best performance. The Buffer Pool Analyzer and the Statistics Report (both part of the Performance Expert tool) can help you with this.
RID Pool Size
The RID pool is used to store all record identifiers (RIDs), and is used for unique key enforcing, RID sorting during list prefetches, and so on. The DB2 for zSeries optimizer takes into account the RID pool size and may choose a table space scan if this pool is too small.
Similar to DB2 for LUW, the RUNSTATS utility in DB2 for zSeries should be run frequently to update the catalog statistics that are used by the DB2 for zSeries optimizer to calculate the best access plan. REORG and REBIND work similarly as in DB2 LUW.
PCTFREE and FREEPAGE
You can use the PCTFREE and FREEPAGE clauses of the CREATE and ALTER TABLESPACE statements and CREATE and ALTER INDEX statements to improve the performance of INSERT and UPDATE operations because free space on pages is reserved.
Distribute Your Datasets Efficiently
To avoid I/O contention, place your frequently used datasets in fast devices and across your available disk volumes to distribute the I/O load.
Create Additional Work File Table Spaces
Work file table spaces are used as temporary spaces when sort operations, joins, temporary tables, and so on are used. By creating more work files, you reduce contention among these table spaces.
When inserting records, DB2 pre-formats space within a page set as needed. The allocation amount, which is either CYLINDER or TRACK, determines the amount of space that is preformatted at any one time. Choose CYLINDER particularly if you are performing mass inserts.
Avoid Excessive Extents
Try to minimize the number of extents, as the extent size is more important to performance than the number of extents for a data set.
Maximum Number of Open Datasets
Use parameter DSMAX to control the limit of open datasets, and also the number of datasets that are closed when that limit is reached.
Resolving Excessive Wait Time or Timeouts for UDFs and Stored Procedures
Possible causes include:
Tuning Your Queries
Use Visual Explain or the BIND option EXPLAIN to obtain more information about the access path chosen by the optimizer for a given query. If you are performing a table scan, for example, make sure you have appropriate indexes or increase your RID pool size. Reviewing the Visual Explain output should provide you with useful information to determine which actions to perform to improve performance.
Giving Optimization Hints to DB2
This feature is very helpful in a production environment where you have critical queries performing well. If you want to upgrade your DB2 for zSeries code to a higher version or maintenance level, you may want to ensure that such change will not affect the access plan for your critical queries. By executing the EXPLAIN statement on these critical queries, you store their access plans in the PLAN_TABLE. After upgrading your code, if the access plan changes you can give DB2 optimization hints so that it looks in the PLAN_TABLE for the old access plan and uses it instead. There are some restrictions to optimization hints, so make sure to review the DB2 for zSeries Administration Guide, because the optimizer may not necessarily choose to use the hint.
Limit Resources for a Stored Procedure
DB2 for zSeries allows you to establish limits for stored procedures. This can be accomplished by either of these methods:
Considerations for Stored Procedures in a Distributed Environment
Consider using the COMMIT ON RETURN YES clause of the CREATE PROCEDURE statement to indicate that DB2 should issue an implicit COMMIT on behalf of the stored procedure upon return from the CALL statement. Using the clause can reduce the length of time locks are held, and can reduce network traffic. With COMMIT ON RETURN YES, any updates made by the client before calling the stored procedure are committed with the stored procedure changes.