IBM Support

PM58732: SQLCODE901 ISSUED BY IDAA IF QUERY QUALIFIES FOR OFFLOADING AND IT CONTAINS COMMON TABLE EXPRESSION (CTE)

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • This APAR addresses the following 3 DB2 IDAA offload related
    issues:
    1. If a complex query contains CTE and many subqueries, when
       DB2 offloads query to IDAA, a syntax error and SQLCODE901
       could occur.
    2. When user requests DB2 IDAA offload for a query run from
       a DB2 package that is bound REOPT(AUTO) and the query
       validly does not qualify for IDAA offload but is run in DB2
       instead, the following DB2 application abend is possible
       during a second or later OPEN cursor for that query :
          ABEND04E RC00E70005 at DSNXGRDS . DSNXERT :P115
    3. DB2 does not support IDAA offload for a dynamic SQL query
       that is on the same input line that begins with an SQL
       simple comment (--) and a linefeed is used to separate
       the SQL simple comment from the SQL query.
    
    Additional search keywords. OFFSETP115 AB04E REOPT AUTO
                                IDAAV2R1/K
    Additional dump symptoms: 0C4 DSNXONZC OFFSET8568 856E
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 9 and DB2 10 for z/OS who   *
    *                 are also using IDAA ( IBM DB2 Analytics      *
    *                 Accelerator )                                *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR contains the following fixes  *
    *                      for DB2 IDAA support:                   *
    *                                                              *
    *                      1. Before this APAR, a query with at    *
    *                      least one common table expression       *
    *                      (CTE) and converted query length        *
    *                      greater than 32K bytes would return     *
    *                      SQLCODE -901 when offloaded to IDAA.    *
    *                                                              *
    *                      2.Before this APAR, a query with at     *
    *                      least one UNION, UNION ALL,             *
    *                      INTERSECT, INTERSECT ALL, EXCEPT,       *
    *                      EXCEPT ALL with a SELECT list item      *
    *                      that involves an aggregate function     *
    *                      for a decimal, date, time, or           *
    *                      timestamp result column would           *
    *                      return SQLCODE -904 when offloaded to   *
    *                      IDAA.                                   *
    *                                                              *
    *                      3. When user requests DB2 IDAA offload  *
    *                      for a query run from a DB2 package      *
    *                      that is bound REOPT(AUTO) and           *
    *                      the query validly does not qualify      *
    *                      for IDAA offload but is run in DB2      *
    *                      instead, the following DB2 application  *
    *                      abend is possible during a second or    *
    *                      later OPEN cursor when DB2 does an      *
    *                      implicit reoptimization re-prepare of   *
    *                      the query for the Dynamic Statement     *
    *                      Cache:                                  *
    *                       ABEND04E RC00E70005 at DSNXERT :P115   *
    *                                                              *
    *                      4. When using DB2 DSNTEP4 to run        *
    *                      dynamic SQL queries and user requested  *
    *                      IDAA offload for those queries, and     *
    *                      Dynamic Statement Caching is not        *
    *                      active, the following SQL error         *
    *                      invalidly occurred:                     *
    *                       SQLCODE -249  SQLERRP = DSNLXRSQ       *
    *                                                              *
    *                      5. DB2 does not support IDAA offload    *
    *                      for a dynamic SQL query that is         *
    *                      on the same input line that begins      *
    *                      with an SQL simple comment (--)         *
    *                      and a linefeed is used to separate      *
    *                      the SQL simple comment from the         *
    *                      SQL query.                              *
    *                                                              *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR contains the following fixes for DB2 IDAA offload
    support of queries:
                                                                   .
    1. Before this APAR, a query with at least one common table
    expression (CTE) and converted query length greater than 32K
    bytes would return SQLCODE -901 when offloaded to IDAA. To
    determine the converted query length, EXPLAIN the query and
    select LENGTH(QI_DATA) from DSN_QUERYINFO_TABLE.
                                                                   .
    2.Before this APAR, a query with at least one UNION, UNION ALL,
    INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL with a SELECT list
    item that involves an aggregate function for a decimal, date,
    time, or timestamp result column would return SQLCODE -904 when
    offloaded to IDAA.
      For a date, time or timestamp result column, the SQLCODE -904
    is returned when offloaded to IDAA only if one of the SELECT
    list items invovles a constant.
    For example:
    SELECT MAX('02/24/2012')
    FROM T1
    UNION ALL
    SELECT MAX(C15DATE)
    FROM T2;
      For a decimal result column, the SQLCODE -904 is always
    returned when offloaded to IDAA.
    For example:
    SELECT MAX(C8DECIMAL)
    FROM T1
    UNION ALL
    SELECT MAX(C8DECIMAL)
    FROM T2;
                                                                   .
    3. The user requested DB2 IDAA offload for a query from a DB2
    package that is bound with bind option REOPT(AUTO) and DB2
    Dynamic Statement Caching ( DSC ) is active. For whatever
    valid reason the query is not eligible for IDAA offload and is
    cached in the DSC and run in DB2 instead.  During the second or
    later OPEN cursor for the query running in DB2, the following
    DB2 application abend is possible when DB2 does an implicit
    reoptimization re-prepare of the query for the DB2 DSC (due to
    the REOPT(AUTO) behavior):
         ABEND04E RC00E70005 at DSNXGRDS . DSNXERT :P115
    The DB2 abend occurred because DB2 did not correctly handle
    the implicit reopt re-prepare for this query that was not
    offloaded to IDAA. This REOPT(AUTO) problem only occurs when
    the user has requested IDAA offload and DB2 validly does not
    offload the query.
                                                                   .
    4. When using DB2 DSNTEP4 to run dynamic SQL queries and user
    requested IDAA offload for those queries, and Dynamic Statement
    Caching (DSC) is not active, the following SQL error occurred:
     DSNT408I SQLCODE = -249, ERROR:  DEFINITION OF ROWSET ACCESS
              FOR CURSOR <unknown> IS INCONSISTENT WITH THE FETCH
              ORIENTATION CLAUSE  SPECIFIED
     DSNT418I SQLSTATE   = 24523 SQLSTATE RETURN CODE
     DSNT415I SQLERRP    = DSNLXRSQ SQL PROCEDURE DETECTING ERROR
                                                                   .
    This error is issued by DB2. The error does not occur when DSC
    is active.
                                                                   .
    DSNTEP4 default cursor behavior is ROWSET and 100 rows for
    a fetch. DB2 IDAA offload is not supported for ROWSET queries
    and multi-row fetching, so DB2 does not offload such queries
    (by design).  Instead, DB2 runs the query in DB2. However,
    the query should not have failed with SQLCODE249 but should have
    run successfully in DB2.  If DSC is active in this scenario,
    the query runs successfully. The SQLCODE249 occurred invalidly
    because, when DSC is not active DB2 did not correctly handle
    the ROWSET query and the SQLCODE249 resulted. It is not a TEP4
    error or problem.
                                                                   .
    This SQLCODE249 can also occur if another application or vendor
    product attempted to run a query with ROWSET in this same IDAA
    offload request scenario.
                                                                   .
    5. An SQL simple comment starts with the characters '--', and
    any characters that follow '--' on the same input line are
    considered as a comment, even if what follows is an SQL
    statement.  However, if a linefeed character is embedded or
    used on that input line to separate the comment from the
    SQL statement specified, then the SQL statement is not
    considered part of the SQL simple comment.
    In this linefeed case, if that simple comment line is input
    for a dynamic SQL statement and the SQL statement specified
    is a query, DB2 does not consider the dynamic query for
    IDAA offload.
    

Problem conclusion

  • 1. Code is fixed to offload a query with at least one CTE
    and converted query length greater than 32K bytes if there
    are no other restrictions preventing the query from offloading.
                                                                   .
    2. Code is fixed to offload a query involving a UNION,
    UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL with
    a SELECT list item that is a decimal, date, time, or timestamp
    result column.
                                                                   .
    3. DB2 REOPT(AUTO) support was changed to correctly handle
    the DB2 implicit reoptimization re-prepare on OPEN for a query
    that did not qualify for DB2 IDAA offload.
                                                                   .
    4. DB2 was changed to correctly run a ROWSET query in DB2 when
    the user requested IDAA offload and Dynamic Statement Caching
    is not active. The ROWSET query is correctly *not* offloaded to
    IDAA.
                                                                   .
    5. DB2 now supports IDAA offload for a dynamic SQL query that
    is on the same input line that begins with an SQL simple
    comment (--) and a linefeed is used to separate the SQL
    simple comment from the SQL query.
                                                                   .
    Additional Search Keywords:
    IDAAV2R1/K SQLCODE904 SQLCODE901 SQLUNION SQLUNIONALL
    SQLINTERSECT SQLINTERSECTALL SQLEXCEPT SQLEXCEPTALL
    REOPT AUTO SQLDYNSTMTCACHE AB04E OFFSETP115 00E70005
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM58732

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-02-22

  • Closed date

    2012-04-11

  • Last modified date

    2012-08-13

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK77857 UK77858

Modules/Macros

  • DSNXEDP  DSNXONZC
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK77857

       UP12/04/26 P F204

  • R910 PSY UK77858

       UP12/04/26 P F204

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
13 August 2012