Cost categories

Db2 uses cost categories to differentiate estimates for which adequate information is available from those for which it is not.

Begin program-specific programming interface information. You probably wouldn't want to spend a lot of time tuning a query based on estimates that are returned in cost category B, because the actual cost could be radically different based on such things as what value is in a host variable, or how many levels of nested triggers and user-defined functions exist.

Similarly, if system administrators use these estimates as input into the resource limit specification table for governing (either predictive or reactive), they probably would want to give much greater latitude for statements in cost category B than for those in cost category A.

Because of the uncertainty involved, category B statements are also good candidates for reactive governing.

Cost category A

Db2 puts everything that doesn't fall into category B into category A.

Cost category B

Db2 puts a statement's estimate into cost category B when any of the following conditions exist:

  • The statement has UDFs.
  • Triggers are defined for the target table:
    • The statement uses an insert operation, and insert triggers are defined on the target table.
    • The statement uses an update operation, and update triggers are defined on the target table.
    • The statement uses a delete operation, and delete triggers are defined on the target table.
  • The target table of a delete statement has referential constraints defined on it as the parent table, and the delete rules are either CASCADE or SET NULL.
  • The WHERE clause predicate has one of the following forms:
    • COL op constant, and the constant is a host variable, parameter marker, or special register. The operator can be >, >=, <, <=, LIKE, or NOT LIKE.
    • COL BETWEEN constant AND constant where either constant is a host variable, parameter marker, or special register.
    • LIKE with an escape clause that contains a host variable.
  • The cardinality statistics are missing for one or more tables that are used in the statement.
  • A subselect in the SQL statement contains a HAVING clause. End program-specific programming interface information.