OPTHINT bind option
The OPTHINT option controls whether access paths that are specified in PLAN_TABLE instances are used for static SQL statements.
| Command option | Option values | Used with |
|---|---|---|
| OPTHINT | (' hint-id ') |
Note:
- The OPTHINT bind option is not valid for REBIND of packages for native SQL procedures, native REST services, or advanced triggers.
The OPTHINT option also sets the default value
for special register CURRENT OPTIMIZATION HINT, which provides optimization
hints for dynamic SQL.
- OPTHINT('hint-id')
- A character string of up to 128 characters in length.
The value is used during query optimization to identify PLAN_TABLE
rows that specify the use a of a specific access path. The delimiters
can only be single quotation marks (').
If ' hint-id ' contains all blank characters, Db2 does not use optimization hints for static SQL statements.
Db2 uses optimization hints only when optimization hints are enabled for your system. To enable optimization hints, specify YES for the value of the OPTHINTS subsystem parameter.
Restriction: The PACKAGE does not inherit from the PLAN.
Default values for OPTHINT
| Process | Default value |
|---|---|
| BIND SERVICE | All blanks, use normal optimization |
| BIND PLAN | All blanks, use normal optimization |
| BIND PACKAGE | All blanks, use normal optimization |
| REBIND PLAN | Existing value |
| REBIND PACKAGE | Existing value |
Catalog records for OPTHINT
See the OPTHINT column in SYSPACKAGE catalog table and SYSPLAN catalog table.