PK88575: SQLCODE401 INCORRECTLY ISSUED ON QUERY WITH MERGE AND VIEW

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE401 was received when running a query which contains
  Merge statement on view.
  
  An example of the failing query is like:
   SELECT COUNT(*) FROM FINAL TABLE
   (MERGE INTO IBMAPAR1.ACT2       $T
      INCLUDE (ROW_COUNT INTEGER)
   USING (VALUES(2,'A') FOR 1 ROWS )
   AS $S(ACTNO,ACTKWD)
   ON $S.ACTNO=$T.ACTNO
   WHEN MATCHED
   THEN UPDATE SET
        ROW_COUNT =  1
      , $T.ACTKWD = $S.ACTKWD
   WHEN NOT MATCHED THEN INSERT(ACTNO, ACTKWD)
   VALUES ($S.ACTNO,$S.ACTKWD)
   NOT ATOMIC CONTINUE ON SQLEXCEPTION )
   WHERE ROW_COUNT  =     1;
  DB2INCORR/K
  

Local fix

• To work around -401, referring the base table (instead
  of the view) inside the Merge statement.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 R910 DB2 users of SELECT FROM        *
  *                 MERGE, SELECT FROM INSERT and SELECT         *
  *                 FROM UPDATE with include columns.            *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE401 is issued incorrectly        *
  *                      or incorrect result may occur           *
  *                      for a SELECT FROM MERGE,                *
  *                      SELECT FROM INSERT or                   *
  *                      SELECT FROM UPDATE SQL statement        *
  *                      that satisfies the following            *
  *                      conditions:                             *
  *                      1. The target table of the MERGE,       *
  *                      INSERT or UPDATE is a view.             *
  *                      2. The view is created with a subset    *
  *                      of columns from a base table.           *
  *                      3. The SELECT statement includes an     *
  *                      INCLUDE column                          *
  *                      4. The include column is used in        *
  *                      a predicate.                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  SQLCODE401 is issued incorrectly or incorrect result
  may occur for a SELECT FROM MERGE, SELECT FROM INSERT
  or SELECT FROM UPDATE SQL statement that satisfies
  the following conditions:
  1. The target table of the MERGE, INSERT or UPDATE is a view.
  2. The view is created with a subset of columns from a base
  table.
  3. The SELECT statement includes an INCLUDE column
  4. The include column is used in a predicate.
  
  For Example,
  
  CREATE TABLE T1(C1 INT,C2 CHAR(1));
  INSERT INTO T1 VALUES (2,'A');
  
  CREATE VIEW V1 AS SELECT C1 FROM T1;
  
  SELECT * FROM FINAL TABLE
    (MERGE INTO V1
       INCLUDE (ROW_COUNT INTEGER)
       USING (VALUES(2,'A') FOR 1 ROWS)
       AS $S(ACTNO,ACTKWD)
       ON $S.ACTNO=C1
     WHEN MATCHED THEN
          UPDATE SET C1 = 1, ROW_COUNT = 1
     NOT ATOMIC CONTINUE ON SQLEXCEPTION)
  WHERE ROW_COUNT = 1;
  
  SQLCODE401 is issued incorrectly for the SELECT statement
  above because the INCLUDE column is not processed correctly.
  
  
  CREATE TABLE T2(C1 INT,C2 BIGINT);
  INSERT INTO T2 VALUES (2,20);
  
  CREATE VIEW V2 AS SELECT C1 FROM T2;
  
  SELECT * FROM FINAL TABLE
    (MERGE INTO V2
       INCLUDE (ROW_COUNT INT)
       USING (VALUES(2,'A') FOR 1 ROWS)
       AS $S(ACTNO,ACTKWD)
       ON $S.ACTNO=C1
     WHEN MATCHED THEN
          UPDATE SET C1 = 1, ROW_COUNT = 1
     NOT ATOMIC CONTINUE ON SQLEXCEPTION)
  WHERE ROW_COUNT = 1;
  
  No row is returned for the SELECT statement above because the
  the INCLUDE column is not processed correctly for the predicate
  of the SELECT part of the SELECT FROM MERGE statement. However,
  the MERGE part of the SELECT FROM MERGE statement has completed
  successfully.
  

Problem conclusion

• DB2 was fixed to processed the INCLUDE column correctly.
  Additional Keywords: SQLVIEW, SQLINCORROUT SQLINCORR
                       SQLINSWSELECT SQLSELWINSERT
                       SQLSELWUPDATE SQLSELWMERGE
                       SQLINCLUDE INCORROUT
  

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:

  UK49990

Modules/Macros

• DSNXOVC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK49990

     UP09/09/26 P F909

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.