Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: Delete 25M rows out of a 100M table - your suggestion please

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There have been several recent posts on this exact subject. You should search the forum.

    Andy

  3. #3
    Join Date
    Mar 2005
    Posts
    108
    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?

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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Mar 2005
    Posts
    108
    Thanks to Andy and Sathyaram for all the suggestions and remindings. These really help a lot. Appreciate.

    Gary

Posting Permissions

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