Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / December 2005

Tip: Looking for answers? Try searching our database.

Error logging with Merge statemet

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mamidinx - 14 Dec 2005 20:01 GMT
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.

Thanks in advance,
mamidinx
Serge Rielau - 14 Dec 2005 22:37 GMT
> 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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.