Here is a stored procedure.

Questions:

1. When the stored procedure is executed in Java in a WebLogic cluster
the procedure hangs.
A benign SQLWarning is set off - but that has the effect of calling
the continue handler - which does nothing - and then the procedure
times out.

Is there a way to make it to make it so the continue handler works as
a do-nothing no-op?

2. Is there a scoping rule that says that all fetch statements must
occur within a while loop?

3. Should an exit handler have an exit statement in it? And what is a
clean way to cause an exception to test the handler?

Thanks.



CREATE PROCEDURE SOME_SCHEMA.SOME_PROC(
OUT a_sqlcode INTEGER
,OUT a_sqlstate CHAR(5)
,OUT a_error_msg VARCHAR(2000)
)
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA

BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE l_update_count int default 0;
DECLARE l_counter int default 0;
DECLARE l_last_executed_date_p1 timestamp;
DECLARE l_commit_freq int default 1000;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 a_error_msg = MESSAGE_TEXT;
VALUES (SQLCODE,SQLSTATE) INTO a_sqlcode,a_sqlstate;
END;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
BEGIN
-- Do nothing. This makes it so a SQLWarning doesn't appear
END;

BEGIN

DECLARE cs_cursor CURSOR WITH HOLD FOR
SELECT *some sql*
FOR UPDATE;

OPEN cs_cursor;

FETCH cs_cursor into *some sql*

set l_last_executed_date_p1 = current date;

WHILE (SQLSTATE = '00000' )
DO

UPDATE *some sql*
WHERE current of cs_cursor;

SET l_update_count = l_update_count + 1;
SET l_counter = l_counter + 1;

IF l_counter = l_commit_freq
THEN
COMMIT WORK;
SET l_counter = 0;
END IF;

FETCH cs_cursor into *some sql*
END WHILE;
CLOSE cs_cursor;


END@

On Jun 7, 3:43*pm, "gimme_this_gimme_t... (AT) yahoo (DOT) com"
<gimme_this_gimme_t... (AT) yahoo (DOT) com> wrote:
DB2 will swallow all the warnings
SQLEXCEPTION catches all negative return codes
SQLWARNING catches all positiva return codes except +100
NOT FOUND the +100 event


An exit handler always jumps to the end of the block.
I would suggest that you label your blocks so that you know where the
handler will go next.
you can fetch outside a loop but why bother.

Exit is not needed in an exit handler.

Hope this is a help