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

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 what not involve the FK) > > 0x00000001000b76a8 (oninit)afstack > 0x00000001000b97bc (oninit)afhandler > 0x0000000100130b90 (oninit)check_traperror > 0x00000001001faa94 (oninit)sqerr > 0x00000001001fa550 (oninit)sqerr1 > 0x00000001001f9e54 (oninit)sqnameerr > 0x000000010066e4dc (oninit)chkmajvers > 0x000000010066eb24 (oninit)openrel > 0x00000001006523b4 (oninit)chkparent > 0x000000010065b2d0 (oninit)chkrowcons > 0x0000000100782790 (oninit)addone > 0x0000000100784e10 (oninit)insone_next > 0x0000000100769994 (oninit)doinsert > 0x000000010041cff8 (oninit)aud_doinsert > 0x00000001004259f8 (oninit)excommand > 0x000000010044b3bc (oninit)sq_execute > 0x000000010022463c (oninit)sqmain > 0x000000010037c160 (oninit)listen_verify > 0x000000010037a608 (oninit)spawn_thread > 0x0000000100dd528c (oninit)startup > > > > At my point of view, Informix isn't able to work 100% online if you need to > change your scheme (add a simple index using online mode) when you use > referential integrity. > I believed this is because our method of work (prepare at start of > application, and keep this prepared active to reuse it), anyway, should we > suffer with this consequences!??? > > Is there some technique to alter a table what I miss here ? > > Cesar > > Hi Cesar, I am the developer who originally wrote AUTO_REPREPARE, and if you say that you have AUTO_REPREPARE set to 1 and whenever you alter a table all client applications using it yield a -710, then I would dispute this is expected behaviour. If, on the other hand, you experience random -710 from one client at most, then that is expected behaviour: there is a window of opportunity in between the time a session checks that tables in use by a statement haven't changed and the time in which they actually get used in which another session could alter the table. Bear also in mind that AUTO_REPREPARE cannot handle certain alters: for instance if you drop a column that a table is using in a select, when executed that select will have to yield a -710 because the columns expected by the client application do not match the columns that the engine can provide. Other examples would be adding a column on a table used by an insert without a column list. If you can permanently reproduce a -710, let me know, and I'll try to figure it out 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