Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2009
    Posts
    43

    Unanswered: Big (for me) row deletion

    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.
    Last edited by hazy_dba; 03-28-11 at 11:42. Reason: Forgot something

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2009
    Posts
    43
    Thanks Andy. I appreciate the clear instructions.

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by hazy_dba View Post
    (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.
    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.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is a missing step.

    7) Rebind any packages that use the table.

    Andy

  6. #6
    Join Date
    Dec 2009
    Posts
    43
    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.


    Quote Originally Posted by dr_te_z View Post
    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.
    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?

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by hazy_dba View Post
    It's ok to prune logs with "DB2 PRUNE LOGFILE PRIOR TO ..."
    Yes, that is the way
    Quote Originally Posted by hazy_dba View Post
    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?
    Correct.
    Quote Originally Posted by hazy_dba View Post
    Would they be used in a crash as well?
    No, crash recovery uses active logs only.

  8. #8
    Join Date
    Jan 2010
    Posts
    335
    Quote Originally Posted by dr_te_z View Post
    No, crash recovery uses active logs only.
    Unless you use Infinite logging.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by dr_te_z View Post
    No, crash recovery uses active logs only.
    Quote Originally Posted by nvk@vhv
    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) ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •