DB2 dynamic statement cache

Why the cache exists

Static SQL is prepared at BIND time and stored in packages. Dynamic SQL is prepared at run time—JDBC, ODBC, REXX, Python, many frameworks, and ad-hoc tools all live here. When the same SELECT runs thousands of times per minute, preparing it thousands of times is wasteful. The dynamic statement cache stores the prepared form so Db2 can reuse it.

Caching is not magic immunity. Object changes, RUNSTATS, invalidation, cache size limits, and mismatched statement text all force new prepares. Your job as a DBA or developer is to maximize intentional reuse and understand when a miss is healthy versus harmful.

Statement cache entries

A statement cache entry is the in-memory record of a prepared dynamic statement: the SQL text (as Db2 keys it), the executable runtime structures, and attributes that affect matching. The cache is finite. Under pressure, Db2 can discard least-used entries. That discard looks like a miss the next time the statement arrives.

Subsystem parameters such as CACHEDYN (and related sizing/controls in your release) determine whether caching is on and how generous the pool is. Statements that cannot be cached—certain DGTT patterns, some REOPT behaviors, or environments with caching disabled—never build a reusable entry.

Cache hit versus cache miss

• Cache hit — Db2 finds a usable matching entry and skips the expensive full prepare path for that execution
• Cache miss — no usable entry; Db2 prepares, and usually tries to insert a new entry for next time

High hit ratios usually mean lower prepare CPU and more stable plans for repeating SQL. A sudden drop in hits after a change window often means invalidation, literal explosion, or a smaller effective cache. Track prepare counts and cache statistics in Db2 statistics/accounting rather than guessing from wall-clock alone.

Statement matching

Statement matching decides whether the incoming PREPARE can use an existing entry. Exact text matters. Whitespace and case rules follow Db2’s matching rules for your release—do not assume every cosmetic change is ignored. Auth context and special registers also participate: two users with different CURRENT PATH values may not share an entry even if the SQL string looks identical.

Parameter markers

Parameter markers (? in dynamic SQL) are the developer’s best friend for cache reuse. One statement text serves many values:

1
2
3
SELECT CUSTNO, BALANCE
  FROM ACCOUNT
 WHERE CUSTNO = ?

Contrast that with building SQL by string concatenation for every customer number. Each literal value creates a distinct text, flooding the cache with near-duplicates and driving misses. Prefer markers in JDBC PreparedStatement, ODBC parameter binds, and similar APIs. Markers also help security (less SQL injection surface) while they help performance.

Statement concentration

When you cannot change the application overnight, statement concentration can rewrite literals into markers so similar statements share an entry. In SQL you may see CONCENTRATE STATEMENTS WITH LITERALS (and related prepare attributes / client settings depending on the stack). Concentration raises hit rates for literal-heavy frameworks.

Trade-off: dynamic plan stability (stabilized dynamic SQL) is tightly coupled to the cache and, per IBM guidance, concentrated statements are excluded from that stability path. Choose concentration for hit-rate emergencies; choose markers + stabilization when you need long-term access-path stability for dynamic SQL.

Dynamic statement cache statistics

Dynamic statement cache statistics tell you whether the cache is earning its keep: hits, misses, inserts, discards, and prepare activity. Look at subsystem statistics traces, monitor tools that surface DSC metrics, and statement-level accounting when diagnosing a prepare storm. Pair cache stats with EXPLAIN and REAL-TIME STATISTICS / catalog insights so you know whether a miss also changed the access path.

Operational questions to ask weekly:

• Is prepare CPU rising while transaction volume is flat?
• Did a release introduce unique literal SQL from a new ORM default?
• After RUNSTATS or DDL, did invalidations clear hot entries?

EXPLAIN and dynamic statements

You can EXPLAIN dynamic statements to capture access paths for what the cache is (or will be) running. Techniques include EXPLAIN on the statement text, capturing from the cache with IBM tooling, and using CURRENT EXPLAIN MODE patterns where appropriate. When a statement is stabilized, understanding the frozen path matters as much as understanding the in-memory cache copy.

If production performance changes but the SQL text did not, compare EXPLAIN before/after invalidation. A miss that rebuilds a bad plan is often worse than a miss that rebuilds the same plan—stabilization exists to reduce that second class of surprise.

Stabilized dynamic SQL (dynamic plan stability)

Stabilized dynamic SQL extends package-like stability to repeating cached dynamic statements. After a statement is stabilized, Db2 stores its statement cache structures in the catalog. On a later cache miss, Db2 can reload those structures instead of doing a full prepare. The goal is access-path stability comparable to static SQL for the dynamic statements you care about.

Prerequisites and limits matter: the statement must be cacheable; concentration, certain REOPT options, DGTTs, and CACHEDYN=NO scenarios fall outside the happy path. Stabilization groups (STBLGRP) let you manage sets of queries together for capture and FREE.

START, STOP, and DISPLAY DYNQUERYCAPTURE

-START DYNQUERYCAPTURE stabilizes qualified statements already in the dynamic statement cache when they meet your rules. Important options include:

• STBLGRP — stabilization group name for later FREE by group
• THRESHOLD — minimum execution count before stabilization is scheduled
• CURSQLID — scope to a CURRENT SQLID or * for all
• STMTID / STMTTKN — target one cached statement
• MONITOR(YES/NO) — also watch qualifiers that have not yet hit the threshold

1
2
3
-START DYNQUERYCAPTURE STBLGRP(APPS1) THRESHOLD(50) CURSQLID(APPUSER) MONITOR(YES)
-DISPLAY DYNQUERYCAPTURE
-STOP DYNQUERYCAPTURE

Example intent: stabilize dynamic SQL running under SQLID APPUSER once a statement has executed at least 50 times, and keep monitoring siblings that are not there yet. -STOP DYNQUERYCAPTURE ends that capture/monitor activity. -DISPLAY DYNQUERYCAPTURE confirms what is active.

FREE QUERY and FREE STABILIZED DYNAMIC QUERY

FREE QUERY (DSN subcommand) removes rows from certain catalog tables for one or more queries and, if those queries sit in the dynamic statement cache, purges them from the cache. Use it when you need to clear dynamic query artifacts that are no longer valid or wanted.

FREE STABILIZED DYNAMIC QUERY removes stabilized dynamic queries from the catalog tables that hold them and purges matching cache copies. Free by name and/or stabilization group when a stabilized path must go—after a deliberate statistics strategy change, for example.

1
2
FREE STABILIZED DYNAMIC QUERY STBLGRP(APPS1)
FREE QUERY ...

Freeing is a performance event, not only a cleanup chore. The next execution pays for prepare again (and, if not re-stabilized, may pick a new path). Schedule FREEs with the same care you give REBIND PACKAGE.

Practical tuning habits

Prefer parameter markers in new code. Monitor prepare CPU after every middleware upgrade. Use concentration only with eyes open about stabilization limits. Stabilize the top repeating dynamic statements that hurt when plans flip. Keep a runbook that pairs START DYNQUERYCAPTURE thresholds with the FREE commands you will use on rollback.

Remember the cache is shared infrastructure. One noisy ad-hoc user generating unique SQL can crowd out hot OLTP entries. Education and governors (profiles, RLF) protect the cache as much as enlarging it does.

Explain It Like I'm Five

Dynamic SQL is like asking the teacher to solve a math problem every time. The dynamic statement cache is a folder of already-solved problems. If you ask the exact same question again, the teacher pulls the folder (hit) instead of redoing all the work (miss). Using a blank (?) for the changing number keeps the question looking the same. Stabilizing a problem is photocopying the solved page into a locked filing cabinet so even if the desk folder is cleaned, the teacher can grab the photocopy instead of starting over.

Exercises

1. Rewrite a literal-heavy JDBC string into a PreparedStatement with one parameter marker. Explain how that changes cache matching.
2. List three reasons a statement might miss the cache even if the SQL looks familiar.
3. Draft a START DYNQUERYCAPTURE command with STBLGRP, THRESHOLD, and CURSQLID. State what MONITOR(YES) adds.
4. Describe when you would choose statement concentration versus stabilization.
5. Write the difference between FREE QUERY and FREE STABILIZED DYNAMIC QUERY in two sentences.