Conditions that prevent query routing to an accelerator
Db2 for z/OS does not route a query to an accelerator if any of the following conditions applies.
- You use a distributed data facility (DDF) client, such as an Open Database Connectivity (ODBC) client, that does not support level 8 of the IBM Distributed Relational Database Architecture™ (DRDA).
- The encoding scheme of an SQL statement is
Mbecause the tables use different encoding schemes, or the query contains an expression that explicitly relates to a coded character set identifier (CCSID), for example, a cast specification with a CCSID option.
- The FROM clause of the query specifies a data-change-table-reference, or, in other words, the query is selected from a FINAL TABLE or from an OLD TABLE.
- The query contains a string expression (including columns) with an unsupported subtype. See Table 3.
- The query contains an expression with an unsupported result data type or subtype. See Supported data types.
- The query refers to a column that uses a field procedure (FIELDPROC).
- The query uses a special register other than:
- CURRENT DATE
- CURRENT TIME
- CURRENT TIMESTAMP
- A LOCAL date format is used, that is, one or more of the following conditions apply:
Exception: You can enable LOCAL date expressions in the format
- The query contains a CHAR function in which LOCAL is specified as the second argument.
- The query contains a date or time expresssion and the DATE FORMAT field of the DSNTIP4 installation panel specifies LOCAL.
- Application programs that process SQL on Db2 for z/OS have been precompiled with the DATE(LOCAL) option.
yyyymmdd(with two space characters at the end) by including the
7. For more information, see Installing Db2 libraries with IBM Db2 Analytics Accelerator for z/OS support. You find a link to this topic under Related information.
- The query contains a sequence expression (NEXTVAL or PREVVAL).
- The query contains a user-defined function (UDF) other than an inline SQL scalar UDF
(for more information, see https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_createfuncinlinesqlscalar.html).
However, queries inside a compiled SQL scalar UDF can be routed to an accelerator if the UDF is defined with a QUERY ACCELERATION option other than NONE (for more information, see https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_createfunctionsqlscalar.html).
- The query contains a ROW CHANGE expression
- The query contains a string constant that is longer than 16000 characters.
- A new column name is referenced in a sort-key expression, for
SELECT C1+1 AS X, C1+2 AS Y FROM T WHERE ... ORDER BY X+Y;
- The query uses a DAY function where the argument of the function specifies a duration.
- The query uses a TRANSLATE function with only two arguments.
- The query uses one of the following scalar functions and one or more of its arguments contain a
parameter marker or a scalar fullselect:
- The query uses a table function, such as ADMIN_TASK_LIST or ADMIN_TASK_STATUS.
- The query uses an unsupported scalar function. See Table 3
- The GET_ACCEL_ARCHIVE special register is set to the value YES (meaning that archived
data will be included in the query) and the query references a table that has the value
Cin the ARCHIVE column of the SYSACCEL.SYSACCELRATEDTABLES catalog table. A value of
Cindicates that the table data has been archived on an accelerator. The value
Cmeans that the archived data is not stored on the accelerator that is supposed to process the query. In other words, the active query data and the archived data are not located on the same accelerator. To process the query successfully, the ARCHIVE column must show the value
Aor no value. The value
Aindicates that both, the active data and the archived data, are located on the same accelerator.
- The instruction to process “SELECT FROM ... INSERT INTO ... ” statements (ZPARM
QUERY_ACCEL_OPTIONS includes the value
2) is ignored if the target table uses an encoding scheme that is different from the encoding scheme of the tables in the SELECT statement.
- A static SQL query that includes a
FOR SYSTEM_TIME ...or
FOR BUSINESS_TIME ...is not routed to an accelerator if one of the following bind options (value YES, default) is used.
Table 1. Unsupported bind options Clause Bind option (value YES, which is the default)
FOR BUSINESS_TIME AS OF
FOR BUSINESS_TIME FROM ... TO ...
FOR BUSINESS_TIME BETWEEN ... AND...
FOR SYSTEM_TIME AS OF
FOR SYSTEM_TIME FROM ... TO ...
FOR SYSTEM_TIME BETWEEN ... AND ...
In cases like this, you receive error message -4742 with reason code 18.