SET CURRENT QUERY ACCELERATION statement

Invocation for SET CURRENT QUERY ACCELERATION

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization for SET CURRENT QUERY ACCELERATION

None required.

Description for SET CURRENT QUERY ACCELERATION

NONE

Specifies that no query acceleration is done.

ENABLE

Specifies that queries are accelerated only if Db2 determines that it is advantageous to do so. If an accelerator failure occurs while a query is running or if the accelerator returns an error, Db2 returns a negative SQLCODE to the application.

ENABLE WITH FAILBACK

Specifies that queries are accelerated only if Db2 determines that it is advantageous to do so. If the accelerator returns an error during the PREPARE or first OPEN for the query, Db2 executes the query without the accelerator. If the accelerator returns an error during a FETCH or a subsequent OPEN, Db2 returns the error to the user and does not execute the query.

Restriction: FL 504 If the query contains a passthrough-only expression, Db2 returns an error and does not accelerate the query, even if a matching user-defined function exists. For more information about passthrough-only expressions, see Accelerating queries with passthrough-only expressions.

ELIGIBLE

Specifies that queries are accelerated if they are eligible for acceleration. Db2 does not use cost information to determine whether to accelerate the queries. Queries that are not eligible for acceleration are executed by Db2. If an accelerator failure occurs while a query is running or if the accelerator returns an error, Db2 returns a negative SQLCODE to the application.

ALL

Specifies that queries are accelerated if they are eligible for acceleration. Db2 does not use cost information to determine whether to accelerate the queries. Queries that are not eligible for acceleration are not executed by Db2, and an SQL error is returned. If an accelerator failure occurs while a query is running or if the accelerator returns an error, Db2 returns a negative SQLCODE to the application.

Exceptions:

• If a dynamic query cannot be processed by IBM® Db2 Analytics Accelerator because the tables do not exist on the accelerator, the query usually fails with an error message (-4742, reason code 12). However, such a query is processed by Db2 if it references tables with the following qualifiers only:
  • SYSIBM
  • SYSACCEL
  • DB2GSE
  • SYSXSR
  • DGTT
• Dynamic queries whose top query block is pruned, and which therefore return an empty result set, are processed by Db2. To check whether a query falls into this category, follow these steps:
  1. Explain the query using the Db2 EXPLAIN function. Use the following special register setting in the SQL statement:

     SET CURRENT QUERY ACCELERATION = NONE
     

  2. Check the PLAN_TABLE. If the top query block has been pruned, the entry for the query block shows PRUNED in the QBLOCK_TYPE column and no value in the ACCESSTYPE column.

host-variable

A variable with a data type of CHAR or VARCHAR. The length must not exceed 255 bytes. Valid values are NONE, ENABLE, ENABLE WITH FAILBACK, ELIGIBLE, or ALL. If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value. The value of host-variable must be left justified and must be padded on the right with blanks.

Notes for SET CURRENT QUERY ACCELERATION