 |
IDS Forum
Re: Someone have a technique to alter tables w....
Posted By: Cesar Inacio Martins
Date: Thursday, 3 May 2012, at 7:21 a.m.
In Response To: Re: Someone have a technique to alter tables w.... (Fernando Nunes)
Fernando,
In the message sent by both PMR :
Date: 06/06/2011 Time: 11:43:34
Sql : -710 Table (informix.x_xxxxxx) has been dropped, altered
or renamed.
Isam : 0 Unknown error message 0.
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR
Vers Explain
5320611 INSERT xxxxxxxxx DR Wait -710 0
9.28 Off
I don't remember if the last time (last week) have this same ISAM ...
I will get the ISAM error and tell you later (I need to recovery our
application log from tape).
The PMR do not reflect all situations what I said (create index and
alter table) because I get the support answer (work as design) before
need to execute an alter table and I do not want open a PMR for each
situation since the others two PMRs just do not have any evolution....
At finish the behave what I experience here is the same (same error,
same consequences).... because that I consider all the same thing....
(maybe internally isn't , but as *customer* I don't have this point of
view , I need they "just works" )....
Thanks for your script I will test it.
Cesar
On 2/5/2012 21:34, Fernando Nunes 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.
>>
>>
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
