Access paths, EXPLAIN, indexes and statistics

How an access path is chosen

For each statement the optimizer considers many ways to get the data: which index to use, if any, which table to read first in a join, which join method, whether to sort. It estimates the cost of each using statistics in the DB2 catalog and picks the cheapest. The result is the access path.

For static SQL the choice is made at bind time and stored in the package; it does not change until the package is rebound. For dynamic SQL it is made at prepare time, and the result can be reused from the dynamic statement cache.

EXPLAIN

EXPLAIN writes the optimizer's choices into tables you own, chiefly PLAN_TABLE, with others such as DSN_STATEMNT_TABLE for cost estimates. You can run EXPLAIN PLAN FOR a statement, or bind with EXPLAIN(YES) so every statement in the package is recorded. Visual tools from IBM and vendors such as BMC and Broadcom present the same data graphically.

QUERYNO QBLOCKNO PLANNO METHOD TNAME     ACCESSTYPE MATCHCOLS ACCESSNAME INDEXONLY
     10        1      1      0 ACCOUNT   I                  2 ACCTIX1    N
     10        1      2      1 TXNHIST   I                  1 TXNIX2     N

Read it as: first access ACCOUNT through ACCTIX1 matching two columns, then join TXNHIST with a nested loop join through TXNIX2. A row showing R on a large table, or MATCHCOLS 0 on an index you expected to match, is usually where tuning starts.

Indexing strategy

• Match the predicates. Put columns used with equals first, then a range column. An index on (BRANCH, ACCTNO) matches WHERE BRANCH = ? AND ACCTNO BETWEEN ? AND ? on both columns.
• The clustering index sets the physical order DB2 tries to keep rows in. Choose it for the most important range or sequential access, often the way batch reads the table.
• Index-only access avoids reading the table. Adding a frequently selected column to an index (or using INCLUDE columns on a unique index) can help hot queries.
• Every index has a cost. Each insert, delete and key update must maintain every index, and every index needs space, REORG and recovery. Unused indexes are pure overhead.
• Write indexable predicates. Functions or arithmetic on a column, such as WHERE YEAR(TXNDATE) = 2026, often stop an index from matching. Rewriting as a date range lets it match. Expression-based indexes are an alternative.

Statistics and RUNSTATS

The RUNSTATS utility reads tables and indexes and records statistics such as row counts (CARDF), number of distinct key values (FIRSTKEYCARDF, FULLKEYCARDF), index levels and leaf pages, and cluster ratio. It can also collect frequency and histogram statistics for skewed columns, where a few values account for most rows.

Many sites run RUNSTATS after REORG or large LOADs and drive it from real-time statistics (counters DB2 keeps in catalog tables such as SYSTABLESPACESTATS), sometimes using the DSNACCOX stored procedure to recommend which objects need attention. Stale statistics, for example from when a table was nearly empty, are one of the most common causes of bad access paths.