Re: Alter Table non-exclusive

I'm not the best person to answer this, since your questions can be considered a feature request. In any case, and because this is a problem I've faced before (as any other 24x7 Informix DBA) I will make some comments. First, I think technically, you'll always need some instant of "exclusive access" to the table structures in order to change them. It would not be a good idea to allow two ALTER TABLE statements at the first time. Obviously this should be "serializable" and users should not notice it. But there are other implications which are more tricky, harder to understand and to solve. If you're able to alter a table while it's in use, what would happen to sessions with cursors opened on that table? These cursors could reference a column that is changed or removed. Note that Informix (specially in newer versions) already has a set of features that try to minimize the impacts of these changes: - New fragmentation schemes allow for "online" add and drop of fragments - With a special variable set you're able to "kill" the transactions that would otherwise stop you from adding/dropping a table fragment (many people would like to see this extended to all table ALTER operations) - You're able to create indexes online - Many table operations are done "in place" which means that they're very fast (so the "exclusive" period is much shorter than in the past) In any case, if I put my "Informix DBA" hat, I can easily agree with you and I think that: - The way "online" index creation works could be extended to other ALTER TABLE operations - The special variable for ALTER FRAGMENT could be extended to other operations - Some operations that require "exclusive mode" could possible get done with "shared mode". Sometimes maintaining read access would be "good enough" although not perfect - The requirements for exclusive in other tables (foreign key/primary key changes) should be perfectly documented and I think they're not - It should be trivial to identify which sessions are preventing you to run an ALTER TABLE. Many times I spend more time trying to figure out the sessions that must be stopped than actually stoping them and making the change. This increases unnecessarily the time that normal operations are impacted by these changes. Regarding this last aspect I have created some scripts and defined some techniques that try to ease this procedures. I've written an article about this a few years ago on my blog, and since then I tried to create a script that tells me the sessions "using" a table. It's not nice to say this, but the truth is that i't not as simple as checking "onstat -g opn". Currently I check this and the locks. Lately I haven't been doing this tasks in 24x7 systems, but the persons who do it, usually give me positive feedback on this. Finally, note that on a 24x7 system, usually people only look at the whole system uptime. I agree with you that this "masks" the fact that some operations may cause partial unavailability of some parts of the data model. That's why I think that whenever possible, some "maintenance" window should be considered, but not on the whole system. I perfectly understand that a 24x7 system is supposed to be available every time. But the truth is that most of the time you can "turn off" some parts of it at lower peak times without great impact. Web based systems are probably the biggest exception to this idea. Specially if you provide service to different time zones. Regards. On Wed, Aug 17, 2011 at 11: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 > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...