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…
Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon 🙂
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;
You or someone you know looking to buy or sell?