-710 when adding foreign key constraint

I have followed the discussion regarding the problem with -710 when adding foreign key constraints and I have some additional input. We have experienced this problem a number of times, now we got the problem again after some alters last saturday. Some facts: - 11.70.FC7 on HP-UX - ER is being used for a number of tables - SPL is used for updates of tables - mix of 4GL and JDBC access - when doing the alters all applications is brought down, this normally prevents problems - the AUTO_REPREPARE is set to 1 The alters was done, a number of foreign key constraints was added to the database. After that -710 errors turns up when trying to update or insert in the tables that was referenced in the foreign keys. It does not matter if the update is done using SPL or from a dbaccess session, there is no way to work with the affected tables, you get an -710 error. It is not a one time thing with a reprepare, you cannot work with the tables. The way we solved this before and the one that solved the problem this time as well is run dostats for the whole database. It does not help to run dostats just for the affected table(s). Another way would be to restart the server I would suppose. I think that dostats somehow empties a number of caches and once this is done everything works as it supposed to. It is not satisfactory however that you have to restart the server or rely on that dostats or some other mechanism will solve the problem. Ulf

On 14/05/12 09:05, ULF ÅKERBERG wrote: > I have followed the discussion regarding the problem with -710 when adding > foreign key constraints and I have some additional input. > > We have experienced this problem a number of times, now we got the problem > again after some alters last saturday. Some facts: > > - 11.70.FC7 on HP-UX > - ER is being used for a number of tables > - SPL is used for updates of tables > - mix of 4GL and JDBC access > - when doing the alters all applications is brought down, this normally > prevents problems > - the AUTO_REPREPARE is set to 1 > > The alters was done, a number of foreign key constraints was added to the > database. After that -710 errors turns up when trying to update or insert in > the tables that was referenced in the foreign keys. It does not matter if the > update is done using SPL or from a dbaccess session, there is no way to work > with the affected tables, you get an -710 error. It is not a one time thing > with a reprepare, you cannot work with the tables. > > The way we solved this before and the one that solved the problem this time as > well is run dostats for the whole database. It does not help to run dostats > just for the affected table(s). Another way would be to restart the server I > would suppose. > > I think that dostats somehow empties a number of caches and once this is done > everything works as it supposed to. > > It is not satisfactory however that you have to restart the server or rely on > that dostats or some other mechanism will solve the problem. > > Ulf > > Ulf - if you can provide a testcase, I'll be happy to fix it for you -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Ulf, Do you have triggers on these tables that call SPL routines? 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 14, 2012 at 4:05 AM, ULF ÅKERBERG < ulf.akerberg@migrationsverket.se> wrote: > I have followed the discussion regarding the problem with -710 when adding > foreign key constraints and I have some additional input. > > We have experienced this problem a number of times, now we got the problem > again after some alters last saturday. Some facts: > > - 11.70.FC7 on HP-UX > - ER is being used for a number of tables > - SPL is used for updates of tables > - mix of 4GL and JDBC access > - when doing the alters all applications is brought down, this normally > prevents problems > - the AUTO_REPREPARE is set to 1 > > The alters was done, a number of foreign key constraints was added to the > database. After that -710 errors turns up when trying to update or insert > in > the tables that was referenced in the foreign keys. It does not matter if > the > update is done using SPL or from a dbaccess session, there is no way to > work > with the affected tables, you get an -710 error. It is not a one time thing > with a reprepare, you cannot work with the tables. > > The way we solved this before and the one that solved the problem this > time as > well is run dostats for the whole database. It does not help to run dostats > just for the affected table(s). Another way would be to restart the server > I > would suppose. > > I think that dostats somehow empties a number of caches and once this is > done > everything works as it supposed to. > > It is not satisfactory however that you have to restart the server or rely > on > that dostats or some other mechanism will solve the problem. > > Ulf > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8eb8226f3704bffc6a61

On 14/05/12 11:19, Art Kagel wrote: > Ulf, > > Do you have triggers on these tables that call SPL routines? > > 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 14, 2012 at 4:05 AM, ULF ÅKERBERG< > ulf.akerberg@migrationsverket.se> wrote: > >> I have followed the discussion regarding the problem with -710 when adding >> foreign key constraints and I have some additional input. >> >> We have experienced this problem a number of times, now we got the problem >> again after some alters last saturday. Some facts: >> >> - 11.70.FC7 on HP-UX >> - ER is being used for a number of tables >> - SPL is used for updates of tables >> - mix of 4GL and JDBC access >> - when doing the alters all applications is brought down, this normally >> prevents problems >> - the AUTO_REPREPARE is set to 1 >> >> The alters was done, a number of foreign key constraints was added to the >> database. After that -710 errors turns up when trying to update or insert >> in >> the tables that was referenced in the foreign keys. It does not matter if >> the >> update is done using SPL or from a dbaccess session, there is no way to >> work >> with the affected tables, you get an -710 error. It is not a one time thing >> with a reprepare, you cannot work with the tables. >> >> The way we solved this before and the one that solved the problem this >> time as >> well is run dostats for the whole database. It does not help to run dostats >> just for the affected table(s). Another way would be to restart the server >> I >> would suppose. >> >> I think that dostats somehow empties a number of caches and once this is >> done >> everything works as it supposed to. >> >> It is not satisfactory however that you have to restart the server or rely >> on >> that dostats or some other mechanism will solve the problem. >> >> Ulf >> Tables used by triggers are covered by AUTO_REPREPARE - however if the REFERENCEd table has triggers and those use tables that have changed, AUTO_REPREPARE might not be able to cope... -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Of the 5 tables affected by the problem one has triggers the others do not. 4 of the 5 is replicated. Ulf

I will try to reproduce it but as we do not get this problem in our development or test servers I am not so sure that I will be able to do it. I am not so certain that this is connected to the AUTO REPREPARE as it does not matter how you try the table refuse to accept inserts or updates. Ulf