DB2- PLAN_TABLE Columns After EXPLAIN

USA DB2 JobsWe need to verify the following columns in the PLAN_TABLE after explain a query.

1) QUERYNO: A number identifies 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 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, 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 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.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.