DB2 object and SQL tuning techniques

Pick the lever from the symptom

Index performance

Design indexes for matching predicates on leading columns, then for screening and index-only. A three-column index (WORKDEPT, LASTNAME, EMPNO) helps WORKDEPT = ? AND LASTNAME = ?. It does little for LASTNAME alone (nonmatching scan) unless you also have a different index.

• Equality and IN on leading keys beat leading wildcards and functions on the column
• INCLUDE columns (or extra trailing keys) can make INDEXONLY = Y
• Every extra index is paid on INSERT, clustered UPDATE of keys, DELETE, REORG, COPY, and RUNSTATS
• Foreign-key and join columns are classic index candidates

Measure with EXPLAIN (MATCHCOLS, INDEXONLY, PREFETCH) and with getpages/sync reads, not with “we added an index so it must be faster.”

Clustering

The clustering index is the one REORG uses to put rows in key order. CLUSTERRATIOF in the catalog tells the optimizer how well data still matches that order. After random inserts, a once-beautiful matching scan becomes random I/O.

Choose clustering for the dominant range access (often the parent-key or a date). You get one clustering order per table (per partition, with partitioned indexes). Secondary indexes can still match; they just will not walk data sequentially.

REORG and RUNSTATS

REORG restores clustering, reclaims space, and applies pending DDL. DSNACCOX (and real-time statistics) recommend when. REORG without statistics leaves the optimizer blind to the new cluster ratio unless you take inline STATISTICS or run RUNSTATS afterward.

RUNSTATS is how the optimizer learns CARD, COLCARDF, FREQVAL, HISTOGRAM, COLGROUP, and index NLEAF/NLEVELS. After RUNSTATS, REBIND static packages (or rely on dynamic prepare) or the new numbers sit unused. Access path stability (APREUSE) may freeze an old path on purpose—know which packages you freeze.

1
2
3
4
5
6
7
8
REORG TABLESPACE HRDB.HRTS
  SHRLEVEL CHANGE
  STATISTICS TABLE(ALL) INDEX(ALL)
 
RUNSTATS TABLESPACE HRDB.HRTS
  TABLE(ALL) INDEX(ALL)
  COLGROUP(WORKDEPT, LASTNAME)
  SHRLEVEL CHANGE

SQL rewrite that actually matters

• Replace YEAR(HIREDATE) = 2010 with a range on HIREDATE
• Avoid SELECT * if you want index-only
• Keep join predicates type-compatible
• Do not OR a stage 2 predicate with an indexable one if you can UNION two indexable legs
• OPTIMIZE FOR n ROWS / FETCH FIRST when the application stops early

Rewrite is cheaper than a hint. Hints are the next tutorial’s cousin, not the first tool.

Sort performance

Sorts appear as METHOD 3, SORTN_*/SORTC_* flags, and work-file activity. They cost CPU and, when they spill, I/O. Reduce sort key length, avoid DISTINCT you do not need, use an index that already provides ORDER BY, and give the sort pool / work file database enough space. Merge join sorts are sometimes the correct join method—removing them by forcing nested loop can make elapsed worse.

RID pool and EDM pool

The RID pool (MAXRBLK) holds RID lists for list prefetch, multiple index access, and hybrid join. Failures show in Statistics/Accounting RID sections and as abandoned list prefetch (often a surprise tablespace scan). MAXTEMPS_RID allows work-file overflow.

The EDM pool holds skeleton cursor tables, DBDs, and package copies— the static package cache. Too small: I/O to load packages, CPU, and thread wait. The dynamic statement cache is sized separately (CACHEDYN, EDMSTMTC, related ZPARMs depending on version). Cache hits skip full prepare; invalidation from RUNSTATS/DDL can empty it.

1
2
3
-- Prefer parameter markers so one cached path serves many values
PREPARE S1 FROM
  'SELECT EMPNO, LASTNAME FROM HR.EMPLOYEE WHERE WORKDEPT = ?';

Literal-heavy dynamic SQL fragments the cache. CONCENTRATE STATEMENTS or host variables help. KEEPDYNAMIC(YES) keeps prepared statements across commit on a thread—useful and easy to overuse (memory, thread reuse rules).

Work file database and temporary tables

Sorts, sparse indexes, some RID lists, declared/created global temporary tables, and materializations use the work file database. Tune the number and size of work file table spaces, buffer pools for 4K/32K work files, and watch for contention when many queries sort at once (nightly batch plus parallelism).

Temporary tables (DGTT/CGTT) let you stage a working set: gather keys with a short lock duration, commit, then join the temp to other tables. Remember to drop DGTTs so DDF threads can go inactive.

CTE materialization and MQTs

A WITH clause may be merged (like a view) or materialized into a work file. Materialization is good when the CTE is referenced twice and expensive; it is bad when the CTE is huge and could have been filtered earlier. EXPLAIN shows extra query blocks / table types for materialization.

A materialized query table (MQT) is a real table that stores a query result. With the right CURRENT REFRESH AGE and maintained-table special registers, the optimizer may rewrite user SQL to read the MQT. Refresh (user-maintained or automatic in the designs your version supports) must stay current or reports go stale. MQTs are a warehouse technique; they are rarely the fix for a 2-ms OLTP transaction.

A worked mini-playbook

1. Accounting: class 2 CPU high, getpages high, few sync reads → likely scan or nonmatching index; EXPLAIN; add matching predicate/index or accept the scan if 40% of rows qualify.
2. Accounting: sync I/O high, matching index → check CLUSTERRATIOF; REORG; RUNSTATS; REBIND.
3. RID failures → MAXRBLK / stats / less RID-hungry SQL.
4. Prepare time high on dynamic SQL → cache, parameter markers, fewer invalidations.
5. Sort elapsed → index for ORDER BY, or work-file capacity.

Explain It Like I'm Five

Indexes are tabs on a notebook. Clustering is writing the pages in the same order as the tabs. REORG is rewriting the notebook neatly. RUNSTATS is counting how many pages and how messy they are so the librarian (optimizer) can plan. The dynamic cache is keeping yesterday’s instructions on a sticky note so you do not rewrite them every time. Work files are scratch paper for sorting. An MQT is a photocopied answer key for a huge homework problem you keep assigning.

Exercises

1. For one slow SELECT, list whether the bottleneck was CPU, sync I/O, or lock wait, then pick one lever from the table above.
2. Check CLUSTERRATIOF for the clustering index of a busy table.
3. Find whether a package was rebound after the last RUNSTATS.
4. EXPLAIN a query with a CTE referenced twice; note whether EXPLAIN suggests materialization.
5. On Statistics, find RID pool failures and EDM requests that waited for I/O.