Re: Alter Table non-exclusive
Don't get me wrong. I think every Informix customer has "dirty readers". And currently most of the OLTP systems are also used for decision support queries (or at least for ETL). In that case, and someone else already explained, IFX_DIRTY_WAIT will certainly help. But, back to basics, you may have a problem if you have queries on the affected tables that last for days. I'm saying this because all this talk comes down to one thing: identify and stop the sessions that prevent the access. For a complete personal overview about this topic check: http://informix-technology.blogspot.com/2006/10/when-exclusive-is-not-really-exclusive.html Regards. On Thu, Aug 18, 2011 at 3:14 PM, Habichtsberg, Reinhard < RHabichtsberg@arz-emmendingen.de> wrote: > Fernando,**** > > ** ** > > we have DIRTY READERS on the tables, yes, we have a „mixed system“ with > OLTP and decision support with queries which can last days.**** > > ** ** > > The IFX_DIRTY_WAIT variable may help for short dirty reads. I have to think > about it and will do some tests. Meanwhile I thank you for your help.**** > > ** ** > > Reinhard.**** > > ** ** > > *From:* Fernando Nunes [mailto:domusonline@gmail.com] > *Sent:* Thursday, August 18, 2011 2:02 PM > > *To:* Habichtsberg, Reinhard > *Cc:* informix-list@iiug.org > *Subject:* Re: Alter Table non-exclusive**** > > ** ** > > We're having a semantic problem. What you say is true. If an exclusive lock > is needed, than you can't get it while the table is in use. But that's a > general question regarding locks. That's why we have lock mode... > If the sessions leave open cursors on the tables for a long period than > you'll have issues. > Again, having a downtime in production is a "scary way" of putting it. > Having to hold or interrupt a few sessions for a few seconds is much more > understandable. > As someone else already mentioned, if you're having DIRTY READERS on the > tables the situation is much more complex. > And as I reference in my blog article you can use a small trick to prevent > new sessions of getting into the way: Open a transaction, grant some > privilege to the table and then run the alter inside that transaction. The > grant will place a lock on the table's systable record and this prevents > other sessions (assuming they're in committed read and lock mode wait) to > read the table structure. So the sessions will wait on systables and not on > the table itself. > This is useless for dirty readers, but for those you'll have the > IFX_DIRTY_WAIT variable. > > Meanwhile, while searching interanlly for other things I found a feature > request for this. But the issue is complex and not easy to solve... meaning > there's no compromise that it will be implemented. It just means that > formally IBM knows about this concern. > > Regards.**** > > On Thu, Aug 18, 2011 at 6:34 AM, Habichtsberg, Reinhard < > RHabichtsberg@arz-emmendingen.de> wrote:**** > > If an exclusive lock is required it is impossible to do the job while the > table is in use. And some tables in our environment are in use of one or > more session permanently. So it doesn’t matter how long the alter job lasts. > It cannot be accomplished without a downtime of production. **** > > **** > > That is what we (better: my boss) wish(es) to improve.**** > > **** > > @Fernando: To find the sessions that use a table I found a tool in the IIUG > software corner long ago: **** > > 1 #!/usr/bin/ksh**** > > 2 # who-access.sh - Find out who is accessing a specified table.**** > > 3 #**** > > 4 # Author: Jacob Salomon**** > > 5 # Date: 07/15/1999**** > > 6 # Credits: Original idea from a script by:**** > > 7 # Rick Bernstein …**** > > **** > > It’s really very usefull and reliable.**** > > **** > > Reinhard.**** > > **** > > *From:* Art Kagel [mailto:art.kagel@gmail.com] > *Sent:* Wednesday, August 17, 2011 1:26 PM > *To:* Habichtsberg, Reinhard > *Cc:* informix-list@iiug.org > *Subject:* Re: Alter Table non-exclusive**** > > **** > > Some things already only require an exclusive lock momentarily. Most > alters that add or drop a column or change it's type, adding or dropping an > index, and others are accomplished in-place or online and do not lock that > table for long. What are you trying to do that's causing you trouble? > > 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, Aug 17, 2011 at 6:17 AM, Habichtsberg, Reinhard < > RHabichtsberg@arz-emmendingen.de> wrote:**** > > Hi all**** > > **** > > We have 24 x 7 production with our Informix databases. We all heard of > Informix Server that ran a year or longer without interruption<http://dict.leo.org/ende?lp=ende&p=Ci4HO3kMAA&search=interruption&trestr=0x8001>;. > **** > > **** > > What we wish is the ability to do „Alter tables“ and other administrative > tasks that needs „exclusive locks“ without this need. Each change on the > table structures which come continuous from our software developement causes > trouble with our production: Workflows has to be stopped, dialog programs > can’t run.**** > > **** > > Is there any chance do these tasks without exclusive locks, perhaps in > futur? Or, is there any other way to do these tasks without a production > break?**** > > **** > > TIA, Reinhard.**** > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list**** > > **** > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list**** > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently...**** > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...