DB2 Incremental Delete

Ever have to delete a lot of records from a table in DB2 and can’t truncate, but you keep filling the transaction log? One way of solving the problem without having to manually update ranges of values in a where clause is to do an incremental delete in a stored proc. Here’s an example/template to get you started…


WHILE EXISTS (  SELECT  ITEM_ID 
		FROM    XX.TABLE
		WHERE   SOMETHING_NB = 1
		FETCH FIRST 1 ROWS ONLY )
DO
	DELETE FROM 
		(SELECT ROW_NUMBER() OVER() AS ROW_NB
		FROM    XX.TABLE
		WHERE   SOMETHING_NB = 1
		FETCH FIRST 20000 ROWS ONLY)
	WHERE ROW_NB <= 20000;
	
	IF ((L_SQLCODE_NB <> 0) AND (L_SQLCODE_NB <> 100)) THEN
		SIGNAL SQLSTATE '20003' SET MESSAGE_TEXT = 'Error deleting records';
	END IF; 
	
	COMMIT;
END WHILE;
Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?
Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

Leave a Reply