Performance & Tuning Optimization

REORG is about restoring physical organization so Db2 can execute logical access paths efficiently. When table rows drift away from index key order (or become fragmented), IXSCAN and range scans can degrade into random I/O storms.

db2 reorgchk current statistics on table <schema>.<tabname>
db2 reorgchk current statistics on table <schema>.<tabname> and indexes all

-- Table indicators
SELECT tabschema, tabname, npages, fpages, overflow
FROM sysstat.tables
WHERE tabschema = '<SCHEMA>' AND tabname = '<TABNAME>';

-- Index clustering indicator
SELECT indschema, indname, cluster_ratio
FROM sysstat.indexes
WHERE tabschema = '<SCHEMA>' AND tabname = '<TABNAME>';

Predicate classification governs where (and how efficiently) Db2 can apply filters: early (Stage 1), late (Stage 2 residual), and whether an index can be used for range delimiting. This is a foundational concept in Part 4 Unit 1. :contentReference[oaicite:0]{index=0}

Db2 rewrites SQL aggressively: subquery-to-join, predicate pushdown, transitive closure, OR expansion, constant folding, and more. Therefore, analyze the optimized statement and access plan, not just the original SQL text. :contentReference[oaicite:1]{index=1}

-- 1) Ensure explain tables exist (one-time per DB)
-- db2 -tvf EXPLAIN.DDL  (or use db2exmig)

-- 2) Capture an explain for a statement
EXPLAIN PLAN FOR
SELECT * FROM <schema>.<table> WHERE <predicate>;

-- 3) Format the explain output
db2exfmt -d <dbname> -1 -o plan.txt

Beyond basic index scans, Db2 can use special access paths when predicates do not perfectly align with the leading index keys, or when multiple indexes can be combined to reduce table access.

CREATE INDEX ix_orders ON orders(region, order_date);

-- Predicate only on the non-leading key (order_date)
SELECT *
FROM orders
WHERE order_date = DATE('2026-01-01');

-- Suppose we have:
-- CREATE INDEX ix_region ON orders(region);
-- CREATE INDEX ix_status ON orders(status);

SELECT *
FROM orders
WHERE region = 'EAST'
  AND status = 'OPEN';

Db2 uses statistics to estimate filter factors (selectivity). When data is skewed, uniform assumptions fail. Distribution statistics improve accuracy: N most frequent values and M quantiles. :contentReference[oaicite:2]{index=2}

-- Database-level defaults for distribution statistics
UPDATE DB CFG USING num_freqvalues 20;
UPDATE DB CFG USING num_quantiles 50;

RUNSTATS ON TABLE employee
WITH DISTRIBUTION
ON COLUMNS (
  status NUM_FREQVALUES 50,
  salary NUM_QUANTILES 100
)
AND INDEXES ALL;

When predicates reference correlated columns, independence assumptions can cause severe misestimation. Column group statistics capture joint cardinality so the optimizer estimates selectivity correctly. :contentReference[oaicite:3]{index=3}

RUNSTATS ON TABLE carmodels
ON COLUMNS ((manufacturer, model))
WITH DISTRIBUTION
AND INDEXES ALL;

This is a practical tuning SOP you can give learners as “how a real DBA works”. It aligns with course emphasis: maintenance (RUNSTATS/REORG), explain, and indexing choices. :contentReference[oaicite:4]{index=4}

1. Confirm stats freshness: RUNSTATS age, table volatility, and whether distribution/correlation stats exist.
2. Explain the statement: capture and render with db2exfmt; focus on optimized statement + operators + cardinalities.
3. Validate predicate stages: identify residual predicates and non-sargable expressions.
4. Inspect index suitability: leading keys, clustering, and whether multi-index access is being used (IXAND/IXOR).
5. Decide: stats vs index vs REORG:
   • Wrong selectivity → improve distribution/correlation stats
   • No efficient access path → adjust indexing
   • Good plan but high I/O → check clustering / overflow → REORG
6. Re-test & compare: measure with consistent inputs; document plan changes and reasons.