DB2 - anonymous block to do batch-wise deletion of records

Bandi
Posted by Bandi under Others category on | Points: 40 | Views : 533
BEGIN
DECLARE V_COUNT INT DEFAULT 0;
DECLARE V_DIA INT DEFAULT 0;

DECLARE SQLCODE INT;
SELECT COUNT (EMPLOYEE_ID) INTO V_COUNT FROM EMPBKP;

WHILE (V_COUNT - V_DIA >0) AND (SQLCODE = 0)
DO
DELETE FROM (SELECT * FROM EMPBKP FETCH FIRST 100 ROWS ONLY);
GET DIAGNOSTICS v_dia = row_count;
END WHILE;
END@


Here, the code is for deleting EMPBKP tables data in batches.. the batch size is 100....

It is very usefull whenever we have large number records to delete from a table...

Comments or Responses

Login to post response