> Hi,
>
> With the MERGE statement I am using in DB2 it rolls back all the way if
> an error is encountered. Is there a way to save each of the encountered
> exception but proceed to the next data record rather than a complete
> rollback? Any help regarding this would be appreciated.
Teh short answer is no.
SQL Statements by definition are ATOMIC.
DB2 zOS has the concept of non atomic "array insert" which may be
extended to non atomic "array merge" is some future and perhaps find
it's way into DB2 for LUW.
Until this day the only way to do it is to loop over each individual row
either in a procedure or in a the APP and perform single-row MERGE
Something like:
CREATE PROCEDURE ...
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE .... BEGIN END;
FOR source AS SELECT pk, c1 FROM S
DO
MERGE INTO T
USING (VALUES (source.pk, source.c1)) AS S(pk, c1)
ON S.pk = T.pk
WHEN MATCHED THEN UPDATE SET c1 = S.c1
WHEN NOT MATCHED THEN INSERT VALUES (pk, c1);
END FOR;
END
Cheers
Serge

Signature
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Mamidinx - 15 Dec 2005 13:51 GMT
Thanks Serge.
Another question. My 'select' in the MERGE returns approximately 150K
to 200K records. So, if I do a single MERGE for each of these 200K
records will it be slower than doing a MERGE for all the 200K records
at one time? Also, when I ran the Select statement by itself I got all
the records but when I used it in the MERGE I got SQL0100W error. But I
don't understand why I am getting this error. When I looked at the
error description it said check for StmtHeap and it is 2048 on my DB.
Is this too small? If so, what should it be for such loads and do I
need to consider anymore configuration variables? We have DB2 on zLinux
and I am also using Federated objects. Appreciate some answers.