You are currently on IBM Systems Media’s archival website. Click here to view our new website.

MAINFRAME > Administrator > Db2

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

Real-world samples provide valuable lessons.

Real-world samples provide valuable lessons.

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.

Lee Siegmund supports customers using DB2 with IBM. Lee can be reached at siegmunj@us.ibm.com.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

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

Real-world samples provide valuable lessons.

MAINFRAME > ADMINISTRATOR > DB2

A Db2 Utilities Migration Project

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
Mainframe News Sign Up Today! Past News Letters