How to EXPLAIN your SQL query

Hi,

In continuation of my discussion regarding Plan and DSN_STATEMNT table, I am now giving you the code thru which you can EXPLAIN your SQL statements and find the parameters populated in both of the tables mentioned above. This will help you in your analysis of performance regarding SQL you are going to run.

Consideration:
1) You should have all the objects (Referenced in your SQL) created in the same env where you will Explain the SQL.
2) You tables must be runstated before running Explain
3) You can replace host variables with any Char/Int Literals in the SQL statements.
4) Make sure your PLAN_TABLE and DSN_STATEMNT_TABLE present in the same Env.

Here is one example of SQL code regarding How to Explain your query.
DELETE FROM PLAN_TABLE WHERE QUERYNO = 99999;
COMMIT;

EXPLAIN PLAN SET QUERYNO = 99999 FOR

SELECT B.STORE_NO, B.STOCK_ITEM, B.DELIVERY_DATE
FROM V1FOGH01 B

WHERE DELIVERY_DATE = (SELECT MAX(DELIVERY_DATE)
FROM V1FOGH01 A
WHERE A.STORE_NO = B.STORE_NO
AND A.STOCK_ITEM = B.STOCK_ITEM
AND A.QTY_TYPE ='7'
AND A.DELIVERY_DATE <= CURRENT DATE)
AND B.QTY_TYPE ='7';

SELECT * FROM PLAN_TABLE
WHERE QUERYNO = 99999
ORDER BY TIMESTAMP,QUERYNO,QBLOCKNO,PLANNO,MIXOPSEQ;

SELECT * FROM DSN_STATEMNT_TABLE
WHERE QUERYNO = 99999;
========

I have Added the Select statement so that immediately you can view the result. You can run this statement in Batch as well as Online (Spufi).

I have already discussed some important columns of PLAN_TABLE and DSN_STATEMNT_TABLE. So Enjoy while analysing the values you got for these 2 tables.


Regards,
Prakash C Singh
IBM Certified DB2 DBA.

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

EXPLAIN Tables (PLAN_TABLE ,DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE)

Just mangaed to get some time to post this information.

This is helpful while creating Explain tables to anayse a badly performing query.

I am currently working on DB2 V7 product.

There is one member in the installation library of DB2 production where you will extract the defination of EXPLAIN tables (PLAN_TABLE ,DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE). Also you can see the defination of the Optimizer hint tables too.

Here is the member you should look for D710.DSN1.SDSNSAMP(DSNTESC)

For the benefit of many I am putting the defination of these tables here


CREATE TABLE SCHEMANM.PLAN_TABLE
( "QUERYNO" INTEGER NOT NULL,
"QBLOCKNO" SMALLINT NOT NULL,
"APPLNAME" CHAR(8) NOT NULL,
"PROGNAME" CHAR(8) NOT NULL,
"PLANNO" SMALLINT NOT NULL,
"METHOD" SMALLINT NOT NULL,
"CREATOR" CHAR(8) NOT NULL,
"TNAME" CHAR(18) NOT NULL,
"TABNO" SMALLINT NOT NULL,
"ACCESSTYPE" CHAR(2) NOT NULL,
"MATCHCOLS" SMALLINT NOT NULL,
"ACCESSCREATOR" CHAR(8) NOT NULL,
"ACCESSNAME" CHAR(18) NOT NULL,
"INDEXONLY" CHAR(1) NOT NULL,
"SORTN_UNIQ" CHAR(1) NOT NULL,
"SORTN_JOIN" CHAR(1) NOT NULL,
"SORTN_ORDERBY" CHAR(1) NOT NULL,
"SORTN_GROUPBY" CHAR(1) NOT NULL,
"SORTC_UNIQ" CHAR(1) NOT NULL,
"SORTC_JOIN" CHAR(1) NOT NULL,
"SORTC_ORDERBY" CHAR(1) NOT NULL,
"SORTC_GROUPBY" CHAR(1) NOT NULL,
"TSLOCKMODE" CHAR(3) NOT NULL,
"TIMESTAMP" CHAR(16) NOT NULL,
"REMARKS" VARCHAR(254) NOT NULL,
"PREFETCH" CHAR(1) NOT NULL WITH DEFAULT,
"COLUMN_FN_EVAL" CHAR(1) NOT NULL WITH DEFAULT,
"MIXOPSEQ" SMALLINT NOT NULL WITH DEFAULT,
"VERSION" VARCHAR(64) NOT NULL WITH DEFAULT,
"COLLID" CHAR(18) NOT NULL WITH DEFAULT,
"ACCESS_DEGREE" SMALLINT,
"ACCESS_PGROUP_ID" SMALLINT,
"JOIN_DEGREE" SMALLINT,
"JOIN_PGROUP_ID" SMALLINT,
"SORTC_PGROUP_ID" SMALLINT,
"SORTN_PGROUP_ID" SMALLINT,
"PARALLELISM_MODE" CHAR(1),
"MERGE_JOIN_COLS" SMALLINT,
"CORRELATION_NAME" CHAR(18),
"PAGE_RANGE" CHAR(1) NOT NULL WITH DEFAULT,
"JOIN_TYPE" CHAR(1) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" CHAR(8) NOT NULL WITH DEFAULT,
"IBM_SERVICE_DATA" VARCHAR(254) NOT NULL WITH DEFAULT,
"WHEN_OPTIMIZE" CHAR(1) NOT NULL WITH DEFAULT,
"QBLOCK_TYPE" CHAR(6) NOT NULL WITH DEFAULT,
"BIND_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"OPTHINT" CHAR(8) NOT NULL WITH DEFAULT,
"HINT_USED" CHAR(8) NOT NULL WITH DEFAULT,
"PRIMARY_ACCESSTYPE" CHAR(1) NOT NULL WITH DEFAULT,
"PARENT_QBLOCKNO" SMALLINT NOT NULL WITH DEFAULT,
"TABLE_TYPE" CHAR(1)
)
IN DBNAME.TABLESPC
CCSID EBCDIC;



CREATES THE V7.1 SAMPLE DSN_FUNCTION_TABLE

CREATE TABLE SCHEMANM.DSN_FUNCTION_TABLE
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"QBLOCKNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" CHAR(8) NOT NULL WITH DEFAULT,
"PROGNAME" CHAR(8) NOT NULL WITH DEFAULT,
"COLLID" CHAR(18) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" CHAR(8) NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"SCHEMA_NAME" CHAR(8) NOT NULL WITH DEFAULT,
"FUNCTION_NAME" CHAR(18) NOT NULL WITH DEFAULT,
"SPEC_FUNC_NAME" CHAR(18) NOT NULL WITH DEFAULT,
"FUNCTION_TYPE" CHAR(2) NOT NULL WITH DEFAULT,
"VIEW_CREATOR" CHAR(8) NOT NULL WITH DEFAULT,
"VIEW_NAME" CHAR(18) NOT NULL WITH DEFAULT,
"PATH" VARCHAR(254) NOT NULL WITH DEFAULT,
"FUNCTION_TEXT" VARCHAR(254) NOT NULL WITH DEFAULT
)
IN DBNAME.TABLESPC
CCSID EBCDIC;



CREATES THE V7.1 SAMPLE DSN_STATEMNT_TABLE

CREATE TABLE SCHEMANM.DSN_STATEMNT_TABLE
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" CHAR(8) NOT NULL WITH DEFAULT,
"PROGNAME" CHAR(8) NOT NULL WITH DEFAULT,
"COLLID" CHAR(18) 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
)
IN DBNAME.TABLESPC
CCSID EBCDIC;


We will discuss more on this in my next post.


Cheers,
Prakash C Singh
IBM Certified DB2 DBA