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=C...uption&tres t
r=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.

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 (AT) arz-emmendingen (DOT) de> wrote:


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

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 (AT) arz-emmendingen (DOT) 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 (AT) gmail (DOT) com]
Sent: Wednesday, August 17, 2011 1:26 PM
To: Habichtsberg, Reinhard
Cc: informix-list (AT) iiug (DOT) 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 (AT) arz-emmendingen (DOT) 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=C...uption&tres t
r=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 (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

If the table involved is repeatedly used by applications but not
continuously by any one, the fix can be to set environment variable
IFX_DIRTY_WAIT (as well as SET LOCK MODE TO WAIT) to the number of
seconds required. This has been available since IDS 9 or 10, but is
underdocumented. Combine this with IDS 11 AUTO_REPREPARE, and prepared
statements on the table will also then be unaffected.

Regards,
Doug Lawry

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 (AT) arz-emmendingen (DOT) de> wrote:


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

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 (AT) gmail (DOT) com]
Sent: Thursday, August 18, 2011 2:02 PM
To: Habichtsberg, Reinhard
Cc: informix-list (AT) iiug (DOT) 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 (AT) arz-emmendingen (DOT) 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 (AT) gmail (DOT) com]
Sent: Wednesday, August 17, 2011 1:26 PM
To: Habichtsberg, Reinhard
Cc: informix-list (AT) iiug (DOT) 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 (AT) arz-emmendingen (DOT) 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=C...uption&tres t
r=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 (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list




_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) 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...

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

Regards.

On Thu, Aug 18, 2011 at 3:14 PM, Habichtsberg, Reinhard <
RHabichtsberg (AT) arz-emmendingen (DOT) de> wrote:


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

On Aug 18, 7:14*am, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
emmendingen.de> wrote:
Why do have queries that can last days?

Can you either add indexes, denormalize or add most cpus/spindles/
fragments.?

How many threads per query are you using now?

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 (AT) gmail (DOT) com]
Sent: Thursday, August 18, 2011 5:22 PM
To: Habichtsberg, Reinhard
Cc: informix-list (AT) iiug (DOT) 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....sive-is-not-re
ally-exclusive.html

Regards.

On Thu, Aug 18, 2011 at 3:14 PM, Habichtsberg, Reinhard
<RHabichtsberg (AT) arz-emmendingen (DOT) 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 (AT) gmail (DOT) com]
Sent: Thursday, August 18, 2011 2:02 PM


To: Habichtsberg, Reinhard
Cc: informix-list (AT) iiug (DOT) 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 (AT) arz-emmendingen (DOT) 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 (AT) gmail (DOT) com]
Sent: Wednesday, August 17, 2011 1:26 PM
To: Habichtsberg, Reinhard
Cc: informix-list (AT) iiug (DOT) 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 (AT) arz-emmendingen (DOT) 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=C...uption&tres t
r=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 (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list




_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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...