472,958 Members | 2,697 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

LARGE INSERT in STORED PROCEDURE

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!!!

Nov 12 '05 #1
6 4610
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
Nov 12 '05 #2
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..
Nov 12 '05 #3
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
Nov 12 '05 #4
You keep refering to "SPL". SP I can figure out... but what is the "L" for?
Nov 12 '05 #5
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
Nov 12 '05 #6
Can you just post the exact error number?

Thiru.

Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Bill Kellaway | last post by:
Hi there - this should be fairly simple for someone. Basically I can't figure out how to pass the parameters from ASP to a Stored Procedure on SQL. Here's my code: I just need to help in...
4
by: sherkozmo | last post by:
SQL2000 - AccessXP I built an adp file with a stored procedure from SQL as follows: SELECT * FROM Z_mis_sjk_job_code_access WHERE job_code=@JobCode UNION ALL SELECT * FROM...
2
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have...
4
by: nicholas | last post by:
Could someone tell me how to implement an INSERT on an aspx-page using a stored procedure, in VB-code? THX, Nic PS: I have been trying this, but it ain't working: Sub insert_new_content...
9
by: David Helgason | last post by:
I'm calling one stored procedure with a prepared statement on the server with 6 arrays of around 1200 elements each as parameters. The parameters are around 220K in total. This is taking a...
0
by: Scarab | last post by:
Hi all, When I use following sql to get data in stored procedure, error occurs: insert into #tmp EXECUTE dbo.prc_1 @date1,@date1 Here is the source code, Thanks CREATE TABLE ( NULL , ...
6
by: SandySears | last post by:
I am trying to use a stored procedure to insert a record using VS 2005, VB and SQL Server Express. The code runs without errors or exceptions, and returns the new identifer in the output...
1
by: sheenaa | last post by:
Hello Members, I m creating my application forms in ASP.Net 2005 C# using the backend SQL Server 2005. What i have used on forms :: ? On my first form i have used some...
8
by: Lee | last post by:
guys, I have a project need to move more than 100,000 records from one database table to another database table every week. Currently, users input date range from web UI, my store procedure will...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.