Diagnose a bad DB2 access path

What “bad access path” means

Not every tablespace scan is a bad path, and not every index access is a good one. A path is “bad” when measured work is unreasonable for the result size: millions of pages read for a few output rows, nested-loop joins exploding intermediate cardinality, repeated nonmatching index probes, or sorts spilling heavily to work files.

Symptoms show up as long-running SQL, high class 2 CPU in accounting, GETPAGE spikes, sync I/O waits, lock/latch time that is really “too much work,” or sudden regressions after RUNSTATS, REORG, bind, software upgrade, or data growth. Start with evidence, not with creating random indexes.

Separate three causes early: (1) the optimizer lacks good statistics or faces non-indexable predicates, (2) the schema lacks a useful index or clustering, (3) the SQL asks for more work than the business needs (over-fetch, wrong join type, missing filters). Fixes differ for each.

• Compare actual rows returned versus pages/rows examined.
• Prefer EXPLAIN PACKAGE for live static SQL; EXPLAIN PLAN FOR for candidate dynamic text.
• Check whether the problem appeared after a change (stats, data, bind, release).

Prerequisites

Identify the exact statement text, package/collection/version or dynamic cache ID, isolation, and special registers. Gather accounting or monitor data for one representative execution: elapsed, CPU, GETPAGES, rows processed.

Ensure PLAN_TABLE (and helpful DSN_* tables) exist for your performance ID. Authority to EXPLAIN PACKAGE or rebind EXPLAIN(ONLY) in the target collection is often required. Have catalog access to SYSINDEXES, SYSCOLUMNS, and statistics views your shop uses.

Know change history: last RUNSTATS, REORG, BIND, application release, and volume growth. Many “bad paths” are stale or misleading statistics after a bulk load.

Steps: diagnose systematically

Step 1 — Reproduce and measure. Capture a bad run’s metrics. Note whether the statement is static or dynamic and whether REOPT is in play.

Step 2 — Capture the path. For production static SQL, EXPLAIN PACKAGE (or use your monitor’s explain of the bound path). For a proposed change, EXPLAIN PLAN or BIND EXPLAIN(ONLY). Read PLAN_TABLE in QBLOCKNO/PLANNO order.

Step 3 — Classify the smell. Large-table ACCESSTYPE R with selective predicates; MATCHCOLS 0 on a critical filter; nested loop with a huge outer; unexpected sort; wrong join order versus selectivity; STAGE 2 predicates that cannot use an index.

Step 4 — Validate statistics and predicates. Check whether leading index columns match literal/host-variable predicates without wrapping columns in functions. Confirm RUNSTATS recency and whether histograms or frequency stats are needed for skewed data.

Step 5 — Choose the smallest fix. Often: fix SQL predicates, add or adjust an index, refresh RUNSTATS, or rebind after stats. Reserve optimization hints and profile tables for governed exceptions after simpler fixes fail.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 1) Capture live package path (conceptually)
EXPLAIN PACKAGE COLLECTION 'SALESCOL' PACKAGE 'ORDERRPT';
 
-- 2) Inspect the suspicious steps
SELECT QBLOCKNO, PLANNO, METHOD, TNAME, ACCESSTYPE, MATCHCOLS,
       ACCESSNAME, INDEXONLY, PREFETCH,
       SORTN_JOIN, SORTC_JOIN, SORTN_ORDERBY, SORTC_ORDERBY
  FROM TRAIN01.PLAN_TABLE
 WHERE PROGNAME = 'ORDERRPT'
 ORDER BY QBLOCKNO, PLANNO;
 
-- 3) Classic predicate problem: function on column defeats matching
-- Bad:  WHERE YEAR(ORDER_DATE) = 2024
-- Better: WHERE ORDER_DATE BETWEEN '2024-01-01' AND '2024-12-31'

Common root causes and fixes

Non-indexable or poorly sargable predicates: wrapping columns in SCALAR functions, mismatched data types causing transition, OR conditions that prevent matching, or leading wildcard LIKE. Rewrite predicates so indexed columns stay bare on one side.

Missing or wrong index: no index leads with the selective columns; index exists but wrong column order; index not covering enough columns so Db2 rejects index-only and prefers a scan. Design indexes from the predicate and join columns that matter, not from guesswork.

Bad or stale statistics: after LOAD REPLACE or massive growth, the optimizer still believes old cardinality. RUNSTATS (and sometimes REORG first for clustering) then REBIND or rely on dynamic prepare with fresh stats.

Join order / method blow-ups: nested loop when both inputs are large; missing filter pushed too late. Ensure early filters, correct inner indexes, and consider whether merge/hash is available and chosen after stats fix.

Over-fetching SQL: SELECT * , missing WHERE, DISTINCT used to hide a bad join. Fix the query shape before adding indexes to paper over it.

• Anti-pattern: functions on indexed columns
• Anti-pattern: correlated subqueries that should be joins
• Anti-pattern: neglecting RUNSTATS after bulk change
• Anti-pattern: creating many overlapping indexes that slow inserts without helping the query

Verify results

Re-EXPLAIN with a new QUERYNO or new EXPLAIN(ONLY) bind and confirm ACCESSTYPE, MATCHCOLS, and join METHOD changed as intended. Do not stop at PLAN_TABLE: rerun the statement under similar data conditions and compare CPU, GETPAGES, and elapsed to the baseline.

For static packages, promote the rebind through your change process only after EXPLAIN(ONLY) and controlled testing look good. Watch for access path instability: a fix that helps today but flips after every RUNSTATS may need statistical improvements, APREUSE/APCOMPARE practices, or statement-level controls your site supports.

Document the before/after PLAN_TABLE excerpts and metrics in the ticket so the next regression is faster to diagnose.

Prioritizing fixes under time pressure

When an online region is suffering, triage for the change that is safest and fastest to validate. Predicate rewrites in application code may need a release; RUNSTATS and REBIND can be faster if ops owns them; an emergency index is powerful but must be sized and reviewed for insert overhead. Pick the lever your change process can actually move tonight.

Parallel work helps: one person captures EXPLAIN PACKAGE and PLAN_TABLE narration, another pulls accounting numbers, a third checks whether yesterday’s RUNSTATS job failed. Many “optimizer bugs” are simply missing statistics after a load window.

After the immediate fire, schedule a deeper pass: clustering order, partition pruning, sparse indexes, and whether the statement should be rewritten as a join instead of a correlated subquery. The diagnose loop in this tutorial is the first hour; lasting performance work continues in design reviews.

Teach developers the top three anti-patterns you found (functions on columns, SELECT *, unbounded result sets). Preventing the next bad path is cheaper than diagnosing it at 2 a.m.

Common errors in the diagnosis process

Tuning the wrong statement: the expensive package is a different SQLCODE path or a triggered statement. Confirm STMTNO / statement text from the monitor.

Explaining different SQL than production (literals vs markers, different QUALIFIER). Align text and bind options.

Adding an index without measuring insert/update/delete cost or REORG/RUNSTATS follow-up.

Using optimization hints as the first move: hints freeze a path and can hurt when data changes. Prefer predicate, stats, and index design.

Declaring victory on EXPLAIN alone while elapsed is still dominated by locks, service class delays, or poor buffer pools—those are not access-path problems.

Comparing a dynamic EXPLAIN in a nearly empty test sandbox to production volumes and concluding the path is “fine.” Cardinality drives method choice; test data must be representative or you must reason about scale explicitly.