-4742 THE STATEMENT CANNOT BE EXECUTED BY DB2 OR IN THE ACCELERATOR (REASON reason-code)

Explanation

A statement cannot be executed by Db2 or by an accelerator server.

The statement cannot be executed by Db2 if any of the following conditions are true:

  • Special register CURRENT GET_ACCEL_ARCHIVE is set to YES, and the statement needs to access data that is stored only in the accelerator server.
  • Special register CURRENT QUERY ACCELERATION is set to ALL, so the statement must be executed in the accelerator server.
  • A function that was referenced can be run only on an accelerator server.
  • The statement references an accelerator-only table.

The statement cannot be executed by an accelerator server if any condition that is indicated by a reason code is true.

reason-code
A numeric value that provides additional information about why the statement cannot be executed in the accelerator server.
Tip: If you need more information about why the statement cannot be executed in the accelerator, issue the EXPLAIN statement and examine the output in the DSN_QUERYINFO_TABLE table.
1
Start of changeNo active accelerator server was found or the table was not enabled for query acceleration 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
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 nested table expression.
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.
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).
  • Start of changeINSERT, UPDATE or DELETE of a row-fullselect with subselect. UPDATE ACCEL_ONLY_TABLE SET (C1, C2) = (SELECT C3®, C4 FROM TABLE2);End of change
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.
Start of change26End of change
Start of changeFL 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.
End of change
Start of changeStart of change27End of changeEnd of change
Start of changeThe DROP TABLE statement specifies a referencing accelerator-only table, which cannot be removed by using the DROP TABLE statement.End of change
Start of change28End of change
Start of changeFL 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.
End of change
Start of change29End of change
Start of changeDuring 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.

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 run on the accelerator due to different reasons on different versions of the accelerator.End of change
Start of change34End of change
Start of changeThe 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.

End of change
Start of change35End of change
Start of changeThe query uses functionality that is available only in Db2 12 or later, and the accelerator server does not support the functionality.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 acceleration 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
Start of change42End of change
Start of changeThe SYSACCEL.SYSACCELERATORS or SYSACCEL.SYSACCELERATEDTABLES tables are not found.End of change

System action

The statement cannot be processed.

Programmer response

Use the reason code to determine the cause of the failure, and take the appropriate action:

1
Start of changeStart the accelerator server or enable the table for query acceleration.End of change
2
Set the CURRENT QUERY ACCELERATION special register to ENABLE, ENABLE WITH FAILBACK, ELIGIBLE, or ALL.
3
None.
4
Ensure that the query meets the criteria for a read-only query. See the information on read-only cursors in DECLARE CURSOR statement.
6
Remove the SCROLL clause or WITH ROWSET POSITIONING clause from the cursor declaration.
7
Ensure that all objects to which the query refers have the same encoding scheme.
8
Rewrite the FROM clause of the query so that it does not include a data-change-table-reference clause.
9
Ensure that the FROM clause of the query does not contain a subquery.
10
Ensure that the FROM clause of the query does not reference a common table expression.
11
Remove the unsupported functionality from the query.
12
Ensure that all tables that are referenced by the query are in the same accelerator server, and that all tables are enabled for query acceleration.
13
Start the accelerator server that contains the tables that are referenced in the query.
14
Load the altered table into the accelerator server again.
15
Ensure that the query does not use any functionality that is not supported by the accelerator server.
17
Enable the acceleration of INSERT from SELECT statements by updating subsystem parameter QUERY_ACCEL_OPTIONS to include option 2.
18
Ensure that the query does not use any functionality that is not supported by the accelerator server.
19
Start of changeUpdate the accelerator server to enable acceleration of the SQL statement that contains the function text or expression text. Check that your QUERY_ACCEL_OPTIONS values are properly specified. Pay special attention to options 4, 7, and 13 which, if not set properly, can cause -4742 with return code 19.End of change
20
If the rowset cursor query usage cannot be changed, use QUERY ACCELERATION NONE.
21
Rewrite the correlated subquery that is not supported for acceleration as a JOIN expression. For some examples, see How IBM Db2 Analytics Accelerator for z/OS handles correlated subqueries.
22
Ensure the statement does not reference an accelerator-only table, or replace the accelerator-only table with a Db2 table so that the query can be executed in Db2.
23
Remove the SELECT INTO statement, or REBIND the package with bind option QUERYACCELERATION(NONE). If the program is an SQL PL procedure or function, use one of the following methods instead of using BIND or REBIND PACKAGE:
  • Alter the procedure or function by using ALTER PROCEDURE or ALTER FUNCTION
  • Drop and then re-create the procedure or function by using CREATE PROCEDURE or CREATE FUNCTION
24
Issue the DDL or DML statement directly from the Db2 for z/OS server or from a requester that does not require two-phase commit protocols, for example, the IBM® Data Server Driver for JDBC.
25
Remove the reference to the column with an unsupported data type.
Start of change26End of change
Start of changeFL 509 Take one of the following actions:
  • Ensure that the IN ACCELERATOR clause in the CREATE TABLE statement specifies one accelerator or an alias that resolves to only one accelerator.
  • Upgrade your Db2 function level to V12R1M509 and add the FEATURE column to the SYSACCEL.SYSACCELERATEDTABLES table.
End of change
Start of change27End of change
Start of changeTo 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 change28End of change
Start of changeFL 509 Set the CURRENT ACCELERATOR special register or ACCELERATOR bind option to specify an accelerator or an alias that resolves to only one accelerator that meets all of the following requirements:
  • It must be a V7 or later accelerator.
  • It must be available.
  • It must be qualified to execute the SQL INSERT, UPDATE, or DELETE statement for the high availability accelerator-only table (AOT).
End of change
Start of change29End of change
Start of changeTake one of the following actions:
  • Rebind the Db2 application package for the static query with bind option QUERYACCELERATION(ENABLEWITHFAILBACK).
  • If a Db2 for z/OS accelerator contains a down-level version of the referenced user table, take one of the following actions on that accelerator:
    • Refresh and reload the user table in that accelerator to match the current table definition that is in the Db2 for z/OS catalog.
    • Permanently remove the user table from that accelerator.
    For more information about the actions on the accelerator, see IBM Db2 Analytics Accelerator for z/OS documentation.

After completing one of these actions, rerun the application for the static query.

If the QUERYACCELERATION bind option is set to ENABLEWITHFAILBACK, failure to accelerate the query results in failback to Db2 and the query is run only on Db2 for z/OS. This failure does not return an error code for your application. If you are using the ENABLEWITHFAILBACK setting and the conditions for reason code 29 occur during the special incremental bind, the query is run only on Db2 for z/OS and SQLCODE -4742 is not returned for your application or transaction.

End of change
Start of change30End of change
Start of changeEither commit the previous Db2 change in the Db2 unit of work before running the query to be accelerated, or do not specify an accelerator WAITFORDATA delay for this unit of work.End of change
Start of change31End of change
Start of changeEither commit the previous Db2 change in the Db2 unit of work before running the query to be accelerated, or do not specify an accelerator WAITFORDATA delay for this unit of work.End of change
Start of change32End of change
Start of changeEither commit the previous accelerator-only table (AOT) change in the Db2 unit of work before running the query, or do not specify an accelerator WAITFORDATA delay for this unit of work.End of change
Start of change33End of change
Start of changeRequest EXPLAIN output and check the REASON_CODE column in the DSN_QUERYINFO_TABLE table. For situations that result in reason code 33, two rows are returned with different REASON_CODE values:
  • A row that contains the REASON_CODE value that explains why the query cannot run on an accelerator version earlier than V7.
  • A row that contains the REASON_CODE value that explains why the query cannot run on a V7 or later accelerator. In this case, the error description might be prefixed by (Vx) to indicate the specific accelerator version. The (Vx) prefix is displayed only when reason code 33 is returned by the original query.

See the programmer responses for the two different reason codes for the corresponding version of the accelerator.

End of change
Start of change34End of change
Start of changeSet the CURRENT QUERY ACCELERATION special register to ALL, ENABLE, or ELIGIBLE to execute passthrough-only expressions.End of change
Start of change35End of change
Start of changeEnsure that the query does not use any functionality that is not supported by the accelerator server.End of change
Start of change36End of change
Start of changeStart the V7 or later accelerator or enable the table for query acceleration.End of change
Start of change37End of change
Start of changeStart the V7 or later accelerator that contains the tables that are referenced in the query.End of change
Start of change38End of change
Start of changeUpdate the V7 or later accelerator server to enable acceleration of the SQL statement that contains the function text or expression text. Verify that your QUERY_ACCEL_OPTIONS values are specified properly.End of change
Start of change42End of change
Start of changeEnsure that all tables that are required for query acceleration are created. For more information, see Tables that support query acceleration.End of change

SQLSTATE

560D5