EXPLAIN Output for SQL statement

EXPLAIN Output for SQL statement provides a convenient way to view DB2 EXPLAIN output for an SQL statement. EXPLAIN passes SQL statements through the Db2 optimizer, which externalizes the access paths it chooses into specified explain tables. Strobe will access the explained data from PLAN_TABLE, and optionally when tables exist, DSN_PREDICAT_TABLE and DSN_STAT_FEEDBACK (DB2 V11). These are standard DB2 tables with predetermined column names and definitions.

Report details

Plan Table Rows

Click next to a query block number to view more details for that row:

QBLOCKNO identifies separate SQL statement sections, for example, query, subquery, or UNION. QBLOCKNO indicates the order of the sections within the entire statement, starting with 1 for the outermost section. QBLOCKNO does not indicate the order of execution.
PLANNO identifies the order in which the steps executed within the QBLOCKNO, for example, the table join order.
METHOD indicates the join method, or whether a sort will be used to process the PLANNO step, as follows:

METHOD	Meaning
0	First table accessed in the current step when PLANNO=1, or the outer table for joins
1	Nested loop join
2	Merge scan join
3	Independent sort required for ORDER BY, GROUP BY, SELECT DISTINCT, UNION, or a quantified predicate; this step does not access a new table.
4	Hybrid join

INDEXONLY is Y(es) if only index entries are accessed for a step (PLANNO), N(o) if all entries are accessed.
CREATOR identifies the authorization ID of the creator of the new table or materialized view accessed in this step (PLANNO). CREATOR is blank when METHOD=3.
TNAME is the name of the new table or materialized view accessed in this step (PLANNO). TNAME is blank when METHOD=3.
ACCESSCREATOR indicates the creator of the index used in an index scan when ACCESSTYPE is I, I1, N, NR, MX, or DX. Otherwise, ACCESSCREATOR is blank.
ACCESSNAME indicates the name of the index used in an index scan when ACCESSTYPE is I, I1, H, MH, N, NR, MX, or DX, or when ACCCESSTYPE=P is the pair-wise join leg in MIXOPSEQ, Otherwise, ACCESSNAME is blank.
ACCESSTYPE defines the method used to access the new table. For index scans, the table is accessed using the index identified by ACCESSCREATOR and ACCESSNAME. Table access is as follows:

METHOD	Meaning
I	Matching index scan when MATCHCOLS>0 or by a non-matching index scan when MATCHCOLS=0
I1	One-fetch index scan
N	Index scan where the matching predicate contains an "IN" keyword
R	Tablespace scan
M	Multiple index scan; followed by: MX -matchind index scan that only retrieves only RIDs MI - intersection of RIDs from multiple indexes MU - union of RIDs from multiple indexes
DI	Intersection of DOCID's from multiple lists to a final DOCID list.
DU	Intersection of DOCID's from multiple lists to a final DOCID list.
DX	XML index scan using the index in ACCCESNAME PLAN_TABLE column to return a DOCID list.
E	Use a row change timestamp column for direct row access.
P	Dynamic pair wise index scan.
RW	Work file scan of the result of a materialized user defined table function.
V	Buffers for an INSERT statement within a SELECT.
H	Hash access is used. The ACCESSCREATOR and ACCESSNAME columns identify the hash overflow index if an overflow condition has occurred.
HN	Hash access using an IN predicate or IN predicate generated by DB2. The ACCESSCREATOR and ACCESSNAME columns identify the hash overflow index if an overflow condition occurred.
IN	Index scan when the matching predicate contains an IN predicate and an in memory table is accessed via the IN list.
MH	The hash overflow index in ACCESSNAME was used.
N	Index scan when DB2 reworks the query to use an IN keyword, or an Index scan where the matching predicate has an IN keyword.
O	Work file scan owing to the result of a subquery.
NR	Range list access.
blank	Destination table for INSERTs or WHERE CURRENT OF cursor for UPDATES and DELETEs; otherwise not applicable

MATCHCOLS indicates the number of index columns used in an index scan when ACCESSTYPE = I, I1, N, NR, MX, or DX. Otherwise, the value of MATCHCOLS is zero..
SORTN_UJOG

The first character represents the value of SORTN_UNIQ: Y(es) if a sort is performed on the new table to remove duplicate rows, or N(o) if a sort is not performed.
The second character represents the value of SORTN_JOIN: Y(es) if a sort is performed on the inner table of a merge scan join (METHOD=2) or a sort is performed on the inner table RIDs and the intermediate table of a hybrid join (METHOD=4), or N(o) if the sorts are not performed.
The third character represents the value of SORTN_ORDERBY: Y(es) if a sort is performed on the new table for an ORDER BY clause, or N(o) if it is not.
The fourth character represents the value of SORTN_GROUPBY: Y(es) if a sort is performed on the new table for a GROUP BY clause, N(o) if it is not.

SORTC_UJOG

The first character represents the value of SORTC_UNIQ: Y(es) if a sort is performed on the composite table to remove duplicate rows, or N(o) if a sort is not performed.
The second character represents the value of SORTC_JOIN: Y(es) if a sort is performed on the composite table in of a nested loop join (METHOD=1), or a sort is performed on the composite table of a merge scan join (METHOD=2), or a sort is performed on the composite table RIDs and the intermediate table of a hybrid join (METHOD=4), or N(o) if the sorts are not performed.
The third character represents the value of SORTC_ORDERBY: Y(es) if a sort is performed on the new table for an ORDER BY clause, or N(o) if it is not.
The fourth character represents the value of SORTC_GROUPBY: Y(es) if a sort is performed on the new table for a GROUP BY clause, N(o) if it is not.

TSLOCKMODE designates the lock mode of the tablespace containing the new table, as follows:

IS	Intent share
IX	Intent exclusive
S	Share
X	Exclusive
SIX	Share with intent exclusive
U	Update
N	No lock, UR isolation

The following values indicate that the lock mode could not be determined at bind time:

NS	If the isolation level at execution time is uncommitted read, Db2 will not acquire a lock. If the level is cursor stability or repeatable read, Db2 will acquire a shared lock.
NIS	If the isolation level at execution time is uncommitted read, Db2 will not acquire a lock. If the level is cursor stability or repeatable read, Db2 will acquire an intent shared lock.
NSS	If the isolation level at execution time is uncommitted read, Db2 will not acquire a lock. If the level is cursor stability, Db2 will acquire an intent shared lock. If the level is repeatable read, Db2 will acquire a shared lock.
SS	If the isolation level at execution time is uncommitted read or cursor stability, Db2 will acquire an intent shared lock. If the level is repeatable read, Db2 will acquire a shared lock.

PREFETCH indicates whether data pages are read in advance by prefetch, as follows:

D	Optimizer expects dynamic prefetch
S	Pure sequential prefetch (tablespace)
L	List prefetch (index)
U	List prefetch with unsorted RID list
blank	Unknown or no prefetch

MIXOPSEQ indicates the sequence of steps in a multiple index operation (ACCESSTYPE=MX, MI, MU, DX, DI, or DU), or in an OR predicate the sequence number of the predicate in the SQL statement (ACCESSTYPE=NR) or zero.
TABNO identifies the sequence of the table referenced in the FROM clause. TABNO helps distinguish multiple references to the same table. TABNO contains a zero when METHOD=3.
ACCESS_DEGREE_ID indicates the number of parallel I/O streams that a query activates.
If the number of parallel I/O streams is 0, DB2 could not determine the number of parallel I/O streams at bind time, because a dependent predicate is coded using a host variable.
If the number of parallel I/O streams is greater than 0, DB2 might execute access in parallel. Although this number is determined at bind time, DB2 may choose a different number of parallel I/O streams at execution time.
JOIN_DEGREE indicates the number of parallel operations or tasks used to join the composite table to the new table, followed by the identifier of the parallel group for joining the composite table with the new table.
If the number of parallel operations is not null, access is executed in parallel.
If the identifier of the parallel group is null, the join is not executed in parallel.

The number of parallel operations and identifier of the parallel group is determined at bind time. Their actual values may be different at execution time.

ACCESS_PGROUP_ID is the identifier of the parallel group for accessing the new table. The identifier of the parallel group is determined at bind time. The actual group identifier may be different at execution time.
JOIN_PGROUP_ID is the identifier of the parallel group for accessing the composite table. The identifier of the parallel group is determined at bind time. The actual group identifier may be different at execution time.
SORTC_PGROUP_ID is the parallel group identifier for the parallel sort of the composite table. A parallel group is consecutive operations that are executed in parallel and have the same number of parallel tasks.
SORTN_PGROUP_ID is the parallel group identifier for the parallel sort of the new table. A parallel group is consecutive operations that are executed in parallel and have the same number of parallel tasks.
COLUMN_FN_EVAL indicates when a column function is evaluated, as follows:

R	At data retrieval time (Stage 1)
S	At sort time (Stage 2)
blank	At run time

PAR MODE is the kind of parallelism (if any) used at bind time:

Value	Meaning
I	query I/O parallelism
C	query CP parallelism
X	Sysplex query parallelism
blank	no parallelism

COLLID indicates the collection identifier for a package. COLLID applies only to embedded EXPLAIN statements executed from a package and to PLAN_TABLE rows generated for the package when you specify EXPLAIN(YES) during BIND processing. Otherwise, COLLID is blank.

For DB2 Version 10 or later, the following values may be contained in the COLLID column:

Value	Meaning
DYNAMICSQLCACHE	Row comes from the dynamic statement cache
DSNEXPLAINMODEYES	Row comes from an application that specifies YES for the value of the CURRENT EXPLAIN MODE special register.
DSNEXPLAINMODEEXPLAIN	Row comes from an application that specifies EXPLAIN for the value of the CURRENT EXPLAIN MODE special register.

VERSION indicates the version identifier for a package. VERSION applies only to embedded EXPLAIN statements executed from a package and to PLAN_TABLE rows generated for the package when you specify EXPLAIN(YES) during BIND processing. Otherwise, VERSION is blank.
PARENT_QBLOCKNO indicates the QBLOCKNO of the parent query block.
PAGE_RANGE indicates whether the table can qualify for page range screening. When PAGE_RANGE is Y, DB2 generates plans that will scan only the partitions needed.
JOIN_TYPE indicates the type of join:

Value	Meaning
F	full outer join
L	left outer join (A right outer join converts to a left outer join when it is used so that JOIN_TYPE is an "L"
P	Pair wise join
S	Star join
blank	inner join or no join

MERGE_JOIN_COLS is the number of columns joined during a merge scan join (METHOD=2). Blank if the join method was not a merge scan join.
CORRELATION_NAME is the correlation name of the table or view specified in the statement. Blank if no correlation name is specified.
GROUP_MEMBER is the member name of the Db2 that executed the EXPLAIN statement. GROUP_MEMBER is blank if the Db2 subsystem was not in a data sharing environment when the EXPLAIN was executed.
PRIMARY_ACCESSTYPE indicates:

Value	Meaning
D	Db2 will try to use direct row access. If it cannot do so at runtime, it uses the access path described by the ACCESSTYPE column of the PLAN_TABLE.
P	A data partitioned secondary index and a part-level operation was used by DB2 to access the data (introduced in Db2 version 11).
T	The base table or result file is materialized to a work file. This work file is accessed by a sparse index. If a base table is associated with the SQL, then the ACCESSTYPE column identifies how the base table is accessed.
blank	Db2 will not try to use direct row access.

TABLE_TYPE indicates the type of new table:

Value	Meaning
I	The IN-LIST predicate generates the new table. When the IN-LIST predicate is selected as the matching predicate, the table will be accessed as an in-memory table.
F	The table is a table function.
Q	The table is a temporary intermediate result table and not materialized.
T	The table is not a table function, temporary table, or a workfile.
B	The table are buffers for SELECT from INSERT, SELECT, from UPDATE, SELECT from MERGE, or SELECT from DELETE statement.
C	The table is a common table expression.
M	The table is a materialized query table.
R	The table is a recursive common table expression.
S	The table is a correlated or non correlated subquery.
W	The table is an intermediate result table and is materialized (put into work file).
blank	The query uses GROUP BY, ORDER BY, or DISTINCT, which requires an implicit sort.

WHEN_OPTIMIZE indicates when access path optimization will occur:

Value	Meaning
B	Access path was determined at bind time, using a default filter factor for any host variables, parameter markers, or special registers. The path will be reoptimized at run time, using input variables for input host variables, parameter markers, or special registers. You must specify the REOPT(VARS) bind option.
R	Access path was determined at run time, using input variables for any host variables, parameter markers, or special registers. You must specify the REOPT(VARS) bind option.
blank	Access path was determined at bind time, using a default filter factor for any host variables, parameter markers, or special registers.

HINT_USED if the value for HINT_USED is equal to the value in the PLAN_TABLE OPTHINT column this indicates DB2 used one or more of the optimization hints, if no optimization hints were used then the HINT_USED column is blank.

OPTHINT indicates whether the PLAN_TABLE row should be used as an optimization hint for DB2. If OPTHINT has a value, then Db2 will use this row as input when choosing an access path, if OPTHINT is blank, then NO optimization hints were requested at bind time.

TABLE_ENCODE indicates the encoding scheme of the table:

Value	Meaning
A	Single CCSID set, ASCII
E	Single CCSID set, EBCDIC
U	Single CCSID set, Unicode
M	Multiple CCSID sets

TABLE_SCCSID indicates the SBCS CCSID value of the table. TABLE_SCCSID is 0 if TABLE_ENCODE is M.
TABLE_MCCSID indicates the mixed CCSID value of the table. TABLE_MSCCSID is 0 if TABLE_ENCODE is M.
TABLE_DCCSID indicates the DBCS CCSID value of the table. TABLE_DSCCSID is 0 if TABLE_ENCODE is M.
ROUTINE_ID is for IBM use only.
CTEREF shows the top-level query block number, if the referenced table is a common table expression.
STMTTOKEN is a user-specified statement token.
PARENT_PLAN_NO identifies the corresponding plan number

Predicate Table Rows

Click next to a predicate number to view more details for that predicate row:

PREDNO is a predicate number that identifies a predicate within a query.
QBLOCKNO identifies separate SQL statement sections, for example, query, subquery, or UNION. QBLOCKNO indicates the order of the sections within the entire statement, starting with 1 for the outermost section. QBLOCKNO does not indicate the order of execution.
TYPE is a character string to indicate the type of predicate or the function of the predicate.
LEFT_HAND_SIDE identifies the name of the column if the left side of the predicate is a table column.
LEFT_HAND_PNO identifies the first child predicate if the predicate is a compound predicate.
LHS TABNO indicates a number to identify a corresponding table reference in a query if the left side of the predicate is a table column or expression.
LHS_QBNO indicates a number to identify the specific query block of a query if the left side of the predicate is a table column or expression.
RIGHT_HAND_SIDE identifies the name of the column if the left side of the predicate is a table column.
RIGHT_HAND_PNO identifies the second child predicate if the predicate is a compound predicate.
RHS TABNO indicates a number to identify a corresponding table reference in a query if the right side of the predicate is a table column or expression.
RHS_QBNO indicates a number to identify the specific query block of a query if the right side of the predicate is a subquery or expression.
SEARCHARG identifies whether or not the data manager can process the predicate.
AFTER JOIN identifies the phase of the predicate evaluation.
ADDED PRED identifies whether or not the predicate is created by a transitive closure.
REDUNANT PRED identifies whether or not a predicate is redundant.
KEY FIELD identifies whether or not the index key column of the table for applicable indexes is included with the predicate.
FILTER FACTOR identifies the filter factor estimation.
BOOLEAN TERM identifies whether or not the predicate can determine the WHERE clause value.
DIRECT ACCESS identifies whether or not the predicate can be accessed by direct navigation to the row.
MARKER identifies any host variables, parameters, or registers included by the predicate.
PARENT PNO identifies the parent predicate number.
NEGATION identifies whether or not the predicate has been negated.
ORIGIN identifies the predicate origin.
CLAUSE identifies the specific clause where the predicate is.
TEXT identifies the text of the predicate.
LITERALS identifies the literal value(s).

Missing or Conflicting Catalog Statistics - Stat Feedback Table Rows (new with DB2 Version 11)

Click next to a query number to view more details for that catalog statistics feedback row:

QUERYNO is a query number that identifies the statement that is being explained.
TYPE OF STATISTIC REQUIRED is the type of statistic to collect.
DATABASE NAME is the name of the database.
TABLESPACE NAME is the name of the table space.
REASON RUNSTATS REQUIRED is the reason that the statistic was recommended.
SECTION NUMBER is the section number of the statement.
TABLE CREATOR is the creator of the TABLE.
TABLE NAME is the name of the TABLE.
INDEX CREATOR is the creator of the INDEX.
INDEX NAME is the name of the INDEX.
NUMBER OF COLUMNS IN GROUP is the number of columns in the column group.
COLUMNS IN GROUP is representation that identifies a set of columns associated with the statistics.
PLAN is the name of the application plan.
PACKAGE is the name of the package containing the statement being explained.
COLLECTION ID is the collection ID.
GROUP NAME is the member name of the Db2 that executed the EXPLAIN.
TIME EXECUTED is the EXPLAIN time.