Results 1 to 7 of 7
  1. #1
    Join Date
    May 2007
    Posts
    27

    Unanswered: Delete in DB2 v7.2

    I see questions related to the delete process but have yet to see them related to DB2... so I'm posting my question.

    I have a DB2 v7.2 database that is fed by Tivoli. I do not have enough disk space to accomodate all the data for even two months. So I have to summarize last months data and then delete it so that I free up disk space for this months data collection. This means I have to delete approximately 150 million records each month. I have to run an sql that deletes 5 days worth of records, then reorg the database, delete 5 days, reorg the database, reorg, delete, reorg, etc... If I try to delete all in one process, my db2diag.log indicates my tablespace is full or my log file space is full..... I use circular logging... My table space is on a 68 gig drive that does go down to zero when processing over extended...

    I'm told that I can do a "purge" but don't know anything about that.

    Any "detailed" help would be appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    - V7 is out of service for more than 1 year. You may want to upgrade to V8.
    - You could use NOT LOGGED INITIALLY and delete all the data in a single shot. Since you are using circular logging, I don't think that recoverability of the database is an issue at all for you, is it?
    - You could truncate the whole table, i.e. _all_ rows. But is this what you want?

    I would recommend that you move to Version 9 and use range partitioned tables with easy roll-in/roll-out mechanisms being available.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2007
    Posts
    27
    Upgrading is not allowed at this time.... that costs money... and money is something management doesn't give to the peons.

  4. #4
    Join Date
    May 2007
    Posts
    27
    Sorry... we are working on requesting an upgrade... in the meantime, I have to work with what I have, which is 7.2. Unfortunately.... the company I work for is under tight budget restrictions right now.

  5. #5
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by stolze
    - V7 is out of service for more than 1 year. You may want to upgrade to V8.
    - You could use NOT LOGGED INITIALLY and delete all the data in a single shot. Since you are using circular logging, I don't think that recoverability of the database is an issue at all for you, is it?
    - You could truncate the whole table, i.e. _all_ rows. But is this what you want?

    I would recommend that you move to Version 9 and use range partitioned tables with easy roll-in/roll-out mechanisms being available.

    Ken... Unfortunately, your suggest is not much help... I can't simply upgrade... you work for IBM, you know it's not that simple. You don't just wave a wand and poof, upgrade appears. I need to work with what I have at the moment.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Export the data you want to keep, truncate the table, and load the remaining data back.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by myBoo
    Unfortunately, your suggest is not much help... I can't simply upgrade... you work for IBM, you know it's not that simple. You don't just wave a wand and poof, upgrade appears. I need to work with what I have at the moment.
    I gave you three different approaches that you could follow. I understand that an upgrade is difficult for you, so that leaves two ways (with n_i's refinement) to deal with that. How about those? As for the upgrade: Your configuration is not a serious production system, so the DB2 Express-C edition (no charge) may be suitable for you.

    p.s: Sorry, I don't know that it's "not that simple" to upgrade for you. How could I?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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