DB2 Version 9.7 for Linux, UNIX, and Windows

EXPLAIN_ARGUMENT table

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

Table 1. EXPLAIN_ARGUMENT Table. PK means that the column is part of a primary key; FK means that the column is part of a foreign key.
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.
SOURCE_VERSION VARCHAR(64) No FK Version of the source of the 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(2M) 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 2. ARGUMENT_TYPE and ARGUMENT_VALUE column values
ARGUMENT_TYPE Value Possible ARGUMENT_VALUE Values Description
AGGMODE COMPLETE
PARTIAL
HASHED PARTIAL
HASHED COMPLETE
INTERMEDIATE
FINAL
COMPLETE UNIQUE
PARTIAL UNIQUE
INTERMEDIATE UNIQUE
FINAL UNIQUE		 Partial aggregation indicators.
APREUSE TRUE Indicates if access plan reuse bind option is in effect for this statement.
BITFLTR INTEGER
FALSE		 Size of bit filter used by hash join.
BLDLEVEL Database build identifier Internal identification string for source code version.
BLKLOCK EXCLUSIVE
INTENT EXCLUSIVE
INTENT SHARE
NONE
SHARE
UPDATE		 Block level lock intent.
BUFFSORT TRUE Indicates if SORT is used as a buffering operation.
BY DPART TRUE
FALSE		 Indicates whether ZZJN is performed across the dimensions of a data partitioned table.
CONCACCR Each row of this type will contain:
  • Level of the setting for this statement:
    BIND
    Application BIND with 
CONCURRENT ACCESS RESOLUTION 
option
    PREP
    Statement prepared with 
CONCURRENT ACCESS RESOLUTION 
attributes
  • The concurrent access resolution in effect:
    USE CURRENTLY COMMITTED
    Concurrent access resolution 
of application bind or 
statement prepare is 
USE CURRENTLY COMMITTED
    WAIT FOR OUTCOME
    Concurrent access resolution 
of application bind or 
statement prepare is 
WAIT FOR OUTCOME
 Indicates the concurrent access resolution used to generate the access plan for this statement.
CSERQY TRUE
FALSE		 Remote query is a common subexpression.
CSETEMP TRUE
FALSE		 Temporary Table over Common Subexpression Flag.
CUR_COMM TRUE
Access currently committed rows when the value for the database configuration parameter cur_commit is not DISABLE. This access plan is enabled for applicable statements by using either:
  • CONCURRENT ACCESS RESOLUTION with the USE CURRENTLY COMMITTED option on bind or prepare
  • The database configuration parameter cur_commit with a value of ON
DIRECT TRUE Direct fetch indicator.
DPESTFLG TRUE
FALSE		 Indicates whether or not the DPNUMPRT value is based on an estimate. Possible values are 'TRUE' (DPNUMPRT represents the estimated number of accessed data partitions) or 'FALSE' (DPNUMPRT represents the actual number of accessed data partitions).
DPFXMLMV REFERENCE
COMBINATION		 Indicates whether XML column data is moved between DPF partitions.
DPLSTPRT NONE
CHARACTER		 Represents accessed data partitions. It is a comma-delimited list (for example: 1,3,5) or a hyphenated list (for example: 1-5) of accessed data partitions. A value of 'NONE' means that no data partition remains after specified predicates have been applied.
DPNUMPRT INTEGER Represents the actual or estimated number of data partitions accessed.
DSTSEVER Server name Destination (ship from) server.
DUPLWARN TRUE
FALSE		 Duplicates Warning flag.
EARLYOUT LEFT
RIGHT
GROUPBY
NONE		 Early out indicator. LEFT indicates that each row from the outer table only needs to be joined with at most one row from the inner table. RIGHT indicates that each row from the inner table only needs to be joined with at most one row from the outer table. NONE indicates no early out processing. GROUPBY indicates that early out processing is allowed because of a group by operation.
ENVVAR Each row of this type will contain:
  • Environment variable name
  • Environment variable value
 Environment variable affecting the optimizer
ERRTOL Each row of this type will contain an SQLSTATE and SQLCODE pair. A list of errors to be tolerated.
EVALUNCO TRUE Evaluate uncommitted data using lock deferral. This is enabled with the DB2_EVALUNCOMMITTED registry variable.
EXECUTID An opaque
binary token
formatted as
an hexadecimal
string representing
the executable ID.		 Indicates the executable ID of the section being explained.
FETCHMAX IGNORE
INTEGER		 Override value for MAXPAGES argument on FETCH operator.
GREEDY TRUE Indicates optimizer used greedy algorithm to plan access.
GLOBLOCK EXCLUSIVE
INTENT EXCLUSIVE
INTENT NONE
INTENT SHARE
NO LOCK OBTAINED
SHARE
SHARE INTENT EXCLUSIVE
SUPER EXCLUSIVE
UPDATE		 Represents global lock intent information for a partitioned table object.
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.
HASHCODE 24
32		 Size (in bits) of hash code used for hash join.
HASHTBSZ INTEGER The number of expected entries in the hash table of a hash join.
IDXMSTLY TRUE Indicates whether the FETCH is performed over block identifiers returned from a multi dimensional clustered index.
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.
INPUTXID A context node identifier INPUTXID identifies the input context node used by the XSCAN operator.
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.
LCKAVOID TRUE Lock avoidance: row access will avoid locking committed data.
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		 Start of changeIndicates the GROUP BY conditions are satisfied by the first row produced by the input stream.End of change
OPROFERR TRUE
FALSE		 Indicates that one or more errors occurred while parsing or applying the optimization profile. For details, see explain diagnostic messages.
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
FULL
LEFT (ANTI)
RIGHT (ANTI)		 Outer join indicator.
PARTCOLS Name of Column Partitioning columns for operator.
PREFETCH LIST
NONE
SEQUENTIAL		 Type of Prefetch Eligible.
REOPT ALWAYS
ONCE		 The statement is optimized using bind-in values for parameter markers, host variables, and special registers.
RMTQTXT Query text Remote Query Text
RNG_PROD Function name Range producing function for extended index access.
ROWLOCK EXCLUSIVE
NONE
REUSE SHARE
SHORT (INSTANT) SHARE
UPDATE		 Row Lock Intent.
ROWWIDTH INTEGER Width of row to be sorted.
RSUFFIX Query text Remote SQL suffix.
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.
SKIP_INS TRUE Skip inserted. Row access will skip uncommitted inserted rows. This behavior is enabled with the DB2_SKIPINSERTED registry variable or when currently committed semantics are in effect.
SKIPDKEY TRUE Skip deleted keys. Row access will skip uncommitted deleted keys. This behavior is enabled with the DB2_SKIPDELETED registry variable.
SKIPDROW TRUE Skip deleted rows. Row access will skip uncommitted deleted rows. This behavior is enabled with the DB2_SKIPDELETED registry variable.
SKIPLOCK TRUE The concurrent access resolution "skip locked data" is in effect.
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.
SRCSEVER Server name Source (ship to) server.
SPEED SLOW
FAST		 'SLOW' indicates that the scan is expected to progress slowly over the table. For example, if the scan is the outer of a nested loop join). 'FAST' indicates that the scan is expected to progress with higher speed. This information is used to group scans together for efficient sharing of bufferpool records.
SPILLED INTEGER Estimated number of pages in SORT spill.
SQLCA Warning information Warnings and reason codes issued during Explain operation.
STARJOIN YES The IXAND operator is part of a star join
STMTHEAP INTEGER Size of statement heap at start of statement compile.
STREAM TRUE
FALSE		 Remote source is streaming.
TABLOCK EXCLUSIVE
INTENT EXCLUSIVE
INTENT NONE
INTENT SHARE
REUSE
SHARE
SHARE INTENT EXCLUSIVE
SUPER EXCLUSIVE
UPDATE		 Table Lock Intent.
TEMPSIZE INTEGER Temporary table page size.
THROTTLE TRUE
FALSE		 Throttling improves the performance of other scans that would otherwise lag behind and be forced to reread the same pages. 'TRUE' if the scan can be throttled. 'FALSE' if the scan must not be throttled.
TMPCMPRS YES
ELIGIBLE		 The value YES indicates that compression is applied. The value ELIGIBLE indicates that compression may be applied if the table becomes large enough. The absence of TMPCMPRS indicates that the temporary table is not compressed.
TQDEGREE INTEGER Intra-partition parallelism, number of subagents accessing Table Queue.
TQMERGE TRUE
FALSE		 Merging (sorted) Table Queue indicator.
Start of changeTQNUMBEREnd of change Start of changeINTEGEREnd of change Start of changeTable queue identification number.End of change
TQREAD READ AHEAD
STEPPING
SUBQUERY STEPPING		 Table Queue reading property.
Start of changeTQSECNFMEnd of change Start of changeINTEGEREnd of change Start of changeSubection number at the sending end of the table queue.End of change
Start of changeTQSECNTOEnd of change Start of changeINTEGEREnd of change Start of changeSubsection number at the receiving end of the table queue.End of change
TQSEND BROADCAST
DIRECTED
SCATTER
SUBQUERY DIRECTED		 Table Queue send property.
TQ TYPE LOCAL Intra-partition parallelism, Table Queue.
TQ_ORIGIN ASYNCHRONY
XTQ		 The reason that Table Queue was introduced into the access plan.
TRUNCTQ INPUT
OUTPUT
INPUT AND OUTPUT		 Truncated Table Queue indicator. INPUT indicates that truncation occurs on input to the Table Queue. OUPUT indicates that truncation occurs on output from the Table Queue. INPUT and OUTPUT indicates that truncation occurs on both input to the Table Queue and on output from the Table Queue.
TRUNCSRT TRUE Truncated sort (limits number of rows produced).
TUPBLKSZ INTEGER Component of the total sort heap required to perform a hash join that determines the number of bytes that a tuple will be stored in. This can be used by service to diagnose memory, temporary table and to some degree sort heap usage.
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.
UR_EXTRA TRUE

Uncommitted read isolation, but with extra processing to ensure correct isolation. This access has extra table level locking; the same table level locking as cursor stability. Also, when the statement is executing, the isolation level might upgrade to cursor stability, for example, if an online load is running concurrently.

Another part of the statement execution plan will ensure the isolation level is correct, such as a FETCH operator at a higher isolation level.
VISIBLE TRUE
FALSE		 Whether shared scans are visible to other shared scans. A shared scan that is visible can influence the behavior of other scans. Examples of affected behavior include start location and throttling.
VOLATILE TRUE Volatile table
WRAPPING TRUE
FALSE		 Whether a shared scan is allowed to start at any record in the table and wrap once it reaches the last record. Wrapping allows bufferpool records to be shared with other ongoing scans.
XDFOUT DECIMAL XDFOUT indicates the expected number of documents to be returned by the XISCAN operator for each context node.
XLOGID An identifier consisting of an SQL schema name and the name of an index over XML data XLOGID identifies the index over XML data chosen by the optimizer for the XISCAN operator.
XPATH An XPATH expression and result set in an internal format This argument indicates the evaluation of an XPATH expression by the XSCAN operator.
XPHYID An identifier consisting of an SQL schema name and the name of a physical index over XML data XPHYID identifies the physical index that is associated with an index over XML data used by the XISCAN operator.