How to do a commit after each insert inside an SPL ?

[harborboy76]

Hi,
I had posted one topic earlier, but somehow the link
has been broken and I cannot see the topic. So sorry if
this is a duplicate topic for some. Here is the issue
that I'm running into.
Basically, I have this SPL that does a lot of insert
and update on many different tables. And I also have one
error table that would trap all the information like
SQLCODE, table name, column name, etc.. when an exception
arises. Everything runs fine when there are no exceptions.
But from time to time, when there are rows that cannot be
update/insert into a table, this error table would be
populated with many rows (sometimes over 20,000 rows).
And when this error table is being inserted, the table is
locked. And we get deadlock error when a SQL statement
is issued against the error table.
Is there a way to write a COMMIT statement so that
each time it inserts into the error table, it commits.
I tried adding a commit right after the insert statement,
but I was getting an error saying that there are no
open cursors. Is there a workaround this ?

Here is an example :

------------

CREATE PROCEDURE db2.test ...

BEGIN
DECLARE..
DECLARE..
DECLARE..

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
INSERT INTO db2.ERROR (..)
VALUES (..);
--COMMITS; --want to do a commit here
END;

SET ..

[Serge Rielau]

Now that makes sense.
It's not your COMMIT that's failing.
The COMMIT closes the cursor.
DECLARE the cursor WITH HOLD and you'll be in business.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3a*************@individual.net...

Now that makes sense.
It's not your COMMIT that's failing.
The COMMIT closes the cursor.
DECLARE the cursor WITH HOLD and you'll be in business.

Cheers
Serge

Make sure you explicitly close the cursor if you use the WITH HOLD option
when you are finished using the cursor.