-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 MEDIAN function can be run only on an accelerator server.
- The statement reference 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.Note: If you need more information about why the statement cannot be executed in the accelerator, issue the EXPLAIN statement and examine the output of the table DSN_QUERYINFO_TABLE.Possible values are:
- 1
- No active accelerator server was found when the statement was executed.
- 2
- The special register CURRENT QUERY ACCELERATION is set to NONE.
- 4
- The query is not read-only.
- 6
- The cursor is defined as scrollable or is a rowset-positioned cursor.
- 7
- The query uses 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 unsupported functionality.
- 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.
- 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 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.
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 the accelerator server.
- 2
- Set special register CURRENT QUERY ACCELERATION to ENABLE, ENABLE WITH FAILBACK, ELIGIBLE, or ALL.
- 4
- Ensure that the query meets the criteria for a read-only query. See the information on read-only cursors in DECLARE CURSOR.
- 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.
- 19
- Update 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 option 4, which, if not set properly, can cause -4742 with return code 19.
- 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.
SQLSTATE
560D5