IBM Support

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

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

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 number

    PH12139

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-05-17

  • Closed date

    2019-06-11

  • Last modified date

    2019-06-11

  • 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



Document information

More support for: DB2 for z/OS

Software version: 12.0

Reference #: PH12139

Modified date: 11 June 2019