Hi everyone,

This is my first time posting. I am quite new to DB2. My company is using DB2/UDB v8.1.6 on a Win2k3 Box to run J.D. Edwards application. I am the unofficial DB2 administrator in the company.

I have a question which I hope someone can help me with. It's regarding deleting a whole bunch of tables in a script files.

To help me speed up the deletion, I know I have to issue a command:

ALTER TABLE table1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

to reduce the logging overhead.

My question is that, in my script. If I have 100 tables, do I need to run the ALTER TABLE command for the 100 tables 100 times?

E.g. Script1.sql:

ALTER TABLE table1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
DELETE FROM table1;
ALTER TABLE table2 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
DELETE FROM table2;
ALTER TABLE table3 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
DELETE FROM table3;
.
.
.
ALTER TABLE table100 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
DELETE FROM table100;

COMMIT;

Many thanks if anyone could provide some insights.

Issue 100 IMPORT REPLACE or LOAD REPLACE statements with an empty data file. The filled tables will all be replaced with the data of the empty file, resulting in empty tables.

db2 import from emptyfile.del of del replace into table1
db2 import from emptyfile.del of del replace into table2
....
db2 import from emptyfile.del of del replace into table100

Hi Wim,

Thanks for the suggestion.

I am just wondering whether this will take long to run? Reason I'm asking is because some of the tables I'm trying to delete contains millions of records.

The best way to delete such huge table is to IMPORT blank files into those tables as Wim suggested.
We have tried this for tables with more than 50 Million records and it got deleted in few seconds.

Create a blank file blank.csv and run the following:

db2 "import from blank.csv of del replace into tb_tabl1"

Thanks,
Jay

Well, actually the option you specify, WITH EMPTY TABLE, should delete all data in the table, so you don't need to do the DELETE part.