Predicates that Db2 generates

Db2 might generate certain predicates to improve access path selection.

The goal of most generated predicates is to increase the likelihood that Db2 can select more efficient and index-based access paths for a query. Db2 generates these predicates automatically, and you generally do not need to take action to benefit from them. However, you might notice the additional predicates in EXPLAIN output, and when you use query tuning tools to view access path diagrams or formatted query text.

Db2 might remove either the generated predicate or the original predicate if it is not required for the selected access path.

Db2 might generate additional predicates when it encounters the following types of situations:

Stage 2 predicates on DATE and YEAR functions

For example, when Db2 encounters a predicate of the following form:

DATE(timestamp-column) = non-column-expression

Db2 might generate an additional indexable stage 1 BETWEEN predicate, which uses the computed first and last timestamp values for the date returned by the DATE function. Db2 might also generate additional range predicates that use computed first and last timestamp values. The format of the generated range predicate depends on the range operator of the original predicate. Similarly, Db2 might also generate additional predicates for predicates that contain YEAR functions.

Stage 2 predicates on SUBSTR functions

For example, when Db2 encounters a predicate of the following form

SUBSTR(column-name, 1, length) = non-column-expression

Db2 might generate an additional stage 1 indexable predicate that compares computed boundaries for the predicate.

Predicate through transitive closure

Db2 might generate additional predicates through transitive closure to improve access path efficiency. Transitive closure occurs when several predicates taken in combination logically imply the existence of additional predicates. Because those implied predicates might enable the selection of a more efficient access path, Db2 might generate such predicates.