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.
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
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
Prakash C Singh
IBM Certified DB2 DBA