Hi All,
I am using DB2 V8.1 on linux. I have a need to backup and delete 25 million rows from a table having 100 million rows. If deleting all, it would be much easier. What is your best suggestion to do this? Thanks in advance.

There have been several recent posts on this exact subject. You should search the forum.

Andy

I came up with this idea (let me call my table T1):
1. create a new table T2 like T1
2. LOAD data to T2 with a cursor which select only needed rows from T1
3. drop T1
4. rename T2 to T1

Would this be faster than DELETEing directly from T1 suppose I ALTER T1 ACTIVATE NOT LOGGED INITIALLY before I delete?

You would also have to do several more steps.

5) add all RI that existed for T1 for T2 (this includes tables that T1 points to and tables that point to T1).
6) You may need to SET INTEGRITY on T2 because of LOAD
7) You may need to BACKUP because of LOAD

I have never liked using ACTIVATE NOT LOGGED INITIALLY since it can make you table unusable if it encounters a problem.

Andy

RI addition can be done after the rename of table T2 to T1. Hence no SET INTEGRITY following LOAD.

You can do a NONRECOVERABLE LOAD on T2 to avoid taking a BACKUP... But, if this is advisable, is dependent on your recovery requirements ...

And, if there are dependent objects, eg. views, then you will have to drop and recreate the views ... These dependet objects have to be recreated after the rename only ... If there are dependent objects, rename table stmt will fail ...

Do not forget RUNSTATS ...

rebind of packages may be needed if there are packages dependent on the table T1 ...

By all means, make sure you do your testing in a environment whose db object definitions is same as prod ... Data volumes similar to prod will help in timing your prod change ...

BTW, do not drop table T1 in step 3 ... Just rename it to T1_OLD, just in case ...

Sathyaram

Thanks to Andy and Sathyaram for all the suggestions and remindings. These really help a lot. Appreciate.

Gary