RE: Alter Table non-exclusive

Hi Fernando, I now had the situation to do an "alter table" on a highly frequented table in production system. I did the following: - Waited for dialog free time (got up very early in the morning ;-) ) - Killed some dialog sessions from users, which didn't log out - Monitored some sessions, which which where connected to the table in question. I found 6 sessions which were active (you saw current sql statement changing every second). These are workflow batches which run all the time and one 4gl-program that produces certain reports. - set IFX_DIRTY_WAIT=300 - Ran the following sql commands: 1 set lock mode to wait; 2 begin work; 3 -- lock table table1 in exclusive mode; 4 grant select on table1 to public; 5 grant select on table2 to public; 6 alter table table1 7 add (column1 char(1)) 8 ; 9 10 --lock table table2 in exclusive mode; 11 alter table table2 12 add (column1 char(1)) 13 ; 14 15 drop view view1; 16 17 create view view1 18 as select 19 table1.column_x, .... 470 471 grant select on view1 to public ; 472 grant select on view2 to public; 473 474 commit work; I didn't believe it before but the "alter table"-statements ran successfully. The ALTER TABLE of table1 lasted some minutes. I believe because table1 has 548.675.744 rows fragmented in round robin. I monitored locks with waiters and found, that the 4gl-program waited for systable which was locked by the alter table session. Unfortunately the 4gl-program crashed with an error: Date: 01.09.2011 Time: 06:32:51 Program error at "ka_st_sl.4gl", line number 2076. SQL statement error number -243. Could not position within a table (informix.table1). SYSTEM error number -106. ISAM error: non-exclusive access. The 4gl-programm ran in modus: lockmode wait. I asked the developer and controlled the code by myself. The time to wait was not restricted. How can we avoid the crash of a program under these circumstances? TIA, Reinhard. From: Fernando Nunes [mailto:domusonline@gmail.com] Sent: Thursday, August 18, 2011 5:22 PM To: Habichtsberg, Reinhard Cc: informix-list@iiug.org Subject: 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-re ally-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 ref