Every SQL statement you BIND or PREPARE is handed to the DB2 optimizer. The optimizer does not execute your English intent. It builds an internal query tree, may rewrite that tree into a cheaper equivalent, estimates how many rows survive each predicate, enumerates join orders, and picks the access path with the lowest estimated cost. This page is the map of that process for Db2 for z/OS.
A rule-based optimizer would say things like “an index is always better than a tablespace scan.” That fails when 99% of rows match STATUS = 'A' and the index would bounce randomly through almost every data page. Db2 for z/OS has used cost-based optimization (CBO) since the first release: enumerate strategies, estimate cost, choose the minimum.
Cost is a relative number used to rank plans. It is not a guaranteed elapsed time. Buffer hits, concurrent workload, and storage caching are mostly unknown at bind time. If plan A costs 500 and plan B costs 50,000, A is the winner; you should not read 500 as “500 milliseconds.”
| Component | What the optimizer approximates |
|---|---|
| I/O | Random vs sequential page reads; prefetch; index and data pages |
| CPU | Predicate evaluation, row copying, join logic, functions |
| Sort / work file | ORDER BY, GROUP BY, DISTINCT, merge join, RID lists, materialization |
| Parallelism | Degree vs sequential; more tasks can lower elapsed estimate but raise CPU |
| Communication | Distributed (DDF) queries: shipping result rows |
Sequential I/O is modeled as cheaper than random I/O, which is why clustering ratio and prefetch matter. CPU cost includes walking index keys and applying stage 1 versus stage 2 predicates. Sorts and materializations add work-file cost. Parallelism can improve the elapsed-time side of the estimate while increasing CPU.
| Phase | What happens |
|---|---|
| Parse and check | Syntax, authorization, object resolution, data types |
| Query rewrite | Predicate generation/simplification, subquery/join transforms, view merge, MQT match |
| Cardinality estimation | Filter factors × table/index stats → estimated rows per step |
| Join enumeration | Candidate join orders and methods (nested loop, merge, hybrid) |
| Access path selection | Cost each candidate (I/O, CPU, sort, parallelism); pick the lowest |
| Plan generation | Internal structures for runtime (package section or dynamic cache) |
Beginners often jump straight to “which index?” Access path selection is the last costing step. If rewrite already turned an EXISTS subquery into a join, or merged a view so a local predicate became matching, the index question is asked about a different statement than the one you typed.
1234567EXPLAIN PLAN SET QUERYNO = 1001 FOR SELECT E.EMPNO, D.DEPTNAME FROM HR.EMPLOYEE E INNER JOIN HR.DEPARTMENT D ON E.WORKDEPT = D.DEPTNO WHERE D.DEPTNO = 'A00' AND E.SALARY > 50000;
After EXPLAIN, read PLAN_TABLE for methods and indexes, DSN_PREDICAT_TABLE for how predicates were used, and DSN_DETCOST_TABLE when you need mini-plan cost comparisons.
IBM documents that Db2 sometimes modifies the internal form of SQL to improve access path efficiency. You should still write simple SQL; do not complicate a statement hoping to “trigger” a transform. Transformations are not guaranteed for any one statement.
Typical rewrite families (details on the predicate-and-query-transformations page):
MQT rewrite is cost-based after the substitution is considered: Db2 keeps the rewritten query only if its estimated cost beats the original. Stale MQT data is your responsibility; the optimizer assumes the MQT matches the base tables for matching purposes.
A filter factor (FF) is IBM’s 0–1 estimate of the fraction of rows for which a predicate is true. Selectivity is the same idea in DBA English. Cardinality is the estimated row count after applying predicates: roughly CARDF × FF1 × FF2 × … when Db2 treats predicates as independent.
For a simple equality COL = constant with uniform distribution:
123-- Conceptual formula (uniform distribution, no frequency stats) -- FF(COL = value) ≈ 1 / COLCARDF -- Example: COLCARDF = 10 → FF ≈ 0.10 → 10% of rows
Filter factor also depends on the operator (equal, range, LIKE, NE) and on the literal when frequency or histogram statistics exist. A popular status code with FREQUENCYF = 0.80 should not be costed as 1/COLCARDF if COLCARDF is 5.
Combined predicates are the danger zone. CITY = 'CHICAGO' AND STATE = 'IL' are correlated. Multiplying two independent filter factors undercounts rows and can make a nested loop look falsely cheap. COLGROUP statistics exist to fix that. Missing stats (COLCARDF = -1) force default filter factors—historically 1/25 for equality—which is a guess, not your data.
Range predicates use HIGH2KEY and LOW2KEY (second-highest and second-lowest values) to interpolate where a literal sits in the domain. Histograms improve ranges when data is clustered in bands rather than uniform.
For n tables, the number of join orders explodes. The optimizer searches a space of join sequences and, for each adjacent pair, considers nested loop, merge scan, and hybrid join when eligible. It uses join predicates to avoid Cartesian products until no join predicate remains.
Estimated cardinality of each intermediate composite drives later choices: a tiny outer favors nested loop with a matching inner index; two large ordered inputs favor merge scan; hybrid join sits between them with RID lists and list prefetch. Bad early cardinality (wrong FF) poisons the whole tree—this is why “the join method flipped after RUNSTATS” is normal, not mysterious.
Composite tables may be materialized between steps. That cost is part of the model, not an accident.
For each table in a given join position, candidates include:
The winner is the cheapest estimated combination, not the “most indexes.” A tablespace scan with sequential prefetch can beat an unclustered matching index that returns 40% of the table.
During access path selection, Db2 notices missing or conflicting statistics (for example COLCARDF that cannot reconcile with SYSCOLDIST CARDF). It externalizes recommendations:
RUNSTATS can clear SYSSTATFEEDBACK rows once the requested stats are collected. DSN_STAT_FEEDBACK is not cleaned that way. If STATFDBK_PROFILE is YES, Db2 can maintain statistics profiles in SYSTABLES_PROFILES from that feedback so USE PROFILE jobs collect what the optimizer asked for. Feedback is not a substitute for a regular RUNSTATS schedule; histogram stats on ever-increasing keys go stale fast.
Do not update catalog statistics by hand unless you understand interpolation formulas. IBM warns that invented HIGH2KEY/COLCARDF combinations produce worse plans than defaults.
You ask a grown-up to fetch snacks. A rule-based grown-up always uses the ladder because “ladders are best.” A cost-based grown-up looks at how many snacks, which shelf, and how heavy the ladder is. Query rewrite is the grown-up saying “you really meant the cookie jar next to the fridge” and then costing that shorter walk. Filter factor is guessing how many cookies match “chocolate.” If the guess is wrong, they bring a dumpster instead of a plate. Statistics are counting the cookies so the guess is not a wild one.
1. Db2 for z/OS has used which kind of optimizer since the first release?
2. What is a filter factor?
3. When does query rewrite happen relative to access path costing?
4. Default filter factor for COL = literal when COLCARDF is known and distribution is treated as uniform is:
5. Optimizer statistics feedback is written to: