Setting ZPARMs for IBM Db2 Analytics Accelerator in Db2 for z/OS

The following ZPARMs are available:

ACCEL=COMMAND | AUTO | NO

COMMAND

To start the accelerator by manually invoking the -start ACCEL <name> command, where <name> is the name of the accelerator.

AUTO

To automatically start the accelerator when the Db2 for z/OS subsystem starts.

NO

To specify that the accelerator cannot be used with this Db2 subsystem.

GET_ACCEL_ARCHIVE NO | YES

Specifies whether data that has been archived by the High Performance Storage Saver is searched when a query is executed.

YES

Archived data is searched.

NO

Archived data is not searched.

QUERY_ACCELERATION

The treatment of incoming queries depends, among other factors, on the setting of the CURRENT QUERY ACCELERATION special register, which is a Db2 for z/OS special register that was introduced for IBM Db2 Analytics Accelerator for z/OS.

The value of the QUERY_ACCELERATION ZPARM provides the default setting for the CURRENT QUERY ACCELERATION special register. Both, the ZPARM and the special register accept the following values:

1 (NONE)

No routing of dynamic SQL queries to an accelerator. Queries will be processed by Db2 for z/OS only (inhouse query processing).

2 (ENABLE)

A dynamic SQL query will be routed to an accelerator if it fulfills all required conditions. An incoming query is tested against a set of heuristics, which include the table size and a response time estimate based on cost information from the SYSIBM.DSN_PROFILE_ATTRIBUTES table. Both tests ensure that a query will only be routed to an accelerator if the query can be expected to run faster than in Db2 for z/OS. However, if an error occurs while the query is being processed by the accelerator, Db2 for z/OS will return a negative SQLCODE to the application and query processing will stop.

3 (ENABLE WITH FAILBACK)

Dynamic queries are accelerated only if Db2 for z/OS determines that it is advantageous to do so. If an accelerator returns an error during the PREPARE phase or when first opening (OPEN) the query, the query is processed by Db2 for z/OS rather than sent to the accelerator. If the accelerator returns an error during a FETCH operation or a subsequent OPEN operation, Db2 for z/OS returns an error to the user and the query ends abnormally.

4 (ELIGIBLE)

Dynamic queries are accelerated if they are eligible for acceleration. Db2 for z/OS does not use cost information to determine whether to accelerate the queries. Queries that are not eligible for acceleration are executed by Db2 for z/OS. If an accelerator fails while a query is running, or if the accelerator returns an error, Db2 for z/OS returns a negative SQL code to the application.

5 (ALL)

A dynamic query will always be routed to an accelerator, no matter if it fulfills the conditions or not. If processing cannot start or continue because an incoming query fails to fulfill all the conditions for accelerated query processing, Db2 for z/OS returns a negative SQLCODE to the application and query processing ends abruptly. That is, the query will not be processed at all.

Exceptions:

• If a dynamic query cannot be processed by IBM Db2 Analytics Accelerator because the tables do not exist on the accelerator, the query usually fails with an error message (-4742, reason code 12). However, such a query is processed by Db2 for z/OS if it references tables with the following qualifiers only:
  • SYSIBM
  • SYSACCEL
  • DB2GSE
  • SYSXSR
  • DGTT
• Dynamic queries whose top query block is pruned, and which therefore return an empty result set, are processed by Db2 for z/OS. To check whether a query falls into this category, follow these steps:
  1. Explain the query using the DB2® EXPLAIN function. Use the following special register setting in the SQL statement:

     SET CURRENT QUERY ACCELERATION = NONE

  2. Check the PLAN_TABLE. If the top query block has been pruned, the entry for the query block shows PRUNED in the QBLOCK_TYPE column and no value in the ACCESSTYPE column.

QUERY_ACCEL_OPTIONS = NONE | TRUE

The following options are currently available for this ZPARM.

Draft comment: kuester
Still valid?

NONE (default)

Means that values cannot be set.

TRUE

Brings up the DSNTIP8A panel, which allows you to set one or more of the values in the range from 1 to 13 (separated by a comma):

+------------------------ 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           |
| 13 ENABLE ACCELERATOR SPECIFIC RESULTS ===> NO    NO or YES           |         
|                                                                       |
| PRESS:  ENTER to continue   UP/DOWN to scroll   RETURN to exit        |
+-----------------------------------------------------------------------+

Important: The options 1, 3, 5, and 6 apply to IBM Db2 Analytics Accelerator Version 5.1 only, and are therefore not described here.

2 INSERT FROM SELECT

NO

For an INSERT operation that includes a SELECT statement, the SELECT portion of the statement is not routed to an accelerator, that is, the entire statement is executed in Db2 for z/OS.

YES

For an INSERT operation that includes a SELECT statement, this option causes the SELECT portion of the statement to be routed to an accelerator and the INSERT operation to be performed by Db2 for z/OS.

Notes:

• It is not necessary to set this option to YES for in-database transformation with accelerator-only tables because transactions on this type of table can only be executed on an accelerator. The processing location is automatically determined by the type of the table.
• The referenced tables on the accelerator might not be in sync with the tables in Db2 for z/OS.
• Db2 for z/OS does not route the SELECT portion of the statement if the target table in the INSERT statement uses an encoding scheme that is different from the scheme of the tables in the SELECT statement.

4 DD/MM/YYYY LOCAL DATE FORMAT

NO

Queries containing expressions with a DATE data type in a LOCAL format are not accelerated.

YES

Allows the acceleration of queries that use expressions with a DATE data type in a LOCAL format.

IBM Db2 Analytics Accelerator uses the dd/mm/yyyy format to interpret the input and the output date values.

Use this option only if the DATE FORMAT field of install panel DSNTIP4 specifies LOCAL or if application programs that process SQL on DB2 have been precompiled with the DATE(LOCAL) option. In either case, 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 fail with SQLCODE -4742 and reason code 19 or return unpredictable results.

Option 4 cannot be used if option 7 is specified because these options are mutually exclusive.

The installation panel or bind option specifies LOCAL as the format for dates and if dd/mm/yyyy is used as the representation of the LOCAL format. If dd/mm/yyyy is not used as the date representation of the LOCAL format, queries might return unpredictable results.

Recommendation: If you want to use this option in connection with a Db2 data sharing group, specify it on all members of the group.

7 YYYYMMDD LOCAL DATE FORMAT

NO

Queries containing expressions with a DATE data type in a LOCAL format are not accelerated.

YES

Same as option 4, but for LOCAL Db2 DATE values in the yyyymmdd format (with two space characters at the end).

Use this option only if the DATE FORMAT field of install panel DSNTIP4 specifies LOCAL or if application programs that process SQL on Db2 have been precompiled with the DATE(LOCAL) option. In either case, the LOCAL date exit routine must define the specific YYYYMMDD date format. If the LOCAL format is not defined as YYYYMMDD , the query might fail with SQLCODE -4742 and reason code 19 or return unpredictable results.

Option 7 cannot be used if option 4 is enabled because these options are mutually exclusive.

8 FAVOR NEW ACCELERATOR_TYPE

NO

Favors the older accelerator (version 5.x.x) in an environment that contains version 5 and version 7 accelerators. That is, queries eligible for query acceleration on both types of accelerators are always sent to the version 5 accelerator.

YES

Favors the newer accelerator (version 7.x.x) in an environment that contains version 5 and version 7 accelerators. That is, queries eligible for query acceleration on both types of accelerators are always sent to the version 7 accelerator.

Important: FAVOR NEW ACCELERATOR_TYPE is ignored if option 11, USE ONLY NEW ACCELERATOR_TYPE, is set to YES.

9 ENABLE UNCERTAINTY COST ESTIMATION

NO

Uncertainty cost consideration is disabled for accelerated queries.

YES

Enables uncertainty cost consideration for accelerated queries.

10 BALANCE WORKLOAD BETWEEN VERSIONS

NO

In an environment with version 5 and version 7 accelerators, the individual workload on each accelerator will not be taken into account when the routing decision is made. That is, a query will be sent to one of the available accelerators at random or according to the setting of the options 8 or 10 on this panel.

YES

Bases the routing decision on the individual workload of each accelerator in an environment with version 5 and version 7 accelerators. That is, the accelerator with the lowest workload will be selected.

You can also influence workload balancing by using the CURRENT ACCELERATOR special register. For example, if this option is set to YES, and the CURRENT ACCELERATOR special register specifies a particular accelerator, the workload will be routed to that server only if it is available. This holds true even if accelerators with a lower 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

NO

In an environment with version 5, version 7, and even newer accelerators, EXPLAIN processing, accelerator modeling, and query execution is available for all accelerators regardless of their version.

YES

Reserves EXPLAIN processing, accelerator modeling, and query execution exclusively for the newest accelerator type in environments where version 5, version 7, or later accelerator types coexist.

Important: If 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

NO

Specifies that queries having IN list predicates with more than 32767 (32K) elements neither run in Db2 for z/OS, nor in IBM Db2 Analytics Accelerator.

YES

Specifies that queries having IN list predicates with more than 32767 (32K) elements can qualify to run in IBM Db2 Analytics Accelerator version 7 or later versions because these versions use IBM Db2 Warehouse to process SQL queries.

When such queries run on a version 7 (or later) accelerator, 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 rather than to the SQL limitations of Db2 for z/OS. For more information, see IN predicate.

13 ENABLE ACCELERATOR SPECIFIC RESULTS

NO

Prevents queries from running on the accelerator if these queries reference expressions that might return different results, such as the RAND SQL function. Queries of this type are processed by Db2 for z/OS.

For more information, see Supported SQL functions and expressions.

YES

Allows queries to run on the accelerator even if these queries reference expressions that might return different results. The queries are, unless other conditions prohibit this, processed by IBM Db2 Analytics Accelerator.

The following ZPARMs are relevant for the IBM Integrated Synchronization function only. If you do not want to use this function, you can ignore the rest of the list.

CMTSTAT ACTIVE | INACTIVE

Set this to CMTSTAT INACTIVE, which is also the default. So you have to change the parameter value just in case the current value is ACTIVE. You need not change the setting if the parameter is not set at all.

The CMTSTAT subsystem parameter controls whether in-use database access threads (DBATS) can be pooled, for use by other connections, after they successfully commit or roll back. For more information, see DDF THREADS field (CMTSTAT subsystem parameter).

The setting CMTSTAT ACTIVE is rarely used these days and has not been recommended for a long time. In the past, a performance improvement could be achieved by using it in a controlled environment with a fixed (known) number of connections.

However, the use of this setting leads to a drawback if the number of connections varies. To restrict or control the number of connections, you need to set the MAXDBAT parameter, which defines a maximum for the number of connections. For example, if you want 10000 client connections, you have to set MAXDBAT to 10000. The use of such a limit is very resource-intensive.

Another drawback is that HTTP sessions cannot be reused. This means that every single HTTP request requires an SSL handshake. This is true for both, the HTTP application programming interface (API) and the log reader API of Db2 for z/OS. The higher demand for SSL handshakes binds AT-TLS resources unnecessarily.

TCPKPALV ENABLE | DISABLE | <integer>

Set this parameter to the integer value 120 or ENABLE. The value 120 is also the default. The value ENABLE means that the TCP/IP KeepAlive configuration value is used and not overridden with the TCPKPALV value instead (see the TCPCONFIG code sample in Defining TTLS rules). Change the parameter value just in case it is set to DISABLE. You need not change the setting if the parameter is not set at all.

The TCPKPALV subsystem parameter determines whether the TCP/IP KeepAlive value in the TCP/IP configuration is to be overridden with a different value. For more information, see TCP/IP KEEPALIVE field (TCPKPALV subsystem parameter)

POOLINAC <integer>

Set this parameter to the integer value 120, which is also the default. So you have to change this parameter just in case it is set to a different value. You need not change the setting if the parameter is not set at all.

The POOLINAC subsystem parameter specifies the approximate time, in seconds, that a database access thread (DBAT) can remain idle in the pool before it is terminated. For more information, see POOL THREAD TIMEOUT field (POOLINAC subsystem parameter)

Important:

• You can change the value of QUERY_ACCEL_OPTIONS online.
• If you use IBM Db2 Analytics Accelerator with a Db2 for z/OS data sharing group, make sure that all members of the data sharing group use the same setting for QUERY_ACCEL_OPTIONS.