First steps: To get started, you’ll need several things. SQL coding standards are a good start. Also useful is a “Top-50 SQL” report, which lists the 50 (or whatever number you deem best for your environment) longest-running SQL statements. This data is available in Service Management Facility (SMF) records, and can be limited and filtered by DB2 environment (i.e., development, test, stage, production), by application, or other criteria. SMF reports are described in the IBM publication, IBM DB2 Performance Expert for z/OS Report Reference (SC18-7978). Regularly run these reports and review the SQL statements. Poorly performing statements are candidates for tuning, while those already addressed can be optionally excluded from later reports.
Finally, you’ll need a process for regular Explains and reviews of production SQL statements. One common tactic for static SQL statements is to bind all plans and packages with EXPLAIN(YES), ensuring you have current access path information available. After that, develop processes for detecting production access path changes and reviewing potential performance problems.
Resource Constraint Tuning
There are always perceived resource constraints, so there are always opportunities for resource constraint tuning. Most of this tuning revolves around “trading” short-term constraints for other resource availability. The usual trade-offs are among CPU, elapsed time, I/O, and memory usage. For more on this, see “The Shifting Sands of DB2 Performance Tuning” in the August/ September 2006 issue of z/Journal.
What you analyze: Memory usage in the DB2 virtual pools, CPU usage, the I/O subsystem, application throughput, and data availability.
Using what tools: The primary tuning tools are the performance reports available from SMF records, particularly the Accounting Report. You also may use the Resource Management Facility (RMF), but that tool requires more expertise.
Where to look for the largest productivity gains: This depends on your particular resource constraints. A few common trade-offs include:
• CPU for DASD. DASD allocations for large table space objects can be reduced by implementing data compression. This occurs during table space creation by using the COMPRESS YES parameter. Most character data and some numeric data can be stored in a compressed format; this shortens table rows. The trade-off is that some additional CPU may be consumed by compression and decompression of the data; the exact tradeoffs depend on your CPU configuration.
• DASD for elapsed time. It may be possible to shorten the execution elapsed times of SQL statements by adding indexes. One way is to specify an index containing sufficient table columns to allow “index-only” access paths for some queries. Of course, the additional indexes will require more DASD space. They also may result in longer-running Reorg and Load utilities and could affect any data recovery situation.
These examples of resource constraint trade-offs are generic; they may not work in some situations and may actually hurt performance. Nothing substitutes for a complete, careful analysis of the costs and benefits of such changes.