DSN_QUERYINFO_TABLE

The query information table, DSN_QUERYINFO_TABLE, contains information about the eligibility of query blocks for automatic query rewrite, information about the materialized query tables that are considered for eligible query blocks, reasons why ineligible query blocks are not eligible, and information about acceleration of query blocks.

Begin program-specific programming interface information.
Recommendation: Do not manually insert data into system-maintained EXPLAIN tables, and use care when deleting obsolete EXPLAIN table data. The data is intended to be manipulated only by the Db2 EXPLAIN function and optimization tools. Certain optimization tools depend on instances of the various EXPLAIN tables. Be careful not to delete data from or drop instances EXPLAIN tables that are created for these tools.

Qualifiers

Your subsystem or data sharing group can contain more than one of these tables:
userID
You can create additional instances of EXPLAIN tables that are qualified by user ID. These tables are populated with statement cost information when you issue the EXPLAIN statement or bind, or rebind, a plan or package with the EXPLAIN(YES) option. SQL optimization tools might also create EXPLAIN tables that are qualified by a user ID. You can find the SQL statement for creating an instance of these tables in member DSNTESC of the SDSNSAMP library.

Sample CREATE TABLE statement

You can find a sample CREATE TABLE statement for each EXPLAIN table in member DSNTESC of the prefix.SDSNSAMP library. You can call the ADMIN_EXPLAIN_MAINT stored procedure to create EXPLAIN tables, upgrade them to the format for the current Db2 release, or complete other maintenance tasks. See ADMIN_EXPLAIN_MAINT stored procedure for information about using the action input parameter to request each of these tasks.

Column descriptions

Table 1. Descriptions of columns in DSN_QUERYINFO_TABLE
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.

Start of changeWhen 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.End of change

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.

Start of changeWhen TYPE='ACCELMDL', this statement used accelerator modeling.End of change

Start of changeFor static queries, the value in this field might be the name of the accelerator server that is available at execution time.End of change

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.

Start of changeFor static queries, the value in this field might be the name of the accelerator server that is available at execution time.End of change

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.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column is not used, and is blank.End of change

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.

Start of changeWhen 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.End of change

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.

Start of changeWhen 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.End of change

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.

Start of changeWhen 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.End of change

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
Start of changeNo active accelerator server was found or the table was not enabled for query offloading when the statement was executed.End of change
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
Start of changeThe cursor is defined as a scrollable cursor or is a row set-positioned cursor.End of change
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.
Start of change20End of change
Start of changeThe query is a rowset cursor that is declared WITH RETURN, runs remotely, or runs under an SQL PL routine.End of change
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.
Start of change24End of change
Start of changeThe 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.End of change
25
The statement contains a reference to a column with an unsupported data type.
Start of change26End of change
Start of changeThe CREATE TABLE statement clause IN ACCELERATOR specifies an accelerator alias that resolves to more than one accelerator. End of change
Start of change27End of change
Start of changeThe 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.

End of change
Start of change30End of change
Start of changeAccelerator 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.End of change
Start of change31End of change
Start of changeAccelerator 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.End of change
Start of change32End of change
Start of changeAccelerator 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.End of change
Start of change33End of change
Start of changeThe query could not be offloaded to the accelerator due to different reasons on different versions of the accelerator.End of change
Start of change36End of change
Start of changeThe 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.End of change
Start of change37End of change
Start of changeThe 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.End of change
Start of change38End of change
Start of changeThe USE ONLY NEW ACCELERATOR_TYPE subsystem parameter is set to YES, but the V7 or later accelerator is not at the correct level.End of change
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.
Start of change EXPANSION_REASON End of change Start of change CHAR(2) NOT NULL WITH DEFAULT End of change Start of changeThis 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.
End of change
End program-specific programming interface information.