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.
Qualifiers
- 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
| 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:
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.
|
| 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 TYPE='ACCELMDL', this statement used accelerator modeling.
|
| 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.
|
| 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.
|
| 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.
|
| 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.
 The value is blank for a statement in:
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:
|
| 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:
|
| TYPE | CHAR(8) NOT NULL WITH DEFAULT | The type of the output for this row:
|
| REASON_CODE | SMALLINT NOT NULL WITH DEFAULT | The reason code for the row when TYPE='A'. For a description of the meaning, see REASON_CODE values. You can also check the QI_DATA column value, which might contain more specific information. |
| QI_DATA | CLOB(2M) NOT NULL WITH DEFAULT | When TYPE='A':
|
| 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:
This column can have one of the following values:
|
| AP_PLANID |
CHAR(16) FOR BIT DATA |
A unique identifier for BIND or PREPARE optimizations for an SQL statement, in the form of an extended timestamp value. |
REASON_CODE values
The REASON_CODE values correspond in most cases to the reason-code values that are returned with SQL code -4742.
For rows with non-zero REASON_CODE values, the QI_DATA column contains a description of reason code, sometimes with specific values included.
- 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 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.The QI_DATA column contains the statement text, after it is converted for processing by the accelerator.
- 1
- No active accelerator server was found or the table was not enabled for query acceleration 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 scrollable or is a rowset-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 recursive reference to a 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:
- Is not defined in the accelerator server
- Is defined in a different accelerator server from another table in the query
- Is defined in the accelerator server, but is not enabled for query acceleration
- 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 in Db2 after the data was loaded in the accelerator server.
- 15
- The query uses functionality that is available only in DB2 10 or later, and the functionality is not supported by the accelerator server.
- 17
- The query is an INSERT from SELECT statement. Subsystem parameter QUERY_ACCEL_OPTIONS does not specify option 2 to enable the acceleration of INSERT from SELECT statements.
- 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, executes remotely, or executes 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 cannot run on the accelerator. 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-fullselect 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 run on the accelerator 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 run a DDL or DML statement on an accelerator.
- 25
- The statement contains a reference to a column with an unsupported data type.
- 26
- FL 509 The CREATE TABLE statement clause IN ACCELERATOR specifies an accelerator alias that resolves to more than one accelerator and at least one of the following conditions is true:
- Your Db2 function level is lower than V12R1M509.
- The SYSACCEL.SYSACCELERATEDTABLES table does not have the FEATURE column.
- 27
- The DROP TABLE statement specifies a referencing accelerator-only table, which cannot be removed by using the DROP TABLE statement.
- 28
- FL 509 The SQL INSERT, UPDATE, or DELETE statement references a high availability accelerator-only table (AOT) that is defined in multiple accelerators and one of the following conditions is true:
- Neither the CURRENT ACCELERATOR special register nor the ACCELERATOR bind option is used.
- The CURRENT ACCELERATOR special register or the ACCELERATOR bind option specifies one of the following values:
- An alias that resolves to multiple accelerators. The alias must resolve to one V7 or later accelerator.
- An accelerator that is not a V7 or later accelerator. A V7 or later accelerator is required to execute the SQL statement.
- An accelerator that does not exist or is not available when the SQL statement is executed.
- An accelerator that is not qualified to execute the SQL statement for a high availability AOT.
- 29
- During a Db2 for z/OS special runtime incremental bind of a static query that was originally bound for acceleration with the QUERYACCELERATION bind option value of ELIGIBLE or ENABLE (not ENABLEWITHFAILBACK), the query cannot be bound for acceleration to the target accelerator that Db2 has selected for this particular run of the query. This special incremental bind usually occurs for one of the following reasons:
- The user table has been dropped and re-created in Db2 for z/OS but has not been refreshed in the target accelerator since the last time the static query was bound for acceleration.
- The archive status of the user table in the target accelerator has changed since the last time the static query was bound for acceleration.
Failure to bind the static query for acceleration during this incremental bind can occur if the user table in the target accelerator is down-level from the current definition of that table in the Db2 for z/OS catalog. This failure might also occur due to other reasons.
- 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 run on the accelerator due to different reasons on different versions of the accelerator.
- 34
- The statement included an expression that can run on an accelerator server only when the CURRENT QUERY ACCELERATION special register is set to ALL, ENABLE, or ELIGIBLE. However, the CURRENT QUERY ACCELERATION special register is set to NONE or ENABLE WITH FAILBACK.
See the programmer responses for the two different REASON_CODE values for the corresponding version of the accelerator.
- 35
- The query uses functionality that is available only in Db2 12 or later, and the accelerator server does not support the functionality.
- 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 acceleration 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.
- 42
- The SYSACCEL.SYSACCELERATORS or SYSACCEL.SYSACCELERATEDTABLES tables are not found.
- 900-999
- For IBM internal use only.