PM76503: ABEND04E RC000E70005 AT DSNXOEXB M200 WHEN PREPARING A SQL STATEMENT FROM A COMPLEX VIEW WITH UNION ALL

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • ABEND04E RC000E70005 AT DSNXOEXB M200 WHEN PREPARING A SQL
    STATEMENT FROM A COMPLEX VIEW WITH UNION ALL
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 9 and 10 for z/OS users of a view    *
    *                 that contains outer joins and a              *
    *                 table expression with UNION ALL and side-way *
    *                 references.                                  *
    ****************************************************************
    * PROBLEM DESCRIPTION: ABEND04E RC00E70005 at the location     *
    *                      DSNXGRDS DSNXOEXB M200 or ABEND04E      *
    *                      RC00E72018 at the location DSNXGRDS     *
    *                      DSNXSMRE OFFSET4EAE(V9 only) could      *
    *                      happen for an SQL statement referencing *
    *                      a view that satisfies all of the        *
    *                      following conditions:                   *
    *                                                              *
    *                      1. It contains outer joins.             *
    *                      2. It contains a table expression.      *
    *                      3. The table expression contains UNION  *
    *                         ALL and side-way references.         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    ABEND04E RC00E70005 at the location DSNXGRDS DSNXOEXB M200 or
    ABEND04E RC00E72018 at the location DSNXGRDS DSNXSMRE
    OFFSET4EAE(V9 only) could happen for an SQL statement
    referencing a view that satisfies all of the following
    conditions:
    
    1. It contains outer joins.
    2. It contains a table expression.
    3. The table expression contains UNION ALL and side-way
       references.
    
    An examples is shown below:
    
    CREATE TABLE T1 (
       C1 INT
    );
    CREATE TABLE T2 (
       C1 INT
    );
    CREATE TABLE T21 (
       C1 INT
    );
    CREATE TABLE T22 (
       C1 INT
    );
    
    CREATE VIEW V1 AS
    SELECT T1.C1
    FROM T1,
         TABLE(SELECT C1
                 FROM T21
                 WHERE T21.C1 = T1.C1
               UNION ALL
               SELECT C1
                 FROM T22
                 WHERE T22.C1 = T1.C1
               ) TX
         LEFT OUTER JOIN T2
           ON T2.C1 = TX.C1
    ;
    
    SELECT * FROM V1;
    
    The SELECT statement above will cause the ABEND.
    
    DB2 did not correctly process the aforementioned SQL statements,
    which caused the ABEND.
    

Problem conclusion

  • DB2 has been modified to correctly process the aforementioned
    SQL statement, so there will be no ABEND.
    
    Additional keywords: SQLVIEW SQLTABLEEXPR SQLUNIONALL SQLSIDEWAY
                         SQLSIDEWAYREF SQLOUTERJOIN SQLOUTER SQLJOIN
                         SQLFULL SQLFULLJOIN
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM76503

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-11-05

  • Closed date

    2013-01-09

  • Last modified date

    2013-02-04

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

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

    UK90816 UK90817

Modules/Macros

  •    DSNXOCSC DSNXODSO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK90816

       UP13/01/24 P F301

  • R910 PSY UK90817

       UP13/01/24 P F301

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM76503

Modified date:

2013-02-04

Translate my page

Machine Translation

Content navigation