SQL Reference

EXPLAIN_ARGUMENT Table

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

For the definition of this table, see EXPLAIN_ARGUMENT Table Definition.

Table 128. EXPLAIN_ARGUMENT Table

Column Name Data Type Nullable? Key? Description
EXPLAIN_REQUESTER VARCHAR(128) No FK Authorization ID of initiator of this Explain request.
EXPLAIN_TIME TIMESTAMP No FK Time of initiation for Explain request.
SOURCE_NAME VARCHAR(128) 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 VARCHAR(128) 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 INTEGER No FK Statement number within package to which this explain information is related.
SECTNO INTEGER No FK Section number within package to which this explain information is related.
OPERATOR_ID INTEGER 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(1024) Yes No The value of the argument for this operator. NULL if the value is in LONG_ARGUMENT_VALUE.
LONG_ARGUMENT_VALUE CLOB(1M) Yes No The value of the argument for this operator, when the text will not fit in ARGUMENT_VALUE. NULL if the value is in ARGUMENT_VALUE.

Column Name	Data Type	Nullable?	Key?	Description
EXPLAIN_REQUESTER	VARCHAR(128)	No	FK	Authorization ID of initiator of this Explain request.
EXPLAIN_TIME	TIMESTAMP	No	FK	Time of initiation for Explain request.
SOURCE_NAME	VARCHAR(128)	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	VARCHAR(128)	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	INTEGER	No	FK	Statement number within package to which this explain information is related.
SECTNO	INTEGER	No	FK	Section number within package to which this explain information is related.
OPERATOR_ID	INTEGER	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(1024)	Yes	No	The value of the argument for this operator. NULL if the value is in LONG_ARGUMENT_VALUE.
LONG_ARGUMENT_VALUE	CLOB(1M)	Yes	No	The value of the argument for this operator, when the text will not fit in ARGUMENT_VALUE. NULL if the value is in ARGUMENT_VALUE.

Table 129. ARGUMENT_TYPE and ARGUMENT_VALUE Column Values

ARGUMENT_TYPE Value Possible ARGUMENT_VALUE Values Description
AGGMODE
COMPLETE
PARTIAL
INTERMEDIATE
FINAL

Partial aggregation indicators.
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.
ENVVAR Each row of this type will contain:

Environment variable name
Environment variable value
Environment variable affecting the optimizer
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.
RMTQTEXT Query text Remote Query Text
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.
SERVER Remote server Remote server
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.
VOLATILE TRUE Volatile table

ARGUMENT_TYPE Value	Possible ARGUMENT_VALUE Values	Description
AGGMODE	COMPLETE PARTIAL INTERMEDIATE FINAL	Partial aggregation indicators.
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.
ENVVAR	Each row of this type will contain: Environment variable name Environment variable value	Environment variable affecting the optimizer
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.
RMTQTEXT	Query text	Remote Query Text
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.
SERVER	Remote server	Remote server
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.
VOLATILE	TRUE	Volatile table

[ Top of Page | Previous Page | Next Page ]