Err 239 on Insert to serial column

Hello, I have recently been presented with a problem whereby the error 239 is generated from dbaccess when trying to insert a row. ie INSERT INTO XXXXX VALUES (0,"DDF",1,"etc") where the first column is the serial. There is a unique index on the column which was dropped and re-created with no success. After further investigation the serial value was found to be 70650. I then used the following statement to force the insert.. INSERT INTO XXXXX VALUES (70651,"DDF",1,"etc") which worked. After this, using the value 0 to insert into the serial field worked and I was able to put in the values of 70652, 70653 etc. Has anybody seen this problem anywhere else? The system is Dynamic Server 7.3.UC8 running on a HP Thanks in advance Stu Worrall

In article <7u31ml$r4c$1@news4.svr.pol.co.uk>, Stuart Worrall <Stuart@sw orrall.freeserve.co.uk> writes >Hello, > >I have recently been presented with a problem whereby the error 239 is >generated from dbaccess when trying to insert a row. ie INSERT INTO XXXXX >VALUES (0,"DDF",1,"etc") where the first column is the serial. > >There is a unique index on the column which was dropped and re-created with >no success. > >After further investigation the serial value was found to be 70650. I then >used the following statement to force the insert.. INSERT INTO XXXXX >VALUES (70651,"DDF",1,"etc") which worked. > >After this, using the value 0 to insert into the serial field worked and I >was able to put in the values of 70652, 70653 etc. > >Has anybody seen this problem anywhere else? > Yes, using value 0 means use the next value. if you insert a fixed value you can change the next value to be used. Also you can alter the serial column to change the next value. >The system is Dynamic Server 7.3.UC8 running on a HP > >Thanks in advance > >Stu Worrall > > > > -- David Williams

Yes I realise that you have to use zero, the problem was that error 239 was being reported when we tried to insert a row. It was only fixed after forcing the next serial value. Thanks Stu Worrall David Williams <djw@smooth1.demon.co.uk> wrote in message news:B5E+xFAolRB4EwjB@smooth1.demon.co.uk... > In article <7u31ml$r4c$1@news4.svr.pol.co.uk>, Stuart Worrall <Stuart@sw > orrall.freeserve.co.uk> writes > >Hello, > > > >I have recently been presented with a problem whereby the error 239 is > >generated from dbaccess when trying to insert a row. ie INSERT INTO XXXXX > >VALUES (0,"DDF",1,"etc") where the first column is the serial. > > > >There is a unique index on the column which was dropped and re-created with > >no success. > > > >After further investigation the serial value was found to be 70650. I then > >used the following statement to force the insert.. INSERT INTO XXXXX > >VALUES (70651,"DDF",1,"etc") which worked. > > > >After this, using the value 0 to insert into the serial field worked and I > >was able to put in the values of 70652, 70653 etc. > > > >Has anybody seen this problem anywhere else? > > > > Yes, using value 0 means use the next value. if you insert a fixed > value you can change the next value to be used. Also you can alter the > serial column to change the next value. > > >The system is Dynamic Server 7.3.UC8 running on a HP > > > >Thanks in advance > > > >Stu Worrall > > > > > > > > > > -- > David Williams

Everyone is missing Stuart's point! Let me try Stuart: He has a table where the MAX(serial_col) = 70650 and where the next serial value should be 70651. When he tried to insert with serial_col = 0 to get the next value auto inserted it failed with, an apparently spurious, duplicate key error (-239). However when he forced the very same value into serial_col (ie 70651) it was taken without error showing that the value was indeed NOT a duplicate! Now when he continued to insert using serial_col = 0 the sequence continued with the very next value, 70652, and continues to work without error. He did nothing flaky here folk and wants to know if any of us has seen the same anomolous behavior. So have we? Honestly, not I. One question, when you say the unique index was dropped and recreated with "no success" do you mean the rebuild did not solve the problem or that you could not rebuild the index? I suspect the former, but I have to ask. Art S. Kagel Stuart Worrall wrote: > > Hello, > > I have recently been presented with a problem whereby the error 239 is > generated from dbaccess when trying to insert a row. ie INSERT INTO XXXXX > VALUES (0,"DDF",1,"etc") where the first column is the serial. > > There is a unique index on the column which was dropped and re-created with > no success. > > After further investigation the serial value was found to be 70650. I then > used the following statement to force the insert.. INSERT INTO XXXXX > VALUES (70651,"DDF",1,"etc") which worked. > > After this, using the value 0 to insert into the serial field worked and I > was able to put in the values of 70652, 70653 etc. > > Has anybody seen this problem anywhere else? > > The system is Dynamic Server 7.3.UC8 running on a HP > > Thanks in advance > > Stu Worrall

Hi all Thanks to everyone who replied to my earlier message. In response to some of the questions Art S. Kagel wrote: >One question, when you say the unique index was dropped and recreated >with "no success" do you mean the rebuild did not solve the problem or >that you could not rebuild the index? I suspect the former, but I have >to ask. The index was dropped and re-created in order to try and fix the problem which did not work. It was only after we forced the next logical serial value of 70651 that the problem deceased. Rick wrote: >What did an "oncheck -pt <db:table>" show for the "current serial >value" before Stuart performed the successfully insert. Im afraid that I was not aware of "oncheck -pt" but I have looked at this and it will certainly be very useful in the future if the problem occurs again. As for the problem itself, it won't really be a bother if it only happens once. However if it gets to the stage where it happens twice in two weeks as in Dave Killoughs case then Informix will have to look into it further than they already have. Informix suggested running "oncheck -cDI" which checked out ok but you would think that if the table was corrupted, forcing the next value would not fix it. Thanks again to Art S. Kagel for clarifying my mail into a more readable form. Thanks very much Stu Worrall. In article <3806043C.EEC2F59A@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes Stuart Worrall Lintel Software Consultancy Ltd e-mail: stuart@NO.SPAMlintel.co.uk web: www.lintel.co.uk Tel: 01244 316297 Fax: 01244 400171