How Db2 determines whether to accelerate eligible queries

Only eligible queries are candidates to be passed to an accelerator server. However, even if a query is eligible for acceleration, Db2 might not pass the query to an accelerator server.

Begin program-specific programming interface information.

Here are the criteria that make a query eligible to be passed to an accelerator server:

  • The accelerator must support all of the SQL functions in the query.
  • The tables that the query references must be in the accelerator server.
  • Each table that the query references must have a row in the SYSACCEL.SYSACCELERATEDTABLES table.
  • The query is dynamic or static and is defined as read-only.
  • The query is a SELECT statement, an INSERT with SELECT statement, or a local SELECT INTO statement.
  • The static query is not a remote SELECT INTO statement and is not part of an expression of the SET host-variable assignment statement.
  • The cursor for the query is not a scrollable or rowset cursor.
  • All of the other conditions are true, and Db2 determines that the query should be accelerated.

After a query is determined to be eligible, Db2 bases the decision to pass a query to the accelerator server on the following factors:

  • Db2 determines that it can run the query more quickly than the accelerator. In general, for a short-running query, Db2 requires less time to run a query than it would take to send the query to the accelerator server and receive the data from the accelerator server. This criterion is used only in the following cases:
    • If the QUERY_ACCELERATION subsystem parameter is set to ENABLE or ENABLE_WITH_FAILBACK
    • If the CURRENT QUERY ACCELERATION special register overrides the QUERY_ACCELERATION value with a setting of ENABLE or ENABLE WITH FAILBACK
    • If the Db2 package is bound with the QUERYACCELERATION bind option value of ENABLE or ENABLEWITHFAILBACK
  • The isolation level for the query is not supported by the accelerator server.
  • Db2 cannot guarantee that the results that are generated by the accelerator server are the same as the results that are generated by Db2.

An accelerator server is qualified to process a query if it is active and contains all of the tables that are referenced in the query. If more than one accelerator server is qualified to process a query, Db2 either sends the query to the first qualified accelerator server, or if the accelerator supports workload balancing, Db2 sends the query to the accelerator server that has greater capacity and lower utilization.

End program-specific programming interface information.
Start of change

Accelerating queries with passthrough-only expressions

FL 504

Passthrough-only expressions cannot run on Db2 for z/OS® and are supported only by accelerator servers. Db2 only verifies that the data types of the parameters are valid for the functions. The accelerator engine does all other function resolution processing and validation.

To process a query that contains a passthrough-only expression, Db2 takes one of the following actions:
  • Passes the query through to IBM® Db2 Analytics Accelerator
  • Finds and runs an existing user-defined function with basic parameter attributes that match the attributes of the passthrough-only expression
The following values for the QUERY_ACCELERATION subsystem parameter, CURRENT QUERY ACCELERATION special register, or QUERYACCELERATION bind option determine the action that Db2 takes on queries with passthrough-only expressions:
Value Effect on queries with passthrough-only expressions
ALL, ENABLE, or ELIGIBLE Db2 passes the query through to the accelerator server.

Db2 for z/OS does not provide cost estimates for passthrough-only expressions, which cannot run on z/OS.
NONE Db2 does not pass the query through to the accelerator server. Instead, Db2 uses the usual function resolution process to resolve to an existing user-defined function. Db2 then uses this function to run the query.

If a matching user-defined function does not exist, Db2 does not run the query and issues an error.
ENABLE WITH FAILBACK Db2 does not pass the query through to the accelerator server. Instead, Db2 issues an error, even if a user-defined function that matches the passthrough-only expression exists.
End of change