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

Obtain the fix for this APAR.

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