IT27712: MERGE STATEMENT WITH DISTINCT CAN INCORRECTLY UPDATE MULTIPLE TARGET ROWS

Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Data corruption is possible with MERGE statement using DISTINCT.
  Statement should have returned SQL0788N.
  
  Example:
  
  create table S (C1 smallint, C2 varchar(10) );
  create table T (C1 smallint, C2 varchar(10) );
  
  insert into S values (1, 'A');
  insert into S values (2, 'B');
  insert into S values (3, 'C');
  insert into S values (3, 'C2');
  insert into S values (4, 'D');
  
  insert into T values (1, 'A');
  insert into T values (2, 'B');
  insert into T values (3, 'C');
  insert into T values (4, 'D');
  
  db2 => merge into T using (select distinct c1, c2 from S) S
  on T.C1 = S.C1
  when matched then update set T.C2 = T.C2 || '-' || S.C2
  DB20000I  The SQL command completed successfully.
  
  Both target rows with C1=3 are updated.
  The above statement should get error -788.
  
  db2 => select * from T;
  
  C1     C2
  ------ ----------
       1 A-A
       2 B-B
       3 C-C2
       4 D-D
  
    4 record(s) selected.
  

Local fix

• If the DISTINCT clause is not strictly necessary, remove it.
  Otherwise, ensure that each column in the source table-reference
  is also included in the MERGE search condition, e.g. for the
  example above, add predicate T.C2=S.C2:
  
  merge into T using (select distinct c1, c2 from S) S
  on T.C1 = S.C1 and T.C2 = S.C2
  when matched then update set T.C2 = T.C2 || '-' || S.C2;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1 Mod 4 Fixpack 4 iFix 001 or higher       *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 11.1 Mod 4 Fixpack 4 iFix 001
  

Temporary fix

Comments

APAR Information

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

  IT27705

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

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP