DB2 Stored Procedure - controlled large record del

Ive written a DB2 Stored Procedure for a housekeeping job that in theory could be deleting large volumes for data from the database.

The requirement is to control the deletion by committing a defined number of records at a time.

Firstly, I would like some feedback on my Stored Procedure to see if there is any improvements I could make.

Secondly, Ive got a question about SQL Errors. If an error occurs during an iteration of the loop, does the Stored Procedure exit immediately ? Ideally I would like to continue the loop trying to delete as many records as I can. Im not sure if my script works in this way or not.


CREATE PROCEDURE leave_loop(IN commit_unit INTEGER, OUT counter INTEGER)
LANGUAGE SQL
BEGIN
  DECLARE v_prod_id INTEGER;
  DECLARE v_delete_counter INTEGER DEFAULT 0;
  DECLARE v_total INTEGER DEFAULT 0;
  DECLARE not_found CHAR(1) DEFAULT 'N';

  DECLARE c1 CURSOR WITH HOLD FOR
    SELECT prod_id
    FROM product
    WHERE status_deleted = 1
    ORDER BY prod_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET not_found = 'Y'; 

  SET counter = 0;

  OPEN c1;
  delete_loop:
  LOOP
    -- Fetch the record
    FETCH c1 INTO v_prod_id;

    -- If not row found then leave the loop
    IF not_found = 'Y' THEN
      -- If we have not reached the commit unit the commit the outstanding records
      IF v_delete_counter > 0 THEN
        COMMIT;
      END IF;
      LEAVE delete_loop;
    END IF;

    -- Perform the deletion
    DELETE FROM product WHERE prod_id = v_prod_id;

    SET v_delete_counter = v_delete_counter + 1;

    -- Check if the commit unit has been reached
    IF MOD(v_delete_counter, commit_unit) = 0 THEN
      COMMIT;
      SET v_delete_counter = 0;
      SET v_total = v_total + 1;
    END IF; 

  END LOOP delete_loop;
  CLOSE c1;

  SET total = v_total;
END @

I had similar requirements in the past. Please find below the stored procedure which I wrote to serve my needs.

SET SERVEROUTPUT ON@ 

drop procedure DELETE_WITH_COMMIT_COUNT@
CREATE PROCEDURE DELETE_WITH_COMMIT_COUNT(IN v_TABLE_NAME VARCHAR(24), IN v_COMMIT_COUNT INTEGER, IN v_WHERE_CONDITION VARCHAR(1024))
    NOT DETERMINISTIC
    LANGUAGE SQL
BEGIN
-- DECLARE Statements
DECLARE SQLCODE INTEGER;
DECLARE v_COUNTER INTEGER DEFAULT 0;
DECLARE v_DELETE_QUERY VARCHAR(1024);
DECLARE v_DELETE_STATEMENT STATEMENT;

SET v_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_TABLE_NAME || ' WHERE ' || v_WHERE_CONDITION
    || ' FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_TABLE';

PREPARE v_DELETE_STATEMENT FROM v_DELETE_QUERY;

DEL_LOOP:
    LOOP
        SET v_COUNTER=v_COUNTER + 1;

        EXECUTE v_DELETE_STATEMENT;

        IF SQLCODE = 100 THEN
            LEAVE DEL_LOOP; 
        END IF;
        COMMIT;

    END LOOP;

COMMIT;

END@

You can add 'DECLARE CONTINUE HANDLER FOR SQLSTATE XXXX' to your stored procedure which will bypass the erroneous executions and shall avoid abrupt termination of your stored proc.