Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010

    Unanswered: How to purge 1 month old data?

    Hello guys,
    I am currently working on a project which is to purge data older than 31 days on a daily basis...

    Today we have 10-12 tables which needs this daily purging. Some of these tables have a billion records older than 31 days.I was wondering how we can do this without any appl down time...our daily inserts into these tables will be around 1 million. That means I have to purge atleast 10-15 million records everyday.

    A daily back up and deletion suggestion would be great.


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    Deleting the rows is very costly and and slow. You could look into range partitioned tables. It would help if you supplied your DB2 version and OS.


  3. #3
    Join Date
    Oct 2009
    221B Baker St.
    If there will be a one-time mass delete, suggest you bite the bullet and schedule downtime to do this. For as much as you want to delete, suggest you unload the tables to sequential media, copy the rows that are less than 31 days old into a new file and reload the tables. Once the "billions" of obsolete rows are deleted, the 30million or so monthly inserts (and deletes) will be easier to manage.

    While you are preparing for the mass "delete", consider if partitioning or any other change would help.

  4. #4
    Join Date
    Sep 2004
    I'd also suggest partitioning the tables, one partition per day.
    Partition rotation could then be performed once a day, i.e., creating a logical new partition at the same moment (and possibly at the same physical location) when you "drop" the oldest partition.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

Posting Permissions

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