PH12139: ABEND04E RC00E70005 DSNXGRDS.DSNXRBND:M131 OR INCORROUT WITH VIEW, TABLE EXPRESSION, OR CTE CONTAINING A CTE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00E70005 DSNXGRDS.DSNXRBND:M131 or incorrout with
  view, table expression, or CTE that contains a CTE.
  It may occur when a query references CTE within which there is a
  table expression or a view in a join query block or when a query
  references a table expression, view, or CTE within which there
  embeds CTEs in a join query block.
  .
  Below list of external conditions:
  (a) When there is a view / table expression in a CTE query
  block, or there is a CTE embedded underneath the view / table
  expression / CTE
  (b) The PLAN_TABLE.ACCESSTYPE is 'O'
  (c) There is join predicate(s) between the view / table
  expression / CTE and the outer tables.
  If all conditions are fullfilled an abend may occur.
  Incorrect output could consist of unexpected null values in
  one or more columns.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 users of 12 for z/OS users using                     *
  * a view, table expression, or common table                    *
  * expression (CTE) within which there are                      *
  * CTEs.                                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Sometimes ABEND04E RC00E70005 DSNXRBND                       *
  * M131 or incorrect output with CTE can                        *
  * happen when the following conditions                         *
  * are met.                                                     *
  *                                                              *
  * (1) A view, table expression, or CTE                         *
  * exists                                                       *
  * (2) On the view, table expression, or                        *
  * CTE in (1), the PLAN_TABLE.ACCESSTYPE                        *
  * has the value ?O?                                            *
  * (3) There is a CTE embedded                                  *
  * underneath the view, table                                   *
  * expression, or CTE in (1)                                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Sometimes ABEND04E RC00E70005 DSNXRBND M131 or incorrect output
  can happen when the following conditions are met.
  
  (1) A view, table expression, or CTE exists;
  (2) On the view, table expression, or CTE in (1), the
      PLAN_TABLE.ACCESSTYPE has the value ?O?; and
  (3) There is a CTE embedded underneath the view, table
      expression, or CTE in (1).
  
  For example:
  WITH   SYSCTE AS (
    SELECT A."NAME"
         , A.TBNAME
         , CASE WHEN A.KEYSEQ = 0 THEN 'N' ELSE 'Y' END AS KEYSEQ
      FROM "SYSIBM".SYSCOLUMNS A
      LEFT JOIN "SYSIBM".SYSTABLES B
        ON B.NAME = A.TBNAME
     WHERE B.NAME IN ('AAA','BBB','CCC')
    ),
         SYSB AS (
    SELECT CHAR(B.NAME, 24) AS COLNAME
         , B.COLNO          AS COLNO
         , A.KEYSEQ         AS IS_KEY
         , B.TBNAME         AS SOURCE_TABLE
      FROM "SYSIBM".SYSCOLUMNS B
      LEFT JOIN SYSCTE A
       AND B.TBNAME    = A.TBNAME
       AND B."NAME"    = A."NAME"
     WHERE B.TBNAME     IN ('AAA','BBB','CCC')
    ),
         TABLEA (NAME2, CREATOR2) AS (
    SELECT 'SYSTABLES', 'SYSIBM'
    FROM SYSIBM.SYSDUMMY1
    )
    SELECT A.NAME2, A.CREATOR2,
           B.COLNAME, B.COLNO, B.IS_KEY
      FROM SYSB B
     INNER JOIN TABLEA A
        ON A.NAME2     = B.SOURCE_TABLE;
  
  CTE SYSB contains a reference to CTE SYSCTE. If 'O'
  accesstype is chosen for SYSB, then this problem can occur.
  
  The abend or incorrect output can happen because Db2 lacks
  support to process the view, table expression, or CTE when
  the PLAN_TABLE.ACCESSTYPE has the value ?O?.
  
  The incorrect output consists of unexpected null values
  in one or more columns.
  
  Additional Keywords:
  SQLTABLEEXPR SQLVIEW SQLCTE SQLINCORR SQLINCORROUT
  INCORROUT DB2INCORR/K
  

Problem conclusion

• PH12139 fixes the problem described above by not allowing the
  PLAN_TABLE.ACCESSTYPE to have the value ?O? on the view, table
  expression, or CTE when there is a CTE underneath.
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UI63558

Modules/Macros

• DSNXOPTH DSNXOGP  DSNXOCS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI63558

     UP19/06/26 P F906 ¢

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.