IBM Db2 performance tuning fundamentals for administrators

Buffer pools, indexes, RUNSTATS, the Db2 explain facility and package cache — a practical order of investigation for Db2 LUW performance problems.

Db2 performance work is mostly a search problem: find the few statements consuming the resources, then fix them. Work top-down and resist the urge to change configuration parameters first.

Step 1 — find the expensive statements

SELECT SUBSTR(STMT_TEXT,1,80) AS stmt,
       NUM_EXEC_WITH_METRICS AS execs,
       TOTAL_CPU_TIME/1000 AS cpu_ms,
       ROWS_READ, ROWS_RETURNED
FROM   TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2))
ORDER  BY TOTAL_CPU_TIME DESC
FETCH FIRST 15 ROWS ONLY;

A high ROWS_READ to ROWS_RETURNED ratio is the classic signature of a missing or unusable index.

Step 2 — make sure statistics are current

RUNSTATS ON TABLE schema.tab WITH DISTRIBUTION AND DETAILED INDEXES ALL;

Stale statistics cause bad access plans far more often than bad SQL does. Enable automatic RUNSTATS, but run it manually after large loads.

Step 3 — read the plan

db2 set current explain mode explain
db2 -tvf myquery.sql
db2 set current explain mode no
db2exfmt -d SAMPLE -1 -o plan.txt

Look for table scans on large tables, sorts spilling to disk, and nested-loop joins over large row counts. db2advis can suggest indexes and MQTs — treat its output as a hypothesis, not a plan.

Step 4 — memory and buffer pools

Enable self-tuning memory (STMM) for most workloads: DATABASE_MEMORY AUTOMATIC and buffer pools set to AUTOMATIC.
Check buffer pool hit ratios with MON_GET_BUFFERPOOL; below ~90% on a data pool usually means memory pressure or a scan-heavy query.
Separate temporary tablespaces onto fast storage if sorts spill.
Size SORTHEAP/SHEAPTHRES_SHR deliberately for reporting workloads.

Step 5 — locking and concurrency

SELECT * FROM TABLE(MON_GET_LOCKS(NULL,-2)) WHERE LOCK_STATUS = 'W';

Lock waits and deadlocks are usually application design issues: long transactions, missing indexes forcing wider locks, or the wrong isolation level. Currently Committed semantics (default in modern Db2) removes most reader-writer blocking — verify it is enabled before chasing symptoms.

Routine hygiene

REORG tables and indexes when REORGCHK flags them.
Keep automatic maintenance windows aligned with your quiet period.
Archive logs to separate storage and monitor log full events.
Track growth with MON_GET_TABLE so capacity surprises never reach production.