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

Obtain the fix for this APAR.

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 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.