Stabilizing dynamic SQL statements

You can use the dynamic SQL plan stability feature of Db2 to help achieve access path stability for dynamic SQL statements. When this feature is enabled, Db2 stores and retrieves statement cache structures in the catalog to avoid full prepare operations.

Before you begin

Before you can stabilize dynamic SQL statements, all of the following conditions must be true:

• The subsystem must be running on Db2 12 function level 500 or higher.
• The CACHEDYN subsystem parameter must be set to YES, and the CACHEDYN_STABILIZATION subsystem parameter must be set to either BOTH or CAPTURE. (To change these parameter values, follow the instructions in Managing Db2 subsystem parameters.)

To stabilize dynamic SQL statements:

1. Identify dynamic SQL statements to stabilize.
2. Capture dynamic SQL statements for stabilization.
3. Optional: View active dynamic query capture monitors.
4. Optional: View stabilized dynamic SQL statements.
5. Optional: Remove stabilized SQL statements from the catalog.