CACHE DYN STABILITY field (CACHEDYN_STABILIZATION subsystem parameter)
The CACHEDYN_STABILIZATION subsystem parameter specifies whether dynamic SQL statements can be captured for stabilization and whether Db2 uses the captured run time structures to process stabilized dynamic SQL statements that are not present in the dynamic statement cache. Db2 avoids processing full prepares when it uses the captured run time structures.
This subsystem parameter is new in Db2 12. It can be set at any function level, but it takes effect only after activation of function level 500 or higher.
| Acceptable values: | BOTH, CAPTURE, LOAD, NONE |
|---|---|
| Default: | BOTH |
| Data sharing scope: | Same setting recommended on all members |
| Update: | option 29 on panel DSNTIPB |
| Online changeable: | Yes |
| DSNZPxxx: | DSN6SPRM.CACHEDYN_STABILIZATION |
The following table summarizes the effects of the CACHEDYN_STABILIZATION subsystem parameter on the processing of START DYNQUERYCAPTURE commands and dynamic SQL statements:
Effects of CACHEDYN_STABILIZATION subsystem parameter values
| CACHEDYN_STABILIZATION value | Db2 accepts START DYNQUERYCAPTURE commands and captures run time structures for in-progress MONITOR(YES) commands ? | Db2 dynamic SQL uses captured run time structures to avoid full prepares for dynamic SQL? |
|---|---|---|
| BOTH | Yes | Yes |
| CAPTURE | Yes | No |
| LOAD | No | Yes |
| NONE | No | No |
- BOTH
- Dynamic SQL statements are captured to the catalog, and Db2 uses captures access paths for stabilized statements.
- CAPTURE
- Dynamic SQL statements can be captured to the catalog, but Db2 does not use any captured access paths.
- LOAD
- Db2 uses captured access paths for dynamic SQL statements, but statements cannot be captured for stabilization.
- NONE
- Dynamic statements cannot be captured and Db2 does not use any captured access paths.
