IBM Support

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

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

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 number

    IT27712

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-01-10

  • Closed date

    2019-03-07

  • Last modified date

    2019-03-08

  • 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

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 March 2019