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.
- PLAN_TABLE access path hints
- Statement-level access paths or parameters for a specific version, collection, and package.
- Statement-level access paths or parameters for a specific collection and package.
- Statement-level access paths or parameters that have a system-wide scope.
- Statement-level access paths that are created internally by Db2 for access path reuse
- 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.
| 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' |
- Overriding the selectivities of predicates.
- Specifying statement-level optimization parameters.
- Specifying statement level access paths.
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:
- 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 - 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 - 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 - 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 - 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