|
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 or native SQL procedure, 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 or native SQL
procedure.
|
|
QBLOCKNO
|
SMALLINT NOT NULL
|
A number that identifies each query
block within a query. The value of the numbers are not in any particular order, nor are they
necessarily consecutive. |
| QINAME1 |
VARCHAR(128) NOT NULL WITH DEFAULT |
When TYPE='A':
- When REASON_CODE=0, this value is the name of the accelerator server to which the
statement is sent.
- When REASON_CODE<>0, the statement was not sent to an accelerator server. The
REASON_CODE value indicates why the statement was not sent to the accelerator server.
When TYPE='ACCELMDL', this statement used accelerator
modeling.
For static queries, the value in this field
might be the name of the accelerator server that is available at execution time.
|
| QINAME2 |
VARCHAR(128) NOT NULL WITH DEFAULT |
When TYPE='A' and REASON_CODE=0, this value is the name of the location name of the
accelerator server to which the statement is sent.
For static queries, the value in this field
might be the name of the accelerator server that is available at execution time.
|
|
APPLNAME
|
VARCHAR(24) NOT NULL
|
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 or native SQL procedure, 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.
|
|
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. 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 version
identifier of the compiled SQL function or native SQL procedure.
|
|
COLLID
|
VARCHAR(128) NOT NULL
|
The collection ID:
- '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 or native SQL procedure, this column indicates the schema name
of the compiled SQL function or native SQL procedure.
|
|
GROUP_MEMBER
|
VARCHAR(24)
NOT NULL
|
The member name of the Db2 that executed EXPLAIN. The column is blank if
the Db2 subsystem was not in a data sharing
environment when EXPLAIN was executed. |
|
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. |
| SEQNO |
INTEGER NOT NULL WITH DEFAULT |
The sequence number for
this row if QI_DATA exceeds the size of its column. |
|
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.
|
| TYPE |
CHAR(8) NOT NULL WITH DEFAULT |
The type of the output for this row:
- A
- This row is for a statement that Db2 attempts
to run on an accelerator server. The value in column REASON_CODE indicates the outcome.
|
| REASON_CODE |
SMALLINT NOT NULL WITH DEFAULT |
The reason code for the row when TYPE='A'.
- 0
- The query block qualifies for routing to an accelerator server. The values of QINAME1 and
QINAME2 identify the accelerator server.
For example, for version 1 of the IBM®
Db2 Analytics Accelerator for z/OS®, the associated data mart name is recorded in the QINAME2
column, with the following naming convention:
data-mart-name@accelerator-name@digits.
- 1
No active accelerator server was found or the table was not enabled for query
offloading when the statement was executed.
- 2
-
The CURRENT QUERY ACCELERATION special register is set to NONE.
- 3
- Db2 classified the query as a
short-running query, or Db2 determined that
sending the query to an accelerator server provided no performance advantage.
- 4
- The query is not read-only.
- 6
-
The cursor is defined as a scrollable cursor or is a row
set-positioned cursor.
- 7
- The query references objects with multiple encoding schemes.
- 8
- The FROM clause of the query specifies a data-change-table-reference.
- 9
- The query contains a table expression with one or more correlated references to other tables in
the same FROM clause.
- 10
- The query contains a reference to a recursive common table expression.
- 11
- The query contains an unsupported expression. The text of the expression is in QI_DATA.
- 12
- The query references a table that has any of the following characteristics:
- The table is not defined in the accelerator server.
- The table is defined in a different accelerator server from another table in the query.
- The table is defined in the accelerator server, but is not enabled for processing by an
accelerator.
- 13
- The accelerator server that contains the tables that are referenced in the query is not
started.
- 14
- A column that is referenced in the query was altered by Db2 after the data was loaded in the accelerator
server.
- 15
- The query uses functionality that is available only in Db2 for z/OS
Version 10 new-function mode or later, and the functionality is not supported by the accelerator
server.
- 17
- The query is an INSERT statement, but the Db2 subsystem parameter DSN6SPRM.QUERY_ACCEL_OPTIONS
does not specify option 2 to enable its acceleration.
- 18
- The query uses functionality that is available only in Db2 11 or later, and the functionality is not
supported by the accelerator server.
- 19
- The accelerator server is not at the correct level and does not support a function in the SQL
statement. The QI_DATA column contains the function text or expression text that is using the
unsupported function for the accelerator server.
20
The query is a rowset cursor that is declared WITH RETURN, runs remotely, or runs under an SQL
PL routine.
- 21
- The query contains a correlated subquery that is not supported for acceleration.
- 22
- The statement references an accelerator-only table, but the statement is not offloadable. For example:
- MERGE statement references an accelerator-only table.
- UPDATE or DELETE statement references an accelerator-only table, but the target table of the
UPDATE, DELETE is a normal Db2 table:
UPDATE DB2_TABLE SET .. (SELECT .. FROM ACCEL_ONLY_TABLE).
- INSERT, UPDATE or DELETE of a row expression with subselect. UPDATE ACCEL_ONLY_TABLE SET (C1,
C2) = (SELECT C3®, C4 FROM TABLE2);
- 23
- The SELECT INTO statement is bound for acceleration but is run as a remote SELECT INTO
statement, which is not supported for acceleration.
24
The DDL or DML statement cannot be offloaded because the connection to the accelerator server
does not allow updates. This problem can occur when a two-phase commit requester (for example, a
Db2 for z/OS requester) connects to a Db2 for z/OS server to offload a DDL or DML
statement.
- 25
- The statement contains a reference to a column with an unsupported data type.
26
The CREATE TABLE statement clause IN ACCELERATOR specifies an accelerator alias that resolves to
more than one accelerator. 
27
The DROP TABLE statement specifies a referencing accelerator-only table, which cannot be removed
by using the DROP TABLE statement.To drop a referencing accelerator-only table, invoke the
IBM Db2 Analytics Accelerator for z/OS ACCEL_REMOVE_REFERENCE_TABLES
stored procedure.

30
Accelerator WAITFORDATA behavior is requested but cannot be achieved for this query. The query
will not be accelerated. The query specifies a Db2 accelerated table, but the same Db2 unit of work includes a previous uncommitted
Db2 change that will not be available to
the query when it is run on the accelerator. The Db2 change might or might not be related to the
Db2 accelerated table that is referenced in
the query.
31
Accelerator WAITFORDATA behavior is requested but cannot be achieved for this query that
specifies both an accelerator-only table (AOT) and a Db2 accelerated table. The query will not be
accelerated and cannot be run in Db2. The
same Db2 unit of work includes a previous
uncommitted Db2 change that will not be
available to the query when it is run on the accelerator. The change might or might not be related
to the Db2 accelerated table that is
referenced in the query.
32
Accelerator WAITFORDATA behavior is requested but cannot be achieved for this query. The query
will not be accelerated. The query specifies a Db2 accelerated table, but the same Db2 unit of work includes a previous uncommitted
accelerator-only table (AOT) change. This uncommitted change resulted in the creation of an
accelerator database snapshot isolation (SI) for this unit of work before the query was
run. This accelerator database SI can prevent committed and replicated Db2 changes, made either by this transaction or by a
different transaction, from being available to the accelerated query, even if the Db2 changes are replicated to the accelerator before
the query is run there.
33
The query could not be offloaded to the accelerator due to different reasons on different
versions of the accelerator.
36
The USE ONLY NEW ACCELERATOR_TYPE subsystem parameter is set to YES; however, either an active V7 or later accelerator was not found or the table was not enabled for query offloading in the V7 or later accelerator when the statement was executed.
37
The USE ONLY NEW ACCELERATOR_TYPE subsystem parameter is set to YES, but the V7 or later accelerator that contains the tables of the query is not started.
38
The USE ONLY NEW ACCELERATOR_TYPE subsystem parameter is set to YES, but the V7 or later accelerator is not at the correct level.
- 900-999
- For IBM internal use only.
|
| QI_DATA |
CLOB(2M) NOT NULL WITH DEFAULT |
When TYPE='A':
- For REASON_CODE values other than 0, this value is the description of the REASON_CODE
value.
- For a REASON_CODE value of 0, this value is the statement text, after it is
converted for processing by the accelerator.
|
| SERVICE_INFO |
BLOB(2M) NOT NULL WITH DEFAULT |
IBM internal use only.
|
| QB_INFO_ROWID |
ROWID NOT NULL GENERATED ALWAYS |
IBM internal use
only. |
EXPANSION_REASON
 |
CHAR(2) NOT NULL
WITH DEFAULT
 |
This column applies only to
statements that reference archive tables or temporal tables. For other
statements, this column is blank.Indicates the effect of the CURRENT TEMPORAL BUSINESS_TIME
special register, the CURRENT TEMPORAL SYSTEM_TIME special register, and the SYSIBMADM.GET_ARCHIVE
built-in global variable. These items are controlled by the BUSTIMESENSITIVE, SYSTIMESENSITIVE, and
ARCHIVESENSITIVE bind options.
Db2 implicitly adds certain syntax to the query if
one of the following conditions are true:
- The SYSIBMADM.GET_ARCHIVE global variable is set to Y and the ARCHIVESENSITIVE bind option is
set to YES
- The CURRENT TEMPORAL BUSINESS_TIME special register is not null and the BUSTIMESENSITIVE bind
option is set to YES
- The CURRENT TEMPORAL SYSTEM_TIME special register is not null and the SYSTIMESENSITIVE bind
option is set to YES
This column can have one of the following values:
- 'A'
- The query contains implicit query transformation as a result of the SYSIBMADM.GET_ARCHIVE
built-in global variable.
- 'B'
- The query contains implicit query transformation as a result of the CURRENT TEMPORAL
BUSINESS_TIME special register.
- 'S'
- The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME
special register.
- 'SB'
- The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME
special register and the CURRENT TEMPORAL BUSINESS_TIME special register.
- blank
- The query does not contain implicit query transformation.
 |