OPTIMIZATION HINTS field (OPTHINTS subsystem parameter)
The OPTHINTS subsystem parameter specifies whether Db2 applies various methods for influencing access path selection when static SQL statements are bound or dynamic SQL statements are prepared.
Db2 applies matching various methods for influencing access path selection for SQL statements only when the value of the OPTHINTS subsystem parameter is set to YES.
Examples of these methods include:
- Statement-level predicate selectivity overrides.
- Statement-level access paths.
- Statement-level optimization parameters.
- PLAN_TABLE access path hints.
| Acceptable values: | NO, YES |
|---|---|
| Default: | NO |
| Update: | option 29 on panel DSNTIPB |
| DSNZPxxx: | DSN6SPRM OPTHINTS |
If you accept the default value, you the methods are not applied and the statements are optimized normally.
When the value is set to YES, Db2 applies hints in the following situations:
- For rows in a PLAN_TABLE instance that contain a value in the OPTHINT column that matches the value of the OPTHINT bind option or the CURRENT OPTIMIZATION HINT special register.
- For any instance of a statement whose application environment and text matches rows in the SYSIBM.SYSQUERY catalog table.