How about the capability to do an IGNORE? I have a web service that I retrieve records from, some of the records are duplicates due to how a GPS generates records. I use a MERGE statement to INSERT if the record doesn't already exist and to IGNORE it if it is already in the database. Obviously there are other ways to handle this but the MERGE seemed cleaner to me.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, February 03, 2014 2:14 PM
To: Midrange Systems Technical Discussion
Subject: SQL Merge vs. CPYF MBROPT(*UPDADD)

I'm glad that DB2 for i finally got the SQL MERGER statement...

And I see that it offers lot's of functionality...

However, for a basic merge ie.
MERGE INTO archive ar
USING (SELECT activity, description FROM activities) ac ON (ar.activity = ac.activity) WHEN MATCHED THEN UPDATE SET description = ac.description WHEN NOT MATCHED THEN INSERT (activity, description) VALUES(ac.activity, ac.description)

I don't really see any benefits over CPYF MBROPT(*UPDADD) FMT(*MAPDROP).
Other than perhaps the ability to use commitment control with merge.

Am I missing anything?

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page