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

[CRPence <CRPbottle@xxxxxxxxx>]

  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?