Administration Guide

EXPLAIN_ARGUMENT Table

The EXPLAIN_ARGUMENT table represents the unique characteristics for each individual operator, if there are any.

Table 121. EXPLAIN_ARGUMENT Table

Column Name Data Type Nullable? Key? Description
EXPLAIN_REQUESTER CHAR(8) No FK Authorization ID of initiator of this Explain request.
EXPLAIN_TIME TIMESTAMP No FK Time of initiation for Explain request.
SOURCE_NAME CHAR(8) No FK Name of the package running when the dynamic statement was explained or name of the source file when static SQL was explained.
SOURCE_SCHEMA CHAR(8) No FK Schema, or qualifier, of source of Explain request.
EXPLAIN_LEVEL CHAR(1) No FK Level of Explain information for which this row is relevant.
STMTNO SMALLINT No FK Statement number within package to which this explain information is related.
SECTNO SMALLINT No FK Section number within package to which this explain information is related.
OPERATOR_ID SMALLINT No No Unique ID for this operator within this query.
ARGUMENT_TYPE CHAR(8) No No The type of argument for this operator.
ARGUMENT_VALUE VARCHAR(30) No No The value of the argument for this operator.

Column Name	Data Type	Nullable?	Key?	Description
EXPLAIN_REQUESTER	CHAR(8)	No	FK	Authorization ID of initiator of this Explain request.
EXPLAIN_TIME	TIMESTAMP	No	FK	Time of initiation for Explain request.
SOURCE_NAME	CHAR(8)	No	FK	Name of the package running when the dynamic statement was explained or name of the source file when static SQL was explained.
SOURCE_SCHEMA	CHAR(8)	No	FK	Schema, or qualifier, of source of Explain request.
EXPLAIN_LEVEL	CHAR(1)	No	FK	Level of Explain information for which this row is relevant.
STMTNO	SMALLINT	No	FK	Statement number within package to which this explain information is related.
SECTNO	SMALLINT	No	FK	Section number within package to which this explain information is related.
OPERATOR_ID	SMALLINT	No	No	Unique ID for this operator within this query.
ARGUMENT_TYPE	CHAR(8)	No	No	The type of argument for this operator.
ARGUMENT_VALUE	VARCHAR(30)	No	No	The value of the argument for this operator.

Table 122. ARGUMENT_TYPE and ARGUMENT_VALUE Column Values

ARGUMENT_TYPE Value Possible ARGUMENT_VALUE Values Description
AGGMODE
COMPLETE PARTIAL INTERMEDIATE FINAL

Partial aggregation indicators.
BUFFERS INTEGER Buffers consumed.
BITFLTR
TRUE FALSE

Hash Join will use a bit filter to enhance performance.
CSETEMP
TRUE FALSE

Temporary Table over Common Subexpression Flag.
DIRECT
TRUE

Direct fetch indicator.
DUPLWARN
TRUE FALSE

Duplicates Warning flag.
EARLYOUT
TRUE FALSE

Early out indicator.
FETCHMAX
IGNORE INTEGER

Override value for MAXPAGES argument on FETCH operator.
GROUPBYC
TRUE FALSE

Whether Group By columns were provided.
GROUPBYN Integer Number of comparison columns.
GROUPBYR Each row of this type will contain:

Ordinal value of column in group by clause (followed by a colon and a space)
Name of Column
Group By requirement.
INNERCOL Each row of this type will contain:

Ordinal value of column in order (followed by a colon and a space)
Name of Column
Order Value

(A)
Ascending
(D)
Descending

Inner order columns.
ISCANMAX
IGNORE INTEGER

Override value for MAXPAGES argument on ISCAN operator.
JN_INPUT
INNER OUTER

Indicates if operator is the operator feeding the inner or outer of a join.
LISTENER
TRUE FALSE

Listener Table Queue indicator.
MAXPAGES
ALL NONE INTEGER

Maximum pages expected for Prefetch.
MAXRIDS
NONE INTEGER

Maximum Row Identifiers to be included in each list prefetch request.
NUMROWS
INTEGER

Number of rows expected to be sorted.
ONEFETCH
TRUE FALSE

One Fetch indicator.
OUTERCOL Each row of this type will contain:

Ordinal value of column in order (followed by a colon and a space)
Name of Column
Order Value

(A)
Ascending
(D)
Descending

Outer order columns.
OUTERJN
LEFT RIGHT

Outer join indicator.
PARTCOLS Name of Column Partitioning columns for operator.
PREFETCH
LIST NONE SEQUENTIAL

Type of Prefetch Eligible.
ROWLOCK
EXCLUSIVE NONE REUSE SHARE SHORT (INSTANT) SHARE UPDATE

Row Lock Intent.
ROWWIDTH INTEGER Width of row to be sorted. ***
SCANDIR
FORWARD REVERSE

Scan Direction.
SCANGRAN INTEGER Intra-partition parallelism, granularity of the intra-partition parallel scan, expressed in SCANUNITs.
SCANTYPE LOCAL PARALLEL intra-partition parallelism, Index or Table scan.
SCANUNIT
ROW PAGE

Intra-partition parallelism, scan granularity unit.
SHARED TRUE Intra-partition parallelism, shared TEMP indicator.
SLOWMAT
TRUE FALSE

Slow Materialization flag.
SNGLPROD
TRUE FALSE

Intra-partition parallelism sort or temp produced by a single agent.
SORTKEY Each row of this type will contain:

Ordinal value of column in key (followed by a colon and a space)
Name of Column
Order Value

(A)
Ascending
(D)
Descending

Sort key columns.
SORTTYPE
PARTITIONED SHARED ROUND ROBIN REPLICATED

Intra-partition parallelism, sort type.
TABLOCK
EXCLUSIVE INTENT EXCLUSIVE INTENT NONE INTENT SHARE REUSE SHARE SHARE INTENT EXCLUSIVE SUPER EXCLUSIVE UPDATE

Table Lock Intent.
TQDEGREE INTEGER intra-partition parallelism, number of subagents accessing Table Queue.
TQMERGE
TRUE FALSE

Merging (sorted) Table Queue indicator.
TQREAD
READ AHEAD STEPPING SUBQUERY STEPPING

Table Queue reading property.
TQSEND
BROADCAST DIRECTED SCATTER SUBQUERY DIRECTED

Table Queue send property.
TQTYPE LOCAL Intra-partition parallelism, Table Queue.
TRUNCSRT TRUE Truncated sort (limits number of rows produced).
UNIQUE
TRUE FALSE

Uniqueness indicator.
UNIQKEY Each row of this type will contain:

Ordinal value of column in key (followed by a colon and a space)
Name of Column
Unique key columns.

ARGUMENT_TYPE Value	Possible ARGUMENT_VALUE Values	Description
AGGMODE	COMPLETE PARTIAL INTERMEDIATE FINAL	Partial aggregation indicators.
BUFFERS	INTEGER	Buffers consumed.
BITFLTR	TRUE FALSE	Hash Join will use a bit filter to enhance performance.
CSETEMP	TRUE FALSE	Temporary Table over Common Subexpression Flag.
DIRECT	TRUE	Direct fetch indicator.
DUPLWARN	TRUE FALSE	Duplicates Warning flag.
EARLYOUT	TRUE FALSE	Early out indicator.
FETCHMAX	IGNORE INTEGER	Override value for MAXPAGES argument on FETCH operator.
GROUPBYC	TRUE FALSE	Whether Group By columns were provided.
GROUPBYN	Integer	Number of comparison columns.
GROUPBYR	Each row of this type will contain: Ordinal value of column in group by clause (followed by a colon and a space) Name of Column	Group By requirement.
INNERCOL	Each row of this type will contain: Ordinal value of column in order (followed by a colon and a space) Name of Column Order Value (A) Ascending (D) Descending	Inner order columns.
ISCANMAX	IGNORE INTEGER	Override value for MAXPAGES argument on ISCAN operator.
JN_INPUT	INNER OUTER	Indicates if operator is the operator feeding the inner or outer of a join.
LISTENER	TRUE FALSE	Listener Table Queue indicator.
MAXPAGES	ALL NONE INTEGER	Maximum pages expected for Prefetch.
MAXRIDS	NONE INTEGER	Maximum Row Identifiers to be included in each list prefetch request.
NUMROWS	INTEGER	Number of rows expected to be sorted.
ONEFETCH	TRUE FALSE	One Fetch indicator.
OUTERCOL	Each row of this type will contain: Ordinal value of column in order (followed by a colon and a space) Name of Column Order Value (A) Ascending (D) Descending	Outer order columns.
OUTERJN	LEFT RIGHT	Outer join indicator.
PARTCOLS	Name of Column	Partitioning columns for operator.
PREFETCH	LIST NONE SEQUENTIAL	Type of Prefetch Eligible.
ROWLOCK	EXCLUSIVE NONE REUSE SHARE SHORT (INSTANT) SHARE UPDATE	Row Lock Intent.
ROWWIDTH	INTEGER	Width of row to be sorted. ***
SCANDIR	FORWARD REVERSE	Scan Direction.
SCANGRAN	INTEGER	Intra-partition parallelism, granularity of the intra-partition parallel scan, expressed in SCANUNITs.
SCANTYPE	LOCAL PARALLEL	intra-partition parallelism, Index or Table scan.
SCANUNIT	ROW PAGE	Intra-partition parallelism, scan granularity unit.
SHARED	TRUE	Intra-partition parallelism, shared TEMP indicator.
SLOWMAT	TRUE FALSE	Slow Materialization flag.
SNGLPROD	TRUE FALSE	Intra-partition parallelism sort or temp produced by a single agent.
SORTKEY	Each row of this type will contain: Ordinal value of column in key (followed by a colon and a space) Name of Column Order Value (A) Ascending (D) Descending	Sort key columns.
SORTTYPE	PARTITIONED SHARED ROUND ROBIN REPLICATED	Intra-partition parallelism, sort type.
TABLOCK	EXCLUSIVE INTENT EXCLUSIVE INTENT NONE INTENT SHARE REUSE SHARE SHARE INTENT EXCLUSIVE SUPER EXCLUSIVE UPDATE	Table Lock Intent.
TQDEGREE	INTEGER	intra-partition parallelism, number of subagents accessing Table Queue.
TQMERGE	TRUE FALSE	Merging (sorted) Table Queue indicator.
TQREAD	READ AHEAD STEPPING SUBQUERY STEPPING	Table Queue reading property.
TQSEND	BROADCAST DIRECTED SCATTER SUBQUERY DIRECTED	Table Queue send property.
TQTYPE	LOCAL	Intra-partition parallelism, Table Queue.
TRUNCSRT	TRUE	Truncated sort (limits number of rows produced).
UNIQUE	TRUE FALSE	Uniqueness indicator.
UNIQKEY	Each row of this type will contain: Ordinal value of column in key (followed by a colon and a space) Name of Column	Unique key columns.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]