MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2014

Re: SQL Merge vs. CPYF MBROPT(*UPDADD)



fixed

On 03-Feb-2014 13:14 -0800, Charles Wilt wrote:
I'm glad that DB2 for i finally got the SQL MERGE statement...

And I see that it offers lots of functionality...

However, for a basic merge i.e.
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?

The Copy File (CPYF) utility has a requirement for a Unique key [constraint] on the target file. IIRC the MERGE does not have any key\index requirements. FWiW: The ability to participate under isolation is a common cause for needing to use SQL instead of the copy commands.






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

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact