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: NONE, YES
Default: NONE
DSNZPxxx: DSN6SPRM.QUERY_ACCEL_OPTIONS

Choose NONE if you do not want to choose whether Db2 allows certain types of queries to run on an accelerator server. If you choose NONE, Db2 does not allow any of those types of queries to run on an accelerator server. If you choose YES, you proceed to panel DSNTIP8A after completing panel DSNTIP82.

This panel is displayed:

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           |         
|                                                                      |
| PRESS:  ENTER to continue   UP/DOWN to scroll   RETURN to exit       |
+----------------------------------------------------------------------+

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.

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.

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.

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.

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.

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.

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

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

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

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.

End of change
Start of change

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