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

[CRPence <CRPbottle@xxxxxxxxx>]

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.