Limitations on specified access paths

DB2® cannot always apply the information that you provide to influence access path selection.

Begin program-specific programming interface information.
In certain situations, DB2 cannot apply specified access paths, and hints might be used differently from release to release, or not at all in certain releases. For example:
  • Query transformations, such as subquery transformation to join or materialization or merge of a view or table expression, cannot be forced or undone.

    A query that is not transformed in one release of DB2 might be transformed in a later release of DB2. If you use a hint in one release for a query that is not transformed, but the query is transformed in a later release, DB2 cannot use the hint in the later release.

  • DB2 might apply a specified access path differently in different releases, and an equivalent access path might not look the same in both releases. For example, before DB2 9 DB2 work files that represent non-correlated subqueries were not shown in the PLAN_TABLE. An access path that is based on the same hint that was used in earlier version might contain a row for such work file in newer releases.
  • DB2 ignores any PLAN_TABLE row that contains METHOD=3.
  • When access paths are specified, DB2 ignores any PLAN_TABLE row that contains the following values for the QBLOCK_TYPE column:
    • INSERT
    • UNION
    • UNIONA
    • INTERS
    • INTERA
    • EXCEPT
    • EXCEPTA
  • Start of changeIf the PLAN_TABLE contains multiple rows that specify for parallelism, DB2 uses only the first one. It does not compare multiple rows to check for consistency.End of change
  • Start of changeIf parallelism is specified, DB2 uses the hinted degree of parallelism unconditionally, regardless of the value of the PARAMDEG subsystem parameter. IN-list parallelism and hints for DEGREE=0 are exceptions to this rule. Use care to specify only reasonable degrees of parallelism.End of change
End program-specific programming interface information.