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 PLAN_TABLE after EXPLAIN

Hi,

Here are some information you should be looking at various columns of the PLAN_TABLE. This will help you analysing the SQL queries and it's various attributes.

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

2) METHOD:
0 - First table accessed, continuation of previous table accessed or not used
1 - Nested Loop Join
2 - Merge Scan Join
3 - Sorts required by ORDER BY, GROUP BY, SELECT DISTINCT, uNION
4 - Hybrid Join

3) ACCESSTYPE:
I - By an Index
I1 - One fetch Index Scan
M - Multiple index scan
MX - By Index mentioned in ACCESSNAME
MI - Intersection of Multiple indexes
MU - Union of multiple indexes
N - Index scan when matching predicated in IN keyword
R - Tablespace scan
RW - Work file scan of a materialized user defined table funtion
T - By a spare index - Star join work files
V - By buffers for an INSERT statement within a SELECT
Blank - NA

4) INDEXONLY:
Whether access to an Index alone is enough to carry out the step

5) TSLOCKMODE:
IS - Intent Share Lock
IX - Intent Exclusive lock
S - Share Lock
U - Update Lock
X - Exclusive Lock
SIX - Share with Intent Exclusive lock
N - UR Isolation: No Lock
NS - For CS, RS, RR an S Lock
NIS - For CS, RS, RR an IS Lock
NSS - For CS, RS an IS Lock and for RR an S Lock
SS - For UR, CS, RS an IS Lock and for RR an S Lock

6) PREFETCH:
S - Pure Sequential
L - thru a page List
D - Optimizer expects dynamic prefetch
Blank - Unknown at bind time or NA

7)ACCESS_DEGREE:
Number of Parallel tasks or operations activated by a Query
0 - if there is a host variable

8) PARALLELISM_MODE:
I - Query I/O parallelism
C - Query CP parallelism
X - Sysplex query parallelism

9) PAGE_RANGE:
Whether the table qualifies for page-range(scan only the partitions those are needed)
Y - Yes
Blank - No

10) JOIN_TYPE:
F - Full Outer Join
L - Left Outer Join
S - Star Join
Blank - Inner Join or No join
Note: Right Outer Join always converted to Left Outer Join

11) WHEN_OPTIMIZE:
Blank: At bind time, using a default filter factor for any host variables, parameter markers or special registers
B - Above facts + Bind option REOPT (ALWAYS) or REOPT (ONCE) must be specified.
R - At Runtime, using input variables, parameter markers or special registers. Bind option REOPT (ALWAYS) or REOPT (ONCE) must be specified.

12) QBLOCK_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
CORSUB - Correlated Subquery
NCOSUB - NonCorrelated Subquery
TABLEX - Table Expression
TRIGGER - WHEN caluse on CREATE TRIGGER
UNION - Union
UNIONA - Union All

13) PRIMRY-ACCESSTYPE:
D - Direct Row access
Blank - No Direct Row access

14) TABLE_TYPE:
B - Buffers for an INSERT stament within a SELECT
C - Common Table Expression
F - Table Function
M - Materialized Qery Table
Q - Temp intermediate table(Not Materialized), name of the viewor nested table expression, Contains a UNION ALL where materialization was vertual not actual.
RB - Recursive Common Table Expression
T - Table
W - Work file (Materialized)

15) TABLE_ENCODE:
A - ASCII
E - EBCDIC
U - Unicode
M - when multiple CCSID is in one table


We will be discussing more on these on my post regarding Performance.

Cheers,
Prakash C Singh
IBM Certified DB2 DBA