I faced one issue in one of the programs {ESQL-C} in development which interacts with Informix. Before touching the program for enhancement {current production version}, it throws -268 for duplicates now after the enhancement it is throwing -239 in test.
Please note: The duplicates error code -268 is fine as we handle that inside the program. We are expecting duplicate insert. My question is all our duplicate inserts give SQLCODE -268 while only one started giving -239 in TEST after our enhancement. The program handles -268 not -239. So I am curious to find out what can be the reasons the INSERT statement started giving -239 instead of -268.

Hi san,

errors -239 and -268 deal with the same problem, but in a different way.
Error -239 points to a duplicated value revealed by a UNIQUE INDEX.
Error -268 point to a duplicated value detected by a UNIQUE CONSTRAINT, which is not exactly the same if we consider the following:
- a UNIQUE CONSTRAINT will rely on an UNIQUE INDEX and will create it if not existing already.
- a UNIQUE INDEX doesn't oblige to have a UNIQUE CONSTRAINT.
In other words, you can have a unique index without any unique constraint, but you cannot have a unique constraint without a unique index. The interest of the unique constraint is that you cannot drop the unique index unless you drop the constraint.

So my guess is that you may have slight differences between your prod and test environment, ie one has the unique index and no unique constraint, giving an error -239, and the other one has the unique constraint, thus giving error -268.

Another possible cause would be if you use different ISOLATION LEVELS ( committed read OR REPEATABLE READ ), which would produce similar behaviour.

In any case, you will want to handle error -268 and -239 exactly the same way.

Hope this helps
Eric

thanks Eric, Our DBA expert found the reason for us. The primary key order were different in production and Test. The DBA team accidently change the PK order in TEST while adding one new column. So INFORMIX checks the PK constraints first if the order is proper and then UNIQUE INDEX constraint. As the order was changed in TEST, INFMX was checking on UNIQUE index as mentioned by expert and it is throwing -239 instead -268. From application development point of view, both are same but from DB ADMIN respect they are different objects.
He went ahead and changed the order of Primary Key just like production and the program worked fine. Yes he also mentioned like you to incorporate -239 along with -268 in the error routine. I am thinking to do that.

Thanks for the nice post. It was really helpful.

Regards,
San