Purge data and reclaim storage
A DBA on IDS 9.40 (HP-UX 11i) wanted to purge about 25% of the rows from four 50-million-row tables and actually reclaim the disk space, asking whether ALTER TABLE or index rebuilds were best, within a 2-day window. Consensus: deleting doesn't free space, so instead rename the old table, create a new empty one with proper first/next extent sizes (raw, no indexes), copy over only the rows to keep (HPL with named pipes, or INSERT...SELECT via dbaccess), drop the old table, then rebuild indexes/constraints and run UPDATE STATISTICS. TRUNCATE was suggested but isn't available in 9.40. The poster reported the approach worked well; links to HPL blog articles were shared.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
To improve performance and reclaim storage, I plan to purge 1/4 data from our production database system. I plan to use unload before delete. Records unloaded will be saved in unloaded text format for availability. 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? Each table will have about 50 million records, and the time window I have to do this maintenace is 2 days. Thanks, Chushia
Hi, better to use unload I advice to use HPL. I used it recently and I was very satisfied. About your real question, I don't know surely gurus at ids list will answer you. Regards, MArc On Thu, Feb 5, 2009 at 4:00 PM, CHUSHIA CHEN <chushia.chen@priszm.com>wrote: > To improve performance and reclaim storage, I plan to purge 1/4 data from > our > production database system. I plan to use unload before delete. Records > unloaded will be saved in unloaded text format for availability. 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? Each table will > have about 50 million records, and the time window I have to do this > maintenace is 2 days. > > Thanks, > Chushia > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd298246283cd04622d9926
IDS version?? OS version? If it's 10 or above, you can use "TRUNCATE TABLE" after your unload and then reload into one continuous extent. You'll want to drop indexes before loading though. Bob ----- Original Message ----- From: "CHUSHIA CHEN" <chushia.chen@priszm.com> To: ids@iiug.org Sent: Thursday, February 5, 2009 10:00:34 AM GMT -05:00 US/Canada Eastern Subject: Purge data and reclaim storage [14759] To improve performance and reclaim storage, I plan to purge 1/4 data from our production database system. I plan to use unload before delete. Records unloaded will be saved in unloaded text format for availability. 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? Each table will have about 50 million records, and the time window I have to do this maintenace is 2 days. Thanks, Chushia ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
> 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
Thanks for your reply. IDS 9.40, OS HP-UX 11i v1. I can not use trancate or drop table. I only unload 1/4 data and the majority of data are still in the table.
Andrew, thank you for so detailed plan. I think I'm going this way. I have 4 of such tables. I need to read your article first, I'm not familiar with HPL yet. I'll update you when I'm done. Thanks very much!
You can also create your new tables and use dbaccess like this, if you
don't want to dig through the 500 page HPL manual:
INSERT INTO new_table
SELECT * FROM old_table
Either way you do it, creating the new table as raw with no indexes will
make your load (or insert if you use the way above) speed much faster.
Just don't forget to change the new table to standard and create your
indexes when you've finished loading.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> CHUSHIA CHEN
> Sent: Thursday, February 05, 2009 10:17 AM
> To: ids@iiug.org
> Subject: Re: Purge data and reclaim storage [14765]
>
> Andrew, thank you for so detailed plan. I think I'm going this way. I
have
> 4
> of such tables. I need to read your article first, I'm not familiar
with
> HPL
> yet.
>
> I'll update you when I'm done.
>
> Thanks very much!
>
>
>
************************************************************************
**
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
Why waste time with the deletes? Just write new copies of the tables bringing over only the rows you DON't want to delete, then drop the old table. Yes, by all means use HPL to write them. j. On Thu, Feb 5, 2009 at 10:53 AM, ANDREW FORD wrote: >> 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 <http://www.ibmdatabasemag.com/blog/main/archives/2008/06/the_informix_hi_1.html > <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.
Yes, that's even better. No need to delete, just load the data that's needed and then drop the old table. Thanks, Chushia
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.
> but the above is "part2", am I missing anything on this topic? is there a > part1? can you send a link to part1? Thanks Kern, part 1: http://www.ibmdatabasemag.com/blog/main/archives/2008/05/the_informix_hi.html part 2: http://www.ibmdatabasemag.com/blog/main/archives/2008/06/the_informix_hi_1.html part 3: http://www.ibmdatabasemag.com/blog/main/archives/2008/07/the_informix_hi_2.html all informix blog entries: http://www.ibmdatabasemag.com/blog/main/archives/informix/index.html Andrew
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.
Thank you so much Andrew! It works like a dream. Chushia