Cost Estimates and the DSN_STATEMNT_TABLE

 <  Day Day Up  >  

Cost Estimates and the DSN_STATEMNT_TABLE

At the same time EXPLAIN populates the PLAN_TABLE with access path information, it also can populate cost estimate information into another table, DSN_STATEMNT_TABLE . The DSN_STATEMNT_TABLE is also referred to simply as the statement table . You can use the DDL in Listing 25.2 to create a DSN_STATEMNT_TABLE.

Listing 25.2. DDL to Create the DSN_STATEMNT_TABLE
 CREATE TABLE  creator  .DSN_STATEMNT_TABLE  (  QUERYNO               INTEGER        NOT NULL WITH DEFAULT,  APPLNAME              CHAR(128)      NOT NULL WITH DEFAULT,  PROGNAME              CHAR(128)      NOT NULL WITH DEFAULT,  COLLID                CHAR(128)       NOT NULL WITH DEFAULT,  GROUP_MEMBER          CHAR(8)        NOT NULL WITH DEFAULT,  EXPLAIN_TIME          TIMESTAMP      NOT NULL WITH DEFAULT,  STMT_TYPE             CHAR(6)        NOT NULL WITH DEFAULT,  COST_CATEGORY         CHAR(1)        NOT NULL WITH DEFAULT,  PROCMS                INTEGER        NOT NULL WITH DEFAULT,  PROCSU                INTEGER        NOT NULL WITH DEFAULT,  REASON                VARCHAR(254)   NOT NULL WITH DEFAULT,  STMT_ENCODE           CHAR(1)        NOT NULL WITH DEFAULT ) IN  database.tablespace  ; 

An EXPLAIN provides cost estimates, in service units and in milliseconds , for static and dynamic SELECT , INSERT , UPDATE , and DELETE statements. Keep in mind that the estimates are indeed just estimates. DB2 does not factor parallel processing, triggers, or user -defined functions into the cost estimation process.

The cost estimate information is useful in helping you to determine general performance characteristics of an application. You can use the cost estimates to determine roughly whether or not your programs can execute within planned service levels.

The cost estimates determined by DB2 will be tagged with a category. The category represents the confidence DB2 has in the estimate. There are two categories ”category A and category B. Category A estimates were formulated based on sufficient information. Estimates in category A are more likely to be closer to reality than estimates in category B. A cost estimate is tagged as category B if DB2 must use default values when formulating the estimate. This can occur when RUNSTATS has not been run or when host variables are used in a query.

Changes for DB2 Version 8

graphics/v8_icon.gif

The STMT_ENCODE column was added to the DSN_STATEMNT_TABLE as of DB2 Version 8 (to support multiple encoding schemes).


The DSN_STATEMNT_TABLE Columns

When EXPLAIN is run and an appropriate statement table exists, DB2 populates that table with SQL cost estimates. To review these estimates, you need to understand the meaning of the DSN_STATEMNT_TABLE columns. A description of every column of the DSN_STATEMNT_TABLE is provided in Table 25.2.

Table 25.2. DSN_STATEMNT_TABLE Columns

Column

Description

QUERYNO

Indicates an integer value assigned by the user issuing the EXPLAIN , or by DB2. Enables the user to differentiate between EXPLAIN statements.

APPLNAME

Contains the plan name for rows inserted as a result of running BIND PLAN specifying EXPLAIN(YES) . Contains the package name for rows inserted as a result of running BIND PACKAGE with EXPLAIN(YES) . Otherwise, contains blanks for rows inserted as a result of dynamic EXPLAIN statements.

PROGNAME

Contains the name of the program in which the SQL statement is embedded. If a dynamic EXPLAIN is issued from QMF, this column contains DSQIESQL .

COLLID

Contains the collection ID for the package.

GROUP_MEMBER

Indicates the member name of the DB2 that executed EXPLAIN . The column is blank if the DB2 subsystem was not in a data sharing environment when EXPLAIN was executed.

EXPLAIN_TIME

Indicates the time the plan or package for the statement or query block was explained. The time is the same as the BIND_TIME column in the PLAN_TABLE .

STMT_TYPE

The type of statement being explained. Possible values are as follow:

SELECT

SELECT

INSERT

INSERT

UPDATE

UPDATE

DELETE

DELETE

SELUPD

SELECT with FOR UPDATE OF

DELCUR

DELETE WHERE CURRENT OF CURSOR

UPDCUR

UPDATE WHERE CURRENT OF CURSOR

COST_CATEGORY

Indicates whether the estimate is in category A or B. Informs as to whether DB2 had to use default values when formulating cost estimates. Valid values are as follow:

A

DB2 had enough information to make a cost estimate without using default values.

B

At least one condition existed forcing DB2 to use default values. The REASON column outlines why DB2 was unable to put this estimate in cost category A.

PROCMS

The cost estimate in milliseconds, for the SQL statement (rounded up to the next whole integer). The maximum value is 2,147,483,647 milliseconds (the equivalent of about 24.8 days). If the estimated value exceeds this maximum, the maximum value is reported .

PROCSU

The cost estimate in service units, for the SQL statement (rounded up to the next whole integer). The maximum value for this cost is 2,147,483,647 service units. If the estimated value exceeds this maximum, the maximum value is reported.

REASON

A character string representing the reasons a cost estimate was tagged as category B.

HOST VARIABLES

The statement uses host variables, parameter markers, or special registers.

TABLE CARDINALITY

The cardinality statistics are missing for one or more of the tables that are used in the statement (or, the statement required materialization of views or nested table expressions).

UDF

The statement uses user-defined functions.

TRIGGERS

Triggers are defined on the target table of an INSERT , UPDATE , or DELETE statement.

REFERENTIAL

CONSTRAINTS

CASCADE or SET NULL referential constraints exist on the target table of a DELETE statement

STMT_ENCODE

Indicates the encoding scheme for the statement, as follows :

A

ASCII

E

ENCDIC

U

Unicode

M

Multiple CCSID sets


 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net