Results 1 to 3 of 3

Thread: MASS Delete DB2

  1. #1
    Join Date
    Feb 2017
    Posts
    1

    Exclamation Unanswered: MASS Delete DB2

    We need to delete 100+ million rows from table. The table is having 30 partitions with 30+ million rows. What would be best approach to delete rows and recover the space.

  2. #2
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    can you load replace particular partitions with dummy load file? Another option is to unload the data you wish to keep and then load/replace with that data.

  3. #3
    Join Date
    Feb 2017
    Posts
    1
    Having had to so something similar (albeit on a non-partitioned DB), your options are limited.

    What worked best for me was to do a runstats, then do a delete from subselect, looped many many times, with reorg and runstats after.

    The syntax is easy, but you will be deleting for a long long time depending on the load of your system. The biggest stumbling block is that you will kill your transaction log if you attempt to do it in one go, it just won't work. You have to break it down into chunks interspersed with commits.

    DELETE FROM (SELECT * FROM <MYTABLE> WHERE <CLAUSE> FETCH FIRST 5000 ROWS ONLY); COMMIT;
    DELETE FROM (SELECT * FROM <MYTABLE> WHERE <CLAUSE> FETCH FIRST 5000 ROWS ONLY); COMMIT;
    DELETE FROM (SELECT * FROM <MYTABLE> WHERE <CLAUSE> FETCH FIRST 5000 ROWS ONLY); COMMIT;
    DELETE FROM (SELECT * FROM <MYTABLE> WHERE <CLAUSE> FETCH FIRST 5000 ROWS ONLY); COMMIT;
    .
    .
    .
    .
    .

    And you repeat this until there's nothing left to delete. You can either run this in a script or create a proc with a loop in it. The former is easier to halt and manage, especially if you have time constraints on when you can run your deletes (ie at night only, etc)

    Hope you get sorted!
    Riaan
    Last edited by RocketRooster; 02-21-17 at 05:44.

Tags for this Thread

Posting Permissions

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