Hi All,

I am trying to delete some unwanted records from a table in db2 and want to commint in between the records like say for every 1000 recrods I want to do a commit on the table just to avoid the filling of the logs.

Thanks in advance.

Srinivas.

Srinivas,
You cannot do that. All you can do is use the WHERE clause and run
the delete multiple times.

Instead of: DELETE FROM MyTable
You can try:
DELETE FROM MyTable where myUniqueCol between (0 and 10000)
DELETE FROM MyTable where myUniqueCol between (10000 and 20000)
DELETE FROM MyTable where myUniqueCol between (20000 and 30000)
DELETE FROM MyTable where myUniqueCol between (30000 and 40000)
DELETE FROM MyTable where myUniqueCol between (40000 and 50000)
etc.

HTH

Andy

Thanks Andy for the quick respond. I also thought of another way like
delete from table_name where col_name in (select col_name from table_name
fetch first 1000 rows only);

commit;

and repeat the same for multiple times.

Thanks
Srinivas

Alter table table-name ACTIVATE NOT LOGGED INITIALLY;
Delete from table-name where ...;
commit;

You must execute the above script with auto-commit turned off (+c). The delete will not be logged. Make sure you have a backup, because in the event of a system failure the table will be corrupted.