SQL and Application Performance for DBAs
This domain tests access-path reasoning and the bind and SQL choices that change performance.
Core rules
- Stage 1 predicates are evaluated by Db2; stage 2 predicates are passed later. Non-sargable predicates such as functions on columns, arithmetic, or mismatched data types prevent index matching.
- Join methods include nested loop, merge scan, and hybrid. The optimizer chooses based on cardinality, clustering, and index availability.
- Isolation levels are CS, RS, RR, and UR. Currently committed and SKIP LOCKED DATA reduce contention. REOPT ALWAYS, ONCE, or AUTO lets Db2 re-optimize dynamic statements with actual values.
- Bind options matter: EXPLAIN, ISOLATION, RELEASE, DEFER, and plan management. Use PLAN_TABLE and DSN_STATEMNT_TABLE to inspect access paths and costs.
Worked example
A query filters WHERE YEAR(ORDER_DATE) = 2025 and is slow. The DBA rewrites it as WHERE ORDER_DATE >= '2025-01-01' AND ORDER_DATE < '2026-01-01'. Reasoning: the original applies a function to the column, so Db2 cannot use the index on ORDER_DATE and scans; the range predicate is indexable and stage 1.
Traps
- Applying a function or implicit cast to a column kills index matching.
- FETCH FIRST n ROWS does not necessarily reduce the optimizer's cost estimate unless OPTIMIZE FOR n ROWS is used.
- REOPT ALWAYS can consume extra prepare CPU; reserve it for skewed data.
- Cursor sensitivity and isolation affect lock waits, not just result correctness.