Chapter 6: DB2 Optimizer Internals

Understanding Access Paths, Filter Factors, and Why Your Query Changed Plans Overnight

"The optimizer doesn't care about your intentions. It cares about statistics. And when the statistics change, the plan changes. That's not a bug — that's the design." — Lisa Tran, DBA, Continental National Bank

6.1 Why Your Query Changed Plans Overnight — The Production Incident That Teaches the Lesson

It was 3:47 AM on a Tuesday when Rob Calloway's phone rang.

The nightly batch cycle at Continental National Bank — the one that reconciles 500 million daily transactions across four LPARs — had blown past its SLA window. Job ACCTRCN7, which normally completed in 22 minutes, had been running for 88 minutes and showed no signs of finishing.

Rob called Lisa Tran.

"What changed?" Lisa asked, already pulling up her laptop.

"Nothing," Rob said. "No code changes. No DDL. No new volumes. Same JCL we've been running for eighteen months."

Lisa logged into the production DB2 subsystem and ran the first command every experienced DBA runs:

SELECT QUERYNO, PROGNAME, METHOD, ACCESSTYPE, MATCHCOLS,
       ACCESSNAME, INDEXONLY, SORTN_JOIN, SORTC_JOIN,
       TSLOCKMODE, PREFETCH
FROM   PLAN_TABLE
WHERE  PROGNAME = 'ACCTRCN7'
  AND  QUERYNO = 15
ORDER BY QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;

What she found stopped her cold. Query 15 — the big reconciliation join between DAILY_TRANSACTIONS and ACCOUNT_MASTER — had changed access paths. Where it had been using a matching index scan on IX_DTRAN_ACCTDT with four matching columns and a nested loop join, it was now performing a tablespace scan on the 180-GB DAILY_TRANSACTIONS tablespace with a merge scan join.

Nothing had changed — except RUNSTATS had run on Sunday night as part of the weekly maintenance window.

🔍 DIAGNOSTIC INSIGHT: When a query that hasn't been modified suddenly changes performance, the first question is always: "When did RUNSTATS last run?" The second question: "What statistics changed?" The optimizer makes decisions based on catalog statistics. Change the statistics, change the plan.

This is the production incident that will frame our entire chapter. By the time you reach Section 6.7, you'll understand not just what happened but why it happened, how Lisa fixed it, and — most importantly — how to prevent it from happening to you.

The Fundamental Truth

Here is the threshold concept that separates junior COBOL/DB2 programmers from architects:

🚪 THRESHOLD CONCEPT — Cost-Based Optimization: DB2 does not execute your SQL the way you wrote it. It evaluates dozens to thousands of possible access paths, estimates the cost of each one using catalog statistics, and picks the cheapest. Your SQL is a declaration of what you want, not an instruction for how to get it. When the statistics that feed those cost estimates change — after RUNSTATS, after mass inserts or deletes, after REORG — the optimizer may choose a completely different plan. This is not a flaw. This is the architecture. And once you internalize it, everything about SQL performance diagnosis clicks into place.

This understanding transforms your relationship with DB2. You stop asking "Why didn't DB2 use my index?" and start asking "What did the optimizer's cost model see that made it think a tablespace scan was cheaper?" Those are profoundly different questions.

The Cost of Not Understanding

At CNB, we've tracked optimizer-related incidents over the past five years. The numbers tell the story:

Every single one of these incidents could be diagnosed faster — or prevented entirely — by someone who understands the optimizer internals we cover in this chapter.

6.2 Inside the Optimizer — How DB2 Decides to Access Your Data

The DB2 optimizer is the most sophisticated component of the DB2 engine. It takes your SQL statement — that declarative request — and transforms it into an executable access plan. Let's walk through exactly how.

The Optimization Pipeline

When DB2 processes a SQL statement (at BIND time for static SQL, at first execution for dynamic SQL), it passes through these stages:

1. Parse — Syntax validation, object resolution
2. Semantic check — Authorization, data type compatibility
3. Query rewrite — Predicate pushdown, subquery transformation, view merge
4. Access path selection — This is where the optimizer lives
5. Code generation — Build the executable section

Stage 4 is our focus. The optimizer performs these sub-steps:

Step A: Enumerate candidate access paths. For each table reference in the query, DB2 identifies every possible way to access the data — every index, tablespace scan, and for joins, every join sequence and method.

Step B: Estimate filter factors for each predicate. This is where catalog statistics become critical. DB2 estimates what fraction of rows each predicate will eliminate.

Step C: Estimate cost of each candidate. Using filter factors, catalog statistics (cardinality, page counts, cluster ratios), and the DB2 cost model, the optimizer calculates the estimated cost of each access path in units called timeron (a dimensionless cost unit).

Step D: Select the lowest-cost plan.

💡 ARCHITECT'S NOTE: The optimizer doesn't find the optimal plan. For complex queries with many table references, the search space is too large. It finds the best plan it can within the optimization budget. This is why very complex queries sometimes get worse plans than simpler reformulations — the optimizer ran out of time exploring the search space.

Filter Factors — The Heart of Cost Estimation

A filter factor (FF) is a decimal between 0 and 1 representing the estimated fraction of rows that will satisfy a predicate. If a table has 10 million rows and a predicate has a filter factor of 0.01, the optimizer estimates the predicate will return 100,000 rows.

How DB2 calculates filter factors depends on the predicate type:

Equal predicates (COL = value): - If uniform distribution is assumed: FF = 1 / COLCARDF (column cardinality) - If COLVALUE frequency statistics exist for that value: FF = FREQUENCYF for that value - If a histogram (QUANTILEF) exists: FF is interpolated from the distribution

Range predicates (COL > value, COL BETWEEN v1 AND v2): - Uses HIGH2KEY, LOW2KEY, and histogram statistics - FF = (HIGH2KEY - value) / (HIGH2KEY - LOW2KEY) for COL > value

LIKE predicates: - COL LIKE 'ABC%' — DB2 can use range estimation on the literal prefix - COL LIKE '%ABC%' — no leading literal, FF defaults to a built-in estimate (often 1/10 for LIKE with leading wildcard)

IN-list predicates: - FF = N * (1 / COLCARDF) where N is the number of values, capped at 1.0

Compound predicates: - AND: FF = FF1 * FF2 (assumes independence) - OR: FF = FF1 + FF2 - (FF1 * FF2)

⚠️ CRITICAL WARNING — The Independence Assumption: DB2 assumes predicates are statistically independent. If your WHERE clause says STATE = 'CA' AND CITY = 'LOS ANGELES', DB2 multiplies the filter factors, potentially underestimating the result by orders of magnitude because cities are correlated with states. This is the single most common source of optimizer misestimates. We'll address mitigation strategies in Section 6.6.

Let's make this concrete with Lisa Tran's investigation. The reconciliation query had two key predicates on DAILY_TRANSACTIONS:

WHERE  DT.TRAN_DATE = :WS-PROCESS-DATE
  AND  DT.ACCT_STATUS IN ('A', 'P', 'H')

Before Sunday's RUNSTATS: - COLCARDF for TRAN_DATE: 365 (one year of data) → FF = 1/365 = 0.00274 - COLCARDF for ACCT_STATUS: 6 → FF for IN-list = 3/6 = 0.50 - Combined FF: 0.00274 * 0.50 = 0.00137 (estimated ~685,000 rows out of 500M)

After Sunday's RUNSTATS — which captured a data migration that had temporarily loaded three years of historical data: - COLCARDF for TRAN_DATE: 1,095 (three years) → FF = 1/1095 = 0.000913 - Combined FF: 0.000913 * 0.50 = 0.000457 (estimated ~228,000 rows)

The lower estimate made the optimizer think a tablespace scan with sequential prefetch would beat the index path, because DB2's cost model factors in the cost of random I/O for index access versus sequential I/O for tablespace scans. When the estimated result set shrank, the crossover point shifted.

🧩 CONNECTING CONCEPT: Remember from Chapter 1 how DB2 operates as a z/OS subsystem with its own address spaces (DBM1, MSTR, DIST)? The optimizer runs in the DBM1 address space during BIND or dynamic SQL prepare. The cost model it uses is calibrated to the specific z/OS LPAR configuration — CPU speed, I/O subsystem characteristics, buffer pool sizes. This is why a plan that's optimal on your test LPAR might not be optimal on production. The optimizer knows the hardware.

Matching Columns vs. Screening Columns

When DB2 uses an index, not all predicates contribute equally. This distinction is critical:

Matching columns are consecutive leading columns of the index key that have equality or range predicates that DB2 can use to navigate the B-tree. They determine the entry point and exit point in the index.

Screening columns are non-leading (or non-consecutive) columns in the index that DB2 evaluates after positioning in the index but before accessing the base table. They reduce the number of RID (Row ID) lookups but don't narrow the B-tree search.

Example. Consider an index defined as:

CREATE INDEX IX_DTRAN_ACCTDT
  ON DAILY_TRANSACTIONS (ACCOUNT_ID, TRAN_DATE, TRAN_TYPE, AMOUNT)

Query predicates:

WHERE ACCOUNT_ID = :WS-ACCT-ID      -- Matching col 1
  AND TRAN_DATE >= :WS-START-DATE    -- Matching col 2 (range ends matching)
  AND TRAN_TYPE = 'CR'               -- Screening (after range, no longer matching)
  AND AMOUNT > 10000.00              -- Screening

MATCHCOLS = 2 in the PLAN_TABLE. TRAN_TYPE and AMOUNT are evaluated against the index key values but don't narrow the B-tree traversal. If TRAN_DATE had been an equality predicate, MATCHCOLS would be 3.

💡 DESIGN PRINCIPLE: When designing composite indexes, order the columns so that equality predicates come first, then the most selective range predicate. Every column after the first range predicate can only be a screening column, never a matching column.

The Cost Formula (Simplified)

DB2's actual cost formula is proprietary, but the conceptual model is:

Cost = (CPU_cost) + (I/O_cost)
     = (instructions_per_row * estimated_rows * CPU_factor)
       + (sync_IO * sync_IO_cost + seq_prefetch_pages * seq_IO_cost
          + list_prefetch_pages * list_IO_cost)

Key insight: random I/O is dramatically more expensive than sequential I/O, even on modern storage. A single random 4K page read might cost 40x what a single page within a sequential prefetch stream costs. This asymmetry drives many optimizer decisions:

• With high CLUSTERRATIO, index access gives near-sequential I/O → index preferred
• With low CLUSTERRATIO, index access means random I/O → tablespace scan with sequential prefetch may win even for small result sets

Let's quantify this for the CNB environment. DAILY_TRANSACTIONS has 500 million rows across approximately 45 million 4K pages (180 GB). The reconciliation query estimates 685,000 qualifying rows.

Index access cost (CLUSTERRATIO = 95%):

Pages to fetch = 685,000 * (1 - 0.95) random + 685,000 * 0.95 sequential
               = 34,250 random I/O + 650,750 sequential I/O
Random cost:      34,250 * 4.0 ms  = 137,000 ms
Sequential cost:  650,750 * 0.1 ms = 65,075 ms
Total I/O cost:   ~202,000 ms (~3.4 minutes)

Tablespace scan cost:

Pages to read = 45,000,000 (all pages, sequential prefetch)
Sequential cost: 45,000,000 * 0.1 ms = 4,500,000 ms (~75 minutes)

Index access wins by a factor of 22x. But watch what happens when we inflate the row estimate (as happened with the bad statistics):

Index access cost with estimated 228,500 rows:

The optimizer estimates fewer rows, so index cost appears lower.
But it also recalculates the join cost differently...

The optimizer's decision isn't just about single-table access — it's about the entire plan, including join costs, sort costs, and the interaction between the access path and the join method. A lower row estimate from the outer table makes merge scan join appear cheaper because the sort is smaller. This cascading effect is why a seemingly small statistics change can cause a complete plan flip.

💡 ARCHITECT'S NOTE: Never analyze access path decisions in isolation. The optimizer evaluates the entire plan holistically. A change in one table's filter factor can change the join method, which changes the access path for the other table, which changes the sort requirements. It's a system, not a sequence of independent choices.

6.3 Access Path Types — From Tablespace Scans to Index-Only Access

DB2 has a taxonomy of access methods. Understanding each one — and when the optimizer chooses it — is non-negotiable for performance diagnosis.

Tablespace Scan (ACCESSTYPE = 'R')

DB2 reads every page in the tablespace sequentially, evaluating predicates on each row.

When chosen: - No suitable index exists - Filter factors estimate a large fraction of rows will be returned (typically >20-25%) - CLUSTERRATIO of available indexes is low, making index access expensive - Table is small enough that a scan fits in the buffer pool

Performance characteristics: - Excellent sequential prefetch utilization (32-page or 64-page reads) - Predictable elapsed time proportional to tablespace size - CPU cost for evaluating predicates on every row

PLAN_TABLE:
  ACCESSTYPE = 'R'
  MATCHCOLS  = 0
  ACCESSNAME = ''  (no index)
  PREFETCH   = 'S' (sequential prefetch)

⚠️ PRODUCTION REALITY: A tablespace scan on a 180-GB tablespace is not inherently bad if the query legitimately needs most of the data. But the same tablespace scan on a query that needs 500 rows out of 500 million is catastrophic. The access type itself is neutral — it's the context that determines whether it's correct.

Index Access (ACCESSTYPE = 'I')

DB2 traverses the B-tree index to locate qualifying rows, then accesses the base table to retrieve remaining columns.

Variations by MATCHCOLS: - MATCHCOLS = 0: Non-matching index scan — DB2 reads the entire index leaf chain. This happens when the index has useful screening columns or when the index is much smaller than the tablespace (scanning a 2-GB index is faster than scanning a 180-GB tablespace, even without matching). DB2 evaluates predicates against the index entries but doesn't use the B-tree for navigation. - MATCHCOLS > 0: Matching index scan — B-tree traversal directly to qualifying entries. This is the efficient case. DB2 navigates the B-tree (typically 2-4 levels of non-leaf pages) to the first qualifying leaf entry, then scans forward through the leaf pages until the matching condition is no longer satisfied.

The B-tree structure: Understanding B-tree mechanics helps you reason about index access costs:

Level 3 (Root):     [ ---- pointers to level 2 ---- ]          1 page
Level 2 (Non-leaf): [ ------ pointers to level 1 ------ ]      ~100 pages
Level 1 (Non-leaf): [ -------- pointers to leaf ---------- ]   ~10,000 pages
Level 0 (Leaf):     [ key1 | key2 | key3 | ... | RID | RID ]  ~4,000,000 pages

For IX_DTRAN_ACCTDT on DAILY_TRANSACTIONS (500M rows), NLEVELS = 4. A matching index probe costs: 4 page reads to navigate to the first qualifying leaf entry, then sequential leaf page reads for all qualifying entries. With buffer pool caching, the root and level-2 pages are almost always in memory, so the effective cost is often 1-2 physical I/Os for navigation plus leaf page reads.

Performance depends on: - MATCHCOLS (more matching = narrower search = fewer leaf pages scanned) - CLUSTERRATIO/CLUSTERED (determines whether base table access is sequential or random) - Number of qualifying rows (each non-index-only row requires a base table page fetch) - Buffer pool hit ratio (higher hit ratio reduces physical I/O)

PLAN_TABLE:
  ACCESSTYPE = 'I'
  MATCHCOLS  = 4
  ACCESSNAME = 'IX_DTRAN_ACCTDT'
  PREFETCH   = 'S' (sequential) or 'L' (list) or blank (no prefetch)

🔍 DIAGNOSTIC TIP: When MATCHCOLS is lower than you expect, check: (1) Is a predicate non-sargable? (2) Is a column missing from the WHERE clause that breaks the consecutive sequence? (3) Is there a data type mismatch causing an implicit conversion? These are the three most common reasons for unexpectedly low MATCHCOLS.

Index-Only Access (INDEXONLY = 'Y')

DB2 can satisfy the query entirely from the index without touching the base table. This is the holy grail of access paths for narrow queries.

Requirements: - Every column in the SELECT list, WHERE clause, ORDER BY, and GROUP BY must exist in a single index (as key columns or INCLUDE columns) - DB2 sets INDEXONLY = 'Y' in the PLAN_TABLE

Example — converting to index-only:

-- Original query (requires base table access)
SELECT ACCOUNT_ID, TRAN_DATE, AMOUNT, DESCRIPTION
FROM   DAILY_TRANSACTIONS
WHERE  ACCOUNT_ID = :WS-ACCT-ID
  AND  TRAN_DATE = :WS-TRAN-DATE;

-- If we add DESCRIPTION and AMOUNT to the index:
CREATE INDEX IX_DTRAN_ACCTDT_COVR
  ON DAILY_TRANSACTIONS (ACCOUNT_ID, TRAN_DATE)
  INCLUDE (AMOUNT, DESCRIPTION);

💡 ARCHITECT'S NOTE: INCLUDE columns (available since DB2 10) are stored in the index leaf pages but not in the non-leaf pages, so they don't increase non-leaf page levels. They enable index-only access without bloating the B-tree structure. Use them aggressively for frequently-executed narrow queries.

List Prefetch (PREFETCH = 'L')

When DB2 uses an index but the data isn't well-clustered, it can: 1. Collect RIDs from the index 2. Sort them by page number 3. Fetch the pages in physical sequence

This converts random I/O into sequential I/O at the cost of a sort. PLAN_TABLE shows PREFETCH = 'L'.

When chosen: - CLUSTERRATIO is moderate (not high enough for sequential prefetch, not low enough to abandon the index) - Estimated number of qualifying rows is in a middle range

🔍 DIAGNOSTIC TIP: If you see PREFETCH = 'L' in the PLAN_TABLE, check the CLUSTERRATIO for the index being used. If it's below 80%, consider whether a REORG might restore clustering and change the prefetch from list to sequential.

Multiple Index Access (ACCESSTYPE = 'M' or 'MX')

DB2 can use multiple indexes on the same table, combining the RID lists with AND or OR operations.

ACCESSTYPE = 'MX' (RID intersection): Multiple indexes, AND the RID lists ACCESSTYPE = 'MU' (RID union): Multiple indexes, OR the RID lists

-- DB2 might use two indexes with RID intersection:
WHERE TRAN_DATE = '2025-12-15'   -- one index
  AND BRANCH_CODE = 'CHI-045'    -- different index

Production caution: Multiple index access often indicates a missing composite index. If you see MX or MU in production PLAN_TABLEs, evaluate whether a single composite index would serve better.

One-Fetch Access (ACCESSTYPE = 'I1')

For MIN or MAX on the leading column of an index, DB2 reads a single index entry. Extremely efficient.

SELECT MAX(TRAN_DATE) FROM DAILY_TRANSACTIONS;
-- Uses ACCESSTYPE = 'I1' if an index leads with TRAN_DATE

Direct Row Access (ACCESSTYPE = 'D')

When DB2 has the ROWID or RID directly (from a previously saved position or a ROWID column), it accesses the row without any index traversal.

Summary: Access Path Decision Framework

6.4 Join Methods — Nested Loop, Merge Scan, and Hash Join

Most production COBOL/DB2 programs involve multi-table joins. The join method DB2 chooses can make orders-of-magnitude differences in performance.

Nested Loop Join (METHOD = 1)

The workhorse of COBOL/DB2 programs. DB2 reads rows from the outer table and, for each qualifying row, accesses the inner table (typically via an index).

For each row in outer table:
    Use index to find matching rows in inner table
    Return joined row

When chosen: - Inner table has a good matching index on the join columns - Outer table's result set is small (the optimizer's estimate of the outer row count is the key factor) - ORDER BY can be satisfied without a sort (if the outer table is accessed via an index whose key order matches the ORDER BY) - The optimizer estimates that the total number of index probes is cheaper than the sort cost of a merge scan

PLAN_TABLE indicators:

  METHOD = 1
  Outer table: first PLANNO
  Inner table: subsequent PLANNO with ACCESSTYPE = 'I'

Performance characteristics: - Cost proportional to (outer_rows * index_probe_cost_per_row) - Excellent when outer result set is small (<10,000 rows) and inner table has a high-MATCHCOLS index - Degrades linearly as outer result set grows — each outer row triggers an index probe - At CNB, Kwame Mensah's rule of thumb: nested loop is typically optimal when the outer table produces fewer than 50,000 rows and the inner table index has MATCHCOLS >= the number of join columns

The hidden cost of nested loop: Each index probe for the inner table involves B-tree navigation (2-4 I/Os if the non-leaf pages aren't cached) plus the base table page fetch. For a 4-level index, if the top two levels are cached in the buffer pool, each probe costs approximately 2 physical I/Os (one leaf page + one data page). Multiply by 685,000 outer rows and you get 1.37 million I/Os — still much better than a tablespace scan of 45 million pages, but not negligible. Buffer pool sizing for the inner table's index is critical.

Merge Scan Join (METHOD = 2)

Both tables are sorted (or accessed via index in order) on the join columns, then merged. Think of it like merging two sorted decks of cards — you advance through each deck simultaneously, matching as you go.

Sort outer table on join key (if not already ordered)
Sort inner table on join key (if not already ordered)
Merge the two sorted streams:
    Advance outer pointer and inner pointer
    When keys match, produce joined row
    When outer < inner, advance outer
    When inner < outer, advance inner

When chosen: - Both tables have large qualifying result sets (hundreds of thousands or millions of rows) - No suitable index on the inner table join columns (or index access is too expensive due to low CLUSTERRATIO) - Data is already in join-key order from a prior step (sort avoidance) - The optimizer estimates that the sort cost + single-pass merge is cheaper than the repeated index probe cost of nested loop

PLAN_TABLE indicators:

  METHOD = 2
  SORTN_JOIN = 'Y' (new table sorted) and/or SORTC_JOIN = 'Y' (composite sorted)

If SORTN_JOIN = 'N', DB2 is getting the data in join-key order without sorting — either from an index or from a prior sort step. This is an important optimization: sort avoidance on the merge input can significantly reduce elapsed time.

Performance characteristics: - Cost = sort(outer) + sort(inner) + merge - For large result sets, often cheaper than nested loop because each row is read and processed exactly once - Sort can spill to work files (DSNDB07) — monitor work file usage - Merge phase is extremely efficient: single-pass, no random I/O

Estimating sort work file space:

Work file pages = (qualifying_rows * avg_row_length) / 4096
                  * sort_overhead_factor (typically 1.5-2.0)

For the CNB reconciliation query's merge scan plan, the sort on DAILY_TRANSACTIONS required:

685,000 rows * ~200 bytes = 137 MB uncompressed
With sort compression and overhead: ~200 MB in DSNDB07

This isn't catastrophic for a single query, but if 15 batch jobs are running merge scan joins simultaneously, DSNDB07 contention becomes a system-level problem.

🔄 SPACED REVIEW (Ch 4): Remember the dataset management concepts from Chapter 4 — DB2 work files (DSNDB07 tablespaces) are used for sort operations. If your merge scan join is spilling to DASD, the work file placement on high-performance volumes matters. Lisa Tran's CNB standard places DSNDB07 on dedicated DS8950 ranks with SSD caching.

Hash Join (METHOD = 4)

DB2 builds a hash table from the smaller (build) table and probes it with rows from the larger (probe) table. This is conceptually the simplest join algorithm and often the most efficient for large-large joins with no useful indexes.

Build phase:
    Read all qualifying rows from the smaller (build) table
    Hash each row's join key value into a bucket in an in-memory hash table
Probe phase:
    Read all qualifying rows from the larger (probe) table
    For each row, hash the join key value
    Look up the bucket in the hash table
    If match found, produce joined row

When chosen: - Large result sets from both tables (typically >100,000 rows from each) - No useful indexes on join columns — or the available indexes have such poor CLUSTERRATIO that index access would be predominantly random I/O - The build table's qualifying rows fit in memory (or the optimizer determines that spilling to work files is still cheaper than alternatives) - Sufficient buffer pool or work file space for the hash table

PLAN_TABLE indicators:

  METHOD = 4
  ACCESSTYPE for inner table = various
  Look for DSNT398I message in explain for hash join details

Performance characteristics: - Excellent for large equi-joins — both the build and probe phases are single-pass through the data - Build phase: cost proportional to build_rows (linear scan) - Probe phase: cost proportional to probe_rows (linear scan + hash lookup, which is O(1) per row) - Total: O(build_rows + probe_rows) — dramatically better than nested loop's O(outer_rows * inner_access_cost) - Degrades if hash table exceeds available memory and spills to DSNDB07 work files

When hash join is disabled or unavailable: - zparm OPTIQP is not set to YES - zparm MAXTEMPS too small for hash table materialization - Non-equi-join predicates (hash join requires equality on at least one join predicate — you can't hash a range comparison) - The build table's qualifying result set is too small (optimizer chooses nested loop as cheaper)

Sizing the hash table:

Hash table memory = qualifying_build_rows * (join_key_length + row_data_length + overhead)

For a build table with 500,000 qualifying rows and 100 bytes per row, the hash table needs approximately 60-80 MB including hash overhead. If this exceeds the available in-memory work area, DB2 partitions the data and processes it in multiple passes, spilling to DSNDB07.

Join Sequence Matters

The optimizer also decides the order in which tables are joined. For a three-way join:

SELECT ...
FROM   ACCOUNT_MASTER A
       INNER JOIN DAILY_TRANSACTIONS D
         ON A.ACCOUNT_ID = D.ACCOUNT_ID
       INNER JOIN BRANCH_REFERENCE B
         ON A.BRANCH_CODE = B.BRANCH_CODE
WHERE  D.TRAN_DATE = :WS-PROCESS-DATE
       AND A.ACCT_STATUS = 'A';

DB2 might join A→D→B, or D→A→B, or B→A→D. The estimated cardinality after each join step drives the decision. Starting with the most restrictive table (smallest intermediate result set) is generally best, but the optimizer considers all permutations for small join counts and uses heuristics for larger ones.

💡 PRACTICAL TIP: For queries with 5+ table references, the optimizer's search space explodes combinatorially. DB2 uses greedy algorithms and pruning to keep optimization time manageable. If you have a 10-table join that consistently gets a bad plan, consider breaking it into intermediate result sets using common table expressions (CTEs) to guide the optimizer.

Lisa Tran's Join Analysis

Back to the CNB incident. The reconciliation query joins DAILY_TRANSACTIONS (D) with ACCOUNT_MASTER (A):

SELECT D.TRAN_ID, D.AMOUNT, D.TRAN_DATE,
       A.ACCOUNT_NAME, A.BRANCH_CODE, A.ACCT_STATUS
FROM   DAILY_TRANSACTIONS D
       INNER JOIN ACCOUNT_MASTER A
         ON D.ACCOUNT_ID = A.ACCOUNT_ID
WHERE  D.TRAN_DATE = :WS-PROCESS-DATE
       AND D.ACCT_STATUS IN ('A', 'P', 'H')
       AND A.ACCT_TYPE = 'CHK';

Before RUNSTATS: Nested loop join (METHOD=1). D accessed via matching index (4 MATCHCOLS), small result set fed to A via index.

After RUNSTATS: Merge scan join (METHOD=2). Tablespace scan on D, sort, merge with A. The optimizer estimated fewer rows (due to the inflated COLCARDF for TRAN_DATE), but the tablespace scan approach was catastrophically wrong because the actual data didn't match the statistics.

The fix — which we'll detail in Section 6.7 — involved both immediate remediation and long-term prevention.

6.5 EXPLAIN Deep Dive — Reading the Plan That DB2 Chose

EXPLAIN is your primary diagnostic tool. It captures the access path DB2 chose and writes it to tables you can query.

Setting Up EXPLAIN

Before you can use EXPLAIN, the PLAN_TABLE (and optionally DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE, etc.) must exist under the authorization ID that will run the EXPLAIN.

-- Create PLAN_TABLE (DB2 12 for z/OS format)
CREATE TABLE your_authid.PLAN_TABLE
  (QUERYNO        INTEGER     NOT NULL WITH DEFAULT,
   QBLOCKNO       SMALLINT    NOT NULL WITH DEFAULT,
   APPLNAME       CHAR(24)    NOT NULL WITH DEFAULT,
   PROGNAME       CHAR(8)     NOT NULL WITH DEFAULT,
   PLANNO         SMALLINT    NOT NULL WITH DEFAULT,
   METHOD         SMALLINT    NOT NULL WITH DEFAULT,
   CREATOR        CHAR(128)   NOT NULL WITH DEFAULT,
   TNAME          CHAR(128)   NOT NULL WITH DEFAULT,
   TABNO          SMALLINT    NOT NULL WITH DEFAULT,
   ACCESSTYPE     CHAR(2)     NOT NULL WITH DEFAULT,
   MATCHCOLS      SMALLINT    NOT NULL WITH DEFAULT,
   ACCESSCREATOR  CHAR(128)   NOT NULL WITH DEFAULT,
   ACCESSNAME     CHAR(128)   NOT NULL WITH DEFAULT,
   INDEXONLY       CHAR(1)     NOT NULL WITH DEFAULT,
   SORTN_UNIQ     CHAR(1)     NOT NULL WITH DEFAULT,
   SORTN_JOIN     CHAR(1)     NOT NULL WITH DEFAULT,
   SORTN_ORDERBY  CHAR(1)     NOT NULL WITH DEFAULT,
   SORTN_GROUPBY  CHAR(1)     NOT NULL WITH DEFAULT,
   SORTC_UNIQ     CHAR(1)     NOT NULL WITH DEFAULT,
   SORTC_JOIN     CHAR(1)     NOT NULL WITH DEFAULT,
   SORTC_ORDERBY  CHAR(1)     NOT NULL WITH DEFAULT,
   SORTC_GROUPBY  CHAR(1)     NOT NULL WITH DEFAULT,
   TSLOCKMODE     CHAR(3)     NOT NULL WITH DEFAULT,
   TIMESTAMP      CHAR(16)    NOT NULL WITH DEFAULT,
   REMARKS        VARCHAR(762) NOT NULL WITH DEFAULT,
   PREFETCH       CHAR(1)     NOT NULL WITH DEFAULT,
   COLUMN_FN_EVAL CHAR(1)     NOT NULL WITH DEFAULT,
   MIXOPSEQ       SMALLINT    NOT NULL WITH DEFAULT,
   VERSION        VARCHAR(122) NOT NULL WITH DEFAULT,
   COLLID         CHAR(128)   NOT NULL WITH DEFAULT,
   ACCESS_DEGREE  SMALLINT    NOT NULL WITH DEFAULT,
   ACCESS_PGROUP_ID SMALLINT  NOT NULL WITH DEFAULT,
   JOIN_DEGREE    SMALLINT    NOT NULL WITH DEFAULT,
   JOIN_PGROUP_ID SMALLINT    NOT NULL WITH DEFAULT,
   SORTC_PGROUP_ID SMALLINT   NOT NULL WITH DEFAULT,
   SORTN_PGROUP_ID SMALLINT   NOT NULL WITH DEFAULT,
   PARALLELISM_MODE CHAR(1)   NOT NULL WITH DEFAULT,
   MERGE_JOIN_COLS SMALLINT   NOT NULL WITH DEFAULT,
   CORRELATION_NAME CHAR(128) NOT NULL WITH DEFAULT,
   PAGE_RANGE     CHAR(1)     NOT NULL WITH DEFAULT,
   JOIN_TYPE      CHAR(1)     NOT NULL WITH DEFAULT,
   GROUP_MEMBER   CHAR(24)    NOT NULL WITH DEFAULT,
   IBM_SERVICE_DATA VARCHAR(254) NOT NULL WITH DEFAULT,
   WHEN_OPTIMIZE  CHAR(1)     NOT NULL WITH DEFAULT,
   QBLOCK_TYPE    CHAR(6)     NOT NULL WITH DEFAULT,
   BIND_TIME      TIMESTAMP   NOT NULL WITH DEFAULT,
   OPTHINT        CHAR(128)   NOT NULL WITH DEFAULT,
   HINT_USED      CHAR(128)   NOT NULL WITH DEFAULT,
   PRIMARY_ACCESSTYPE CHAR(1) NOT NULL WITH DEFAULT,
   PARENT_QBLOCKNO SMALLINT   NOT NULL WITH DEFAULT,
   TABLE_TYPE     CHAR(5)     NOT NULL WITH DEFAULT,
   TABLE_ENCODE   CHAR(1)     NOT NULL WITH DEFAULT,
   TABLE_SCHEM    CHAR(128)   NOT NULL WITH DEFAULT,
   ROUTINE_ID     INTEGER     NOT NULL WITH DEFAULT,
   STMTNO         INTEGER     NOT NULL WITH DEFAULT,
   SECTNOI        INTEGER     NOT NULL WITH DEFAULT,
   EXPLAIN_TIME   TIMESTAMP
  ) IN database.tablespace;

Triggering EXPLAIN

For static SQL (COBOL programs):

BIND PACKAGE(collection) MEMBER(program) EXPLAIN(YES)

For dynamic SQL:

EXPLAIN PLAN SET QUERYNO = 999 FOR
  SELECT D.TRAN_ID, D.AMOUNT
  FROM   DAILY_TRANSACTIONS D
  WHERE  D.TRAN_DATE = '2025-12-15'
    AND  D.ACCT_STATUS = 'A';

EXPLAIN(YES) vs. EXPLAIN(ONLY): - EXPLAIN(YES): Binds the package AND populates PLAN_TABLE - EXPLAIN(ONLY): Populates PLAN_TABLE without actually binding (useful for what-if analysis)

Reading PLAN_TABLE Output — The Essential Columns

Here is an actual PLAN_TABLE output from Lisa's investigation (anonymized but structurally accurate):

Before RUNSTATS (the good plan):

QUERYNO  QBLOCKNO  PLANNO  METHOD  TNAME                ACCESSTYPE  MATCHCOLS  ACCESSNAME         INDEXONLY  PREFETCH  SORTN_JOIN  SORTC_JOIN
-------  --------  ------  ------  -------------------  ----------  ---------  -----------------  --------  --------  ----------  ----------
15       1         1       0       DAILY_TRANSACTIONS   I           4          IX_DTRAN_ACCTDT    N         S         N           N
15       1         2       1       ACCOUNT_MASTER       I           1          IX_AMAST_ACCTID    N         S         N           N

Reading this: - PLANNO 1, METHOD 0: First table accessed (METHOD 0 = first table, no join method yet). DAILY_TRANSACTIONS via index IX_DTRAN_ACCTDT with 4 matching columns. Sequential prefetch. - PLANNO 2, METHOD 1: ACCOUNT_MASTER accessed via nested loop join (METHOD 1). Index IX_AMAST_ACCTID with 1 matching column.

After RUNSTATS (the bad plan):

QUERYNO  QBLOCKNO  PLANNO  METHOD  TNAME                ACCESSTYPE  MATCHCOLS  ACCESSNAME         INDEXONLY  PREFETCH  SORTN_JOIN  SORTC_JOIN
-------  --------  ------  ------  -------------------  ----------  ---------  -----------------  --------  --------  ----------  ----------
15       1         1       0       DAILY_TRANSACTIONS   R           0                             N         S         Y           N
15       1         2       2       ACCOUNT_MASTER       I           1          IX_AMAST_ACCTID    N         S         N           Y

Reading this: - PLANNO 1: Tablespace scan ('R') on DAILY_TRANSACTIONS. No index. Sequential prefetch. SORTN_JOIN = 'Y' means a sort was needed for the join. - PLANNO 2, METHOD 2: Merge scan join. ACCOUNT_MASTER via index but also SORTC_JOIN = 'Y' (composite sorted).

DSN_STATEMNT_TABLE — Cost Estimates

For deeper analysis, DSN_STATEMNT_TABLE provides the optimizer's cost estimates:

SELECT QUERYNO, PROCSU, PROCMS, REASON,
       COST_CATEGORY, STMT_TYPE
FROM   DSN_STATEMNT_TABLE
WHERE  QUERYNO = 15;

Key columns: - PROCSU: Estimated CPU service units - PROCMS: Estimated elapsed milliseconds - COST_CATEGORY: 'A' (optimizer determined cost) or 'B' (default statistics used — warning sign) - REASON: If access path was limited by special circumstances

⚠️ CRITICAL: If COST_CATEGORY = 'B', DB2 used default statistics because RUNSTATS has never been run on the referenced objects. Any plan based on default statistics is essentially a guess. Run RUNSTATS immediately.

The EXPLAIN Diagnostic Workflow

Here is Lisa Tran's standard diagnostic sequence — and it should become yours:

1. Run EXPLAIN on the problem statement
2. Check DSN_STATEMNT_TABLE — Is COST_CATEGORY = 'A'? If 'B', RUNSTATS first.
3. Read PLAN_TABLE — Identify ACCESSTYPE, MATCHCOLS, METHOD, PREFETCH for each step
4. Compare to expected plan — Do you know what the plan should be? If not, work it out from the indexes and predicates.
5. Check catalog statistics — Query SYSIBM.SYSCOLUMNS (COLCARDF, HIGH2KEY, LOW2KEY), SYSIBM.SYSTABLES (CARDF, NPAGES), SYSIBM.SYSINDEXES (CLUSTERRATIO, NLEAF, NLEVELS, FIRSTKEYCARDF, FULLKEYCARDF)
6. Calculate filter factors manually — Do they match what you'd expect from the data?
7. Identify the discrepancy — Where does the optimizer's model diverge from reality?

🔄 SPACED REVIEW (Ch 1): In Chapter 1, we discussed DB2's catalog as a set of system tables (SYSIBM schema) that describe every DB2 object. The catalog statistics we query for optimizer diagnosis — COLCARDF, HIGH2KEY, CLUSTERRATIO — live in these same SYSIBM tables. The catalog is both DB2's metadata repository and the optimizer's information source.

6.6 RUNSTATS and Statistics — Feeding the Optimizer

RUNSTATS is the utility that populates the catalog statistics the optimizer relies on. If EXPLAIN is your diagnostic tool, RUNSTATS is your preventive medicine.

What RUNSTATS Collects

Tablespace statistics (written to SYSIBM.SYSTABLESPACE and SYSIBM.SYSTABLES): - CARDF: Total number of rows - NPAGES: Number of active pages - NACTIVE: Number of pages with rows - PCTROWCOMP: Percentage of rows compressed

Index statistics (written to SYSIBM.SYSINDEXES): - FIRSTKEYCARDF: Cardinality of the first key column - FULLKEYCARDF: Cardinality of the full key - CLUSTERRATIO or CLUSTERED: How well-ordered the base table data is relative to the index - NLEAF: Number of leaf pages - NLEVELS: Number of B-tree levels

Column statistics (written to SYSIBM.SYSCOLUMNS): - COLCARDF: Column cardinality (distinct values) - HIGH2KEY: Second-highest value - LOW2KEY: Second-lowest value

Distribution statistics (SYSIBM.SYSCOLDIST): - Frequency statistics: Most frequent values and their frequencies - Histogram (quantile) statistics: Value distribution across ranges

RUNSTATS Best Practices for Production

Lisa Tran's CNB RUNSTATS standards, refined over a decade:

1. Run RUNSTATS after every significant data change. "Significant" means: - More than 10% of the table's rows inserted, updated, or deleted - After a REORG - After a LOAD - After any mass data migration

2. Always collect distribution statistics for columns in predicates.

//RUNSTATS EXEC DSNUPROC,SYSTEM=DB2P,UID='RNST',
//         UTPROC=''
//SYSIN    DD *
  RUNSTATS TABLESPACE DBPROD01.TSDTRAN
    TABLE(PROD.DAILY_TRANSACTIONS)
    COLUMN(TRAN_DATE)   FREQVAL COUNT 20 MOST
    COLUMN(ACCT_STATUS) FREQVAL COUNT 10 MOST
    COLUMN(BRANCH_CODE) FREQVAL COUNT 50 MOST
    COLUMN(TRAN_TYPE)   FREQVAL COUNT 15 MOST
    INDEX(ALL)
    SHRLEVEL CHANGE
    REPORT YES
    UPDATE ALL
/*

3. Use SHRLEVEL CHANGE for online execution. SHRLEVEL REFERENCE locks the tablespace — unacceptable for 24x7 systems.

4. Use inline RUNSTATS with REORG.

  REORG TABLESPACE DBPROD01.TSDTRAN
    SHRLEVEL CHANGE
    STATISTICS
    TABLE(PROD.DAILY_TRANSACTIONS)
    COLUMN(TRAN_DATE) FREQVAL COUNT 20 MOST
    INDEX(ALL)
    DRAIN_WAIT 60
    RETRY 3

💡 ARCHITECT'S NOTE: Inline RUNSTATS (the STATISTICS keyword on REORG) collects statistics during REORG's sort phase, when all data passes through memory anyway. It adds negligible overhead and guarantees statistics are fresh the moment REORG completes. There is no reason to run separate RUNSTATS after REORG. Always use inline RUNSTATS.

5. Schedule RUNSTATS before REBIND in migration windows.

If you're migrating to a new DB2 version or applying APARs that affect the optimizer: 1. RUNSTATS on all objects 2. REBIND with EXPLAIN(YES) 3. Compare PLAN_TABLE output to baseline 4. Test before production

6. Use profile-based RUNSTATS (DB2 12+) for consistency.

-- Create a statistics profile
ALTER TABLE PROD.DAILY_TRANSACTIONS
  ALTER STATISTICS
    SET PROFILE
    COLUMN(TRAN_DATE)   FREQVAL COUNT 20
    COLUMN(ACCT_STATUS) FREQVAL COUNT 10
    INDEX(ALL);

-- Then simply:
RUNSTATS TABLESPACE DBPROD01.TSDTRAN
  TABLE(PROD.DAILY_TRANSACTIONS)
  USE PROFILE
  SHRLEVEL CHANGE;

The profile ensures every RUNSTATS execution collects the same statistics, regardless of who runs it or what JCL they use.

How RUNSTATS Interacts with BIND

A critical operational concept: RUNSTATS updates catalog statistics, but the access path doesn't change until the package is rebound. The timeline matters:

1. RUNSTATS runs — catalog statistics updated
2. Package remains bound with old access path — until: - Manual REBIND - AUTOBIND triggered (if package is invalidated) - Next dynamic SQL PREPARE (for dynamic SQL)

At CNB, Lisa Tran's incident occurred because RUNSTATS ran Sunday night, but the package wasn't rebound until Monday afternoon when AUTOBIND triggered. This gap can be a feature (you can validate statistics before rebinding) or a trap (if AUTOBIND fires at an unexpected time).

⚠️ PRODUCTION PRACTICE: For critical packages, disable AUTOBIND (bind with VALIDATE(BIND)) and control rebind timing explicitly. This prevents the scenario where bad statistics + automatic rebind = production outage at 3 AM with no one watching.

Solving the Correlation Problem

Remember the independence assumption from Section 6.2? DB2 assumes STATE and CITY are independent. In reality, they're highly correlated. There are two strategies:

Strategy 1: Multi-column frequency statistics (FREQVAL on column groups)

-- Collect frequency statistics on the combination
RUNSTATS TABLESPACE DBPROD01.TSCLAIMS
  TABLE(PROD.CLAIMS)
  COLGROUP(STATE_CODE, CITY_CODE) FREQVAL COUNT 100 MOST
  SHRLEVEL CHANGE;

This tells the optimizer the actual frequency of the most common (STATE, CITY) combinations.

Strategy 2: Column group cardinality

RUNSTATS TABLESPACE DBPROD01.TSCLAIMS
  TABLE(PROD.CLAIMS)
  COLGROUP(STATE_CODE, CITY_CODE)
  SHRLEVEL CHANGE;

This tells the optimizer the combined cardinality of the column group, allowing better filter factor estimation for compound predicates.

✅ CNB STANDARD: For any table with known correlated predicates (geographic hierarchies, code/subcode pairs, date/status combinations), Lisa Tran requires column group statistics in the RUNSTATS profile. This single practice has prevented more plan regressions than any other.

6.7 When the Optimizer Gets It Wrong — Plan Stability and Regression Prevention

The optimizer is remarkably good, but it fails in predictable ways. An architect needs both the diagnostic skills to fix problems and the prevention strategies to avoid them.

Why the Optimizer Gets It Wrong

1. Stale or inaccurate statistics. This is the #1 cause. Either RUNSTATS hasn't been run, or it captured a transient state (like Lisa's case where historical data was temporarily loaded).

2. The independence assumption. Correlated predicates lead to multiplicative underestimates.

3. Host variable blindness. For static SQL with host variables, DB2 doesn't know the actual values at BIND time. It uses average filter factors. If the actual value distribution is highly skewed, the "average" plan may be terrible for common values.

4. Predicate non-sargability. Predicates that can't be evaluated by the index (function on column, data type mismatch, implicit casting) are invisible to the optimizer's index evaluation.

-- Non-sargable: function on column
WHERE YEAR(TRAN_DATE) = 2025

-- Sargable equivalent
WHERE TRAN_DATE >= '2025-01-01' AND TRAN_DATE < '2026-01-01'

-- Non-sargable: mismatched data types (implicit cast)
WHERE CHAR_ACCOUNT_ID = 12345    -- numeric literal on CHAR column

-- Sargable equivalent
WHERE CHAR_ACCOUNT_ID = '12345'

⚠️ CRITICAL — The COBOL Data Type Trap: In COBOL programs, a common source of non-sargable predicates is declaring host variables with the wrong data type. If the DB2 column is DECIMAL(11,2) but the COBOL host variable is PIC S9(11)V99 COMP-3 (packed decimal), it works fine. But if the host variable is PIC S9(11)V99 DISPLAY (zoned decimal), DB2 may need to convert, losing sargability. Always match COBOL host variable types to DB2 column types precisely.

5. Parameter marker effect for dynamic SQL. With parameter markers (?), DB2 may use a generic plan that isn't optimal for any specific value. DB2 12 introduced CONCENTRATE STATEMENTS WITH LITERALS and improved literal replacement, but the problem persists for highly skewed distributions.

6. Predicate transitive closure missed. DB2 can sometimes infer additional predicates through transitive closure (if A.COL = B.COL and B.COL = 5, then A.COL = 5). But it doesn't always detect these opportunities, especially with complex multi-table joins. Writing the redundant predicate explicitly can help the optimizer.

7. View and nested table expression opacity. When queries reference views or nested table expressions, the optimizer may not be able to merge the view definition into the main query. This creates optimization barriers where the optimizer evaluates the inner and outer query blocks separately, potentially missing the globally optimal plan.

The Non-Sargable Predicate Catalog — A COBOL Developer's Reference

Non-sargable predicates are invisible to index matching. In COBOL/DB2 programs, these crop up more often than you'd expect. Here is the complete catalog of patterns to avoid:

-- PATTERN 1: Function on column (most common)
-- Non-sargable:
WHERE YEAR(TRAN_DATE) = 2025
WHERE MONTH(TRAN_DATE) = 12
WHERE DATE(TIMESTAMP_COL) = '2025-12-15'
WHERE UPPER(LAST_NAME) = 'SMITH'
WHERE SUBSTR(ACCOUNT_ID, 1, 3) = 'CHK'
WHERE DIGITS(NUMERIC_COL) = '00012345'

-- Sargable equivalents:
WHERE TRAN_DATE >= '2025-01-01' AND TRAN_DATE < '2026-01-01'
WHERE TRAN_DATE >= '2025-12-01' AND TRAN_DATE < '2026-01-01'
WHERE TIMESTAMP_COL >= '2025-12-15-00.00.00' AND TIMESTAMP_COL < '2025-12-16-00.00.00'
WHERE LAST_NAME = 'SMITH'  -- (store as uppercase, or create function-based index)
WHERE ACCOUNT_ID LIKE 'CHK%'
WHERE NUMERIC_COL = 12345


-- PATTERN 2: Arithmetic on column
-- Non-sargable:
WHERE AMOUNT * 1.1 > 10000
WHERE SALARY + BONUS > 100000

-- Sargable equivalents:
WHERE AMOUNT > 10000 / 1.1
WHERE SALARY > 100000 - BONUS  -- (still non-sargable if BONUS is a column)


-- PATTERN 3: Data type mismatch (COBOL-specific trap)
-- Non-sargable if ACCOUNT_ID is CHAR(12):
WHERE ACCOUNT_ID = 123456789012   -- numeric literal on CHAR column
-- DB2 must convert every ACCOUNT_ID to numeric for comparison

-- Sargable:
WHERE ACCOUNT_ID = '123456789012'  -- character literal matches column type


-- PATTERN 4: OR predicate spanning different columns
-- Non-sargable for single-index matching:
WHERE ACCOUNT_ID = :HV1 OR BRANCH_CODE = :HV2
-- DB2 might use MU (RID union) with two indexes, but cannot use a single index

-- Consider rewriting as UNION ALL if both predicates are selective:
SELECT ... WHERE ACCOUNT_ID = :HV1
UNION ALL
SELECT ... WHERE BRANCH_CODE = :HV2 AND ACCOUNT_ID <> :HV1

✅ CNB CODE REVIEW STANDARD: Every COBOL program that embeds SQL goes through a predicate sargability review before promotion to production. Lisa Tran's team maintains a one-page checklist that developers must sign off on. This single practice catches 60% of performance problems before they reach QA.

Lisa Tran's Fix — The CNB Incident Resolution

Here's what Lisa did, step by step:

Immediate fix (3:52 AM):

-- Step 1: Verify the current statistics
SELECT COLCARDF, HIGH2KEY, LOW2KEY
FROM   SYSIBM.SYSCOLUMNS
WHERE  TBNAME = 'DAILY_TRANSACTIONS'
  AND  NAME = 'TRAN_DATE';

-- Result: COLCARDF = 1095 (three years — wrong for current data)
-- The historical data load had inflated the cardinality

-- Step 2: Check if historical data was still present
SELECT COUNT(DISTINCT TRAN_DATE) FROM DAILY_TRANSACTIONS;
-- Result: 366 (back to one year — historical data had been deleted,
-- but RUNSTATS captured the inflated state before deletion)

-- Step 3: Re-run RUNSTATS to capture correct statistics
-- (Via JCL submitted from TSO)
RUNSTATS TABLESPACE DBPROD01.TSDTRAN
  TABLE(PROD.DAILY_TRANSACTIONS)
  USE PROFILE
  SHRLEVEL CHANGE;

-- Step 4: REBIND the package
REBIND PACKAGE(ACCTRCNC.ACCTRCN7) EXPLAIN(YES)

-- Step 5: Verify the plan reverted
SELECT ACCESSTYPE, MATCHCOLS, ACCESSNAME, METHOD, PREFETCH
FROM   PLAN_TABLE
WHERE  PROGNAME = 'ACCTRCN7' AND QUERYNO = 15;
-- Result: ACCESSTYPE='I', MATCHCOLS=4, METHOD=1 — the good plan is back

Job ACCTRCN7 was restarted and completed in 24 minutes.

Long-term prevention (implemented the following week):

Plan Stability Mechanisms

1. PLANMGMT (Plan Management) — DB2 11+

PLANMGMT preserves previous access path packages so you can fall back if a new plan regresses.

BIND PACKAGE(collection) MEMBER(program)
  PLANMGMT(EXTENDED)
  EXPLAIN(YES)

• PLANMGMT(BASIC): Keeps one previous copy
• PLANMGMT(EXTENDED): Keeps two previous copies (original and previous)

To fall back:

REBIND PACKAGE(collection.program)
  SWITCH(PREVIOUS)

This switches the active package to the previous version — instant plan reversion without needing to know what the old plan was.

✅ CNB STANDARD: All production packages are bound with PLANMGMT(EXTENDED). After the incident, Lisa added an automated EXPLAIN comparison step to the post-RUNSTATS process. If any critical query's access path changes, an alert fires before the package is used in production.

2. APREUSE (Access Path Reuse) — DB2 12+

APREUSE tells DB2 to reuse the current access path if possible, only generating a new plan if the old one is no longer valid (e.g., an index was dropped).

REBIND PACKAGE(collection.program)
  APREUSE(WARN)

• APREUSE(WARN): Reuse old plan, issue warning if new plan would be different
• APREUSE(ERROR): Reuse old plan, issue error if new plan would be different

This is a powerful tool for preventing unwanted plan changes during DB2 migration or maintenance windows.

3. OPTHINT — Guiding the Optimizer

As a last resort, you can insert rows into a hint table (DSN_USERQUERY_TABLE) to tell the optimizer which index to use or which join method to prefer.

INSERT INTO SYSIBM.DSN_USERQUERY_TABLE
  (QUERYNO, QBLOCKNO, PLANNO, METHOD, TNAME,
   ACCESSTYPE, MATCHCOLS, ACCESSNAME, OPTHINT)
VALUES
  (15, 1, 1, 0, 'DAILY_TRANSACTIONS',
   'I', 4, 'IX_DTRAN_ACCTDT', 'HINT_ACCTRCN7');

Then bind with:

BIND PACKAGE(collection) MEMBER(program) OPTHINT('HINT_ACCTRCN7')

⚠️ USE WITH EXTREME CAUTION: Optimizer hints are a maintenance liability. They override the optimizer permanently until removed. If data distribution changes significantly, the hinted plan may become the worst choice. Use hints only when you've exhausted all other options and document them in your operational runbook.

4. Statistical override — Manual catalog updates

In extreme cases, you can manually update catalog statistics:

UPDATE SYSIBM.SYSCOLUMNS
SET    COLCARDF = 365
WHERE  TBNAME = 'DAILY_TRANSACTIONS'
  AND  NAME = 'TRAN_DATE'
  AND  TBCREATOR = 'PROD';

This forces the optimizer to use a specific cardinality value. Like hints, this is a maintenance burden and should be documented and reviewed regularly.

The Plan Stability Decision Framework

Plan regression detected?
  │
  ├─ Is it caused by stale/wrong statistics?
  │   └─ YES → Re-run RUNSTATS, REBIND, verify. Add COLGROUP if correlated.
  │
  ├─ Is it caused by a DB2 APAR or version migration?
  │   └─ YES → Use APREUSE(WARN) to preserve old plans during testing.
  │
  ├─ Is it caused by data distribution change that's permanent?
  │   └─ YES → Evaluate if new plan is actually correct for new data.
  │         Review indexes, possibly create new index for new pattern.
  │
  ├─ Is it intermittent (dynamic SQL cache invalidation)?
  │   └─ YES → Consider CONCENTRATE STATEMENTS, review PREPARE frequency.
  │
  └─ Nothing else works?
      └─ Use PLANMGMT SWITCH(PREVIOUS) for immediate fix.
         Investigate root cause. Use OPTHINT only as last resort.

Project Checkpoint: Indexing Strategy for the HA Banking System

🧩 PROGRESSIVE PROJECT — HA Banking Transaction Processing System

It's time to apply your optimizer knowledge to the HA Banking system you've been building throughout this book. Your project checkpoint for this chapter:

The Scenario

The HA Banking system's core tables:

CREATE TABLE HABK.ACCOUNT_MASTER (
  ACCOUNT_ID       CHAR(12)     NOT NULL,
  CUSTOMER_ID      CHAR(10)     NOT NULL,
  ACCOUNT_TYPE     CHAR(3)      NOT NULL,  -- CHK, SAV, MMA, CDA
  BRANCH_CODE      CHAR(6)      NOT NULL,
  OPEN_DATE        DATE         NOT NULL,
  CLOSE_DATE       DATE,
  STATUS           CHAR(1)      NOT NULL,  -- A, C, F, H
  CURRENT_BALANCE  DECIMAL(15,2) NOT NULL,
  AVAILABLE_BALANCE DECIMAL(15,2) NOT NULL,
  LAST_ACTIVITY_DATE DATE       NOT NULL,
  PRIMARY KEY (ACCOUNT_ID)
) IN DBHABK01.TSAMAST;

CREATE TABLE HABK.DAILY_TRANSACTIONS (
  TRAN_ID          CHAR(20)     NOT NULL,
  ACCOUNT_ID       CHAR(12)     NOT NULL,
  TRAN_DATE        DATE         NOT NULL,
  TRAN_TIME        TIME         NOT NULL,
  TRAN_TYPE        CHAR(3)      NOT NULL,  -- CRD, DBT, XFR, FEE, INT
  AMOUNT           DECIMAL(13,2) NOT NULL,
  RUNNING_BALANCE  DECIMAL(15,2) NOT NULL,
  CHANNEL          CHAR(3)      NOT NULL,  -- ATM, ONL, MOB, BRN, ACH
  STATUS           CHAR(1)      NOT NULL,  -- P, C, R, V
  REFERENCE_NUM    CHAR(16),
  DESCRIPTION      VARCHAR(80),
  PRIMARY KEY (TRAN_ID)
) IN DBHABK01.TSDTRAN
  PARTITION BY (TRAN_DATE);

Your Tasks

1. Analyze the query patterns below and design an indexing strategy:

-- Query A: Online account inquiry (CICS, 200,000 executions/day)
SELECT CURRENT_BALANCE, AVAILABLE_BALANCE, STATUS,
       LAST_ACTIVITY_DATE
FROM   HABK.ACCOUNT_MASTER
WHERE  ACCOUNT_ID = :WS-ACCT-ID;

-- Query B: Transaction history (CICS, 150,000 executions/day)
SELECT TRAN_DATE, TRAN_TIME, TRAN_TYPE, AMOUNT,
       RUNNING_BALANCE, DESCRIPTION
FROM   HABK.DAILY_TRANSACTIONS
WHERE  ACCOUNT_ID = :WS-ACCT-ID
  AND  TRAN_DATE BETWEEN :WS-START-DATE AND :WS-END-DATE
ORDER BY TRAN_DATE DESC, TRAN_TIME DESC;

-- Query C: Batch reconciliation (Batch, 1 execution/night)
SELECT D.TRAN_ID, D.AMOUNT, D.TRAN_TYPE,
       A.ACCOUNT_TYPE, A.BRANCH_CODE
FROM   HABK.DAILY_TRANSACTIONS D
       INNER JOIN HABK.ACCOUNT_MASTER A
         ON D.ACCOUNT_ID = A.ACCOUNT_ID
WHERE  D.TRAN_DATE = :WS-PROCESS-DATE
  AND  D.STATUS = 'C'
  AND  A.STATUS = 'A';

-- Query D: Branch activity report (Batch, 50 executions/day)
SELECT A.BRANCH_CODE, A.ACCOUNT_TYPE,
       COUNT(*) AS TRAN_COUNT,
       SUM(D.AMOUNT) AS TOTAL_AMOUNT
FROM   HABK.DAILY_TRANSACTIONS D
       INNER JOIN HABK.ACCOUNT_MASTER A
         ON D.ACCOUNT_ID = A.ACCOUNT_ID
WHERE  D.TRAN_DATE = :WS-REPORT-DATE
  AND  D.TRAN_TYPE IN ('CRD', 'DBT')
GROUP BY A.BRANCH_CODE, A.ACCOUNT_TYPE;

-- Query E: Fraud detection scan (Batch, every 15 minutes)
SELECT D.TRAN_ID, D.ACCOUNT_ID, D.AMOUNT,
       D.CHANNEL, D.TRAN_TIME
FROM   HABK.DAILY_TRANSACTIONS D
WHERE  D.TRAN_DATE = :WS-TODAY
  AND  D.AMOUNT > 10000.00
  AND  D.CHANNEL IN ('ONL', 'MOB')
  AND  D.STATUS = 'P';

2. For each index you design, document: - The columns and their order (justify the order) - Which queries it serves - Expected MATCHCOLS for each query - Whether it enables index-only access - RUNSTATS COLGROUP recommendations for correlated columns

3. Predict the access path DB2 will choose for each query with your indexes in place.

4. Design the RUNSTATS strategy — what profile, what frequency, what COLGROUP specifications.

See code/project-checkpoint.md for the detailed solution framework.

Production Considerations

Monitoring Optimizer Behavior in Production

1. Real-time access path monitoring:

DB2 provides IFCID 22 (SQL statement detail) and IFCID 318 (access path detail) trace records. At CNB, these are captured selectively for critical batch jobs and high-volume CICS transactions.

-START TRACE(PERFM) CLASS(29) DEST(SMF)
  PLAN(ACCTRCNP) IFCID(22,318)

⚠️ WARNING: Performance trace classes generate significant overhead. Never leave class 29 running indefinitely in production. Use targeted tracing for specific plans or authorization IDs during diagnosis.

2. Dynamic Statement Cache monitoring:

-- Check the dynamic statement cache for plan information
SELECT STMT_ID, STMT_TEXT, STMT_EXEC_COUNT,
       LITERAL_REPL, STMT_STATUS
FROM   TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2))
WHERE  STMT_TEXT LIKE '%DAILY_TRANSACTIONS%';

3. Accounting trace for CPU consumption:

IFCID 3 (accounting records) includes SQL execution counts and CPU time per package. Comparing across days reveals plan regressions before they become incidents.

The Automated EXPLAIN Baseline

After the incident, Kwame Mensah and Lisa Tran implemented what they call the EXPLAIN Baseline System at CNB:

1. Every production package BIND captures EXPLAIN output to a dedicated baseline table
2. A comparison program runs after every BIND, comparing the new PLAN_TABLE rows against the baseline
3. Differences trigger alerts categorized by severity: - CRITICAL: Tablespace scan replacing index access on tables > 1GB - WARNING: Join method change, MATCHCOLS decrease, PREFETCH change - INFO: Minor cost estimate changes with same access path

This system has caught 23 plan regressions before they reached production in the last two years.

RID Pool and Its Impact on Access Path Selection

The RID (Row Identifier) pool is a shared memory area in the DBM1 address space used for list prefetch and multiple index access. Understanding its behavior is essential for diagnosing certain access path anomalies.

When DB2 uses list prefetch or multiple index access (MX/MU), it accumulates RIDs in the RID pool before sorting and fetching data pages. If the RID pool fills up:

1. For list prefetch: DB2 falls back to sequential detection, which reads data pages without sorting RIDs first. Performance degrades because the random-to-sequential conversion is lost.
2. For multiple index access (MX/MU): DB2 may revert to a tablespace scan for the remaining rows, causing a significant performance cliff.

-- Check RID pool usage
SELECT POOL_SIZE, CURRENT_USAGE, FAILURES
FROM   TABLE(MON_GET_BUFFERPOOL_RID_POOL(-2));

⚠️ PRODUCTION TRAP: A query that performs well in testing (with 10,000 qualifying rows) may fall off a cliff in production (with 10,000,000 qualifying rows) because the RID pool overflows. If you see intermittent performance degradation on queries that use list prefetch or multiple index access, check the RID pool failure count. The fix is usually to either increase the RID pool size (zparm MAXRBLK) or redesign the index to avoid the RID pool path entirely.

The Sequential Detection Mechanism

DB2 has a runtime mechanism called sequential detection that monitors page access patterns. If DB2 detects that a series of random page fetches is actually touching pages in near-sequential order (because the data happens to be well-clustered even though the statistics don't reflect it), it switches to sequential prefetch dynamically.

This means that CLUSTERRATIO in the catalog isn't the final word — runtime behavior can be better than statistics predict. However, you should never rely on sequential detection as a substitute for accurate statistics and appropriate indexes. It's a safety net, not a design strategy.

DB2 zparm Settings That Affect the Optimizer

Several zparm settings influence optimizer behavior:

Summary

Key Concepts

Key Access Paths

Key Metrics to Monitor

1. MATCHCOLS — Are you getting the matching columns you designed for?
2. ACCESSTYPE — Is DB2 using your index or scanning?
3. PREFETCH — 'S' (sequential) is normal; 'L' (list) suggests poor clustering
4. COST_CATEGORY — 'B' means default statistics — run RUNSTATS immediately
5. CLUSTERRATIO — Below 80% means REORG should be evaluated
6. SORTN_JOIN/SORTC_JOIN — Sorts add elapsed time and consume work files

Decision Framework

Query performing poorly?
  │
  ├─ Step 1: EXPLAIN the statement
  │   └─ Compare ACCESSTYPE, MATCHCOLS, METHOD to expectation
  │
  ├─ Step 2: Check COST_CATEGORY
  │   └─ If 'B': Run RUNSTATS, REBIND, re-EXPLAIN
  │
  ├─ Step 3: Verify catalog statistics match reality
  │   └─ COLCARDF, CLUSTERRATIO, CARDF, NPAGES
  │
  ├─ Step 4: Check for non-sargable predicates
  │   └─ Functions on columns, type mismatches
  │
  ├─ Step 5: Check for correlation effects
  │   └─ Add COLGROUP statistics if correlated predicates exist
  │
  ├─ Step 6: Evaluate index design
  │   └─ Column order, INCLUDE columns, covering potential
  │
  └─ Step 7: If all else fails
      └─ PLANMGMT SWITCH, APREUSE, or (last resort) OPTHINT

Spaced Review Connections

🔄 From Chapter 1 (z/OS Subsystem Architecture): The DB2 optimizer runs in the DBM1 address space. The catalog tables it consults (SYSIBM schema) are DB2 objects managed by the same subsystem. When you query PLAN_TABLE, you're using DB2 to examine DB2's own decisions.

🔄 From Chapter 4 (Dataset Management): Tablespaces are VSAM linear datasets. CLUSTERRATIO measures how well the physical VSAM data page sequence matches the index key sequence. REORG physically reorders the VSAM data pages — that's why it improves CLUSTERRATIO. Work files (DSNDB07) used by sorts are also VSAM datasets — their placement on physical volumes matters.

🔄 From Chapter 5 (Advanced SQL): The SQL coding patterns from Chapter 5 — predicate pushdown, sargable predicates, EXISTS vs. IN — all feed directly into how the optimizer evaluates access paths. Writing "optimizer-friendly" SQL isn't about hinting; it's about giving the cost model accurate information to work with.

Next chapter: Chapter 7 takes us from understanding the optimizer to actively managing DB2 locking and concurrency — because even the perfect access path is worthless if your transaction is waiting on a lock.