IBM Support

PH34225: SQLCODE -4742 RSN11 WITH DB2 QUERY ACCELERATION = ALL WHEN RUNNING QUERIES THAT USE A CTE AND CERTAIN 21/03/05 PTF PECHANGE

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • SQLCODE -4742 REASON CODE 11 occurred with Db2 QUERY
    ACCELERATION = ALL when running a query having a common table
    expression (CTE) that only references tables with ANY of the
    following table SCHEMAS:
       SYSIBM , SYSACCEL , SYSXSR , and DB2GSE ,
    and the table(s) is NOT accelerated to an IBM Db2 Analytics
    Accelerator.
    
    The query is not accelerated to the Accelerator but should be
    run only in Db2 for z/OS even under QUERY ACCELERATION = ALL
    where, because of these special SCHEMAS, these specific queries
    are considered special exceptions to the ALL behavior and
    should not fail with SQLCODE4742 RSN11.
    
    Additional search keywords:
    SQLCODE4742 RC11 SQLCTE SQLTABLEEXPR
    IDAAV5R1/K IDAAV7R1/K IDAAV7R5/K
    

Local fix

  • If possible, run the query with CURRENT QUERY ACCELERATION =
    ELIGIBLE
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Db2 12 users of the IBM Db2 Analytics                    *
    * Accelerator using                                            *
    * CURRENT QUERY ACCELERATION = ALL with                        *
    * dynamic queries only referencing tables                      *
    * with the SYSIBM, SYSACCEL, SYSXSR, or                        *
    * DB2GSES SCHEMA, and doing so within a                        *
    * common table expression.                                     *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * With Db2 12 PTF UI71472 applied and                          *
    * CURRENT QUERY ACCELERATION = ALL,                            *
    * SQLCODE -4742 RSN11 or RSN12 issued                          *
    * for a dynamic query that:                                    *
    * - Only references tables with these                          *
    *   SCHEMAs:                                                   *
    *     SYSIBM                                                   *
    *     SYSCCEL                                                  *
    *     SYSXSR                                                   *
    *     DB2GSES                                                  *
    * - Includes at least one such reference                       *
    *   within a common table expression                           *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply corrective PTF when available                          *
    ****************************************************************
    Normally when CURRENT QUERY ACCELERATION = ALL, all queries are
    to be run on an accelerator if able, and when unable,
    SQLCODE -4742 is issued.
    An exception exists such that if a dynamic query contains only
    references to tables of the noted SCHEMAs, the query will still
    run successfully with CQA = ALL, even if the tables are not
    accelerated.
    Upon application of UI71472, this exception no longer happened
    if a query only referenced tables with the noted SCHEMAs, but
    contained at least one such reference within a common table
    expression.
    As a result, SQLCODE -4742 RSN11 or RSN12 was issued.
    

Problem conclusion

  • Db2 code has been corrected to again recognize the exception
    case with common table expression indicated above.
    Additional Keywords:
    IDAAV5R1/K
    IDAAV7R1/K
    SQLCODE4742
    SQLSPECIALREG
    SQLCTE
    

Temporary fix

Comments

APAR Information

  • APAR number

    PH34225

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    YesPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2021-02-05

  • Closed date

    2021-03-29

  • Last modified date

    2021-05-03

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

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

    UI74652

Modules/Macros

  • DSNXONZO DSNXOBZO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RC10 PSY UI74652

       UP21/04/06 P F104 {

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.

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0"}]

Document Information

Modified date:
04 May 2021