Delete Using Commit Count DB2 Stored Procedure

I recently have come across the need to delete millions of rows from a variety of tables within my WebSphere Commerce instance, but have been unable to as the transaction log fills all the way up and the transaction errors out. Granted, there are several options that I could have taken such as to increase the transaction log size; however, that isn’t always feasible depending upon the environment you are working with.

I came up recently with the following stored procedure that allowed me to delete a ton of table rows committing the transaction on a periodic basis as needed. This ensured that I am able to delete the necessary records without completely filling up the transaction log and avoided the need to modify the DB2 instance properties.

This stored procedure is obviously designed for DB2.

This create statement was also designed to be ended with the ‘@’ character, due to the queries that are used to create the stored procedure. To create this stored procedure I copied this create statement to a SQL file and ran that file from the DB2 command line using the following statement.

db2 -td@ -vf my_sql_file.sql

The create statement for the stored procedure is shown below. It takes as arguments 3 parameters:

1. v_TABLE_NAME: The name of the table that the records will be deleted from.
2. v_COMMIT_COUNT: The commit size of the logical units of work to be performed.
3. v_WHERE_CONDITION: The where clause used to determine which records are to be deleted.

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_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
			EXECUTE v_DELETE_STATEMENT;
			IF SQLCODE = 100 THEN 
				LEAVE DEL_LOOP; 
			END IF;
			COMMIT;
		END LOOP;

	COMMIT;
	
END@

This isn’t a perfect stored procedure, and I would love to make some changes to improve it, and most likely will in the near future; however, this quick and dirty solution met my needs for the time being.