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
