Re: Informix V2 - fragmenting tables. Causes and cures. Advice please.

Bryan Tonnet wrote: > > IanClarkUK wrote: > > A potential client has had an application running on his Unix box for 6 > > years now. The users run 4gl programs that interact with the database. > > Am I right in thinking that both data and index tables do fragment? What > > happens to the space that becomes free when records are deleted? > > Given that the Version of Informix is early and less likey to have any of > > the newer, fancier, tools to assist me, will it be necessary to perform > > the following steps to help clean up the database: > > > > - dbschema to a file for recreating database > > - unload all tables to ascci files > > - drop database > > - recreate database from dbschema file > > - load all tables from ascii files > > > > Its been a long time, and I can't be exact with the version (may have even > been 1.10), but I remember having to rebuild data and tables on an SE > database by altering the lengths of character index fields. e.g. A character > index field is char(20). Altering to char(21) and then back to char(20) > rebuilds the indexes and data (twice) for the table. If this works for you, > then you wont need to worry about the system catalogue information. > > Someone wiser will likely point out what's wrong with this method, but I dont > recall having any problems at the time. Good luck.... > > Bryan Tonnet Depending on the version of Informix(<5.x), you could just alter table and not change the column size i.e. alter a char(20) to char(20) and it would rebuild and recover empty record. 5.x and greater they just seem to ignore the rebuild unless you change the field size. If you want to move the table to another dbspace, i.e. Informix Online, you could create an identical table in the new dbspace and run an sql to copy from one to the other: insert into newtable select * from oldtable;drop oldtable; rename table newtable to oldtable; Lots of ways to do it.