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.

Begin program-specific programming interface information. 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.

Table 1. PLAN_TABLE columns that DB2 validates
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.
Start of changeMATCHCOLSEnd of change Start of changeThis value is used only when ACCESSTYPE is IN, N, or HN. The value must be greater than or equal to 0.End of change
TABNO This value is required only when the following columns do not uniquely identify a particular table reference:
  • CREATOR
  • TNAME
  • CORRELATION_NAME
  • QBLOCKNO
This situation might occur when the same table is referenced within multiple views with the same CORRELATION_NAME.

This field is ignored when it is not needed.
ACCESSTYPE Start of changeThe access method for the table. For the list of accepted values, see PLAN_TABLE.
Start of changeEach 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:
  • DI
  • DU
  • MH
  • MI
  • MU
  • MX
End of change

Any row that contains ACCESSTYPE='A' invalidates the specified access path.

End of change
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.
  • Start of changeHEnd of change
  • Start of changeHNEnd of change
  • I
  • I1
  • Start of changeINEnd of change
  • N
  • Start of changeNREnd of change

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.
Start of changeSORTC_ORDERBY and SORTC_GROUPBYEnd of change Start of changeStart of changeFor a list of accepted values, see PLAN_TABLE. Unexpected values are ignored.End of changeEnd of change
PREFETCH Start of changeThis 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.

End of change
Start of changeCOLUMN_FN_EVALEnd of change Start of changeStart of changeFor a list of accepted values, see PLAN_TABLE. Unexpected values are ignored.End of changeEnd of change
PAGE_RANGE Must be Y, N or blank. Any other value invalidates the specified access path.
Start of changeJOIN_TYPEEnd of change Start of changeStart of changeFor a list of accepted values, see PLAN_TABLE. Unexpected values are ignored.End of changeEnd of change
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:
  • C
  • I
  • X
  • null
All of the restrictions involving parallelism still apply when using access path hints. If the specified mode cannot be performed, the specified access path are either invalidated or the mode is modified. A possible result is that the query runs without parallelism. Start of changeA null value indicates no parallelism.End of change

Start of changeIf 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.End of change
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.Start of changeA null value indicates no parallelism.End of change 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.

Start of changeIf 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.End of change
Start of changeSORTN_PGROUP_ID and SORTC_PGROUP_IDEnd of change Start of changeMust be a positive number or null. A null value indicates no parallel sort for the corresponding table.End of change
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.
Start of changeMERGCEnd of change Start of changeMust be Y, or N.End of change

End program-specific programming interface information.