I85PREP
⌘ K
IBM Db2 13 z/OS DBA Professional study materials
IBM Db2 13 z/OS DBA Professional study resources

IBM Db2 13 z/OS DBA Professional: Performance and Tuning: Access Paths, Statistics, and Plan Stability

IBM Db2 13 z/OS DBA Professional · Work through your exam, one topic at a time.

All guide sections

Performance and Tuning: Access Paths, Statistics, and Plan Stability

What this domain tests

This domain tests whether you can diagnose a slow Db2 13 for z/OS statement and change the right lever: statistics, indexes, buffer pools, parallelism, or plan stability. The exam favors reasoning about access paths and the evidence in EXPLAIN output.

Core rules and concepts

  • RUNSTATS feeds the optimizer. Collect table, column, and index statistics together, use histograms for skewed columns, and consider SHRLEVEL CHANGE when availability matters. Statistics profiles make collection repeatable.
  • EXPLAIN exposes ACCESSTYPE, MATCHCOLS, INDEXONLY, PREFETCH, and join method. A matching index scan with few matching columns and a large cardinality estimate often signals stale or missing statistics.
  • Index design balances filtering and coverage. Include columns can make an index-only access path possible; index compression saves space but changes leaf-page density. For PBR tables, decide between a partitioning index and a data-partitioned secondary index (DPSI).
  • Buffer pools and prefetch matter after the access path is sound. Check VPSEQT, DWQT, and sequential detection before adding memory.
  • Plan stability uses PLANMGMT and REBIND with APREUSE/APCOMPARE. Keep previous plans available before rebinding a critical package.

Worked example

A query filters STATUS = 'OPEN' (2% of rows) and an order-date range. The existing index on STATUS has low cardinality and EXPLAIN shows a matching scan with a large estimate. Create an index on (STATUS, ORDER_DATE), run RUNSTATS with a histogram on STATUS, and rebind. EXPLAIN should show more matching columns and, if all selected columns are in the index, INDEXONLY = 'Y'. Reasoning: the composite key narrows the range and the histogram corrects the stale selectivity estimate.

Traps that cost marks

Assuming a new index is always used; rebinding without preserving the old plan; treating RUNSTATS as a one-time task; and forgetting that parameter markers can produce a generic estimate that is poor for skewed data.

Key points

  • Diagnose with EXPLAIN before changing indexes.
  • Histograms are essential for skewed filter columns.
  • Include columns can enable index-only access.
  • PLANMGMT protects against plan regression.
  • Buffer-pool tuning follows access-path tuning.