compressing .dat files
Posted in 1993
Topics: Storage & Space Management, Migration, Import/Export & Data Conversion, Clustering, Grid & MACH11
My project uses the "alter table" command to compress .dat files in our
database. For example, if we have a table whose schema is:
create table tab1 (
field1 integer,
field2 char(10)
);
we would issue the following sql statement to compress the table:
alter table tab1 modify (field1 integer);
This would have the side effect of re-building the .dat and .idx files
and all of the holes in the .dat file would be gone.
This "feature" is very important to our product.
Unfortunately, this "feature" was considered to be a bug by INFORMIX and
they were kind enough to "fix" it in 4.1 version of the Standard Engine.
I spoke with INFORMIX Tech Support and they said the only work-around was
to create a cluster index on the table. Since we go through this consolidation
procedure when our system is already low on space we do not want to waste
the extra space to store the index.
I know we could "unload" each table, "drop" the table, "create" the table
and "load" the table but this also requires space to hold the ascii unload
version of the data.
Does anyone know if there is another way to compress these datafiles and
minimize the amount of disk space used at the same time?
Thanks,
Jim Claffy