Re: Someone have a technique to alter tables with FK ?

JP: The original poster is not using any stored procedures. This is independent SQL statements. 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 Wed, May 2, 2012 at 1:51 PM, <jpierrot@chubb.com> wrote: > This was very frequent with IDS 7.3x and the work around was to run > 'update statistics for procedure' since each time it happens you can also > notice that the sysprocplan and sysprocedures tables are locked. Thus you > had first to clear the locks and update stats for the procedures. I had a > few PMR open at the time with IBM and it was promised that the > re-optimization of the stored procedures plan would be handled more > efficiently in IDS11. From what you are saying it seems that it has not > been the case, but so far I may be just lucky, for since I upgraded to > IDS11 I have not seen it in my environments yet. > > > jp > > [image: Inactive hide details for Cesar Inacio Martins ---05/02/2012 > 01:37:12 PM---Hi Marco, Thank you for your answer.]Cesar Inacio Martins > ---05/02/2012 01:37:12 PM---Hi Marco, Thank you for your answer. > > > From: > Cesar Inacio Martins <cesar_inacio_martins@yahoo.com.br> > To: > informix-list@iiug.org > Date: > 05/02/2012 01:37 PM > Subject: > Re: Someone have a technique to alter tables with FK ? > Sent by: > informix-list-bounces@iiug.org > ------------------------------ > > > > Hi Marco, > > Thank you for your answer. > > My big problem is not able to reproduce this behave. I try sometimes and > every time just works... > > On the last 12 months , we already suffer with this problem at least 6 > times. The last time was one week ago. > > I have pretty sure at least 3 times we just create a new index or > drop/recreate some index over a table, never change they columns. > Other we add a new columns and the last we add a new constraint and new > trigger. > > To discovery what happens, I tried keep the focus at the most "simple" > situation what is create a new index (online) and where is more reasonable > not affect others tables thru they FK... > > When this problem start, I not confirm is 100% of the applications and > user, but is a lot because the application log start to fill with errors > -710 and the tels start to ring here.... and when this occur, do not matter > what the user does, when they start a new session, the -710 will popup... > > > > > On 2/5/2012 12:46, Marco Greco wrote: > > > > On 02/05/12 14:37, Cesar Inacio Martins wrote: > Hi, > > 11.50 FC9X6 , AIX 6.1 > > We suffer with one situation at our production where just not > found a > acceptable way to work yet... > I already open a PMR (for me this is a defect) but for IBM > support is just > "work as design" .... so, no solution... > Just a correction / better documentation was made. > > > THE PROBLEM > - When I alter one table (any changes cited bellow), no > matter if I get the > exclusive lock for this table and all others what exists any > FK from/to it... > new users sessions, when try update/insert/delete any for > this references > tables get the error -710 Table <table-name> has been > dropped, altered, or > renamed. > - create/drop index online/offline OR add/drop trigger OR > add/modify/drop > column OR alter any constraint... > > > TODAY, THE UNIQUE SOLUTION WHEN THIS OCCUR: > - Stop all our production, kill all users (put the instance > in quiescent) and > back online immediately . > > > > HOW I PROCEED WHEN NEED TO CHANGE SOME TABLE: > - set IFX_DIRTY_WAIT > - open dbaccess , start new transaction (begin work) > - lock table in exclusive mode > - grant a dummy select over the table (lock sys*) > - At other session, kill all users what is accessing this > table (identifying > with onstat -g opn + onmode -z) > - execute the alter over the table > - commit > If the table have any FK from/to it , I lock and kill users > for this tables too. > But, at 90% of the situations , after all executed > successfully , the system > stuck with -710 error. > Do not matter if the user close all application and open a > new session, the > error -710 persist. > > > THE ENVIRONMENT > - IFX 11.50 FC9X6 , AIX 6.1 , 4GL 7.32 > - Is a centralized 24x7 system what control all plants of the > company . > - Avg of 1800 sessions concurrent at high peak time and 400 > sessions at lower > peak time. > - User sessions: 70% incoming from 4GL system (most important > and where suffer > with the problem) > - User sessions: 25% incoming from Web/Java system > - User sessions: 5% others.... > - Database have AUTO_REPREPARE and STMT_CACHE active > - The isolation used is 99% 'dirty read' . > - The 4GL system work mostly with prepared SQLs where they > prepare at beginner > of 4GL connection then keep this prepare active during they > execution. > > > > CONSIDERATIONS > - If check with onstat -g dic , always have a refcnt and > dirty over the tables > involved. This before , during and after the alter . Even > during the alter, > consider the table in exclusive lock + lock over sys* (grant > select). > - After the alter, appear new references on onstat -g dic. > (with dirty and > without dirty) > - After finish all alters, if the user got erro -710, close > all system , open > it again e try again, they stuck at -710 again and again and > again and again.... > - I try simulate the problem, but with few users, this behave > do not occur. > - Bellow is a stack trace (trapped with onmode -I 710) when > the user tryed > insert a register over a table where is relationed with the > table altered (in > this case, added a new index online over columns