QUERYACCELERATION bind option

The QUERYACCELERATION bind option specifies whether a static SQL query is bound for acceleration, and if so, with what behavior. If the bind option is specified, it also provides the initial value for the CURRENT QUERY ACCELERATION special register that is used for dynamic queries, if a SET statement has not been issued for the special register.

When a package that is bound with this bind option runs, the QUERYACCELERATION value, instead of the QUERY_ACCELERATION subsystem parameter, provides the initial value for the CURRENT QUERY ACCELERATION special register. The CURRENT QUERY ACCELERATION special register specifies the acceleration behavior for dynamic SQL queries. Therefore, by rebinding the application package with the QUERYACCELERATION bind option, you can also accelerate dynamic SQL queries in an application. You can specify this bind option instead of either modifying the application to add an explicit SET CURRENT QUERY ACCELERATION statement or setting the QUERY_ACCELERATION subsystem parameter. For dynamic SQL queries in a package, the bind option value that you specify overrides the QUERY_ACCELERATION subsystem parameter. This bind option does not have a default value, so if you do not specify this bind option, the QUERY_ACCELERATION subsystem parameter initializes the special register.

Restriction: The QUERYACCELERATION bind option applies only to cursor queries, the SELECT portion of the SQL INSERT from SELECT statement, and static SQL SELECT INTO statements that are run locally. If a static SQL SELECT INTO statement is bound for acceleration but run remotely, Db2 fails the statement at run time and returns a negative SQL code.

Command option	Option values	Used with
QUERYACCELERATION	( NONE ) ( ENABLE ) ( ENABLEWITHFAILBACK ) ( ELIGIBLE ) ( ALL )	BIND PACKAGE REBIND PACKAGE ¹

Note:

The QUERYACCELERATION bind option is not valid for REBIND of packages for native SQL procedures or advanced triggers.

Option descriptions for QUERYACCELERATION

( NONE )

Specifies that no static SQL query in the application is bound for acceleration or will be accelerated when the application is run.

( ENABLE )

Specifies that a static SQL query is bound for acceleration if it satisfies the acceleration criteria, including the cost and heuristics criteria. The query is routed to an accelerator when the application runs. Otherwise, if the static query does not satisfy the acceleration criteria, the query is bound for execution in Db2.

If an error condition, such as one of the following examples, occurs while executing the accelerated static query when the application is run, Db2 fails the static query and returns a negative SQL code to the application:

A failure occurs while running the static query on the accelerator.
The accelerator returns an error for the query.
The accelerator is not started and Db2 cannot route the static query to the accelerator for execution.

( ENABLEWITHFAILBACK )

Results in the same behavior as ENABLE, except if one of the error conditions occurs on the first OPEN of the accelerated static query when the application is run. In this case, instead of failing the static query and returning a negative SQL code to the application, Db2 performs a temporary statement-level incremental bind of the query and runs the query in Db2. The application does not see the acceleration failure. Failback to Db2 is not possible after the application does a successful OPEN for the query on the accelerator.

Restriction: Start of change

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

( ELIGIBLE )

Specifies that a static SQL query is bound for acceleration if the query meets the basic acceleration criteria, regardless of the cost or heuristics criteria. The query is routed to the accelerator when the application runs.

Like the behavior for ENABLE, if an error condition occurs while executing the accelerated static query when the application is run, Db2 fails the static query and returns a negative SQL code to the application.

( ALL )

Specifies that all of the static SQL queries in the application are to be bound for acceleration and routed to the accelerator when the application runs. If Db2 determines that a static query cannot be bound to run on the accelerator and the query references a user base table or view, the BIND or REBIND PACKAGE operation fails with an error message for that query. (A failure exception is made for declared global temporary tables (DGTTs) and created global temporary tables and (CGTTs) because these tables cannot be accelerated.)

The Db2 catalog table SYSIBM.SYSPACKSTMT has a STATUS column value of 'O' for static SQL queries that have been bound for acceleration. For cursor-based static queries, the DECLARE CURSOR, OPEN, FETCH, and CLOSE statements each have a SYSPACKSTMT.STATUS column value of 'O'. For a static INSERT from SELECT statement, where the SELECT has been bound for acceleration, the SYSPACKSTMT.STATUS column value is also 'O'.

Default values for QUERYACCELERATION

Process	Default value
BIND PACKAGE	None
REBIND PACKAGE	None

There is no default value for this bind option. The QUERYACCELERATION bind option does not inherit the setting from the QUERY_ACCELERATION subsystem parameter.