IDS Forum

Re: Someone have a technique to alter tables w....

Posted By: Cesar Inacio Martins
Date: Wednesday, 2 May 2012, at 1:49 p.m.

In Response To: Re: Someone have a technique to alter tables w.... (Fernando Nunes)

If I'm not confusing anything, one of answers from support says the -710
persists because exists references to table where have dirty = yes.
(onstat -g dic)
or something like that.

On 2/5/2012 12:31, Fernando Nunes wrote:
> Hmmmm.... was the dictionary cache mentioned as a possible cause for this?
> I'd like to have the ability to flush all/partial entries in any of the
> engine caches... I've faced a couple of situations where this would help
> (bugs found and fixed, but meanwhile it would have been nice). AFAIK there
> is currently no feature request for this.
>
> Regards.
>
> On Wed, May 2, 2012 at 4:26 PM, Fernando Nunes<domusonline@gmail.com>wrote:
>
>> Long post...
>> Error 710 should only happen on PREPARED statements... And the applications
>> should know how to handle the -710 error (re-prepare the statement)
>> If you're hitting -710 without a prepared statement then this needs further
>> investigation.
>> If -710 comes only from PREPARED statements, then AUTO_REPREPARE should
>> help, but it's not guaranteed 100% of time.
>> But you say that in a new session will also get the -710. This doesn't look
>> normal... Have you tried to flush the statement cache?
>>
>> Can you give the PMR number? Do you have a test case? Have you tried it on
>> a different version?
>>
>> At first glance it doesn't look good, but I'd need to look into it very
>> carefully before issuing a definitive answer... specially if you already
>> checked with support...
>>
>> Regards.
>>
>> On Wed, May 2, 2012 at 2:38 PM, 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
>>>
>>>
>>>
>>>
>>
> *******************************************************************************
>>> 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...
>>
>> --00235452f64c14f90d04bf0f4faf
>>
>>
>>
>>
> *******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>

Messages In This Thread

• Someone have a technique to alter tables with FK ?
  Cesar Inacio Martins -- Wednesday, 2 May 2012, at 9:38 a.m.
  • Re: Someone have a technique to alter tables with
    Fernando Nunes -- Wednesday, 2 May 2012, at 11:26 a.m.
    • Re: Someone have a technique to alter tables w....
      Fernando Nunes -- Wednesday, 2 May 2012, at 11:31 a.m.
      • Re: Someone have a technique to alter tables w....
        Cesar Inacio Martins -- Wednesday, 2 May 2012, at 1:49 p.m.
    • Re: Someone have a technique to alter tables w....
      Cesar Inacio Martins -- Wednesday, 2 May 2012, at 11:59 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.