DB2 UDB SQL Stored Procedure.

[lokesh.kris]

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_UPD ()
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.POST_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_POST_CD_ID;

IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;

END LOOP;

CLOSE c1;
END P1

[jefftyzzer]

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*********@gmail.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_UPD ()
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.POST_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_POST_CD_ID;

IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;

END LOOP;

CLOSE c1;
END P1

[lokesh.kris]

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*********@gmail.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_UPD ()
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.POST_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_POST_CD_ID;

IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;

END LOOP;

CLOSE c1;
END P1

[jefftyzzer]

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*********@gmail.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_UPD ()
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.POST_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_POST_CD_ID;

IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;

END LOOP;

CLOSE c1;
END P1

[jefftyzzer]

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_UPD ()
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*********@gmail.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*********@gmail.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_UPD ()
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.POST_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_POST_CD_ID;
>
IF v_CNT = 500 THEN
SET v_CNT = 0;
COMMIT;
END IF;
>
END LOOP;
>
CLOSE c1;
END P1