You need to verify the following columns in the PLAN_TABLE after you run EXPLAIN query.
PLAN Table in DB2
1) QUERYNO: A number identifies the statement being explained.
2) METHOD:
0 – First table accessed, a continuation of the 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 function
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, and 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 that 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 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 clause 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 statement within a SELECT
C – Common Table Expression
F – Table Function
M – Materialized Query Table
Q – Temp intermediate table(Not Materialized), name of the viewer nested table expression, Contains a UNION ALL where materialization was virtual 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
Apart from the above DBA has to verify Accounting Reports to get CPU time or elapsed time.
Related
