Re: Someone have a technique to alter tables with

Prior to 11.50 you could only handle this in your code by adding a test in your applications after the execution of a prepared statement or the opening of a cursor against a prepared statement so that if the operation returns SQLCODE == -710 then reprepare the statement and reexecute the statement or redeclare and reopen the cursor and all would be well. There is now another way: You can try setting the ONCONFIG parameter or the environment variable AUTO_REPREPARE to '1' which tells the engine to automatically reprepare any prepared statement or recompile any stored procedure that encounters a -710 error 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 9:37 AM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> 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 > > > > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > --14dae9340661131aec04bf0ef83a