Interactions of methods for influencing access paths

Statement level-methods for influencing access path selection and PLAN_TABLE optimization hints can be used in combination for the same statements. However, certain restrictions apply.

Catalog table rows that influence access paths for matching statements can coexist with PLAN_TABLE optimization hints that apply to the same statements. Db2 also creates and uses rows in the same catalog tables when you specify access path reuse at bind or rebind.

Different methods of influencing access paths can coexist. However, Db2 applies only one method. When more than one coexisting method might apply to the same statement, only the first that applies is used, in the following order of precedence:
  1. PLAN_TABLE access path hints
  2. Statement-level access paths or parameters for a specific version, collection, and package.
  3. Statement-level access paths or parameters for a specific collection and package.
  4. Statement-level access paths or parameters that have a system-wide scope.
  5. Statement-level access paths that are created internally by Db2 for access path reuse
  6. Statement-level predicate selectivity overrides

Statement-level access paths cannot coexist with other methods (such as optimization parameters or selectivity overrides) for the same SQL statement in the same application context (schema and package) when a BIND QUERY command is issued. The following DSN_USERQUERY_TABLE columns control the types of hints that are created when you issue a BIND QUERY command.

  • SELECTIVITY_OVERRIDE
  • ACCESSPATH_HINT
  • OPTION_OVERRIDE

When either of ACCESSPATH_HINT or OPTION_OVERRIDE columns are blank when the BIND QUERY command is issued, both must be blank. Blank values are supported for compatibility. However, it is best not to use blank values. When these values are blank, Db2 uses data from several input tables to determine how to populate the catalog tables. The result is sometimes difficult to predict, especially when EXPLAIN output tables are used. When values are specified for these columns, the SELECTIVITY_OVERRIDE and OPTION_OVERRIDE columns can contain 'Y' values at the same time. However, when either of these values is 'Y', ACCESSPATH_HINT must be 'N'. Similarly, whenever ACCESSPATH_HINT is set to 'Y', the other two columns must both be set to 'N'. The following table summarizes the valid combinations.

Table 1. Summary of valid combinations for hint type columns in DSN_USER_QUERY_TABLE
Result of Combination
ACCESSPATH_
HINT column
OPTION_
OVERRIDE column
SELECTIVITY_
OVERRIDE column
Access paths are specified 'Y' 'N' 'N'
Access paths are specified 'Y' blank 'N'
Option and selectivity rows are specified 'N' 'Y' 'Y'
Only option overrides are specified. 'N' 'Y' 'N'
Only selectivity overrides are specified. 'N' 'N' 'Y'
Option overrides might be specified, depending on the values of other columns 'N' blank 'Y' or 'N'
Access paths and optimization parameters might be specified, depending on values in other columns, and in PLAN_TABLE.

Access paths take precedence over other methods. Selectivity overrides are not specified. This combination is provided for compatibility. However, it is best to specify the values of each of these columns when you create new hints or overrides.

blank blank 'N'
You cannot specify in statement-level access paths in combination with other statement-level methods of influencing access path for the same SQL statement in the same application context. However, you can override predicate selectivities and specify statement-level optimization parameters for the same statement in the same application context.
  • Overriding the selectivities of predicates.
  • Specifying statement-level optimization parameters.
  • Specifying statement level access paths.
When you issue a BIND QUERY command, Db2 replaces existing SYSIBM.SYSQUERY catalog table rows that apply to the same statement in the same application context.

For example, assume that SYSIBM.SYSQUERY catalog tables already contains a row that specifies a statement where SQL-text statement text, and S1 schema:

STMTTEXT  SCHEMA  PACKAGE
SQL-text  S1        

Assume that you then issue BIND QUERY commands in sequence when the DSN_USERQUERY table contains the specified rows:

  1. A row that specifies only the SQL-text. Db2 replaces the existing row. The schema-specific catalog table rows are replaced by global rows for matching statements. The SYSIBM.SYSQUERY table still contains only one row.
    STMTTEXT  SCHEMA  PACKAGE
    SQL-text              
  2. A row that specifies SQL-text and the S2 schema. The global catalog table rows are replaced by the new schema-specific rows. The SYSIBM.SYSQUERY table now contains only one row:
    STMTTEXT  SCHEMA  PACKAGE
    SQL-text  S2          
  3. A row that specifies SQL-text and the S1 schema. The new row is inserted and does not replace the existing row. The SYSIBM.SYSQUERY table now contains two rows:
    STMTTEXT  SCHEMA  PACKAGE
    SQL-text  S2          
    SQL-text  S1          
  4. A row that specifies SQL-text, the S1 schema, and the P1 package. The SYSIBM.SYSQUERY table now contains three rows:
    STMTTEXT  SCHEMA  PACKAGE
    SQL-text  S2
    SQL-text  S1
    SQL-text  S1      P1
  5. A row that specifies SQL-text, the S1 schema, and the P2 package. Db2 inserts a new row. The SYSIBM.SYSQUERY table now contains four rows:
    STMTTEXT  SCHEMA  PACKAGE
    SQL-text  S2        
    SQL-text  S1        
    SQL-text  S1      P1
    SQL-text  S1      P2