PH16650: DB2 ABND=04E-00E70005 IN DSNXOTS :M099 IN OUTER JOINS IN A RECURSIVE CTE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DB2 ABND=04E-00E70005 in DSNXOTS :M099
  The problem may happen when there is outer joins in a recursive
  CTE
  
  Keyword: SQLCTE
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of an SQL                          *
  * statement containing a recursive common                      *
  * table expression(CTE) with an outer join.                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * ABEND04E RC00E70005 at the location                          *
  * DSNXGRDS DSNXOTS M099 could happen for                       *
  * an SQL statement containing a                                *
  * recursive common table expression(CTE)                       *
  * with an outer join.                                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1(
    C1 INT
   ,C2 VARCHAR(10)
  )
  ;
  CREATE TABLE T2(
    C1 INT
   ,C2 VARCHAR(10)
  )
  ;
  
  WITH CTE1(C1) AS (
  SELECT 1 FROM SYSIBM.SYSDUMMY1
  UNION ALL
  SELECT CTE1.C1+1
  FROM CTE1,
       T1 LEFT JOIN T2 ON T1.C1=T2.C1
  WHERE CTE1.C1=T2.C1 OR T2.C1 IS NULL
        AND CTE1.C1+1 < 10
  )
  
  SELECT * FROM CTE1
  ;
  
  Db2 did not correctly process the SQL statement containing a
  recursive common table expression with an outer join, and it
  resulted in the ABEND.
  

Problem conclusion

• Db2 has been modified and the aforementioned SQL statement will
  run successfully.
  
  Additional keywords: SQLLEFT SQLLEFTJOIN SQLRIGHT SQLRIGHTJOIN
                       SQLJOIN SQLOUTER SQLOUTERJOIN SQLCTE
                       SQLTABLEEXPR
  

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:

  UI65750

Modules/Macros

• DSNXOMQB
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI65750

     UP19/10/22 P F910

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.