Columns for correlating EXPLAIN tables

You can use a set of standard columns to correlate records for a particular static SQL statement from different EXPLAIN tables.

You can use the columns to uniquely identify and correlate all EXPLAIN records for a particular static SQL statement.

Every EXPLAIN table, except for DSN_STATEMENT_CACHE_TABLE, contains the following columns:

Table 1. Descriptions of standard columns for all EXPLAIN tables.
Column name Data Type Description
QUERYNO INTEGER NOT NULL A number that identifies the statement that is being explained. The origin of the value depends on the context of the row:
For rows produced by EXPLAIN statements
The number specified in the QUERYNO clause, which is an optional part of the SELECT, INSERT, UPDATE, MERGE, and DELETE statement syntax.
For rows not produced by EXPLAIN statements
Db2 assigns a number that is based on the line number of the SQL statement in the source program.

When the values of QUERYNO are based on the statement number in the source program, values that exceed 32767 are reported as 0. However, in certain rare cases, the value is not guaranteed to be unique.

When the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, if the QUERYNO clause is specified, its value is used by Db2. Otherwise Db2 assigns a number based on the line number of the SQL statement in the compiled SQL function, native SQL procedure, or advanced trigger.
APPLNAME VARCHAR(24) NOT NULL1 The name of the application plan for the row. Applies only to embedded EXPLAIN statements that are executed from a plan or to statements that are explained when binding a plan. A blank indicates that the column is not applicable.

When the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, this column is not used, and is blank.
PROGNAME VARCHAR(128) NOT NULL The name of the program or package containing the statement being explained. Applies only to embedded EXPLAIN statements and to statements explained as the result of binding a plan or package. A blank indicates that the column is not applicable.

When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the specific name of the compiled SQL function or native SQL procedure. When the SQL statement is embedded in an advanced trigger, this column contains the name of the trigger.
VERSION VARCHAR(122) NOT NULL The version identifier for the package. Applies only to an embedded EXPLAIN statement executed from a package or to a statement that is explained when binding a package.

If the value is not blank, the value is the same as the VERSION value for the package that was used to create this EXPLAIN table row.

The value is blank for a statement in:
  • A package for a basic trigger (TYPE='T')
  • A package for an application that was precompiled without SQL processing option VERSION
  • A package that was precompiled with an empty string for the VERSION value (TYPE=blank)
When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the version identifier of the function or procedure. When the SQL statement is embedded in an advanced trigger body, this column is not used and will be blank.
COLLID VARCHAR(128) NOT NULL The collection ID:
'DSNDYNAMICSQLCACHE'
The row originates from the dynamic statement cache.
'DSNEXPLAINMODEYES'
The row originates from an application that specifies YES for the value of the CURRENT EXPLAIN MODE special register.
'DSNEXPLAINMODEEXPLAIN'
The row originates from an application that specifies EXPLAIN for the value of the CURRENT EXPLAIN MODE special register.

When the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, this column indicates the schema name of the compiled SQL function, native SQL procedure, or advanced trigger.

SECTNOI INTEGER NOT NULL WITH DEFAULT The section number of the statement. The value is taken from the same column in SYSPACKSTMT or SYSSTMT tables and can be used to join tables to reconstruct the access path for the statement. This column is applicable only for static statements. The default value of -1 indicates EXPLAIN information that was captured in DB2® 9 or earlier.
EXPLAIN_TIME TIMESTAMP NOT NULL The time when the EXPLAIN information was captured:
All cached statements
When the statement entered the cache, in the form of a full-precision timestamp value.
Non-cached static statements
When the statement was bound, in the form of a full precision timestamp value.
Non-cached dynamic statements
When EXPLAIN was executed, in the form of a value equivalent to a CHAR(16) representation of the time appended by 4 zeros.
Notes:
  1. The data type of this column is VARCHAR(128) NOT NULL in the following tables:
    • DSN_COLDIST_TABLE
    • DSN_KEYTGTDIST_TABLE