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

I still support about 20 sites running an old accounting application using Informix SE 2.1/2.0. (Don't ask why - it works and they don't want to change). The following is my comments on this. In 2.1 Cluster Indexs are available, so I creat scripts that alter indexs to cluster: alter index index_name to cluster; This rebuilds the table plus has the benifit of redoing the indexes. No need to load or unload or alter the table. In 2.0 Cluset indexes where not present so I would create scripst to alter a field BUT use the same definition as it was before. If it was char(20), alter it to char(20). You do not need to do the two steps suggested below. However, neither of these solutitions deals with defragmenting the UNIX file system, you will need another utility to do that. Or, back up the fielsystem, remake the filesystem and restore it. But you need to really make sure you backups work before you do this. Regards - Lester > 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 > ############################################################################# # Lester Knutsen lester@access.digex.net # # Advanced DataTools Corporation Voice: 703-256-0267 # # Grant group privileges for Informix databases with DB Privileges # # Visit our Web page: http://www.access.digex.net/~lester # # Washington Area Informix User Group: http://www.access.digex.net/~waiug # #############################################################################