Reorganising an Informix Database

Hello Friends, I would like to know some few steps on Reorganizing an Informix Database. The tablesizes range from anywhere between just a few Mbytes to 30Gigs.... Does anybody know any simple steps for Reorganizing a Database in Informix? Any basic steps would be very helpful. Also, I would like to know how to estimate future space requirements for huge tables... Thanks a lot.

On Jun 11, 1:24 pm, fun-do <prash...@gmail.com> wrote: > Hello Friends, > > I would like to know some few steps on Reorganizing an Informix > Database. The tablesizes range from anywhere between just a few Mbytes > to 30Gigs.... > > Does anybody know any simple steps for Reorganizing a Database in > Informix? > Any basic steps would be very helpful. > > Also, I would like to know how to estimate future space requirements > for huge tables... > > Thanks a lot. In general, Informix databases don't require reorganization except under specific circumstances: 1- You've deleted a large number of rows and the table(s) will never reuse that freed space, so you'd like to release it for reuse by other tables. 2- You perform significant numbers of sequential processing of a large percentage of the data in a particular table and would like the table to physically match the processing order by clause to speed processing for those requests. 3- You want to move a table or index to another dbspace or want/need to fragment a table or index to spread the IO load, take advantage of more parallel processing capabilities of the engine, or the table is approaching 16million pages and will no longer fit in a single dbspace. 4- Your table has a large number of extents and is either approaching the maximum number of extents (~200) or you typically process large subsets of the data so that you have to access many extents inefficiently. Other than that, there's little reason to reorg an Informix database. The indexes are constantly being rebalanced by BTREE cleaner threads, so that's not a concern except shortly after huge deletes. There are several ways to reorg a table: 1- Alter some index TO CLUSTER (if one is already clustered, alter it TO NOT CLUSTER then cluster it again). 2- Export the data, drop and rebuild the table, reload the data. (Usually indexes are rebuilt after the data are loaded. 3- ALTER FRAGMENT FOR TABLE <tablename> INIT IN <dbspace name or fragmentation clause>; I prefer #3 myself when it's practical. Art S. Kagel