reclaiming tblspace

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?) 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 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? 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 | |________________________________________________________________|