Estimating the cost of SQL statements

You can use EXPLAIN to populate a statement table, owner. DSN_STATEMNT_TABLE, at the same time as your PLAN_TABLE is being populated.

About this task

Begin program-specific programming interface information. Db2 provides cost estimates, in service units and in milliseconds, for SELECT, INSERT, UPDATE, and DELETE statements, both static and dynamic. The estimates do not take into account several factors, including cost adjustments that are caused by parallel processing, or the use of triggers or user-defined functions.

Procedure

Use the information provided in the statement table to:

  • Determine if a statement is not going to perform within range of your service-level agreements and to tune accordingly.

    Db2 puts its cost estimate into one of two cost categories: category A or category B. Estimates that go into cost category A are the ones for which Db2 has adequate information to make an estimate. That estimate is not likely to be 100% accurate, but is likely to be more accurate than any estimate that is in cost category B.

    Db2 puts estimates into cost category B when it is forced to use default values for its estimates, such as when no statistics are available, or because host variables are used in a query.

  • Give a system programmer a basis for entering service-unit values by which to govern dynamic statements with predictive governing.
    End program-specific programming interface information.