sample db2 sql pl (ver 8.2.7) having cursors..

I have a sample db2 stored procdure written that has cursors, I needed experts to review and give me hints..

Requirement is to write a SQL PL SP that has cursor to iterate a table and then call another SP with some parameters, also to log the exceptions clearly as the data rejected is sensitive and needs to reviewed by users for re-processing.

Here is my attempt..

create procedure TEST()
LANGUAGE SQL
BEGIN
 Declare v_counter             INTEGER;
 DECLARE v_message             varchar(70);

 DECLARE v_sqlstate            varchar(5);

 DECLARE v_at_delete           smallint;
   DECLARE v_start_timestamp     timestamp;
   DECLARE v_end_timestamp       timestamp;
   DECLARE v_procstart_timestamp     timestamp;
   DECLARE v_procend_timestamp       timestamp;



 declare SQLSTATE char(5);
 DECLARE at_notfound SMALLINT DEFAULT 0;
 DECLARE at_null SMALLINT DEFAULT 0;
 declare not_found
  CONDITION for SQLSTATE '02000';
 declare  SQLEXEPTION
 CONDITION for SQLSTATE '23502';


  DECLARE MyCursor Cursor with hold  for
    SELECT col1,col2...
              from tableA
        ORDER BY col1
    FOR READ ONLY;



 DECLARE CONTINUE HANDLER FOR not_found
    BEGIN
   SET at_notfound = 1;
   END;
    DECLARE CONTINUE HANDLER FOR SQLEXEPTION
    BEGIN
   SET at_null = 1;
    END;
   set v_procstart_timestamp = current timestamp;
   SET v_counter = 0;



   set v_start_timestamp = current timestamp;
   OPEN MyCursor;
   LOOP
   FETCH MyCursor INTO
         v_col1,v_col2...   ;      
            SET  v_counter =v_counter+1;

        CALL Another_SP(v_col1,v_col2...);                              );


  END LOOP;
     CLOSE  MyCursor;
     set v_end_timestamp = current timestamp;
INSERT INTO ERRORS VALUES('TST',current timestamp,
                                 null,char(v_start_timestamp)||' '||char(v_end_timestamp));


set v_procend_timestamp = current timestamp;
INSERT INTO ERRORS VALUES('TST',current timestamp,
                                 null,char(v_procstart_timestamp)||' '||char(v_procend_timestamp));
commit;
END


1. Few points - when I declared cursor not having"with hold" option it gave SQL0501N error so I had to use "Wtih hold" -
I am not sure why I need that explanations on this will be helpful

2. When I ran 2-3 times I got A database manager error occurred.[IBM][CLI Driver][DB2/LINUXX8664] SQL0289N  Unable to allocate new pages in table space "Tab_space1".  SQLSTATE=57011

Not sure this is becuase of my stored procedure, but wanted to find out before I deploy this in production..

Thanks a lot!!Start Free Trial