DB2 FOR LUW / DB2FORLUW / 970 - DB2

MERGE statement may insert incorrect default value into a view 
when all of the following conditions are satisfied: 
 
1. The target of the MERGE statement is a view. 
 
2. An INSTEAD OF INSERT TRIGGER is defined on the view. 
 
3. The MERGE statement has an INSERT operation that specifies 
only a subset of the columns of the view and those missing 
columns are to be substituted with the default value of the 
columns. 
 
4. The WHEN clause containing the INSERT operation is specified 
after a WHEN clause containing an UPDATE or DELETE operation in 
the MERGE statement. 
 
 
Sample scenario : 
 
  $ db2 "create table T1 ( C1 char(1) with default '0', C2 
char(1) with default '0' )" 
  $ db2 "create table T2 ( C1 char(1) with default '0', C2 
char(1) with default '0' )" 
  $ db2 "create view V1 ( T1C1, T2C2 ) as select T1.C1, T2.C2 
from T1, T2 where T1.C1=T2.C1" 
  $ db2 "create trigger TRI instead of insert on V1 
        referencing new as NEW for each row 
        begin atomic 
            insert into T1 ( C1, C2 ) values ( NEW.T1C1, 
NEW.T2C2 ); 
            insert into T2 ( C1, C2 ) values ( NEW.T1C1, 
NEW.T2C2 ); 
        end" 
  $ db2 "create trigger TRU instead of update on V1 
        referencing new as NEW for each row 
            begin atomic 
            end" 
  $ db2 "create trigger TRD instead of delete on V1 
        referencing old as OLD for each row 
            begin atomic 
            end" 
  $ db2 "merge into V1 using table ( values ( '9' ) ) as DMY ( 
T1C1 ) on ( V1.T1C1 = DMY.T1C1 ) 
        when matched     then update set T1C1 = '4' 
        when not matched then insert ( T1C1 ) values ( '3' )" 
 
  > DB20000I  The SQL command completed successfully. 
 
  $ db2 "select * from V1" 
 
  > T1C1 T2C2 
    ---- ---- 
    3 
 
      1 record(s) selected. 
 
  ==> Its result should be "3    -"

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See APAR Description text                                    * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 1.                       * 
****************************************************************

Avoid using default values in the INSERT operations of the MERGE 
statement. 
 
  $ db2 "merge into V1 using table ( values ( '9' ) ) as DMY ( 
T1C1 ) on ( V1.T1C1 = DMY.T1C1 ) 
        when matched     then update set T1C1 = '4' 
        when not matched then insert ( T1C1, T2C2 ) values ( 
'5', NULL )" 
 
  > DB20000I  The SQL command completed successfully. 
 
  $ db2 "select * from V1" 
 
  > T1C1 T2C2 
    ---- ---- 
    3 
    5    - 
 
      2 record(s) selected.

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 Version 9.7 Fix Pack 1.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

21.10.2009
16.02.2010
16.02.2010

9.7.FP1