 |
IDS Forum
Re: Someone have a technique to alter tables w....
Posted By: Fernando Nunes
Date: Wednesday, 2 May 2012, at 6:24 p.m.
In Response To: Re: Someone have a technique to alter tables w.... (Art Kagel)
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
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
