3 Different Types of HINT in DB2

Srinimf
Srinimf

PLAN_TABLE hints
This type of hint to tries to enforce a particular access path for an SQL statement that is issued by a specific single authorization ID. It uses rows in a PLAN_TABLE instance that 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 three types in the editor, and then deploy them one at a time for testing

Read more at IBM

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.