+395 A USER SPECIFIED OPTIMIZATION HINT IS INVALID (REASON CODE = reason-code)
Explanation
DB2® detected
that one or more optimization hints provided for this statement are
invalid. You can use the following reason-code values
to identify why a hint is invalid:
- reason-code
- The reason why a hint was invalid. Only one reason-code is
returned even if the hint contains multiple problems. The following
descriptions refer to the of the values in PLAN_TABLE columns for
the set of rows that contain the same value in the OPTHINT column.
- 2
- The value of the TABNO column is not valid.
Negative values and values greater than the total number of table
references are not valid.
The value of the TABNO column is validated only when the combination of the following columns does not identify a particular table reference within a query block:
- CREATOR
- TNAME
- CORRELATION_NAME
- QBLOCKNO
- 3
- A value is not specified for one of the following columns: CREATOR or TNAME
- 4
- The value of the TABNO column was not specified,
and the combination of the following columns does not uniquely identify
a table reference:
- CREATOR
- TNAME
- CORRELATION_NAME
- QBLOCKNO
- 5
- The value specified for the TABNO column contains a value in the valid range. However, the value does not correctly correspond to the values of the CREATOR, TNAME, and CORRELATION_NAME columns.
- 6
- The table identified by the values specified by the following
columns cannot be found within the query block that is identified
by the QBLOCKNO column:
- CREATOR
- TNAME
- CORRELATION_NAME
- 7
- The PAGE_RANGE column contains an invalid value.
- 8
- The PREFETCH column contains an invalid value.
- 9
- The METHOD column contains an invalid value.
- 10
- The SORTN_JOIN column contains an invalid value.
- 11
- The SORTC_JOIN column contains an invalid value.
- 12
- The ACCESSTYPE column contains an invalid value.
- 13
- One of the following columns contains an invalid value: ACCESSCREATOR or ACCESSNAME.
- 15
- The hint specifies an index that cannot be used.
- 16
- The hint specified multi-index access that cannot be used.
- 17
- The hint specifies an invalid combination of ACCESSTYPE values.
- 18
- The join method for the inner table of a join was not specified in the METHOD column.
- 19
- The nested-loop join that is specified by the value of the METHOD column cannot be used.
- 20
- The merge join that is specified by the value of the METHOD column cannot be used.
- 21
- The hybrid join that is specified by the value of the METHOD column cannot be used.
- 22
- The mode of parallelism that is specified by the value of the PARALLELISM_MODE column requested cannot be used.
- 23
- The PARALLELISM_MODE column contains an invalid value.
- 24
- One of the following columns contains an invalid value: ACCESS_DEGREE or ACCESS_PGROUP_ID
- 25
- One of the following columns contains an invalid value: JOIN_DEGREE or JOIN_PGROUP_ID
- 26
- A row for a table reference that is required for the hint is missing. For query blocks that reference multiple tables, the hint must contain a row for each table. Required tables might not be mentioned in the statement text because DB2 uses materialization or transformations in the access path.
- 27
- The hint contains a redundant table reference
because a table that is identified by a combination of the following
columns appears in more than one row for the same query block:
- CREATOR
- TNAME
- CORRELATION_NAME
- TABNO (when required)
- 28
- The PRIMARY_ACCESSTYPE column contains an invalid value.
- 29
- The SORTN_GROUP_ID column contains an invalid value.
- 30
- The SORTN_GROUP_ID column contains an invalid value.
- 31
- No value is specified in the PARALLELISM_MODE column.
- 32
- One of the following columns contains an invalid value: CREATOR or TNAME
- 33
- The join sequence is incorrect.
- 34
- The column WHEN_OPTIMIZE contains an invalid or inconsistent value.
- 35
- The value of the ACCESSTYPE column cannot contain 'V' for the specified table.
- 36
- The number of dimension tables that are joined before the star join fact table exceeds the maximum number of dimensions that can be joined by a fact table index.
- 37
- When the value of the JOIN_TYPE column for the fact table is 'SJ', a nested-loop join must be used.
- 38
- The join sequence for dimension tables in a star join is incorrect because it does not match the sequence that is specified by the fact table index.
- 39
- The value of ACCESSTYPE column is 'O' but the specified access path cannot be used.
- 40
- Multiple index access cannot be used with the specified indexes .
- 41
- Duplicate hint input records found for the same table.
- 48
- Access path information for a correlated subquery is missing
- 50
- The APREUSE option was specified during a
bind or rebind operation and the hint was accepted. However, during
access path comparison, DB2 detected
that the resulting access path does not match the original access
path.
- 99
- An unexpected error occurred.
Only one reason-code is returned even if the hint contains multiple problems.
System action
The invalid hint is ignored and DB2 determines the access path without that hint. If other hints are supplied for the statement, DB2 might use those hints, if they are valid.
Programmer response
Examine the EXPLAIN output to determine which hints were used by DB2, and which hints were not used. If needed, correct the problem with the hints that were not used, or disable their use as hints to the optimizer.
Suppressing this SQLCODE
You can set the value of the SUPPRESS_HINT_SQLCODE_DYN to suppress SQLCODEs +394 and +395 for dynamic SQL statements.SQLSTATE
01628