I'm having a tough time trying to run this stored procedure in DB2 UDB
8.1 environment. Can someone help me out here. All I'm trying to do
here is update an indicator 'N' if it is a NULL. Approximately 8
million rows will be updated as a result. I'm a newbie to DB2 and
writing my first SP. The problem is that Stored Proc doesnt seem to
update any rows eventhough I have set the commits after every row.
CREATE PROCEDURE DWCRM.POST_CD_U PD ()
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE v_POST_CD_ID INTEGER;
DECLARE v_CNT INTEGER DEFAULT 0;
DECLARE at_end INTEGER DEFAULT 0;
DECLARE at_close INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE cur_open CONDITION FOR SQLSTATE '24501';
-- Declare cursor
DECLARE c1 CURSOR FOR
SELECT POST_CD_ID FROM tstdw.dwcrm.POS T_CD WHERE POBOX_IND IS
NULL;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
DECLARE CONTINUE HANDLER FOR cur_open
SET at_close = 1;
-- Cursor left open for client application
OPEN c1;
upd_loop:
LOOP
-- IF at_close = 1 THEN
-- OPEN C1;
-- END IF;
FETCH c1 INTO v_POST_CD_ID;
SET v_CNT = v_CNT + 1;
IF at_end = 1 THEN
LEAVE upd_loop;
END IF;
update dwcrm.post_cd post set pobox_ind = 'N' where
POST_CD_ID=v_PO ST_CD_ID;
IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;
END LOOP;
CLOSE c1;
END P1 4 9103
Kris, a sincere welcome to SP writing!
Looks like you're COMMITing at every 500th row, right? (if you wanted,
you could also just do a MOD by 500 on the counter instead of resetting
it). Anyway, I definitely wouldn't commit after every row, but if you
are going to commit, you should open your cursors WITH HOLD.
I'm not sure if the below code is the real code or just an example, but
I'm not sure I see the reason for a loop at all. It seems that you
could accomplish this as a straight update, unless you're REALLY
concerned with COMMITing every so often. Are you worried about
recoverability? Log-space usage?
Regards,
--Jeff lo*********@gma il.com wrote:
I'm having a tough time trying to run this stored procedure in DB2 UDB
8.1 environment. Can someone help me out here. All I'm trying to do
here is update an indicator 'N' if it is a NULL. Approximately 8
million rows will be updated as a result. I'm a newbie to DB2 and
writing my first SP. The problem is that Stored Proc doesnt seem to
update any rows eventhough I have set the commits after every row.
CREATE PROCEDURE DWCRM.POST_CD_U PD ()
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE v_POST_CD_ID INTEGER;
DECLARE v_CNT INTEGER DEFAULT 0;
DECLARE at_end INTEGER DEFAULT 0;
DECLARE at_close INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE cur_open CONDITION FOR SQLSTATE '24501';
-- Declare cursor
DECLARE c1 CURSOR FOR
SELECT POST_CD_ID FROM tstdw.dwcrm.POS T_CD WHERE POBOX_IND IS
NULL;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
DECLARE CONTINUE HANDLER FOR cur_open
SET at_close = 1;
-- Cursor left open for client application
OPEN c1;
upd_loop:
LOOP
-- IF at_close = 1 THEN
-- OPEN C1;
-- END IF;
FETCH c1 INTO v_POST_CD_ID;
SET v_CNT = v_CNT + 1;
IF at_end = 1 THEN
LEAVE upd_loop;
END IF;
update dwcrm.post_cd post set pobox_ind = 'N' where
POST_CD_ID=v_PO ST_CD_ID;
IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;
END LOOP;
CLOSE c1;
END P1
Jeff,
Thanks for your quick response. Yes I was attempting to commit every
500th row but it didnt work as well. The reason why I was forced to
write this proc is due to the fact my update statement fails as a
result of the transaction log getting filled. My DBA is not smart
enough to fix this issue so I decided to write a SP.
As is this SP should work right or is there any modifications that
needed to be made for it to function.
Thanks,
Lokesh
jefftyzzer wrote:
Kris, a sincere welcome to SP writing!
Looks like you're COMMITing at every 500th row, right? (if you wanted,
you could also just do a MOD by 500 on the counter instead of resetting
it). Anyway, I definitely wouldn't commit after every row, but if you
are going to commit, you should open your cursors WITH HOLD.
I'm not sure if the below code is the real code or just an example, but
I'm not sure I see the reason for a loop at all. It seems that you
could accomplish this as a straight update, unless you're REALLY
concerned with COMMITing every so often. Are you worried about
recoverability? Log-space usage?
Regards,
--Jeff
lo*********@gma il.com wrote:
I'm having a tough time trying to run this stored procedure in DB2 UDB
8.1 environment. Can someone help me out here. All I'm trying to do
here is update an indicator 'N' if it is a NULL. Approximately 8
million rows will be updated as a result. I'm a newbie to DB2 and
writing my first SP. The problem is that Stored Proc doesnt seem to
update any rows eventhough I have set the commits after every row.
CREATE PROCEDURE DWCRM.POST_CD_U PD ()
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE v_POST_CD_ID INTEGER;
DECLARE v_CNT INTEGER DEFAULT 0;
DECLARE at_end INTEGER DEFAULT 0;
DECLARE at_close INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE cur_open CONDITION FOR SQLSTATE '24501';
-- Declare cursor
DECLARE c1 CURSOR FOR
SELECT POST_CD_ID FROM tstdw.dwcrm.POS T_CD WHERE POBOX_IND IS
NULL;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
DECLARE CONTINUE HANDLER FOR cur_open
SET at_close = 1;
-- Cursor left open for client application
OPEN c1;
upd_loop:
LOOP
-- IF at_close = 1 THEN
-- OPEN C1;
-- END IF;
FETCH c1 INTO v_POST_CD_ID;
SET v_CNT = v_CNT + 1;
IF at_end = 1 THEN
LEAVE upd_loop;
END IF;
update dwcrm.post_cd post set pobox_ind = 'N' where
POST_CD_ID=v_PO ST_CD_ID;
IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;
END LOOP;
CLOSE c1;
END P1
Kris, a sincere welcome to SP writing!
Looks like you're COMMITing at every 500th row, right? (if you wanted,
you could also just do a MOD by 500 on the counter instead of resetting
it). Anyway, I definitely wouldn't commit after every row, but if you
are going to commit, you should declare your cursor WITH HOLD.
I'm not sure if the below code is the real code or just an example, but
I don't readily see the reason for a loop at all--but if you go this
route, you might consider a cursor FOR loop with a positioned update.
It seems that you could accomplish this as a straight update, unless
you're REALLY concerned with COMMITing every so often. Are you worried
about recoverability? Log-space usage?
--Jeff lo*********@gma il.com wrote:
I'm having a tough time trying to run this stored procedure in DB2 UDB
8.1 environment. Can someone help me out here. All I'm trying to do
here is update an indicator 'N' if it is a NULL. Approximately 8
million rows will be updated as a result. I'm a newbie to DB2 and
writing my first SP. The problem is that Stored Proc doesnt seem to
update any rows eventhough I have set the commits after every row.
CREATE PROCEDURE DWCRM.POST_CD_U PD ()
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE v_POST_CD_ID INTEGER;
DECLARE v_CNT INTEGER DEFAULT 0;
DECLARE at_end INTEGER DEFAULT 0;
DECLARE at_close INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE cur_open CONDITION FOR SQLSTATE '24501';
-- Declare cursor
DECLARE c1 CURSOR FOR
SELECT POST_CD_ID FROM tstdw.dwcrm.POS T_CD WHERE POBOX_IND IS
NULL;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
DECLARE CONTINUE HANDLER FOR cur_open
SET at_close = 1;
-- Cursor left open for client application
OPEN c1;
upd_loop:
LOOP
-- IF at_close = 1 THEN
-- OPEN C1;
-- END IF;
FETCH c1 INTO v_POST_CD_ID;
SET v_CNT = v_CNT + 1;
IF at_end = 1 THEN
LEAVE upd_loop;
END IF;
update dwcrm.post_cd post set pobox_ind = 'N' where
POST_CD_ID=v_PO ST_CD_ID;
IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;
END LOOP;
CLOSE c1;
END P1
Lokesh:
Well--I had a 50/50 chance to get your name right and failed! :-)
Here's what I would do, more-or-less (note this code is UNTESTED!),
assuming DB2 UDB LUW. Note also that despite what I said earlier, you
*CANNOT* do a positioned update using a FOR loop. Sorry 'bout that!
CREATE PROCEDURE DWCRM.POST_CD_U PD ()
LANGUAGE SQL
SPECIFIC POST_CD_UPD
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE V_CNT INTEGER DEFAULT 0;--
FOR L_POST_CD AS C_POST_CD CURSOR WITH HOLD FOR
SELECT
POST_CD_ID
FROM
DWCRM.POST_CD
WHERE
POBOX_IND IS NULL
DO
UPDATE
DWCRM.POST_CD
SET
POBOX_IND = 'N'
WHERE
POST_CD_ID = L_POST_CD.POST_ CD_ID;--
SET V_CNT = V_CNT + 1;--
IF
MOD(V_CNT,500) = 0
THEN
COMMIT;--
END IF;--
END FOR;--
END;
--Jeff lo*********@gma il.com wrote:
Jeff,
Thanks for your quick response. Yes I was attempting to commit every
500th row but it didnt work as well. The reason why I was forced to
write this proc is due to the fact my update statement fails as a
result of the transaction log getting filled. My DBA is not smart
enough to fix this issue so I decided to write a SP.
As is this SP should work right or is there any modifications that
needed to be made for it to function.
Thanks,
Lokesh
jefftyzzer wrote:
Kris, a sincere welcome to SP writing!
Looks like you're COMMITing at every 500th row, right? (if you wanted,
you could also just do a MOD by 500 on the counter instead of resetting
it). Anyway, I definitely wouldn't commit after every row, but if you
are going to commit, you should open your cursors WITH HOLD.
I'm not sure if the below code is the real code or just an example, but
I'm not sure I see the reason for a loop at all. It seems that you
could accomplish this as a straight update, unless you're REALLY
concerned with COMMITing every so often. Are you worried about
recoverability? Log-space usage?
Regards,
--Jeff lo*********@gma il.com wrote:
I'm having a tough time trying to run this stored procedure in DB2 UDB
8.1 environment. Can someone help me out here. All I'm trying to do
here is update an indicator 'N' if it is a NULL. Approximately 8
million rows will be updated as a result. I'm a newbie to DB2 and
writing my first SP. The problem is that Stored Proc doesnt seem to
update any rows eventhough I have set the commits after every row.
>
>
CREATE PROCEDURE DWCRM.POST_CD_U PD ()
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
>
DECLARE v_POST_CD_ID INTEGER;
DECLARE v_CNT INTEGER DEFAULT 0;
DECLARE at_end INTEGER DEFAULT 0;
DECLARE at_close INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE cur_open CONDITION FOR SQLSTATE '24501';
-- Declare cursor
DECLARE c1 CURSOR FOR
SELECT POST_CD_ID FROM tstdw.dwcrm.POS T_CD WHERE POBOX_IND IS
NULL;
>
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
DECLARE CONTINUE HANDLER FOR cur_open
SET at_close = 1;
-- Cursor left open for client application
>
OPEN c1;
upd_loop:
LOOP
-- IF at_close = 1 THEN
-- OPEN C1;
-- END IF;
>
FETCH c1 INTO v_POST_CD_ID;
SET v_CNT = v_CNT + 1;
>
IF at_end = 1 THEN
LEAVE upd_loop;
END IF;
>
update dwcrm.post_cd post set pobox_ind = 'N' where
POST_CD_ID=v_PO ST_CD_ID;
>
IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;
>
END LOOP;
>
CLOSE c1;
END P1
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: dinesh prasad |
last post by:
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to...
|
by: Nashat Wanly |
last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
Visual C# .NET
View products that this article applies to.
This article was previously published under Q310070
For a Microsoft Visual Basic .NET version of this article, see 308049.
For a Microsoft Visual C++ .NET version of this article, see 310071.
For a Microsoft Visual J# .NET version of this article, see 320627.
This article refers to the following Microsoft .NET...
|
by: Rhino |
last post by:
I've spent the last couple of hours trying to figure out how to debug a Java
stored procedure and am just going in circles. The last straw came when I
got "Cannot open input stream for default" when I launched the IBM
Distributed Debugger via
D:\IBMDebug>idebug.exe -qdaemon -quiport=8000,8001
First, a bit of background.
I am running DB2 V7.2 with Fixpack 9 applied on Windows XP Professional (all
critical service applied). I've written...
|
by: Rhino |
last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a
Throwable back to the calling program as an OUT parameter?
If yes, what datatype should I use when registering the Throwable as an OUT
parameter and what datatype should I use in the CREATE PROCEDURE and DROP
PROCEDURE statements?
Here's what I tried:
- the method signature for the stored procedure included: Throwable
throwable
|
by: Thomasb |
last post by:
With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.
I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;
works from a query tool.
| |
by: Dino L. |
last post by:
How can I run stored procedure (MSSQL) ?
|
by: Amber |
last post by:
Stored procedures are faster and more efficient than in-line SQL
statements. In this article we will look at two SQL Server stored
procedures; one using an input parameter and one not, and see how to
call them from an ASP.Net page
Every modern database system has a stored procedure language. SQL
Server is no different and has a relatively sophisticated and easy to
use system. This article will not attempt to go into depth in
explaining...
|
by: kd |
last post by:
Hi All,
How to debug a stored procedure?
Thanks,
kd
|
by: Dabbler |
last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuery):
System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
expects parameter '@EMail', which was not supplied.
The field value was null in the database and not changed in the FormView so
is null going back into the stored procedure. I'm stumped and would greatly
appreciate any suggestions.
|
by: jed |
last post by:
I have created this example in sqlexpress
ALTER PROCEDURE .
@annualtax FLOAT
AS
BEGIN
SELECT begin1,end1,deductedamount,pecentageextra
FROM tax
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |