Enabling acceleration of SQL queries
You can enable specific queries to run on an accelerator server by specifying setting various subsystem parameters, bind options, and special registers.
Before you begin
Ensure that the following prerequisite tasks are completed:
- Install and configure an accelerator server, as described in IBM Db2 Analytics Accelerator for z/OS documentation.
- Create a database, a table space, tables, and indexes that are used to support the acceleration of queries.
For more information about these and other prerequisites, see Enabling Db2 for IBM Db2 Analytics Accelerator for z/OS.
About this task
The process of enabling acceleration of an SQL query consists of setting the appropriate combination of subsystem parameters, bind options, and special registers depending on your unique goals. Use the information in this task to understand the effects of setting each acceleration-related element, including the precedence order where applicable, and to set these elements appropriately.
Procedure
- The ACCEL subsystem parameter controls whether a Db2 subsystem can use accelerator servers.
- The QUERY_ACCELERATION subsystem parameter, the QUERYACCELERATION bind option, and the CURRENT QUERY ACCELERATION special register control the circumstances in which Db2 considers sending queries to an accelerator and the behavior when acceleration fails.
The QUERY_ACCELERATION subsystem parameter and the CURRENT QUERY ACCELERATION special register apply only to dynamic SQL queries. The QUERYACCELERATION bind option applies to both static and dynamic SQL queries.
The precedence order (lowest to highest) for setting the CURRENT QUERY ACCELERATION special register is as follows:- The QUERY_ACCELERATION subsystem parameter
- The QUERYACCELERATION bind option, if specified
- An explicit SET CURRENT QUERY ACCELERATION statement
- The QUERY_ACCEL_OPTIONS subsystem parameter specifies other types of SQL queries that are eligible for acceleration.
- The ACCELERATOR bind option and the CURRENT ACCELERATOR special register specify a preferred accelerator where Db2 sends accelerated SQL queries.
The CURRENT ACCELERATOR special register applies only to dynamic SQL queries. The ACCELERATOR bind option applies to both static and dynamic SQL queries. For dynamic SQL queries, the ACCELERATOR bind option is the default value when the SET CURRENT ACCELERATOR statement is not issued.
The precedence order (lowest to highest) for setting the CURRENT ACCELERATOR special register is as follows:- The ACCELERATOR bind option, if specified
- An explicit SET CURRENT ACCELERATOR statement
- The GET_ACCEL_ARCHIVE subsystem parameter, the GETACCELARCHIVE bind option, and the CURRENT GET_ACCEL_ARCHIVE special register control whether a SQL query retrieves data that is archived on the accelerator, instead of active data.
The GET_ACCEL_ARCHIVE subsystem parameter and the CURRENT GET_ACCEL_ARCHIVE special register apply only to dynamic SQL queries. The GETACCELARCHIVE bind option applies to both static and dynamic SQL queries.
The precedence order (lowest to highest) for setting the CURRENT GET_ACCEL_ARCHIVE special register is as follows:- The GET_ACCEL_ARCHIVE subsystem parameter
- The GETACCELARCHIVE bind option, if specified
- An explicit SET CURRENT GET_ACCEL_ARCHIVE statement
- The QUERY_ACCEL_WAITFORDATA subsystem parameter, the ACCELERATIONWAITFORDATA bind option, and the CURRENT QUERY ACCELERATION WAITFORDATA special register specify the maximum amount of time, if any, that the accelerator will delay a query while it waits for the Change Data Capture component to replicate committed Db2 data changes.
The QUERY_ACCEL_WAITFORDATA subsystem parameter and the CURRENT QUERY ACCELERATION WAITFORDATA special register apply only to dynamic SQL queries. The ACCELERATIONWAITFORDATA bind option applies to both static and dynamic SQL queries.
The precedence order (lowest to highest) for setting the CURRENT QUERY ACCELERATION WAITFORDATA special register is as follows:- The QUERY_ACCEL_WAITFORDATA subsystem parameter
- The ACCELERATIONWAITFORDATA bind option, if specified
- An explicit CURRENT QUERY ACCELERATION WAITFORDATA statement
The following table shows the settings that enable the sending of queries to accelerator servers.
| Subsystem parameter, special register, or bind option | Valid settings |
|---|---|
| ACCEL subsystem parameter | COMMAND or AUTO |
| QUERY_ACCELERATION subsystem parameter (sets the default value for the CURRENT QUERY ACCELERATION special register) | NONE, ENABLE, ENABLE_WITH_FAILBACK, ELIGIBLE, or ALL |
| QUERYACCELERATION bind option | NONE, ENABLE, ENABLEWITHFAILBACK, ELIGIBLE, or ALL |
| CURRENT QUERY ACCELERATION special register | NONE, ENABLE, ENABLE WITH FAILBACK, ELIGIBLE, or ALL |
| QUERY_ACCEL_OPTIONS subsystem parameter (sets various acceleration behavior options that Db2 uses for accelerating statements) | NONE, 1, 2, 3, or 4 |
| ACCELERATOR bind option | The name of an eligible accelerator server |
| CURRENT ACCELERATOR special register | The name of an eligible accelerator server |
| GET_ACCEL_ARCHIVE subsystem parameter (sets the default value for the CURRENT GET_ACCEL_ARCHIVE special register) | NO or YES |
| GETACCELARCHIVE bind option | NO or YES |
| CURRENT GET_ACCEL_ARCHIVE special register | NO or YES |
| QUERY_ACCEL_WAITFORDATA subsystem parameter (sets the default value for the CURRENT QUERY ACCELERATION WAITFORDATA special register) | 0.0 - 3600.0 |
| ACCELERATIONWAITFORDATA bind option | 0.0 - 3600.0 |
| CURRENT QUERY ACCELERATION WAITFORDATA special register | 0.0 - 3600.0 |
What to do next
Identify the accelerator servers that are available to Db2 by issuing the -START ACCEL command. For more information, see Identify available accelerators to Db2 for z/OS.