Hello,

This is very basic error that we face many times.

Last time I had faced it while deleting all rows from table and the table had millions of rows.
I went on increasing the LOGSECOND parameter & reach to the maximum limit, still i couldn't empty the table.

Finally i need to drop the table and recreated it to make it empty.

Ok. My question is... is there any other way to delete all data from table without making transaction logs full?

Thanks!


P.S. -- db2 v9.1 on AIX 5

load of an empty file with replace option might do the trick

you may want to use import instead of load if your db is using archival logging

set logsecond to -1.

or increase your logfilsiz/logprimary

or load from null or other truncate tricks

Thank you all for your replies.

"Load/Import" is really a good option to empty the table.

Thanks a lot.

Simple method to empty the table is

ALTER TABLE x ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

You can use COMMITS after some number of deleted records, or better
use LOAD table with REPLACE, using DUMMY input file.

Lenny

Lenny mentioned commits, which is very important when you are trying to delete millions and less of rows, but not the entire table. If you are attempting to delete some subset of rows, you would want to do the deletes within a loop and commit after a particular count of records.

Dave

If you are in 9.7 you can also use truncate table command.

db2 "truncate table tablename IGNORE DELETE TRIGGERS drop storage immediate"