Dynamic SQL plan stability

When you enable dynamic SQL plan stability, Db2 stores statement cache structures for specified dynamic SQL statements in the Db2 catalog. Whenever a stabilized dynamic SQL statement is not present in the dynamic statement cache when issued, Db2 can load the statement cache structures from the Db2 catalog and avoid the full prepare operation. The goal is to achieve access path stability comparable to static SQL statements for repeating cached dynamic SQL statements.

Dynamic SQL statements are more often susceptible to access path regressions than static SQL statements. Db2 prepares the access path for static SQL statements when packages are bound, and the same access path is reused until the next BIND or REBIND for the package. For dynamic SQL statements, Db2 uses the full prepare process for any dynamic SQL statement that is not found in the dynamic statement cache, unless it is stabilized. For example, stabilized dynamic SQL statements are not exposed to the full prepare process by the following events in the Db2 subsystem that do invalidate or remove statements from the dynamic statement cache:

• Db2 is stopped and restarted
• Statements exit and reenter the dynamic statement cache
• Statistics are collected by RUNSTATS or other utilities for changing data
• Subsystem parameters change
• Maintenance is applied to Db2
• Release migration

Another performance benefit of dynamic SQL plan stability can be realized during heavy cache-load periods, such as when application workloads resume after weekend activity. Db2 can avoid the full prepare process for stabilized dynamic SQL statements and load the cache structures from the Db2 catalog.

Db2 uses the full prepare process again for a stabilized dynamic SQL statement when any of the following events occur:

• The statement is removed from the catalog
• The statement is invalidated
• Other changes that result in a cache miss, such as the APPLCOMPAT subsystem parameter, and certain special registers, including DEGREE and OPTHINT, among others.

However, stabilizing dynamic SQL statements involves tradeoffs. Access path changes often improve performance, so you trade away those potential performance improvements for stability. The stabilized dynamic SQL statements also use storage space in the Db2 catalog to store the run-time structures.

Effort is also required for managing the stabilized dynamic SQL, including the following tasks:

• Identifying queries to stabilize, and determining execution thresholds to use
• Identifying when to enable Db2 to select new access paths for stabilized queries
• Managing the space used by catalog objects
• Determining when queries no longer execute and can be freed

Stabilization groups

As stabilization group is a label that associates a related set of stabilized dynamic SQL statements. You can use it as an eye-catcher, and as command input for managing a set of stabilized statements as a group.

Freeing stabilized dynamic SQL statements

To get a full prepare, or reclaim the Db2 catalog storage for unused stabilized dynamic SQL statements, you can issue the FREE STABILIZED DYNAMIC QUERY command. For more information, see Removing stabilized dynamic SQL statements from the Db2 catalog.