DB2 version
DB2 v8.1.16.429 - FixPak 16
Windows server.

I have got a table that has got well over 300 million rows in it.

We are not sure yet why all the records got added, I suspect poorly written code somewhere. They were added over a few days and we didn't think it was going to keep happening so the archive logs got pruned a few times before we realised we were in trouble.

I now have to delete most of those three hundred million records and I would like some advice please. I have put letters against the various bits as I know I am asking a lot and it will make it easier to match answers.

(A) I believe I will have to write a stored procedure to do the work for me and delete a thousand records at a time, commit that and repeat until finished. Does this sound reasonable?

(B) The database is going to generate a lot of archive logs while this is happening. I'm not sure we are going to have the space for them all, so my plan was to schedule something in the background to delete them to keep within the disk resource I have available. As long as I do an offline backup first, is this going to be an issue? Perhaps I should just delete as many as archive logs I can afford to have without pruning, do another offline backup, rinse and repeat.

(C) Once the deletion has completed I need to consider how I am going shrink the database back down to it's usual size. My boss suggested copying everything I want to keep from the table into a new table, truncate the original table then copy everything back into it. What do you think? Is there a better way that you think I should use?

(D) I also need to consider the indexes for that table as well don't I? Would a truncate and re-import rebuild them ok, or should I do something else as well?

(E) What have I missed out? What else would you do please?

Thank you for looking!

Edit:
Forgot to say. I don't believe what caused the issue it still happening, and any plan I use to fix live will be run on a test copy first.

Probably the simplest way to do what you want without having to go through all of the archive log watching is this:

1) Create a "New" table just like the old one
2) LOAD the data you want to keep from the old table into the new one
3) Drop the old table (this will drop the indexes and FKs to and from the old table)
4) rename the new table to the old name
5) create the indexes for the new table, and any FK that were dropped when the old table was dropped.
6) do runstats on the new table.

Andy

Thanks Andy. I appreciate the clear instructions.

Whatever you do, do not delete archive log files by hand. EVER! archive log files are objects managed by DB2 and DB2 should always be able to reach them.
Obviously you've got the space to let a table grow that big so I'd (temporary) redirect my logs to another disk or some USB attached device, as long as you do not delete.
When all this is over you can restore all the original locations for your archive logs.

There is a missing step.

7) Rebind any packages that use the table.

Andy

I have run through the seven step plan (there are no packages for the table, I checked by querying SYSCAT.PACKAGEDEP) and everything has worked on my duplicate TEST database.

Thank you all again for the support.


I appreciate my original post may have sounded a bit cavalier regarding archive logs.

Could you provide me a bit more info on your archive logs quote please.

Ignoring my original plan as a dumb idea. You say not to delete them by hand - I take it you mean don't select a load of them from within Windows explorer and hit the delete button?

It's ok to prune logs with "DB2 PRUNE LOGFILE PRIOR TO ..." ?

I thought archive logs were only used for rolling forward after a database restore? So if I have them backed up to tape, I can delete them from the server and restore them back before using them?

Would they be used in a crash as well?

Yes, that is the wayCorrect.No, crash recovery uses active logs only.

Unless you use Infinite logging.

I am missing something obvious here.

What if a older transaction (which is no more in active logs) is in the bufferpool but has not been written to the disk?

Are there other assumptions (softmax, etc) ?

Ok, I found information here

IBM DB2 9.7 for Linux, UNIX and Windows Information Center