DB2 MERGE

DB2 MERGE 

            Merge statement is used to perform insert and update operations or both. The data is specified by select clause on the source table. When Matched or Not Matched is done on the ON clause of the statement. There can be multiple When Matched / When Not Matched clauses each clause specifying with their own filters. If error occurs in execution, the whole statement is rolled back.

          Note that merge commits only after entire statement is executed and it may consume huge log space in case of large tables.

          There was a situation in our DB where we tried to merge data from one table in DB to another table in DB2. Both the tables where exact same in context of their DDL and both the tables possessed some data (Target table also contained same data as of Source, but target table was not latest). The merge failed as the one of the column (col1) have unique index constraint in both tables. In source table that col got updated and when we tried to update using merge, it failed as new the value for the  col1 in one of the rows that merge tried to inserted was already existing and it thrown a error stating violation of unique index constraint. We had to drop unique index and continue with merge.

         Merge cannot read its own uncommitted data. In case of any error in execution the entire statement is rolled back.

Ex:

  MERGE INTO archive ar

  USING (SELECT activity, description, date, last_modified

         FROM activities_groupA) ac

  ON (ar.activity = ac.activity) AND ar.group = 'A'

  WHEN MATCHED AND ac.date IS NULL THEN

     SIGNAL SQLSTATE '70001'

        SET MESSAGE_TEXT =

           ac.activity CONCAT ' cannot be modified. Reason: Date is not known'

  WHEN MATCHED AND ac.date < CURRENT DATE THEN

     DELETE

  WHEN MATCHED AND ar.last_modified < ac.last_modified THEN

     UPDATE SET

        (description, date, last_modified) = (ac.description, ac.date, DEFAULT)

  WHEN NOT MATCHED AND ac.date IS NULL THEN

     SIGNAL SQLSTATE '70002'

        SET MESSAGE_TEXT =

           ac.activity CONCAT ' cannot be inserted. Reason: Date is not known'

  WHEN NOT MATCHED AND ac.date >= CURRENT DATE THEN

     INSERT

        (group, activity, description, date)

        VALUES ('A', ac.activity, ac.description, ac.date)

  ELSE IGNORE

Click here to access detail merge statement syntax

Please feel free to share your experiences with DB2 MERGE!!