Violations Tables - IFX 11.70

Hi Guys, We have an application which occasionally ends up inserting duplicate data into a table (not ideal I know). We use the Informix violations table to work around this problem. This works well, but occasionally (twice a year?) violations tables seem to stop working. We need run something like this to fix it. drop table items_vio; drop table items_dia;START VIOLATIONS TABLE FOR items; SET CONSTRAINTS FOR items FILTERING WITHOUT ERROR; Any ideas what would cause it? Is there some admin tasks or something that might do it? Restarting the engine? Or is it just a bug? I'm sure there weren't any schema changes to that table. IBM Informix Dynamic Server Version 11.70.FC2GE Redhat Linux 5 Cheers, James Brunskill [cid:image001.jpg@01CE5B87.FFA50340] Process Information Lead Fonterra Co-operative Group Limited james.brunskill@fonterra.com<mailto:james.brunskill@fonterra.com> Phone: +64 7 850 7738 (ext 77808) Mobile: +64 21 2400 215 MES Support: +64 7 849 7895 Fonterra Co-operative Group Limited PO Box 459, Hamilton, 3240, Automation and Process Control, Fonterra Te Rapa, SH1, Hamilton, New Zealand [cid:image002.png@01CE5B87.FFA50340] ________________________________ DISCLAIMER This email contains information that is confidential and which may be legally privileged. If you have received this email in error, please notify the sender immediately and delete the email. This email is intended solely for the use of the intended recipient and you may not use or disclose this email in any way.

Why not just put a unique index/constraint on the table and end the problem altogether? Art On May 27, 2013 6:56 PM, "James Brunskill" <James.Brunskill@fonterra.com> wrote: > Hi Guys, > > We have an application which occasionally ends up inserting duplicate data > into a table (not ideal I know). > > We use the Informix violations table to work around this problem. > > This works well, but occasionally (twice a year?) violations tables seem to > stop working. > We need run something like this to fix it. > > drop table items_vio; > drop table items_dia;> START VIOLATIONS TABLE FOR items; > SET CONSTRAINTS FOR items FILTERING WITHOUT ERROR; > > Any ideas what would cause it? > Is there some admin tasks or something that might do it? Restarting the > engine? Or is it just a bug? > > I'm sure there weren't any schema changes to that table. > > IBM Informix Dynamic Server Version 11.70.FC2GE > Redhat Linux 5 > > Cheers, > > James Brunskill > > [cid:image001.jpg@01CE5B87.FFA50340] > > Process Information Lead > Fonterra Co-operative Group Limited > > james.brunskill@fonterra.com<mailto:james.brunskill@fonterra.com> > Phone: +64 7 850 7738 (ext 77808) > Mobile: +64 21 2400 215 > MES Support: +64 7 849 7895 > Fonterra Co-operative Group Limited > PO Box 459, Hamilton, 3240, Automation and Process Control, Fonterra Te > Rapa, > SH1, Hamilton, New Zealand > > [cid:image002.png@01CE5B87.FFA50340] > > ________________________________ > DISCLAIMER > This email contains information that is confidential and which may be > legally > privileged. If you have received this email in error, please notify the > sender > immediately and delete the email. This email is intended solely for the > use of > the intended recipient and you may not use or disclose this email in any > way. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c38a3e7f3a8304ddbb2932

Yes, the table has a unique constraint, but the application stops processing if an insert fails. Violations tables only work with constraints btw. Unique indexes without associated constraint still return an error for a duplicate key, even with violations tables turned on. Cheers, James Brunskill Process Information Lead Fonterra Co-operative Group Limited james.brunskill@fonterra.com direct +64 7 850 7738 (ext 77808) mobile: +64 21 2400 215 Fonterra Co-operative Group Limited, Te Rapa Dairy Factory, Hamilton, New Zealand -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, 28 May 2013 11:04 a.m. To: ids@iiug.org Subject: Re: Violations Tables - IFX 11.70 [30360] Why not just put a unique index/constraint on the table and end the problem altogether? Art On May 27, 2013 6:56 PM, "James Brunskill" <James.Brunskill@fonterra.com> wrote: > Hi Guys, > > We have an application which occasionally ends up inserting duplicate > data into a table (not ideal I know). > > We use the Informix violations table to work around this problem. > > This works well, but occasionally (twice a year?) violations tables > seem to stop working. > We need run something like this to fix it. > > drop table items_vio; > drop table items_dia;> START VIOLATIONS TABLE FOR items; > SET CONSTRAINTS FOR items FILTERING WITHOUT ERROR; > > Any ideas what would cause it? > Is there some admin tasks or something that might do it? Restarting > the engine? Or is it just a bug? > > I'm sure there weren't any schema changes to that table. > > IBM Informix Dynamic Server Version 11.70.FC2GE Redhat Linux 5 > > Cheers, > > James Brunskill > > [cid:image001.jpg@01CE5B87.FFA50340] > > Process Information Lead > Fonterra Co-operative Group Limited > > james.brunskill@fonterra.com<mailto:james.brunskill@fonterra.com> > Phone: +64 7 850 7738 (ext 77808) > Mobile: +64 21 2400 215 > MES Support: +64 7 849 7895 > Fonterra Co-operative Group Limited > PO Box 459, Hamilton, 3240, Automation and Process Control, Fonterra > Te Rapa, SH1, Hamilton, New Zealand > > [cid:image002.png@01CE5B87.FFA50340] > > ________________________________ > DISCLAIMER > This email contains information that is confidential and which may be > legally privileged. If you have received this email in error, please > notify the sender immediately and delete the email. This email is > intended solely for the use of the intended recipient and you may not > use or disclose this email in any way. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c38a3e7f3a8304ddbb2932 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I do understand that. I guess my question should have been, "Why not just rely on the constraint and not filtering and the violations tables?" So, with your answer in hand, I would ask, "Since the application is the real problem, then can't you fix the application so that it handles the duplicate key/unique constraint violation properly?" I also understand that the violations table and setting the constraint to filtering should just work, and that's a bug to report to IBM, but really, if the app handles the error properly you don't need the filtering set at all. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 27, 2013 at 9:22 PM, James Brunskill < James.Brunskill@fonterra.com> wrote: > Yes, the table has a unique constraint, but the application stops > processing > if an insert fails. > > Violations tables only work with constraints btw. > Unique indexes without associated constraint still return an error for a > duplicate key, even with violations tables turned on. > > Cheers, > > James Brunskill > Process Information Lead > Fonterra Co-operative Group Limited > james.brunskill@fonterra.com direct +64 7 850 7738 (ext 77808) mobile: > +64 21 > 2400 215 > Fonterra Co-operative Group Limited, Te Rapa Dairy Factory, Hamilton, New > Zealand > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Tuesday, 28 May 2013 11:04 a.m. > To: ids@iiug.org > Subject: Re: Violations Tables - IFX 11.70 [30360] > > Why not just put a unique index/constraint on the table and end the problem > altogether? > > Art > On May 27, 2013 6:56 PM, "James Brunskill" <James.Brunskill@fonterra.com> > wrote: > > > Hi Guys, > > > > We have an application which occasionally ends up inserting duplicate > > data into a table (not ideal I know). > > > > We use the Informix violations table to work around this problem. > > > > This works well, but occasionally (twice a year?) violations tables > > seem to stop working. > > We need run something like this to fix it. > > > > drop table items_vio; > > drop table items_dia;> > START VIOLATIONS TABLE FOR items; > > SET CONSTRAINTS FOR items FILTERING WITHOUT ERROR; > > > > Any ideas what would cause it? > > Is there some admin tasks or something that might do it? Restarting > > the engine? Or is it just a bug? > > > > I'm sure there weren't any schema changes to that table. > > > > IBM Informix Dynamic Server Version 11.70.FC2GE Redhat Linux 5 > > > > Cheers, > > > > James Brunskill > > > > [cid:image001.jpg@01CE5B87.FFA50340] > > > > Process Information Lead > > Fonterra Co-operative Group Limited > > > > james.brunskill@fonterra.com<mailto:james.brunskill@fonterra.com> > > Phone: +64 7 850 7738 (ext 77808) > > Mobile: +64 21 2400 215 > > MES Support: +64 7 849 7895 > > Fonterra Co-operative Group Limited > > PO Box 459, Hamilton, 3240, Automation and Process Control, Fonterra > > Te Rapa, SH1, Hamilton, New Zealand > > > > [cid:image002.png@01CE5B87.FFA50340] > > > > ________________________________ > > DISCLAIMER > > This email contains information that is confidential and which may be > > legally privileged. If you have received this email in error, please > > notify the sender immediately and delete the email. This email is > > intended solely for the use of the intended recipient and you may not > > use or disclose this email in any way. > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c38a3e7f3a8304ddbb2932 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0141aa8a58e17e04ddc47486