Approaches to DB2 Tuning: What’s the Best Use of Your Time?

by Lockwood Lyon
July 28, 2009

Assume you’ve been given a directive by IT management to “tune” things. What strategies are available and how do you judge which ones are best? Considering the current economic climate, can any be implemented on a shoestring budget? Can any of these tasks be assigned to resources with basic DBA skills and still be productively accomplished?

A few of the many strategies for approaching and overseeing tuning efforts include:

• Tune from top to bottom. Tune z/OS (i.e., memory, dispatching priorities, WLM environment), then the DB2 subsystem, database objects (i.e., table spaces, indexes), then application systems, individual programs, and SQL. The assumption is that low-level tuning (e.g., SQL statement tuning) can’t succeed unless high-level systems are already tuned.

• Tune from bottom to top. This is the reverse of the above strategy. Start with tuning individual SQL statements, then application programs, and so forth. The assumption here is that most performance issues are due to inefficient SQL statements, poorly written applications, or poor database design.

• Tune the sacred cows. Direct all tuning efforts at those applications and systems most critical to the business.

• Tune the squeaky wheels. Spend time reacting to those customers that complain the loudest.

There are other strategies; perhaps you’re already using one or more of them. Most of these tuning strategies require higher-level DBA skills, teamwork, and coordination with other areas. However, in the current economic environment, IT management may not have the luxury of allocating sufficient DBA resources to tuning efforts that don’t produce immediate, quantifiable results. With limited time and resources you will need to focus on strategies that provide tangible benefits at a minimal cost. Goals to keep in mind are:

• Ease of cost justification. Task time and effort (and therefore cost) should be easy to estimate; results should be quantifiable.

• Little conceptual training required. No in-depth DB2 knowledge should be involved in implementing the tuning tactics.

• Short learning curve. DBAs shouldn’t have to spend a lot of time learning new functions and features or attending training.

• The presence of heuristics or best practices. There should be simple, straightforward methods of implementing tasks, including standards and best practices.

With these objectives in mind, this article takes an in-depth look at the following five tuning strategies:

• SQL tuning involves SQL review and knowledge of potential access paths, table and index data distribution, and statistics.

• Resource constraint tuning is an analysis of possible trade-offs among CPU, elapsed time, I/O, memory, network traffic, and other resources.

• Application tuning focuses attention on suites of programs that access particular tables, batch windows, and online service levels.