 |
IDS Forum
Re: Someone have a technique to alter tables w....
Posted By: Art Kagel
Date: Wednesday, 2 May 2012, at 6:27 p.m.
In Response To: Re: Someone have a technique to alter tables w.... (Fernando Nunes)
I agree, Cesar's apps that were restarted after the ALTER should not be
receiving -710 errors any longer.
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 6:24 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> I don't challenge the fact that they're there. I just don't understand why
> new sessions would have to use them...
> But honestly I don't have enough knowledge to explain this, or even to
> assert if it's acceptable or not.
> Since tech support accepted it as normal I will not go against it unless
> something new pops up...
> Of course, "normal" here doesn't mean desirable.... The situation as Cesar
> describes it doesn't look good. But I think this is a bit strange since I
> work frequently on a customer with a lot of sessions (~2000-3000), a lot of
> activity, some very hot tables, 4GL and web applications and I never
> noticed this issue.
> I hit the issue of being very difficult to ALTER tables (which triggered
> the research that led to the blog article), but once I manage to ALTER,
> everything works...
>
> On the other hand it's not usual to CREATE INDEX ONLINE on those very hot
> tables....
>
> Cesar: Do you have the ISAM error?
>
> Regards.
>
> On Wed, May 2, 2012 at 11:10 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > IB, Fernando, that dirty DD Cache entries are pinned in the cache by
> > existing query plans for prepared statements and running SPL. I've always
> > suspected that they are what's used to identify queries that need to be
> > errored out with -710. Don't know for sure.
> >
> > 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 6:00 PM, Fernando Nunes <domusonline@gmail.com
> > >wrote:
> >
> > > Cesar:
> > >
> > > Both PMRs refer only to a CREATE INDEX ONLINE. The APAR opened for the
> > > documentation refers only to that. The feature request created also
> > refers
> > > only to that.
> > > Is there any other situation where you've seen this behavior?
> > >
> > > 1- I noticed once you tried to kill all the sessions that were using
> the
> > > table, as shown by "onstat -g opn". This will not show all the relevant
> > > tables. I can get you a better script.
> > > 2- You mention a process where you set IFX_DIRTY_WAIT, lock the
> > systable's
> > > record etc... This looks like something we do to ALTER TABLE (I
> > documented
> > > it in a very old blog post). I suppose you don't do that to create an
> > index
> > > online... So, again, is the ONLINE index the only situation? If it
> isn't,
> > > the documentation change and feature request looks insufficient to
> cover
> > > all the scenarios....
> > > 3- Accordingly to the PMR, the dirty entries in the dictionary cache
> may
> > be
> > > related. At first glance it feels odd... (if there are multiple dirty
> > > entries and one clean entry, why not use the clean?). But this is way
> out
> > > of my league... maybe Marco can help
> > >
> > > I was trying to reproduce it and I managed to get -710. But it went
> away
> > > without even closing the session... but I believe I'm still far from a
> > > reproduction... I will continue to look into this as time permits.
> > >
> > > On Wed, May 2, 2012 at 7:36 PM, Cesar Inacio Martins <
> > > cesar_inacio_martins@yahoo.com.br> wrote:
> > >
> > > > Hi Art,
> > > >
> > > > > The statement cache, when I open the first PMR it was disabled
> > because
> > > > > of other problem with it (STMT_CACHE = 1 ), we enable it
> (STMT_CACHE
> > =2
> > > > > ) when we update to FC9 or FC9X6 (I don't remember with sure what
> of
> > > > both).
> > > > > Honestly I don't remember if I try flush the stat_cache.
> > > >
> > > > On 2/5/2012 15:10, Art Kagel wrote:
> > > > > You are correct, the -710 should not affect new sessions. I noticed
> > on
> > > > one
> > > > > post that you have statement caching turned on. What happens if you
> > > > > disable the statement cache? That is probably where the invalid
> query
> > > > > plans are being stored and reused by the new applications with
> nearly
> > > > > identical statements to run.
> > > > >
> > > > > 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:49 PM, Cesar Inacio Martins<
> > > > > cesar_inacio_martins@yahoo.com.br> wrote:
> > > > >
> > > > >> Hi Art,
> > > > >>
> > > > >> Considering the application is closed when reach -710 (the session
> > > with
> > > > >> informix is closed) the PID of 4GL die...
> > > > >> When the user start the application again, the problem should
> > > persists?
> > > > >> Since when the application was restarted, naturally all prepared
> > > > >> statements will be re-executed, so... this isn't enough? (for this
> > > user
> > > > >> in particular)
> > > > >>
> > > > >> Here, the way what the code is structured, isn't viable include
> now
> > a
> > > > >> code to handle this and I not sure if will work since the behave
> > what
> > > I
> > > > >> describe before occur today (new sessions get -710)...
> > > > >>
> > > > >> On 2/5/2012 12:48, Art Kagel wrote:
> > > > >>> If the AUTO_REPREPARE is not solving the situation, then you will
> > > have
> > > > to
> > > > >>> recode your applications to handle the error by repreparing etc.
> > All
> > > of
> > > > >>> the application and middleware code I wrote at Bloomberg years
> ago
> > > was
> > > > >>> coded to do that because prior to version 11.10 this was a much
> > > bigger
> > > > >>> problem. Most -710 errors are internally handled by the engine
> now,
> > > but
> > > > >>> there are still some, like this one of altering a parent table
> in a
> > > > >> foreign
> > > > >>> key relationship, that still can crop up.
> > > > >>>
> > > > >>> 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 11:24 AM, Cesar Inacio Martins<
> > > > >>> cesar_inacio_martins@yahoo.com.br> wrote:
> > > > >>>
> > > > >>>> Hi Art.
> > > > >>>> The database always have the AUTO_REPREPARE active.
> > > > >>>> onstat -c |grep ^AUTO_RE
> > > > >>>> AUTO_REPREPARE 1
> > > > >>>>
> > > > >>>> But we don't try force it thru user environment AUTO_REPREPARE=1
> > ...
> > > > >>>> should ?
> > > > >>>>
> > > > >>>> No Stored procedures are used at this situation, any way we was
> > > tried
> > > > >>>> update statistics for procedure too..
> > > > >>>>
> > > > >>>> On 2/5/2012 12:01, Art Kagel wrote:
> > > > >>>>> 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
> > > > >>>>> <http://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
> > > > >>>>> <mailto: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<mailto:Informix-list@iiug.org>
> > > > >>>>> http://www.iiug.org/mailman/listinfo/informix-list
> > > > >>>>>
> > > > >>>>>
> > > > >>>>
> > > > >>>>
> > > > >>
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > >>>> Forum Note: Use "Reply" to post a response in the discussion
> > forum.
> > > > >>>>
> > > > >>>>
> > > > >>> --14dae9340d59e9b88604bf0f9d2d
> > > > >>>
> > > > >>>
> > > > >>>
> > > > >>
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > >>> Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > >>>
> > > > >>
> > > > >>
> > > > >>
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > >> Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > >>
> > > > >>
> > > > > --14dae9340c0155700404bf1199de
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --
> > > Fernando Nunes
> > > Portugal
> > >
> > > http://informix-technology.blogspot.com
> > > My email works... but I don't check it frequently...
> > >
> > > --20cf3074b01e11c30004bf14d1fb
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --14dae9340e21de3ae504bf14f48c
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3074d4625fad5604bf1524cd
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5299a31931d9c04bf1530d5
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
