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

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 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.