Re: reclaiming tblspace

Clem Akins (cwakins@leia.alloys.rmc.com) wrote: : FYHG22F@prodigy.com (Reed Mathews) : Subject: online engine use of disk space : > I have been working with the informix online engine 4.0 for : > about 2 years now and we have gradually filled the disk space : > allocated to the database. (We are running at about 99% now.) : > We do plan to get a new drive to solve the problem, but in the : > meantime we are experiencing some hardships. We have had : > programs fail because there was not enough scratch space and : > we have had to drop tables that we would like to have kept on : > hand. : > Recently, I deleted about half of the 500,000 customers : > that we had in our active set along with their orders. I expected : > to have a proportional amount of disk space freed. I was surprised : > to find that we got nothing at all returned disk space. : > After some experimentation, we found that even after a table was : > completely emptied and all indexes were dropped, it continued to claim : > the space it occupied when full. We did find that we could re- : > size a table by unloading the data, dropping the table, re-creating : > it, and re-loading the data. We have done this to make more : > space. This seems to be a lot more work than is necessary. : > There has to be an easier way to re-claim the space from deleted : > rows. Is there some utility that does this? Do more recent : > versions address this problem. I would appreciate any advice at all : > on how to deal with this problem. Thanks. : - : REED MATHEWS FYHG22F@prodigy.com : From: billkuruc@aol.com (BILLKURUC) : Subject: Re: online engine use of disk space : > try building a "clustered index" : > this will get back space for Deleted rows without having to unload and : > load : > : > We have asked for a utility to do this for years, but none exists as far : > as we know : Perhaps this one should go in the FAQ. : (I've sent a copy to Kerry. Graeme, does this answer apply well to *these* : questions?) Which answer? The one above, or the one below? I would say that ALTER INDEX TO CLUSTER is the preferred solution, providing you have the free space. Next best is ALTER TABLE to a different dbspace where you have sufficient space. Last resort is unload/reload. On the other hand, I have had situations (pre 5.0) where I gave up waiting (after a day or two) for ALTER INDEX to complete (tens of millions of rows, multiple indexes). I don't yet have experience of trying it on that size of table under 6.0 or 7.1. (basically I would need kicking into it, 'cos I got scared off). In fact, on a sizeable database, ALTER TABLE becomes preferable, because it's much easier to make sure that you are building the new copy on a different set of spindles to the old copy, otherwise you spend most of the time shuffling your disk heads about. My experience above was because I was trying to sort the data, not shrink the table. : Reclaiming space in an empty extent under OnLine v4 and v5. : OnLine v7 has different things to do, involving fragments, migrating tables : between dbspaces, backing up individual dbspaces, and such. : References: Informix "OnLine Administrator's Guide" v5.0 pg 2-107. : Joe Lumbley's "INFORMIX DBA Survival Guide", chapter 7. : any more references? : There are two ways to do this: : 1) If the table has an index, use SQL to ALTER INDEX TO CLUSTER. : This will physically re-arrange the data in the table to match the index, : freeing up unused extents space along the way. This method requires free : dbspace >= (tablesize after purging unused rows), for the alter has to : physically re-create the "new" data before it can delete the "old" data. : 2) Create a schema for the table (OnLine versions <6 remember to alter : the schema to include dbspace, locking, and extent sizing info), : unload the table to a file, : drop the table, : re-create the table from the schema, : reload the table. : BUT... : You should rarely have to! Under normal operations, an application will : add to a table, AND PURGE FROM A TABLE, keeping a set of data which has a You obviously live in a more ideal world than I do!!! Which planet do you hire your app-developers/designers from??? : consistent size. All of your tables will have a max and min size, (before : and after the purge program runs) and you may as well leave the extents : sized for the max size and not have to worry about reclaiming tablespace. : Think about this! You cannot "solve your space problems" by adding another : disk. That disk too will fill up after some period of time and then you : are back where you are today. How big is each table going to get? What : are you going to do about it when it gets that big--purge, archive & : delete, store on optical, or what? In my experience, most application developer/designers assume that somebody else is going to produce a periodic purge routine --- end-of-year maintenance routine, conscientious DBA or some such. The only apps I remember that designed it in are general accounting ones. : If you are having to reclaim space very often then you need to size your : tables smaller and/or purge more often. After some period of time (depends : on data volatility) you should do one of the above just to keep your data : from looking like swiss cheese, but that's a different issue. : Also see the discussion about sizing extents, so you can plan for the : growth of your database to its operating size, and then have extent : space adequate for your needs. : __________________________________________________________________ : | Clem Akins Standard Disclaimers Apply | : |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | : | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | : |________________________________________________________________| : -- graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.