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

ianclarkuk@aol.com (IanClarkUK) wrote: :Once you have stopped laughing at the thought of someone still using :Version 2 Informix can you please help me to consider the issue of table :fragmentation. :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? As you delete and later insert records, yes it is fragmented. : What :happens to the space that becomes free when records are deleted? In the data files at least it is reused upon the next insert into that table. :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 I assume this is SE, (was Turbo/Online avaliable as version 2?) and debexport/dbimport wasn't available this early? You will probably have even more problems with Unix file system fragmentation. As different tables have grown and all other kinds of files have been created and deleted your tables may be spread all over the disk(s). You may have to deal with this as well. This isn't allways easy. If you have a separate disk or volume with only data you may back this up via a file oriented back up program, erase all files (better yet reformat the disk) and reload your backup. Your unload/load sequence above may defragment the disk at the Unix level even more and degrade performance if you don't have a separate disk/volume to do the unloads to. In OnLine you allways have that, so there the sequence is ok. :What about system tables that hold details about table columns that must :be invisible when used on forms, eg. for password protection? Will I mess :anything up with ownership/permission issues? I can't see any problems here. :Any comments on the above issue would be very welcome as would any views :regarding the general housekeeping of a database to help enhance :performance. If you realy want to enhance performance a new machine may be a good way of going. Replacing a 6 year old machine with a new one (probably cheaper) could easily give several times the current performance with no changes in the application. This may of course both be too expensive and not an option due to the difficulty of moving the version of Informix. You want to make realy sure it runs on new hardware and os. I haven't tested much the defragmentation way. For indexed lookups of one or a few records by a relatively small number of users, I wouldn't expect much if any performance enhancements at all. For sequensial scans and some other cases you might see significant speedups. In effect some reports may run faster, while normal interactive usage may gain nothing. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company