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		 Indicates how the operator aggregates values; for example, whether the aggregation is complete or partial aggregation.

HASHED COMPLETE identifies a column-organized data grouping.
APREUSE TRUE Indicates if access plan reuse bind option is in effect for this statement.
BACKJOIN TRUE
FALSE		 Indicates whether the ZZJOIN operator is used as a backjoin in the all-probe list-prefetch plan.
BITFLTR INTEGER
FALSE		 Size of a hash join bit filter. A hash join bit filter can sometimes also be used by a table queue.
BLDLEVEL Database build identifier. Internal identification string for source code version. Db2® vVV.RR.MM.FF : nYYMMDDHHMM, for example Db2 v11.1.1.1: n1610171423
BLKLOCK EXCLUSIVE
INTENT EXCLUSIVE
INTENT SHARE
NONE
SHARE
UPDATE		 Block level lock intent.
BUFFSCAN FALSE
TRUE		 Buffered scan.
BUFFSORT TRUE Indicates whether SORT is used as a buffering operation.
BUSTSENS YES
NO		 Indicates whether the BUSTIMESENSITIVE bind option is in effect for this statement.
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
DEGREE INTEGER If the RETURN operator represents the return from column-organized data processing of the insertion, update, or deletion of rows, the DEGREE of the RETURN operator represents the maximum degree of all the subsections involved in the statement. The degree of parallelism for specific subsections is indicated by the DEGREE argument on plan operators below the RETURN operator.

If the insert/update/delete statements is modifying a column-organized target table, the DEGREE of INSERT/UPDATE/DELETE operator is the number of parallel subagents that are used for the INSERT/UPDATE/DELETE operators.
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
LEFT (REMOVE INNER DUPLICATES)
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. LEFT (REMOVE INNER DUPLICATES) indicates that an attempt to remove some duplicate rows from the inner table has taken place. 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.
EXTROWS Y
N
The argument does not show in the explain tables and formatted output when the value is N.		 Indicates that the maximum row size of the system temporary table might be too large to fit in a 32K page. Some rows might need to be represented as a large object (LOB).
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.
FILTER Each row of this type contains the following items:
  • Filter type identifier (BIT or RANGE)
  • Target operator identifier
  • Size, in bytes, for bit filters; blank for range filters
Each item in the preceding list is separated by a colon and a space.		 Indicates that a hash join bit filter or a hash join range filter is built at the current operator and provides details for that filter. The use of a filter allows the elimination of rows as early as possible during query execution.
A hash join bit filter is a space efficient data structure that is used to test whether an element is a member of a set. The following is an example of a hash join bit filter argument value:
BIT: 13: 8192
A range filter consists of a minimum and a maximum value that defines the valid range for a column value. The following is an example of a hash join range filter argument value:
RANGE: 13
FLTRAPPL TQ PUSHDOWN Indicates the bit filter application method used by the optimizer. A value of TQ PUSHDOWN indicates that the bit filter operation has been pushed down. This argument will not be included at all when the optimizer does not use a pushdown with the hash join.
GREEDY TRUE Indicates whether the optimizer used a 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. This argument can be associated with a GRPBY operator or with a TEMP operator when it is part of a query with multiple distinct aggregations.
GROUPBYN Integer Number of comparison columns. This number may be less than the number of columns present in the GROUP BY clause of the SQL statement if predicates eliminated the need to compare some columns. This argument can be associated with a GRPBY operator or with a TEMP operator when it is part of a query with multiple distinct aggregations.
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. This argument can be associated with a GRPBY operator or with a TEMP operator when it is part of a query with multiple distinct aggregations.
GROUPS Integer Number of times the operator will repeat.
HASHCODE 24
32		 Size (in bits) of hash join hash code used for hash joins. A hash join hash code can sometimes also be used by a table queue.
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.
IDXOVTMP TRUE
FALSE

Indicates whether the scan builds an index or a fast integer sort structure for random access of the temporary tables.

If value is TRUE, the scan builds an index over the temporary tables for random access of the temporary tables.

If value is FALSE, the scan builds a fast integer sort structure for random access of the temporary tables.
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.
JUMPSCAN TRUE
FALSE 		Indicates that the index scan is a jump scan.
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.
MXPPSCAN TRUE
FALSE		 Provides additional information about how MAXPAGES is calculated in the case of a jump scan. A jump scan can be conceptualized as multiple contiguous scans separated by jumps.

If the value is TRUE, then MAXPAGES is the number of pages that are expected to be accessed by each contiguous scan individually.

If the value is FALSE, then MAXPAGES is the number of pages that are expected to be accessed by all the contiguous scans in total.
NUMROWS INTEGER Number of rows expected to be sorted.
ONEFETCH TRUE
FALSE		 Indicates the GROUP BY conditions are satisfied by the first row produced by the input stream.
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.
OVERHEAD DOUBLE Optimizer used OVERHEAD value.
PARTCOLS Name of Column Partitioning columns for operator.
PBLKLOCK EXCLUSIVE
INTENT EXCLUSIVE
INTENT NONE
INTENT SHARE
REUSE
SHARE
SHARE INTENT EXCLUSIVE
SUPER EXCLUSIVE
UPDATE		 Positioning scan table lock intent.
PGLOLOCK EXCLUSIVE
INTENT EXCLUSIVE
INTENT NONE
INTENT SHARE
REUSE
SHARE
SHARE INTENT EXCLUSIVE
SUPER EXCLUSIVE
UPDATE 		Positioning scan global table lock intent.
PLANID Hexadecimal string representing a BIGINT value Identifier uniquely representing a query plan configuration for a given statement. The layout of the operators, accessed objects and relevant operator arguments and other plan properties affecting performance are represented by this value.
PREFETCH DYNAMIC LIST
LIST
NONE
READAHEAD
SEQUENTIAL
SEQUENTIAL, READAHEAD		 Type of prefetch eligible.
PFTCHSZ INTEGER Optimizer used PREFETCHSIZE value.
PROWLOCK EXCLUSIVE
NONE
REUSE SHARE
SHORT (INSTANT) SHARE
UPDATE 		Positioning scan row lock intent.
PTABLOCK EXCLUSIVE
INTENT EXCLUSIVE
INTENT NONE
INTENT SHARE
REUSE
SHARE
SHARE INTENT EXCLUSIVE
SUPER EXCLUSIVE
UPDATE		 Positioning scan table lock intent.
RAND ACC TRUE Indicates that the regular TEMP table allows random access. Random access is required for the ZZJN operator.
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 Intrapartition parallelism, granularity of the intrapartition parallel scan, expressed in SCANUNITs.
SCANTYPE LOCAL PARALLEL Intrapartition parallelism, index scan, table scan, or column-organized data scan.
SCANUNIT ROW
PAGE		 Intrapartition parallelism, scan granularity unit.
SEMEVID Hexadecimal string representing a BIGINT value Identifier for semantic environment at the time the statement was compiled. SEMEVID corresponds to monitoring element SEMANTIC_ENV_ID.
SEMIJOIN TRUE
FALSE		 This argument on the HSJOIN operator indicates whether column-organized processing should deploy the semi-join optimization technique to reduce the size of the hash table that is created for large hash join inner tables.
SHARED TRUE Intrapartition parallelism, shared TEMP indicator.
SHRCSE TRUE Temporary table over common subexpression shared between subsections.
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		 Intrapartition 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
    (R)
    Random
 Sort key columns.
SORTTYPE GLOBAL
MERGE
PARTITIONED
ROUND ROBIN
REPLICATED
SHARED		 Intrapartition 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.
STMTID Hexadecimal string representing a BIGINT value Identifier uniquely representing a normalized form of the SQL statement. The statement normalization follows the optimization profile's inexact matching rules. For details, see the related links.
STREAM TRUE
FALSE		 Remote source is streaming.
SYSTSENS YES
NO		 Indicates that the SYSTIMESENSITIVE bind option is in effect
TABLOCK EXCLUSIVE
INTENT EXCLUSIVE
INTENT NONE
INTENT SHARE
REUSE
SHARE
SHARE INTENT EXCLUSIVE
SUPER EXCLUSIVE
UPDATE		 Table Lock Intent.
TBISOLVL UNCOMMITED READ
READ STABILITY
CURSOR STABILITY
REPEATABLE READ		 Indicates the isolation level used by the operator to access the specific table
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 Intrapartition parallelism, number of subagents accessing Table Queue.

If the TQ operator represents the transition between column-organized data processing and row-organized data processing, the TQDEGREE argument indicates the number of column-organized processing subagents that are used to process the query in parallel.
TQMERGE TRUE
FALSE		 Merging (sorted) Table Queue indicator.
TQNUMBER INTEGER Table queue identification number.
TQREAD READ AHEAD
STEPPING
SUBQUERY STEPPING		 Table Queue reading property.
TQSECNFM INTEGER Subsection number at the sending end of the table queue.
TQSECNTO INTEGER Subsection number at the receiving end of the table queue.
TQSEND BROADCAST
DIRECTED
SCATTER
SUBQUERY DIRECTED		 Table Queue send property.
TQ TYPE LOCAL Intrapartition parallelism, Table Queue.
TQORIGIN ASYNCHRONY
XTQ
COLUMN-ORGANIZED DATA
ROW-ORGANIZED DATA		 The reason that Table Queue was introduced into the access plan.

COLUMN-ORGANIZED DATA indicates that the TQ operator is being used to transfer data from column-organized processing to row-organized processing.

ROW-ORGANIZED DATA indicates that the TQ operator is being used to transfer data from row-organized processing to column-organized processing.

COLUMN-ORGANIZED DATA or ROW-ORGANIZED DATA values result in the TQ operator being displayed as CTQ in 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
HASHED PARTIAL
HASHED COMPLETE		 This operator eliminates rows having duplicate values for a set of columns.

HASHED PARTIAL indicates that a partial early distinct operation was performed to efficiently remove many, if not all, duplicates. This reduces the amount of data that must be processed later in the query evaluation.

HASHED COMPLETE indicates that hashing is used to eliminate duplicates during column-organized data processing.
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.
USAGE SCALAR_SUBQUERY Indicates how the NLJOIN operator is being used. SCALAR_SUBQUERY indicates that the NLJOIN operator is being used to apply a scalar subquery predicate.

Hash join is the only join method used for column-organized tables. However, the optimizer uses the NLJOIN operator to model the application of scalar subqueries during column-organized data processing.

One of the input legs to the NLJOIN operator is the single value that is computed by the scalar subquery. The other input to the operator is the data stream whose predicate references the scalar value.
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.
XFERRATE DOUBLE Optimizer used TRANSFERRATE value.
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.