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

by Lockwood Lyon
July 28, 2009

A few examples:

1) Partition by date can make purging of old or unneeded data a simple matter of emptying a partition rather than executing a long row-byrow purge process; 2) Utility processing, where reorgs and recovery can occur on a partition basis; and 3) Partition by one key, cluster by another; this is table-based partitioning, introduced in DB2 V8 (see the manuals for specifics).

• Poor choice of indexes to support queries. Often, the data architect or DBA will create indexes they feel will provide good performance. However, without specific measurements of what indexes are used and how often, it’s common to end up with either too many indexes unrelated to specific performance requirements or unused indexes.

• Lack of resource consumption and resource cost metrics. DBAs sometimes spend time and resources tuning something that ends up “better” but with no way to show that they tuned the right thing.

First steps: Along with establishing clear standards for object creation and consistently applying them, the most important steps are to research and generate a cross-reference of objects accessed by plan and package, including type of access. This is commonly called a CRUD (Create, Read, Update, and Delete) matrix or, more properly, a data-to-process CRUD matrix. The CRUD matrix allows a multi-dimensional analysis of object access. One can analyze the set of processes that access one or more objects, or the objects accessed by one or more processes. This allows the analyst to understand possible points of contention and pinpoint objects whose allocation and configuration should be considered in more detail.

Systems Tuning

Systems tuning is more enterprise-based than application-based. It involves coordinating installation, configuration, and tuning of DB2 subsystems, hardware and devices, and the interface with z/OS. It also requires knowledge of some relatively complex reports, including RMF reports. A new DBA would be unlikely to be productive in this area because the work requires considerable training and experience.

What you analyze: TCP/IP, virtual pools, logging, recovery, DB2 maintenance, DB2 configuration parameters (ZParms), the Internal Resource Lock Manager (IRLM), and the z/OS interface. Additional information is available from traces that can be turned on in the DB2 environment.

Using what tools: System-related performance reports from SMF records, RMF reports, and System Modification Program/Extended (SMP/E) reports.

First steps: Disaster recovery planning, recovery standards and jobs, software maintenance strategy.

Where to Begin

No matter what areas of tuning you decide to begin with, one thing is certain— you’ll have a lot of work to do! To reduce your workload, concentrate on automating any reporting or analysis processes. A good place to begin is to set up, review, and automate your “Top-n” reports.