IDS Forum

Re: Purge data and reclaim storage

Posted By: Kern Doe
Date: Thursday, 5 February 2009, at 5:28 p.m.

In Response To: Re: Purge data and reclaim storage (Kern Doe)

Thanks so much for sharing Andrew!

________________________________
From: Andrew Ford <aford@networkip.net>
To: kern_doe@yahoo.com
Sent: Thursday, February 5, 2009 4:37:49 PM
Subject: Re: Purge data and reclaim storage [14772]

thanks kern,

oh yeah, there is a part 1. there is even a part 3 and some other blog entries that you can find here:
http://www.ibmdatabasemag.com/blog/main/archives/informix/index.html

andrew

----- Original Message -----
From: "Kern Doe" <kern_doe@yahoo.com>
To: <aford@networkip.net>
Sent: Thursday, February 05, 2009 3:33 PM
Subject: Re: Purge data and reclaim storage [14772]

> Andew,
> This is a great writeup on HP Load you have, thanks.
>
> http://www.ibmdatabasemag.com/blog/main/archives/2008/06/the_informix_hi_1.html
>
> but the above is "part2", am I missing anything on this topic? is there a
> part1? can you send a link to part1?
>
> Kern --
>
> ________________________________
> From: ANDREW FORD <aford@networkip.net>
> To: ids@iiug.org
> Sent: Thursday, February 5, 2009 10:53:41 AM
> Subject: Re: Purge data and reclaim storage [14762]
>
>> Now for the tables that have been deleted 1/4 of data, which is the most
>> efficient way to reclaim storage? alter table ? drop and rebuild each index?
>
> Assuming you have enough space to store a duplicate copy of your largest table
> after the deletes AND you will be the only one touching these tables during
> your 2 day maint window, I suggest you do the following for each table:
>
> 1. rename existing table to tablename_old
> 2. create empty table with same structure, proper first and next extent sizes
> and original table name from step 1
> 3. use HPL with named pipes to unload from the old table and insert into the
> new table.
>
> http://www.ibmdatabasemag.com/blog/main/archives/2008/06/the_informix_hi_1.html
> 4. drop tablename_old
> 5. build indexes/foreign keys
> 6. update statistics
>
>> Each table will have about 50 million records, and the time window I have to
>> do this maintenace is 2 days.
>
> You should probably test this to make sure you do steps 1-6 for all tables in
> your 2 day timeframe. Seems very doable unless you have hundreds of these 50
> million row tables.
>
> Andrew
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Purge data and reclaim storage
  CHUSHIA CHEN -- Thursday, 5 February 2009, at 10:00 a.m.
  • Re: Purge data and reclaim storage
    Marc Patino -- Thursday, 5 February 2009, at 10:31 a.m.
  • Re: Purge data and reclaim storage
    rroussey@comcast.net -- Thursday, 5 February 2009, at 10:42 a.m.
    • Re: Purge data and reclaim storage
      CHUSHIA CHEN -- Thursday, 5 February 2009, at 10:55 a.m.
  • Re: Purge data and reclaim storage
    ANDREW FORD -- Thursday, 5 February 2009, at 10:53 a.m.
    • Re: Purge data and reclaim storage
      CHUSHIA CHEN -- Thursday, 5 February 2009, at 11:16 a.m.
      • RE: Purge data and reclaim storage
        Everett Mills -- Thursday, 5 February 2009, at 11:24 a.m.
    • Re: Purge data and reclaim storage
      jack.parker4@verizon.net -- Thursday, 5 February 2009, at 12:30 p.m.
      • Re: Purge data and reclaim storage
        CHUSHIA CHEN -- Thursday, 5 February 2009, at 2:01 p.m.
    • Re: Purge data and reclaim storage
      Kern Doe -- Thursday, 5 February 2009, at 4:33 p.m.
      • Re: Purge data and reclaim storage
        ANDREW FORD -- Thursday, 5 February 2009, at 4:43 p.m.
      • Re: Purge data and reclaim storage
        Kern Doe -- Thursday, 5 February 2009, at 5:28 p.m.
    • Re: Purge data and reclaim storage
      CHUSHIA CHEN -- Monday, 9 February 2009, at 11:55 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.