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

The pseudo-coded request CPYF MBROPT(*ADDNEW) is already implemented; albeit potentially troublesome for some requirements. The effect is available with the request to CPYF MBROPT(*ADD) ERRLVL(*NOMAX) whereby [¿the first 20-or-so successive?] messages CPF5026 are logged, but those duplicates have no effect otherwise. Obviously what are the _other_ errors being overlooked per that specification to ignore errors, is the source of potential difficulties with requirements. Having an option for CPYF that is specific to just adding only not-already-existing unique keys, would be more beneficial than depending on ignoring all errors [or a certain number of errors, and then re-invoking from where the prior copy was interrupted due to exceeding maximum error level; e.g. restart with FROMRCD or FROMKEY].

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/dm/rbal3limrec.htm>
IBM i 7.1 Information Center -> Files and file systems -> Database file management -> Performance -> Preventing errors when copying files
_Limitation of recoverable errors during copy_

But the SQL already had that more straightforward capability well-covered in a variety of means to perform INSERT requests, long before there was a MERGE statement; i.e. just as alluded with "other ways to handle".

Regards, Chuck

On 03-Feb-2014 14:33 -0800, Charles Wilt wrote:

Sounds like you wanted an *ADDNEW vs. *UPDADD....so sure merger might
make sense. I'll readily agree that MERGE is much more powerful than
CPYF *UPDADD. <<SNIP>>

On Mon, Feb 3, 2014 at 5:14 PM, Scott Mildenberger wrote:

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.

Charles Wilt on Monday, February 03, 2014 2:14 PM wrote:

<<SNIP>>

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?