friedl@vsi.COM (Stephen J. Friedl) (02/20/89)
Hi folks,
We just finished tracking down a really ugly problem with
Informix-SQL version 2.10, and maybe we can save the netsters
some time with this. Our specific environment is an AT&T 3B15
running System V Release 3.1.1 with shared memory locking, and
we're using v2.10.03D (both SQL and ESQL/C).
The bug: indexes with large numbers of duplicates are subject to
corruption in certain circumstances. The bug is in the duplicate
compression algorithm -- probably in C-ISAM -- and if a set of
records with the common duplicate key is deleted and then added
back, index corruption may occur.
We had this problem in an accounting system when we did
a post operation on the main journal file. This file is indexed
on <company, month>, and there are of course many duplicates here.
The post would remove all the entries for a given <company, month>
and regenerate them: this would often be almost an identical
set of records.
The workaround is to add a serial column to the table and
put this column at the tail end of each index -- this makes the
index unique. We've not found that this hurts performance much,
and it appears to have *completely* solved the problem.
Many thanks to Gretchen in Informix Tech Support for her
help in tracking this down. Apparently, this bug just showed up
in the 2.10.03 release, and they have a fix that is due out soon.
Steve
--
Stephen J. Friedl 3B2-kind-of-guy friedl@vsi.com
V-Systems, Inc. I speak for you only attmail!vsi!friedl
Santa Ana, CA USA +1 714 545 6442 {backbones}!vsi!friedl
--------Barbara Bush on... on... No, I just can't do it...--------