How to read DSN_STATEMNT_TABLE

Hi,



While analyzing the SQL queries, it is really worth to calculate the cost of the query. This cost is populated into DSN_STATEMNT_TABLE by the explain SQL statement.



Here are some important column info



1) QUERYNO:A no. identifying the statement being explained.



2) STMT_TYPE:

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

3) COST_CATEGORY:

A - DB2 has enough information to calculate cost without using default values

B - DB2 forced to use default values. See REASON column why DB2 choose default values



4) PROCMS:The estimated processor cost in milliseconds for SQL statements. This is the most important column to look at while analyzing the SQL query



5) PROCSU:The estimated processor cost in Service Units for SQL statements. This is also worth to look at.



6) REASON:Gives the reason why it is in cost category B.

Having ClauseHost Variables - Also Parameter Markers or Special Registers

REFERENTIAL CONSTRAINTS - CASCADE and SET NULL exist for DELETE statement

TABLE CARDINALITY - Cardinality statistics missing

UDF TRIGGERS - defined on target table of INSERT, UPDATE or DELETE statement





Cheers...

Prakash C Singh

IBM Certified DB2 DBA