ACCELERATION OPTIONS field (QUERY_ACCEL_OPTIONS subsystem parameter)
The QUERY_ACCEL_OPTIONS subsystem parameter controls whether certain types of
queries are allowed to execute on an accelerator server.
Acceptable values:
ACCELERATION OPTIONS field: NONE, YES
QUERY_ACCEL_OPTIONS parameter: NONE, options-list
Default:
NONE
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS
NONE
Specifies that you do not want to choose whether Db2 allows certain types of queries to run on an accelerator server. Db2 does not allow any of those types of queries to run on an accelerator server.
YES
Panel DSNTIP8A opens after you complete DSNTIP82, and the options-list value for QUERY_ACCEL_OPTIONS is based on the options that you specify.
For example, if you specify YES for fields 1, 2, and 5 on panel DSNTIP8A, Db2 uses the following setting:
QUERY_ACCEL_OPTIONS=(1,2,5)
Panel DSNTIP8A
On panel DSNTIP8A, you select the types of queries that Db2 allows to run on an accelerator server.
Figure 1. DSNTIP8A
+------------------------ ACCELERATION OPTIONS ------------------------+
| DSNTIP8A Acceleration Options Panel |
| ===> |
| |
| Allow queries to run on an accelerator server that contain: |
| 1 MBCS EBCDIC ===> NO NO or YES |
| 2 INSERT FROM SELECT ===> NO NO or YES |
| 3 BYTE-BASED STRING FUNCTIONS ===> NO NO or YES |
| 4 DD/MM/YYYY LOCAL DATE FORMAT ===> NO NO or YES |
| 5 SYSTEM_TIME TEMPORAL QUERIES ===> NO NO or YES |
| 6 TS COLS WITH PRECISION OF 12 ===> NO NO or YES |
| 7 YYYYMMDD LOCAL DATE FORMAT ===> NO NO or YES |
| 8 FAVOR NEW ACCELERATOR_TYPE ===> NO NO or YES |
| 9 ENABLE UNCERTAINTY COST ESTIMATION ===> NO NO or YES |
| 10 BALANCE WORKLOAD BETWEEN VERSIONS ===> NO NO or YES |
| 11 USE ONLY NEW ACCELERATOR_TYPE ===> NO NO or YES |
| 12 IN PREDICATE WITH MORE THAN 32K ELEMENTS ===> NO NO or YES |
| 13 ENABLE ACCELERATOR SPECIFIC RESULTS ===> NO NO or YES |
| PRESS: ENTER to continue UP/DOWN to scroll RETURN to exit |
+----------------------------------------------------------------------+
1 MBCS EBCDIC
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 1)
NO
Specifies that queries that include data that is encoded in a multi-byte-character-set EBCDIC encoding scheme are blocked from executing on an accelerator server.
YES
Specifies that queries that include data that is encoded in a multi-byte-character-set EBCDIC encoding scheme are allowed to execute on an accelerator server.
If the accelerator server encodes data in another encoding scheme, such as Unicode UTF-8, the collating sequence for the data on the accelerator server is different from the collating sequence for the data in Db2. This difference affects data ordering and the results from range predicates. Therefore, if table columns include any mixture of uppercase characters, lowercase characters, numeric characters, or national language characters, and the SQL statements that query those tables include range predicates or ordering on those columns, the results of executing a query in Db2 might be different from the results of executing the same query on an accelerator server.
2 INSERT FROM SELECT
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 2)
NO
Specifies that for queries that include an INSERT FROM SELECT statement, the SELECT operation does not run on an accelerator server.
YES
Specifies that for queries that include an INSERT FROM SELECT statement, the SELECT operation can run on an accelerator server.
If the SELECT operation runs on the accelerator server, the data that is operated on by the SELECT operation might not be current in the accelerator server.
3 BYTE-BASED STRING FUNCTIONS
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 3)
NO
Specifies that queries that contain built-in functions for which Db2 processes each byte of the input string, rather than each character of the input string, do not run on an accelerator server.
YES
Specifies that queries that contain built-in functions for which Db2 processes each byte of the input string, rather than each character of the input string, can run on an accelerator server. The queries can run on an accelerator server even when the input string is encoded in a multi-byte-character encoding scheme. Unicode is an example of a multi-byte-character encoding scheme.
For an accelerator server that supports only functions that operate on the characters in an input string, if the input data on which the function operates contains only single-byte characters, the function returns the same result when it runs on the accelerator server and when it runs on Db2. However, if the input data contains multi-byte characters, the function returns different results when it runs on the accelerator server and when it runs on Db2.
4 DD/MM/YYYY LOCAL DATE FORMAT
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 4)
NO
Specifies that queries that reference an expression with a DATE data type that uses a LOCAL format are blocked from running on an accelerator server.
YES
Specifies that queries that reference an expression with a DATE data type that uses a LOCAL format are not blocked from running on an accelerator server. The accelerator server uses the format 'dd/mm/yyyy' to interpret the input and output date value.
Option 4 is not permitted when option 7 has been selected.
Specify option 4 only when one of the following conditions exists:
The DATE FORMAT field of the DSNTIP4 panel specifies LOCAL.
Application programs that process SQL on Db2 are precompiled with the DATE(LOCAL) option.
In both of these cases, the LOCAL date exit routine must define the specific 'dd/mm/yyyy' date format. If the LOCAL format is not defined as 'dd/mm/yyyy', the query might return unpredictable results.
5 SYSTEM_TIME TEMPORAL QUERIES
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 5)
NO
Specifies that SYSTEM_TIME temporal queries will not be offloaded to an accelerator.
YES
Specifies that SYSTEM_TIME temporal queries will be offloaded to an accelerator.
6 TS COLS WITH PRECISION OF 12
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 6)
NO
Specifies that queries will not be offloaded to an accelerator if the queries reference timestamp columns with precision of 12.
YES
Specifies that queries will be offloaded to an accelerator if the queries reference timestamp columns with precision of 12.
7 YYYYMMDD LOCAL DATE FORMAT
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 7)
NO
Specifies that queries that reference an expression with a DATE data type that uses a LOCAL format are blocked from running on an accelerator server.
YES
Specifies that queries that reference an expression with a DATE data type that uses a LOCAL format are not blocked from running on an accelerator server. The accelerator server uses the format YYYYMMDD, padded with two blanks at the end, to interpret the input and output data value.
Option 7 is not permitted when option 4 has been selected.
Specify option 7 only when one of the following conditions exists:
The DATE FORMAT field of the DSNTIP4 panel specifies LOCAL.
Application programs that process SQL on Db2 are precompiled with the DATE(LOCAL) option.
In both cases, the LOCAL date exit routine must define the specific 'yyyymmdd' date format. If the LOCAL format is not defined as 'yyyymmdd', the query might return unpredictable results.
8 FAVOR NEW ACCELERATOR_TYPE
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 8)
NO
Specifies that in a Db2 environment that includes both a V5 accelerator server and a V7 accelerator server, statements that are eligible to be accelerated on either of the accelerator servers are sent to the V5 accelerator server.
YES
Specifies that in a Db2 environment that includes both a V5 accelerator server and a V7 accelerator server, statements that are eligible to be accelerated on either of the accelerator servers are sent to the V7 accelerator server.
Important: FAVOR NEW ACCELERATOR_TYPE is ignored if option 11, USE ONLY NEW ACCELERATOR_TYPE, is set to YES.
9 ENABLE UNCERTAINTY COST ESTIMATION
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 9)
NO
Specifies that uncertainty cost consideration is disabled on the accelerator for offloaded queries.
YES
Specifies that uncertainty cost consideration is enabled on the accelerator for offloaded queries.
10 BALANCE WORKLOAD BETWEEN VERSIONS
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 10)
NO
Specifies that Db2 will not attempt to balance query acceleration between different versions of accelerator servers based on the actual workload of each available server.
YES
Specifies that Db2 will attempt to balance query acceleration between different versions of accelerator servers based on the actual workload of each available server.
Workload balancing can also be influenced by the CURRENT ACCELERATOR register. For example, when option 10 = YES, if the CURRENT ACCELERATOR special register specifies a particular accelerator server, the workload will be offloaded only to that server if it is available, even if other accelerator servers with less workload are available.
Important: BALANCE WORKLOAD BETWEEN VERSIONS is ignored if option 11, USE ONLY NEW ACCELERATOR_TYPE, is set to YES.
11 USE ONLY NEW ACCELERATOR_TYPE
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 11)
NO
Specifies that in a Db2 environment that includes V5 accelerators and V7 or later accelerators, EXPLAIN processing, accelerator modeling, and query execution is supported for all accelerators regardless of version.
YES
Specifies that in a Db2 environment that includes V5 accelerators and V7 or later accelerators, EXPLAIN processing, accelerator modeling, and query execution is supported only for V7 and later accelerators.
Important: When option 11 is set to YES, the following options, if specified, are ignored:
8: FAVOR NEW ACCELERATOR_TYPE
10: BALANCE WORKLOAD BETWEEN VERSIONS
12 IN PREDICATE WITH MORE THAN 32K ELEMENTS
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 12)
NO
Specifies that queries having IN list predicates with more than 32767 (32K) elements do not run on Db2 for z/OS® or on IBM® Db2 Analytics Accelerator servers. The query fails with SQL code -101 during Db2 for z/OS PREPARE or bind, as is expected for the Db2 for z/OS limitation of a maximum 32K elements in an IN list.
YES
FL 503 Specifies that queries having an IN list predicate with more than 32K elements can qualify to run IBM Db2 Analytics Accelerator V7 for z/OS, which uses IBM Db2 Warehouse to process SQL statements.
When such queries run on a V7 accelerator, any limits on the number of list elements in the IN list are considered and applied by IBM Db2 Warehouse, according to the SQL limitations of that database product, instead of Db2 for z/OS. For more information, see "Accelerator-only support for more than 32K elements in an IN list" in IN predicate.
13 ENABLE ACCELERATOR SPECIFIC RESULTS
Acceptable values:
NO, YES
Default:
NO
DSNZPxxx:
DSN6SPRM.QUERY_ACCEL_OPTIONS (option 13)
NO
Specifies that queries are blocked from running on an accelerator server if they reference expressions that might return different results when running on the accelerator server instead of Db2.
YES
Specifies that queries are allowed to execute on an accelerator server if they reference expressions that might return different results when run on the accelerator instead of Db2.
