OPTIMIZATION HINTS field (OPTHINTS subsystem parameter)

Start of changeThe 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.End of change

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:

  • Start of changeStatement-level access paths.End of change
  • Start of changeStatement-level optimization parameters.End of change
  • PLAN_TABLE access path hints.
Acceptable values: NO, YES
Default: NO
Update: Start of changeoption 19 on panel DSNTIPBEnd of change
DSNZPxxx: DSN6SPRM OPTHINTS

If you accept the default value, you the methods are not applied and the statements are optimized normally.

Start of changeWhen 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.
  • Start of changeFor any instance of a statement whose application environment and text matches rows in the SYSIBM.SYSQUERY catalog table.End of change
End of change