PH66158: ABEND04E RC00E70005 IN DSNXOVM AT M305 WHEN SELECT FROM MERGE STATEMENT CONTAINS INCLUDE COLUMN.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABND=04E-00E70005,U=EH2MU6V ,M=N ,C=131.RDS
  -RSCTCACH,M=DSNTFRCV,LOC=DSNXGRDS.DSNXOVM :M305 occurred when
  SELECT FROM MERGE statement contains include column, the MERGE
  target table is referenced in the SOURCE TABLE of MERGE
  statement.
  
  KEYWORDS: ABEND04E SQLSELWMERGE SQLINCLUDE
  

Local fix

• BYPASS/CIRCUMVENTION:
  N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 12 and Db2 13 for z/OS users of      *
  *                 SELECT from DATA CHANGE statement.           *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABEND04E RC00E70005 at location         *
  *                      DSNXGRDS DSNXOVM M305,                  *
  *                      ABEND04E RC00C90101 at location         *
  *                      DSNIDM DSNIWKFL 5065, unexpected        *
  *                      SQLCODE -117 or incorrect output        *
  *                      could occur when an SQL statement       *
  *                      satisfies the following conditions:     *
  *                      1. The SQL statement is a SELECT from   *
  *                      INSERT or SELECT from MERGE statement.  *
  *                      2. The INSERT statement or MERGE        *
  *                      statement mentioned in above contains   *
  *                      INCLUDE columns.                        *
  *                      3. A table or view referenced as the    *
  *                      source of the INSERT statement or in    *
  *                      the MERGE USING clause is the same as   *
  *                      the INSERT or MERGE target table or     *
  *                      view, respectively.                     *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available          *
  ****************************************************************
  The following example could help understand the problem
  scenario:
  CREATE TABLE MYTAB.TEST1
        (C1           CHAR(3) NOT NULL,
         C2           CHAR(3) NOT NULL,
         C3           CHAR(3) NOT NULL,
         I1           INT NOT NULL,
         I2           INT NOT NULL);
  
  CREATE VIEW MYTAB.VIEW1 AS SELECT * FROM MYTAB.TEST1;
  
  SELECT * FROM FINAL TABLE
       (MERGE INTO MYTAB.VIEW1 ALT INCLUDE (AKTION CHAR(06))
         USING (SELECT 'AAA',C2,C3,I1,I2 FROM MYTAB.VIEW1
           WHERE C1 = 'AAA'AND I1 = 20) AS NEU(C1,C2,C3,I1,I2)
           ON ( ALT.C1 = NEU.C1 AND ALT.I1 = NEU.I1 )
   WHEN MATCHED THEN UPDATE SET ALT.C3 = NEU.C3
   WHEN NOT MATCHED
   THEN INSERT VALUES (NEU.C1,NEU.C2,NEU.C3,NEU.I1,NEU.I2));
  
  View VIEW1 is the MERGE target, it is also referenced in the
  MERGE USING clause. Abend occurs when Db2 binding the above
  SELECT FROM MERGE statement, while SQLCODE -117 is expected
  for this statement.
  
  SELECT * FROM FINAL TABLE
       (MERGE INTO MYTAB.TEST1 ALT  INCLUDE (AKTION CHAR(06))
         USING (SELECT 'AAA',C2,C3,I1,I2 FROM MYTAB.TEST1
            WHERE C1 = 'AAA'AND I1 = 20) AS NEU(C1,C2,C3,I1,I2)
           ON ( ALT.C1 = NEU.C1 AND ALT.I1 = NEU.I1 )
   WHEN MATCHED THEN UPDATE SET ALT.C3 = NEU.C3
   WHEN NOT MATCHED
   THEN INSERT VALUES (NEU.C1,NEU.C2,NEU.C3,NEU.I1,NEU.I2));
  
  Table TEST1 is the MERGE target table, it is also referenced in
  the MERGE USING clause. Incorrect output occurs when Db2
  binding above SELECT FROM MERGE statement, while SQLCODE -117
  is expected for this statement.
  

Problem conclusion

• Db2 is updated to handle the aforementioned SQL statement
  correctly.
  Additional Keywords: SQLSELWINSERT SQLSELWMERGE SQLINCLUDE
                       SQLINCORR INCORROUT SQLINCORROUT
                       DB2INCORR/K
  

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:

  UO03801 UO03802

Modules/Macros

• DSNXOTDA DSNXOVC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UO03801

     UP25/07/02 P F507 ¢

• RD10 PSY UO03802

     UP25/07/02 P F507 ¢

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.