This type of hint tries to enforce a particular access path for an SQL statement issued by a specific single authorization ID. It uses rows in a PLAN_TABLE instance owned by the same authorization ID to apply hints based on the values specified for the OPTHINT bind option or the CURRENT OPTIMIZATION HINT special register.
Statement-level access path hints (DB2 10 for z/OS NFM or later)
This type of hint specifies that; DB2 tries to use specified PLAN_TABLE rows to determine the access path for matching SQL statements. You can also create access path hints that apply only to matching statements from a specified scope.
Optimization parameter hints (DB2 10 for z/OS NFM or later)
This type of hint specifies that DB2 uses certain optimization parameters, such as the following subsystem parameters and options, to process all statements that match the hint:
- REOPT bind option
- STARJOIN subsystem parameter
- PARAMDEG subsystem parameter (MAX_PAR_DEGREE column)
- CDSSRDEF subsystem parameter (DEF_CURR_DEGREE column)
- SJTABLES subsystem parameter
You can also create hints that specify optimization parameters and that apply only to matching statements from a specified scope.
If you are not sure which type of hint is right for your situation, you can define all these in the editor, and then deploy them one at a time for testing