Error : DB2 STORED PROCEDURES

Hi All,

Can anyone please help me figure out the error with these stored Procedures.I am trying to perform large updates.The Updates are being performed but it goes into an infinite loop.

CREATE PROCEDURE SAMPLE.SPROC (IN p_tablename varchar (50),
IN p_IDNO integer)
BEGIN
DECLARE SQLCODE INTEGER ;
DECLARE txt varchar (10000);
DECLARE stmt varchar (10000);
IF (p_IDNO IS NULL) THEN
SET txt =
'UPDATE (SELECT STATUS FROM ' || p_tablename || ' WHERE STATUS = ' || '''Y''' || ' FETCH FIRST 5000 ROWS ONLY ) SET STATUS = ''' || 'N''';
ELSE
SET txt =
'UPDATE (SELECT STATUS FROM ' || p_tablename || ' where IDNO = ' || char(p_IDNO)|| ' FETCH FIRST 5000 ROWS ONLY ) SET STATUS = ''' || 'N''';
PREPARE stmt FROM txt;
l :
LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE l; END IF;
COMMIT;
END LOOP ;
END IF;
END

This stored procedure returns an error and never returns any result.

CREATE PROCEDURE SAMPLE.SPROC
(IN tabschema VARCHAR(128),
IN tabname VARCHAR(128),
IN predicate VARCHAR(1000),
IN commitcount INTEGER)
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE txt VARCHAR(10000);
DECLARE stmt STATEMENT;
SET txt = 'UPDATE (SELECT 1 FROM "'
|| tabschema || '"."' || tabname || '" WHERE '
|| predicate || ' FETCH FIRST ' ||
RTRIM(CHAR(commitcount)) || ' ROWS ONLY) SET STATUS = ''' || 'N''';
PREPARE stmt FROM txt;
l: LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE l; END IF;
COMMIT;
END LOOP ;
END


Thanks In Advance,

db2venky-at-yahoo.com wrote:

> Hi All,
>
> Can anyone please help me figure out the error with these stored
> Procedures.I am trying to perform large updates.The Updates are being
> performed but it goes into an infinite loop.
>
> CREATE PROCEDURE SAMPLE.SPROC (IN p_tablename varchar (50),
> IN p_IDNO integer)
> BEGIN
> DECLARE SQLCODE INTEGER ;
> DECLARE txt varchar (10000);
> DECLARE stmt varchar (10000);
> IF (p_IDNO IS NULL) THEN
> SET txt = 'UPDATE (SELECT STATUS FROM ' || p_tablename ||
> ' WHERE STATUS = ''Y'' FETCH FIRST 5000 ROWS ONLY ) ' ||
> ' SET STATUS = ''N''';
> ELSE
> SET txt = 'UPDATE (SELECT STATUS FROM ' || p_tablename ||
> ' WHERE IDNO = ' || char(p_IDNO) ||
> ' FETCH FIRST 5000 ROWS ONLY ) SET STATUS = ''N''';
> PREPARE stmt FROM txt;
> l: LOOP
> EXECUTE stmt;
> IF SQLCODE = 100 THEN LEAVE l; END IF;
> COMMIT;
> END LOOP;
> END IF;
> END

The loop in the ELSE branch cannot terminate. You select records for the
UPDATE based on "IDNO", but you newer change the value in the IDNO column.
Therefore, each execution of the UPDATE statement will update the same rows
again.

Also, you want want to declare a CONTINUE handler for NOT_FOUND condition.

(I just reformatted the code a bit so that it can be read and understood
more easily.)

> This stored procedure returns an error and never returns any result.
>
> CREATE PROCEDURE SAMPLE.SPROC
> (IN tabschema VARCHAR(128),
> IN tabname VARCHAR(128),
> IN predicate VARCHAR(1000),
> IN commitcount INTEGER)
> BEGIN
> DECLARE SQLCODE INTEGER;
> DECLARE txt VARCHAR(10000);
> DECLARE stmt STATEMENT;

You don't want to use the above declaration.

> SET txt = 'UPDATE (SELECT 1 FROM "'
> || tabschema || '"."' || tabname || '" WHERE '
> || predicate || ' FETCH FIRST ' ||
> RTRIM(CHAR(commitcount)) || ' ROWS ONLY) SET STATUS = ''' || 'N''';
> PREPARE stmt FROM txt;
> l: LOOP
> EXECUTE stmt;
> IF SQLCODE = 100 THEN LEAVE l; END IF;
> COMMIT;
> END LOOP ;
> END

What's the error that you get exactly?
Which version on DB2 are you using on which platform?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Thanks a lot,
Thats right the ID NO here was the problem for the loop being infinite.But I still need to find a solution to come out of the loop after all the records are updated.I thought SQL100W would fix the problem.

db2venky-at-yahoo.com wrote:

> Thanks a lot,
> Thats right the ID NO here was the problem for the loop being infinite.But
> I still need to find a solution to come out of the loop after all the
> records are updated.I thought SQL100W would fix the problem.

As I said, declare a condition handler for NOT_FOUND. Something like that
would do:

DECLARE end_reached INT DEFAULT 0;
DECLARE not_found FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found SET end_reached = 1;

l: LOOP

IF end_reached <> 0 THEN LEAVE
END LOOP l;


You can do away with the condition declaration for SQLSTATE 02000 if you
want, but I find it easier to understand. If you don't use the condition
declaration, remove the 2nd line and change the 3rd to:

DECLARE CONTINUE HANDLER FOR '02000' SET end_reached = 1;

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Thanks again for the reply.I got it to work as my mistake was I was in an ifinite loop.