 |
IDS Forum
Re: Someone have a technique to alter tables w....
Posted By: Cesar Inacio Martins
Date: Thursday, 3 May 2012, at 7:51 a.m.
In Response To: Re: Someone have a technique to alter tables w.... (Fernando Nunes)
No, no OPTOFC and any other "tunning" variable at client side...
On 2/5/2012 21:42, Fernando Nunes wrote:
> Also... Do you have OPTOFC set on that environment?
> Regards.
>
> On Thu, May 3, 2012 at 1:34 AM, Fernando Nunes<domusonline@gmail.com>wrote:
>
>> So, the PMRs don't reflect the situation completely...
>> I've been trying to reproduce it without success. But I'm using 11.70. I
>> don't see how that would make any difference, but I'll try with 11.50 (the
>> customer I referenced uses 11.5)
>>
>> As for the script:
>>
>> http://onlinedomus.com/informix/viewvc.cgi/PUBLIC/informix/scripts/ix/
>>
>> ixtableuse -h
>>
>> Ping me if you have any doubts. The option to use SQL Admin API is very
>> slow, but allows remote execution...
>> But in any case, mass session killing would never be the perfect
>> solution...
>> Do you have the isam error?
>> Regards.
>>
>> On Thu, May 3, 2012 at 1:07 AM, Cesar Inacio Martins<
>> cesar_inacio_martins@yahoo.com.br> wrote:
>>
>>> Hi Fernando,
>>>
>>> Is true, this two PMRs are when I try create online index what is the
>> first
>>> situations where give me more throuble with the production.
>>> So I not use all procedure (ifx_dirty_read + transaction + lock
>> exclusive +
>>> grant + onmode -z) for this case
>>> The procedure what I user the ifx_dirty_read and etc is when I need to
>> add
>>> a
>>> column over the table and other when I need add a new constraint.
>>> The behave still the same at all situations.... after finish all
>>> alter/index
>>> and etc new sessions still getting -710...
>>>
>>> Please , if you can send or show me where have this script to identify
>> the
>>> tables, I appreciate .
>>>
>>> ________________________________
>>> De: Fernando Nunes<domusonline@gmail.com>
>>> Para: ids@iiug.org
>>> Enviadas: Quarta-feira, 2 de Maio de 2012 19:00
>>> Assunto: Re: Someone have a technique to alter tables w.... [26924]
>>>
>>> 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.
>>>
>>>
>>>
>>>
>>
> *******************************************************************************
>>> 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...
>>
>> --e89a8fb1edea5049f804bf16f576
>>
>>
>>
>>
> *******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
