LARGE INSERT in STORED PROCEDURE

[harborboy76]

Hi,
I am trying to insert a large number of rows into a table
inside a SPL. But every time, I run the SPL, the table is
locked because of the INSERT. When I tried to issue a COMMIT,
right after the INSERT in the SPL. I got an error stating that
there was no open cursor.
I have done some research on here and found that it's not
possible to issue a COMMIT inside an SPL. But it can be done
so on the client side. Can anyone shade me some light on this ?
I'm quite new here and not sure how to do that. What are other
options to get around this if I have to commit after each
insert so that I won't get a deadlock on the table ?

DB2 v 8.2
Linux SUSE SLES 8

Thank you!!!

[Serge Rielau]

ha*********@yahoo.com wrote:

Hi,
I am trying to insert a large number of rows into a table
inside a SPL. But every time, I run the SPL, the table is
locked because of the INSERT. When I tried to issue a COMMIT,
right after the INSERT in the SPL. I got an error stating that
there was no open cursor.
I have done some research on here and found that it's not
possible to issue a COMMIT inside an SPL. But it can be done
so on the client side. Can anyone shade me some light on this ?
I'm quite new here and not sure how to do that. What are other
options to get around this if I have to commit after each
insert so that I won't get a deadlock on the table ?

DB2 v 8.2
Linux SUSE SLES 8

Thank you!!!

COMMIT is only disallowed if the procedur eis called from a function or
a trigger. Can you please explain more about the context under which teh
proc is called.
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[harborboy76]

Hi Serge,
Thank you for your reply. Basically, my SPL is doing an
insert/update into multiple tables, and when an error arise,
I'm trapping (inserting) all the error information like SQLCODE,
Table Name, SPL name, etc into ERROR table. When there are a lot
of errors inserting into the error table, the table is locked.
And no one can issue a SELECT or DELETE statement on the table.
I tried to issue a COMMIT; right after the INSERT statement hoping
that it will commit each row it inserts, so that other people can
do a simple select statement or delete, but that does not seem to
work. The original code is very long and complicated, but the below
is a basic outline of what's going on. What am I missing ? :)

Thank you

Here is an example :

-------------
CREATE PROCEDURE ..

BEGIN

DECLARE ..
DECLARE ..

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
INSERT INTO db2.errors (...)
VALUES ( .. );
END;

UPDATE..
INSERT..

[Serge Rielau]

Well, that still doesn't explain why yoru commit fails....
Anyway to log errors I would use a different approach.
DB2 supporrts DECLARE GLOBAL TEMPORARY TABLE (DGTT).
DGTT can be defined as ON COMMIT PRESERVE ROWS and (in V8.2) ON ROLLBACK
PRESERVE ROWS. This makes them great candidates for logging of errors
since they are not transactional.
When your procedure is done you can then move th4 data to a regular
table in one fast insert from subselect operation.

Cheers
Serge

ha*********@yahoo.com wrote:

Hi Serge,
Thank you for your reply. Basically, my SPL is doing an
insert/update into multiple tables, and when an error arise,
I'm trapping (inserting) all the error information like SQLCODE,
Table Name, SPL name, etc into ERROR table. When there are a lot
of errors inserting into the error table, the table is locked.
And no one can issue a SELECT or DELETE statement on the table.
I tried to issue a COMMIT; right after the INSERT statement hoping
that it will commit each row it inserts, so that other people can
do a simple select statement or delete, but that does not seem to
work. The original code is very long and complicated, but the below
is a basic outline of what's going on. What am I missing ? :)

Thank you

Here is an example :

-------------
CREATE PROCEDURE ..

BEGIN

DECLARE ..
DECLARE ..

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
INSERT INTO db2.errors (...)
VALUES ( .. );
END;

UPDATE..
INSERT..
.
.
.

END
-------------


Serge Rielau wrote:

ha*********@yahoo.com wrote:

Hi,
I am trying to insert a large number of rows into a table
inside a SPL. But every time, I run the SPL, the table is
locked because of the INSERT. When I tried to issue a COMMIT,
right after the INSERT in the SPL. I got an error stating that
there was no open cursor.
I have done some research on here and found that it's not
possible to issue a COMMIT inside an SPL. But it can be done
so on the client side. Can anyone shade me some light on this ?
I'm quite new here and not sure how to do that. What are other
options to get around this if I have to commit after each
insert so that I won't get a deadlock on the table ?

DB2 v 8.2
Linux SUSE SLES 8

Thank you!!!

COMMIT is only disallowed if the procedur eis called from a function

or

a trigger. Can you please explain more about the context under which

teh

proc is called.
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[amurchis]

You keep refering to "SPL". SP I can figure out... but what is the "L" for?

[Serge Rielau]

amurchis wrote:

You keep refering to "SPL". SP I can figure out... but what is the "L"
for?

SPL Informix
PL/SQL Oracle
T-SQL MS SQL Server/Sybase
SQL PL DB2 (sometimes called PSM in reference to ANSI SQL/PSM standard )

Add a couple of marketing folks and executives.. welcome to Babylon.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Thiru]

Can you just post the exact error number?

Thiru.