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 M because 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:
    1. The query contains a CHAR function in which LOCAL is specified as the second argument.
    2. The query contains a date or time expresssion and the DATE FORMAT field of the DSNTIP4 installation panel specifies LOCAL.
    3. Application programs that process SQL on Db2 for z/OS have been precompiled with the DATE(LOCAL) option.
    Exception: You can enable LOCAL date expressions in the format dd/mm/yyyy or yyyymmdd   (with two space characters at the end) by including the QUERY_ACCEL_OPTIONS ZPARM value 4 or 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 example:
    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:
    • LOCATE
    • MICROSECOND
    • MIDNIGHT_SECONDS
    • TRANSLATE
  • 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 C in the ARCHIVE column of the SYSACCEL.SYSACCELRATEDTABLES catalog table. A value of A or C indicates that the table data has been archived on an accelerator. The value C means 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 A or no value. The value A indicates 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...
    • BUSTIMESENSITIVE
    • BUSINESS_TIME SENSITIVE
    • FOR SYSTEM_TIME AS OF
    • FOR SYSTEM_TIME FROM ... TO ...
    • FOR SYSTEM_TIME BETWEEN ... AND ...
    • SYSTIMESENSITIVE
    • SYSTEM_TIME SENSITIVE

    In cases like this, you receive error message -4742 with reason code 18.