Re: How to reduce SE .dat & .idx file sizes?

[posted and mailed] Alan W. Jurgensen wrote: > we're running informix SE version 7.X on AIX RS/6000. I have some > development databases I want to trim the DB file sizes down on... > [...] > Tried the "ALTER INDEX idxname TO CLUSTER; " > suggestion in section 8.1, that didn't do it... Maybe the index "idxname" was already a clustered index before you issued the command. That could be a possible explanation why your command didn't work. Was there any error message? Another problem might be insufficient free space on your filesystem to accomodate the newly created table. What "ALTER INDEX idxname TO CLUSTER" does is copy all records and indexes into new files, physically ordering the rows in the order of the chosen index. After that process is finished, the old .dat/.idx files are deleted and the new .dat/.idx files renamed to the old filenames. This process will efficiently eliminate all "holes" in the database files. However, if there are no such "holes" (records marked as deleted), the newly created files will not be much smaller than the old ones. Remember that records marked as deleted will be overwritten with newly inserted records, so if your delete and insert activities are about balanced out, no space will be saved by the ALTER INDEX TO CLUSTER method. What you could try is ALTER INDEX idxname TO NOT CLUSTER; ALTER INDEX idxname TO CLUSTER; if index idxname was already clustered. HTH, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3413 | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+