Validation of specified access paths
Db2 cannot always use access paths that you specify. When a specified access path cannot be used, Db2 marks the specified access path as invalid.
If an access path that you
specify has problems, Db2 invalidates
it for an entire query block. In that event, Db2 determines
the access path as if no access path was specified.
Db2 uses only the PLAN_TABLE columns that are shown in the following table when it validates specified access paths.
| Column or columns | Accepted values or explanation |
|---|---|
| QUERYNO, APPLNAME, PROGNAME, VERSION, COLLID, OPTHINT | Must match the value of the current query number, application name, program name, version id, collection id, and the CURRENT OPTIMIZATION HINT special register respectively. |
| QBLOCKNO, PLANNO, and MIXOPSEQ | Must be provided to identify PLAN_TABLE rows |
| METHOD | Must be 0, 1, 2, 3, or 4. Any other value invalidates the specified access path. |
| CREATOR and TNAME | Must be specified and must name a table, materialized view, materialized nested table expression. Blank if METHOD is 3. If a table is named that does not exist or is not involved in the query, then the specified access paths are invalid. |
| MATCHCOLS | This value is used only when ACCESSTYPE is IN, N, or HN. The value must be greater than or equal to 0. |
| TABNO | This value is required only when the following
columns do not uniquely identify a particular table reference:
This field is ignored when it is not needed. |
| ACCESSTYPE | The access method for the
table. For the list of accepted values, see PLAN_TABLE. Each row that contains one of the following values
must be preceded by a row that contains ACCESSTYPE='M', or the specified
access path is invalidated:
Any row that contains ACCESSTYPE='A' invalidates the specified access path. |
| ACCESSCREATOR and ACCESSNAME | Ignored
if ACCESSTYPE is R or M. If ACCESSTYPE contains any of the following
values, then these fields must identify an index on the specified
table.
If the index does not exist, or if the index is defined on a different table, then the specified access paths are invalid. Also, if the specified index cannot be used, the specified access paths are invalid. |
| SORTN_JOIN and SORTC_JOIN | Must be Y, N or blank. Any other value invalidates
the specified access path. This value determines if Db2 should sort the new (SORTN_JOIN) or composite (SORTC_JOIN) table. This value is ignored if the specified join method, join sequence, access type and access name dictate whether a sort of the new or composite tables is required. |
| SORTC_ORDERBY and SORTC_GROUPBY | For a list of accepted values, see PLAN_TABLE. Unexpected values are ignored. |
| PREFETCH | This
value determines the type of prefetch that Db2 uses.
For a list of accepted values, see PLAN_TABLE. This value is ignored if the specified access type and access name dictates the type of prefetch required. |
| COLUMN_FN_EVAL | For a list of accepted values, see PLAN_TABLE. Unexpected values are ignored. |
| PAGE_RANGE | Must be Y, N or blank. Any other value invalidates the specified access path. |
| JOIN_TYPE | For a list of accepted values, see PLAN_TABLE. Unexpected values are ignored. |
| PARALLELISM_MODE | This value is used only if it is possible to
run the query in parallel; that is, the SET CURRENT DEGREE special
register contains ANY, or the plan or package was bound with DEGREE(ANY). If
parallelism is possible, this column must contain one of the following
values:
If the plan table contains multiple specifications for parallelism, Db2 uses only the first one. Db2 does not compare multiple specified access paths to check consistency. |
| ACCESS_DEGREE or JOIN_DEGREE | If PARALLELISM_MODE is specified, use this field
to specify the degree of parallelism. If you specify a degree of parallelism,
this must a number greater than zero, and Db2 might
adjust the parallel degree from what you set here.A null value indicates no parallelism. If you want Db2 to determine
the degree, do not enter a value in this field. If you specify a value for ACCESS_DEGREE or JOIN_DEGREE, you must also specify a corresponding ACCESS_PGROUP_ID and JOIN_PGROUP_ID. If Db2 uses a hint for parallelism, other than for IN-list parallelism or a hint for degree=0, Db2 uses the hinted degree of parallelism unconditionally, regardless of the value of the PARAMDEG subsystem parameter. Consequently, be careful to ensure that hints specify reasonable degrees of parallelism. |
| SORTN_PGROUP_ID and SORTC_PGROUP_ID | Must be a positive number or null. A null value indicates no parallel sort for the corresponding table. |
| WHEN_OPTIMIZE | Must be R, B, or blank. Any other value invalidates
the specified access path. When a statement in a plan that is bound with REOPT(ALWAYS) qualifies for reoptimization at run time, and you have provided optimization hints for that statement, the value of WHEN_OPTIMIZE determines whether Db2 reoptimizes the statement at run time. If the value of WHEN_OPTIMIZE is blank or B, Db2 uses only the access path that is provided by the optimization hints at bind time. If the value of WHEN_OPTIMIZE is R, Db2 uses the specified access paths at bind time to determine the access path. At run time, Db2 searches the PLAN_TABLE for hints again, and if specified access paths for the statement are still in the PLAN_TABLE and are still valid, Db2 optimizes the access path again. |
| QBLOCK_TYPE | A value must be specified. A blank in the QBLOCK_TYPE column invalidates the specified access path. |
| PRIMARY_ACCESSTYPE | Must be D, T, or blank. Any other value invalidates the specified access paths. |
| MERGC | Must be Y, or N. |
| EXPANSION_REASON | Db2 uses this column only for statements that reference archive or temporal tables and have multiple plans. |
