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...