Administrator > DB 2

An Intuitive Approach to DB2 for z/OS SQL Query Tuning

November | December 2006 | by Lee Siegmund

Years ago, I heard a product developer describe his experience in creating a DB2* product feature by stating that "the solution was intuitively obvious after studying the problem for several years." I can make the same statement relative to tuning DB2 SQL queries - I find tuning DB2 queries intuitively obvious after having done it for many years. The intent of this article is to help make the SQL tuning process more intuitive for you without requiring years of study and practice. The sample queries used to illustrate tuning techniques are generally from customers. The column names and table names have been changed.

Several options are available when tuning a DB2 SQL query including:

Changing the query
Redesigning the database
Changing the application program
Doing something less disruptive than any of the above options

The first three options are potentially expensive, in terms of both time and computing resources. Clearly, it's preferable to not change the application, query or database design. Costly and time-consuming changes to the application environment can be avoided by strategically exploiting the DB2 optimizer.

This tuning approach is based on the observation that typically - when a query is taking more CPU or elapsed time than expected - it's probably doing more I/O than expected. The focus of query tuning is to minimize I/O, and the I/O will be minimized by exploiting the DB2 optimizer.

The RUNSTATS Utility
For an SQL query, the DB2 optimizer determines the data-access path. The DB2 optimizer requires accurate information to determine an efficient access path. The optimizer is given the necessary information by the RUNSTATS utility, which gathers summary information about the characteristics of data in DB2 tables and associated indexes. For example, RUNSTATS gathers information such as the number of rows in a table and the number of unique keys in an index on the table.

Consider a table with 1 million rows, which has two indexes defined. The first index is on Customer-Number, and it's unique (i.e., there are a million different values for Customer-Number). The second index is on Activity-Code, and it has one of two values: 'A' for active and 'I' for inactive. Half of the Customer records are active and half are inactive. RUNSTATS enables the optimizer to determine what has been noted in this example (there are 1 million rows in the table). Additionally, two indexes have potential data-access paths, but they have widely differing characteristics. With this accurate information, the optimizer is able to make determinations that provide optimal data access.

The key lesson to this tuning experience is to avoid unnecessary I/O. Frequently, many rows are read only to be discarded when the comparison of the predicated fails.

Next page: >>