DB2 access path stability

What “stable” means

The optimizer is cost-based. Give it different statistics, a new index, a different clustering ratio, or a new Db2 release behavior, and it may pick a different method, join order, or index. Stability does not mean “never improve.” It means no surprise regressions on the statements that pay the rent. You want:

• A record of the current path (EXPLAIN / PLAN_TABLE)
• A warning or failure when a bind would change it (APCOMPARE)
• An option to keep the old path (APREUSE)
• A fallback copy of the package (PLANMGMT SWITCH)

Static SQL is bound into a package: the path is chosen at BIND/REBIND (unless REOPT re-optimizes at run time). Dynamic SQL is prepared into the statement cache and can change whenever a new prepare occurs. Stability tools differ slightly for each, but the idea is the same: do not let a catalog change overnight turn a nested loop into a merge join on a 200-million-row table without anyone noticing.

When access paths change

Common triggers:

• RUNSTATS (or inline statistics on LOAD/REORG/REBUILD) — filter factors and cardinalities move; the winner of cost comparisons can flip
• REORG that restores clustering — a matching index plus data access looks cheaper when CLUSTERRATIOF is high; a disorganized table may have preferred list prefetch or a scan
• CREATE INDEX or DROP INDEX — new matching opportunities, or a used index disappears
• REBIND after a Db2 version, APPLCOMPAT, or maintenance that changes rewrite or costing
• SQL text or predicate changes — even a function on a column can drop MATCHCOLS from 3 to 0
• REOPT(ALWAYS/ONCE/AUTO) — host variables or parameter markers get real values; the path can differ from the bind-time generic estimate
• Dynamic cache invalidation — DROP, ALTER, RUNSTATS (depending on options), REVOKE, and similar events invalidate cached statements; the next prepare re-optimizes
• Missing or stale stats (COST_CATEGORY B) — the optimizer guessed; the next explain with real stats may look nothing like the guess

Not every change is bad. A new index that turns a scan into MATCHCOLS = 2 is the point of indexing. Stability processes exist so you see the change, test it, and keep a way back.

Plan management introduction

Plan management (the PLANMGMT bind option and the PLANMGMT ZPARM) saves extra copies of a package:

• NONE — only the current copy. A bad REBIND has no built-in old path to SWITCH to
• BASIC — current and previous
• EXTENDED — current, previous, and original. Original is the copy from the first time EXTENDED (or the policy) saved it—your “known good from go-live” in many shops

After a regression you can REBIND ... SWITCH(PREVIOUS) or SWITCH(ORIGINAL) to activate a saved copy. That is not a new optimization; it is putting the old executable plan back. EXTENDED is the usual subsystem default because the original copy is cheap insurance compared with a production outage.

Plan management also interacts with rebind phase-in (packages that stay executable while a new copy is built). The policy is about how many copies exist and whether you can fall back. It does not by itself freeze the optimizer. Combine it with APREUSE/APCOMPARE when you want freeze or detection.

1
2
3
4
5
REBIND PACKAGE (HR.EMPPGM.(V1))
  PLANMGMT(EXTENDED)
  EXPLAIN(YES)
  APCOMPARE(WARN)
  APREUSE(WARN)

APREUSE — reuse the old path

APREUSE tells BIND/REBIND to treat the previous access path as an internal hint and try to generate that same path again. When reuse succeeds, PLAN_TABLE.HINT_USED often shows APREUSE.

• NONE — optimize as usual
• WARN — try reuse; if a statement cannot reuse, pick a new path and continue. Messages identify failures
• ERROR — if any statement in the package cannot reuse, the operation fails (package-level granularity: one failure fails all reuse for that package)

Reuse can fail even when you want it: the old index was dropped, SQL changed, or the hint cannot be applied in the new release. WARN then silently accepts a new path unless you also look at messages and PLAN_TABLE. ERROR is the “do not install a new path” switch. APREUSE(ERROR) plus PLANMGMT(EXTENDED) is a common pattern for critical packages: either you keep the old path, or the rebind does not succeed.

Packages bound on very old Db2 levels may need an intermediate bind before APREUSE is supported. Check IBM “reusing and comparing access paths” for your version.

APCOMPARE — detect a different path

APCOMPARE does not try to force the old path. It compares the newly selected path to the previous one.

• WARN — accept new paths; write mismatch information (including PLAN_TABLE.REMARKS) so you can review
• ERROR — fail if any path differs

Reuse and compare are independent:

• Reuse can fail while the newly chosen path still matches the old one (comparison succeeds)
• Reuse can succeed and comparison will also succeed
• APREUSE(WARN) + APCOMPARE(WARN) — keep old paths where possible, report all changes
• APCOMPARE(WARN) alone — allow improvements, but make a list of what changed
• APREUSE(ERROR) + APCOMPARE(ERROR) — freeze; fail on reuse failure or any difference

Always add EXPLAIN(YES) or EXPLAIN(ONLY) when you compare or reuse, so PLAN_TABLE has the evidence. EXPLAIN(ONLY) is the rehearsal: generate EXPLAIN rows (and test reuse/compare) without replacing the running package the same way a normal REBIND would.

A practical sequence

1. Keep PLANMGMT(EXTENDED) as the shop default.
2. Before a mass REBIND, run EXPLAIN(ONLY) with APCOMPARE(WARN) to size the change.
3. Rebind packages that can tolerate new paths with APCOMPARE(WARN) and no reuse, or APREUSE(WARN), then performance-test the ones that changed.
4. Freeze critical packages with APREUSE(ERROR) (and often APCOMPARE(ERROR)).
5. If production regresses, SWITCH to PREVIOUS or ORIGINAL, then investigate with PLAN_TABLE before trying another REBIND.

Dynamic SQL stability

Dynamic statements live in the dynamic statement cache. Same SQL text (and compatible attributes) can reuse a prepared path. RUNSTATS, DDL, and some authorization changes invalidate cache entries. The next PREPARE is a new optimization.

Access path stabilization (dynamic query capture) records a path in catalog tables such as SYSIBM.SYSDYNQRY so matching statements can keep that path even across cache loss. Commands such as START DYNQUERYCAPTURE select statements by monitor criteria. Stabilization is for the high-value dynamic statements you have already proven—not for every ad-hoc QMF query.

Parameter markers and REOPT still matter: a stabilized or cached path that assumed a rare literal can be wrong for a popular one. Stability is not a substitute for representative bind-time values or REOPT when data is extremely skewed.

Hints versus stability

Optimization hints (OPTHINT, PLAN_TABLE hints, statement-level hints, optimization profiles) pin a path by telling the optimizer what to use. APREUSE is a hint generated from the previous package. Prefer reuse and plan management for “keep what we have.” Use explicit hints when reuse cannot express the path you need, and document them—hints are easy to forget until an index drop makes them fail.

Explain It Like I'm Five

Binding a package is writing a treasure map for how to fetch rows. Sometimes someone redraws the map because the island “looks different” after a storm (new statistics). Plan management keeps photocopies of the old map in a drawer (previous and original). APREUSE says “trace the old map, do not invent a new one.” APCOMPARE says “you may draw a new map, but circle every place it differs.” SWITCH is opening the drawer and using yesterday's map when the new one leads into a swamp.

Exercises

1. Display ZPARM PLANMGMT on your subsystem (or ask operations) and note BASIC versus EXTENDED.
2. Pick a sandbox package. REBIND with EXPLAIN(YES) APCOMPARE(WARN) and read PLAN_TABLE.REMARKS and HINT_USED.
3. Explain the difference in one sentence each: PLANMGMT SWITCH vs APREUSE vs APCOMPARE.
4. List three events at your shop that could invalidate the dynamic statement cache.
5. Design a rebind weekend plan for 10 critical packages and 200 others: which get APREUSE(ERROR), which get APCOMPARE(WARN) only?