Hello, We have the following situation. Our 4GL developers have a need
to identify and save in a variable the current session isolation
level. If the isolation level is not "dirty read" they would set it
to dirty read. Then they would execute some lines of code. As a final
step they would set the isolation level back to it's original state.
Here is the code they were using to do this (yes we know it could be
one SQL statement, though that is what the developer used):

select odb_sessionid
from sysmaster:sysopendb
where odb_sessionid = dbinfo('sessionid')
and odb_dbname in('bnh','bhi')
and odb_iscurrent = 'Y'

select te.flags,
te.txt
from sysmaster:sysopendb op,
sysmaster:flags_text te,
sysmaster:syssessions se
where op.odb_isolation = te.flags
and te.tabname = 'sysopendb'
and op.odb_dbname in('bnh','bhi')
and se.sid = ?

Here is the issue. When they put this code into production we were
seeing a lot of session - 'S' flag mutexes in onstat -u and it was
slowing the system down. This piece of code is used very often by
hundreds of users. The thing is that because the function where they
put this SQL is called from dozens of other places the isolation level
could be set to anything.

We opened a case with Informix support and the bottom line they came
back with was,

"I have discussed this with a person who works regularly with the
source code. He feels that this is what is going on;

When that query is being run against the syssessions table, it is
placing a lock (AKA a Mutex) on the underlying memory structures that
the pseudo-table it was accessing. Being that it is accessing the
session information, it is putting a mutex on the sessions themselves.
Since you indicated that this code was running often, the session
mutex was being put in place to keep them from changing while this
read was occuring. This would hold up the actual user of those session
for the moment this was done. If this were run often, it could easily
bring the system to its knees."

So the question is, how can our 4GL developers get the current
isolation level for the current session without causing all those
mutexes and locking the table??

Any help would be greatly appreciated.

David
Informix DBA
in4mixdba@gmail.com

Dave wrote:
> Hello, We have the following situation. Our 4GL developers have a need
> to identify and save in a variable the current session isolation
> level. If the isolation level is not "dirty read" they would set it
> to dirty read. Then they would execute some lines of code. As a final
> step they would set the isolation level back to it's original state.
> Here is the code they were using to do this (yes we know it could be
> one SQL statement, though that is what the developer used):
>
> select odb_sessionid
> from sysmaster:sysopendb
> where odb_sessionid = dbinfo('sessionid')
> and odb_dbname in('bnh','bhi')
> and odb_iscurrent = 'Y'
>
> select te.flags,
> te.txt
> from sysmaster:sysopendb op,
> sysmaster:flags_text te,
> sysmaster:syssessions se
> where op.odb_isolation = te.flags
> and te.tabname = 'sysopendb'
> and op.odb_dbname in('bnh','bhi')
> and se.sid = ?
>
> Here is the issue. When they put this code into production we were
> seeing a lot of session - 'S' flag mutexes in onstat -u and it was
> slowing the system down. This piece of code is used very often by
> hundreds of users. The thing is that because the function where they
> put this SQL is called from dozens of other places the isolation level
> could be set to anything.
>
> We opened a case with Informix support and the bottom line they came
> back with was,
>
> "I have discussed this with a person who works regularly with the
> source code. He feels that this is what is going on;
>
> When that query is being run against the syssessions table, it is
> placing a lock (AKA a Mutex) on the underlying memory structures that
> the pseudo-table it was accessing. Being that it is accessing the
> session information, it is putting a mutex on the sessions themselves.
> Since you indicated that this code was running often, the session
> mutex was being put in place to keep them from changing while this
> read was occuring. This would hold up the actual user of those session
> for the moment this was done. If this were run often, it could easily
> bring the system to its knees."
>
> So the question is, how can our 4GL developers get the current
> isolation level for the current session without causing all those
> mutexes and locking the table??
>
> Any help would be greatly appreciated.
>
> David
> Informix DBA
> in4mixdba@gmail.com

Quite simply the 4gl developer should keep track of the isolation level within
the application. Create a function that is used to set the isolation level,
and have it set a global or modular variable every time the isolation level is
changed. use that function throughout the code. The rest he can work out on
his own.
And if he gives you the "we have 4 million lines of code" speach, tell him to
learn awk to script the required code changes.
--
Ciao,
Marco
__________________________________________________ ____________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Marco,

I completely agree you are 100% correct and yes I did get the 4 million
lines of code speech. Though, I am just doing my part to try and help out.

If you have any other solutions/ideas, please let me know.

-- David
Informix DBA
in4mixdba@gmail.com


On 10/16/07, Marco Greco <marco@4glworks.com> wrote:
>
> Dave wrote:
> > Hello, We have the following situation. Our 4GL developers have a need
> > to identify and save in a variable the current session isolation
> > level. If the isolation level is not "dirty read" they would set it
> > to dirty read. Then they would execute some lines of code. As a final
> > step they would set the isolation level back to it's original state.
> > Here is the code they were using to do this (yes we know it could be
> > one SQL statement, though that is what the developer used):
> >
> > select odb_sessionid
> > from sysmaster:sysopendb
> > where odb_sessionid = dbinfo('sessionid')
> > and odb_dbname in('bnh','bhi')
> > and odb_iscurrent = 'Y'
> >
> > select te.flags,
> > te.txt
> > from sysmaster:sysopendb op,
> > sysmaster:flags_text te,
> > sysmaster:syssessions se
> > where op.odb_isolation = te.flags
> > and te.tabname = 'sysopendb'
> > and op.odb_dbname in('bnh','bhi')
> > and se.sid = ?
> >
> > Here is the issue. When they put this code into production we were
> > seeing a lot of session - 'S' flag mutexes in onstat -u and it was
> > slowing the system down. This piece of code is used very often by
> > hundreds of users. The thing is that because the function where they
> > put this SQL is called from dozens of other places the isolation level
> > could be set to anything.
> >
> > We opened a case with Informix support and the bottom line they came
> > back with was,
> >
> > "I have discussed this with a person who works regularly with the
> > source code. He feels that this is what is going on;
> >
> > When that query is being run against the syssessions table, it is
> > placing a lock (AKA a Mutex) on the underlying memory structures that
> > the pseudo-table it was accessing. Being that it is accessing the
> > session information, it is putting a mutex on the sessions themselves.
> > Since you indicated that this code was running often, the session
> > mutex was being put in place to keep them from changing while this
> > read was occuring. This would hold up the actual user of those session
> > for the moment this was done. If this were run often, it could easily
> > bring the system to its knees."
> >
> > So the question is, how can our 4GL developers get the current
> > isolation level for the current session without causing all those
> > mutexes and locking the table??
> >
> > Any help would be greatly appreciated.
> >
> > David
> > Informix DBA
> > in4mixdba@gmail.com
>
> Quite simply the 4gl developer should keep track of the isolation level
> within
> the application. Create a function that is used to set the isolation
> level,
> and have it set a global or modular variable every time the isolation
> level is
> changed. use that function throughout the code. The rest he can work out
> on
> his own.
> And if he gives you the "we have 4 million lines of code" speach, tell him
> to
> learn awk to script the required code changes.
> --
> Ciao,
> Marco
>
> __________________________________________________ ____________________________
> Marco Greco /UK /IBM Standard disclaimers
> apply!
>
> Structured Query Scripting Language
> http://www.4glworks.com/sqsl.htm
> 4glworks
> http://www.4glworks.com
> Informix on Linux
> http://www.4glworks.com/ifmxlinux.htm
>

Informix DBA wrote:
> Marco,
>
> I completely agree you are 100% correct and yes I did get the 4 million
> lines of code speech. Though, I am just doing my part to try and help out.
>
> If you have any other solutions/ideas, please let me know.
>
> -- David
> Informix DBA
> in4mixdba@gmail.com <mailto:in4mixdba@gmail.com>

Well, he can fire an onstat -g sql <sessionid> from 4gl, filter it through
awk, capture the isolation level and pass it back to the application.. If you
have a look at my web site (see sig) in the miscellaneous tools page, there's
4gl code you can use to read text from a pipe.
This would be a very dirty hack, but would essentially avoid any latch
contention issues.
All in all the "track it yourself" approach would be the cleanest in terms of
long term code maintainability

>
>
> On 10/16/07, *Marco Greco* <marco@4glworks.com
> <mailto:marco@4glworks.com>> wrote:
>
> Dave wrote:
> > Hello, We have the following situation. Our 4GL developers have a need
> > to identify and save in a variable the current session isolation
> > level. If the isolation level is not "dirty read" they would set it
> > to dirty read. Then they would execute some lines of code. As a final
> > step they would set the isolation level back to it's original state.
> > Here is the code they were using to do this (yes we know it could be
> > one SQL statement, though that is what the developer used):
> >
> > select odb_sessionid
> > from sysmaster:sysopendb
> > where odb_sessionid = dbinfo('sessionid')
> > and odb_dbname in('bnh','bhi')
> > and odb_iscurrent = 'Y'
> >
> > select te.flags,
> > te.txt
> > from sysmaster:sysopendb op,
> > sysmaster:flags_text te,
> > sysmaster:syssessions se
> > where op.odb_isolation = te.flags
> > and te.tabname = 'sysopendb'
> > and op.odb_dbname in('bnh','bhi')
> > and se.sid = ?
> >
> > Here is the issue. When they put this code into production we were
> > seeing a lot of session - 'S' flag mutexes in onstat -u and it was
> > slowing the system down. This piece of code is used very often by
> > hundreds of users. The thing is that because the function where they
> > put this SQL is called from dozens of other places the isolation level
> > could be set to anything.
> >
> > We opened a case with Informix support and the bottom line they came
> > back with was,
> >
> > "I have discussed this with a person who works regularly with the
> > source code. He feels that this is what is going on;
> >
> > When that query is being run against the syssessions table, it is
> > placing a lock (AKA a Mutex) on the underlying memory structures that
> > the pseudo-table it was accessing. Being that it is accessing the
> > session information, it is putting a mutex on the sessions
> themselves.
> > Since you indicated that this code was running often, the session
> > mutex was being put in place to keep them from changing while this
> > read was occuring. This would hold up the actual user of those
> session
> > for the moment this was done. If this were run often, it could easily
> > bring the system to its knees."
> >
> > So the question is, how can our 4GL developers get the current
> > isolation level for the current session without causing all those
> > mutexes and locking the table??
> >
> > Any help would be greatly appreciated.
> >
> > David
> > Informix DBA
> > in4mixdba@gmail.com <mailto:in4mixdba@gmail.com>
>
> Quite simply the 4gl developer should keep track of the isolation
> level within
> the application. Create a function that is used to set the isolation
> level,
> and have it set a global or modular variable every time the
> isolation level is
> changed. use that function throughout the code. The rest he can work
> out on
> his own.
> And if he gives you the "we have 4 million lines of code" speach,
> tell him to
> learn awk to script the required code changes.
> --
> Ciao,
> Marco
> __________________________________________________ ____________________________
>
> Marco Greco /UK /IBM Standard
> disclaimers apply!
>
> Structured Query Scripting Language
> http://www.4glworks.com/sqsl.htm
> 4glworks
> http://www.4glworks.com
> Informix on
> Linux http://www.4glworks.com/ifmxlinux.htm
>
>

--
Ciao,
Marco
__________________________________________________ ____________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Marco Greco wrote:
> Dave wrote:
>> Hello, We have the following situation. Our 4GL developers have a need
>> to identify and save in a variable the current session isolation
>> level. If the isolation level is not "dirty read" they would set it
>> to dirty read. Then they would execute some lines of code. As a final
>> step they would set the isolation level back to it's original state.
>> Here is the code they were using to do this (yes we know it could be
>> one SQL statement, though that is what the developer used):
>>
>> select odb_sessionid
>> from sysmaster:sysopendb
>> where odb_sessionid = dbinfo('sessionid')
>> and odb_dbname in('bnh','bhi')
>> and odb_iscurrent = 'Y'
>>
>> select te.flags,
>> te.txt
>> from sysmaster:sysopendb op,
>> sysmaster:flags_text te,
>> sysmaster:syssessions se
>> where op.odb_isolation = te.flags
>> and te.tabname = 'sysopendb'
>> and op.odb_dbname in('bnh','bhi')
>> and se.sid = ?
>>
>> Here is the issue. When they put this code into production we were
>> seeing a lot of session - 'S' flag mutexes in onstat -u and it was
>> slowing the system down. This piece of code is used very often by
>> hundreds of users. The thing is that because the function where they
>> put this SQL is called from dozens of other places the isolation level
>> could be set to anything.
>>
>> We opened a case with Informix support and the bottom line they came
>> back with was,
>>
>> "I have discussed this with a person who works regularly with the
>> source code. He feels that this is what is going on;
>>
>> When that query is being run against the syssessions table, it is
>> placing a lock (AKA a Mutex) on the underlying memory structures that
>> the pseudo-table it was accessing. Being that it is accessing the
>> session information, it is putting a mutex on the sessions themselves.
>> Since you indicated that this code was running often, the session
>> mutex was being put in place to keep them from changing while this
>> read was occuring. This would hold up the actual user of those session
>> for the moment this was done. If this were run often, it could easily
>> bring the system to its knees."
>>
>> So the question is, how can our 4GL developers get the current
>> isolation level for the current session without causing all those
>> mutexes and locking the table??
>>
>> Any help would be greatly appreciated.
>>
>> David
>> Informix DBA
>> in4mixdba@gmail.com
>
> Quite simply the 4gl developer should keep track of the isolation level within
> the application. Create a function that is used to set the isolation level,
> and have it set a global or modular variable every time the isolation level is
> changed. use that function throughout the code. The rest he can work out on
> his own.
> And if he gives you the "we have 4 million lines of code" speach, tell him to
> learn awk to script the required code changes.

Hi Marco!
I can't disagree more with this...
In this scenario it may be an acceptable answer but when you go into more
complex environments (let's use the word SOA...) you may need to do a specific
action with a specific isolation level and then return to whatever you had
before. So, a nice and clean way to get the IL would be great!

Regards

--
Fernando Nunes
Portugal

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

Fernando Nunes wrote:
> Marco Greco wrote:
>> Dave wrote:
>>> Hello, We have the following situation. Our 4GL developers have a need
>>> to identify and save in a variable the current session isolation
>>> level. If the isolation level is not "dirty read" they would set it
>>> to dirty read. Then they would execute some lines of code. As a final
>>> step they would set the isolation level back to it's original state.
>>> Here is the code they were using to do this (yes we know it could be
>>> one SQL statement, though that is what the developer used):
>>>
>>> select odb_sessionid
>>> from sysmaster:sysopendb
>>> where odb_sessionid = dbinfo('sessionid')
>>> and odb_dbname in('bnh','bhi')
>>> and odb_iscurrent = 'Y'
>>>
>>> select te.flags,
>>> te.txt
>>> from sysmaster:sysopendb op,
>>> sysmaster:flags_text te,
>>> sysmaster:syssessions se
>>> where op.odb_isolation = te.flags
>>> and te.tabname = 'sysopendb'
>>> and op.odb_dbname in('bnh','bhi')
>>> and se.sid = ?
>>>
>>> Here is the issue. When they put this code into production we were
>>> seeing a lot of session - 'S' flag mutexes in onstat -u and it was
>>> slowing the system down. This piece of code is used very often by
>>> hundreds of users. The thing is that because the function where they
>>> put this SQL is called from dozens of other places the isolation level
>>> could be set to anything.
>>>
>>> We opened a case with Informix support and the bottom line they came
>>> back with was,
>>>
>>> "I have discussed this with a person who works regularly with the
>>> source code. He feels that this is what is going on;
>>>
>>> When that query is being run against the syssessions table, it is
>>> placing a lock (AKA a Mutex) on the underlying memory structures that
>>> the pseudo-table it was accessing. Being that it is accessing the
>>> session information, it is putting a mutex on the sessions themselves.
>>> Since you indicated that this code was running often, the session
>>> mutex was being put in place to keep them from changing while this
>>> read was occuring. This would hold up the actual user of those session
>>> for the moment this was done. If this were run often, it could easily
>>> bring the system to its knees."
>>>
>>> So the question is, how can our 4GL developers get the current
>>> isolation level for the current session without causing all those
>>> mutexes and locking the table??
>>>
>>> Any help would be greatly appreciated.
>>>
>>> David
>>> Informix DBA
>>> in4mixdba@gmail.com
>> Quite simply the 4gl developer should keep track of the isolation level within
>> the application. Create a function that is used to set the isolation level,
>> and have it set a global or modular variable every time the isolation level is
>> changed. use that function throughout the code. The rest he can work out on
>> his own.
>> And if he gives you the "we have 4 million lines of code" speach, tell him to
>> learn awk to script the required code changes.
>
> Hi Marco!
> I can't disagree more with this...
> In this scenario it may be an acceptable answer but when you go into more
> complex environments (let's use the word SOA...) you may need to do a specific
> action with a specific isolation level and then return to whatever you had
> before. So, a nice and clean way to get the IL would be great!
>
> Regards
>

And a way there is! however what you should realize is that accessing shared
memory structures, and in particular volatile ones such as statements,
sessions, or threads, requires by definition a hefty amount of latching,
otherwise the engine would crash, which I'm sure you would very much like to
avoid.
So while a sysmaster query done infrequently is quite allright, several
hundred users accessing syssessions several hundreds times a second each is
most definitely over the top, in particular when you have a cheap alternative
readily available.

The moral of the story is simple - know the strengths and limitations of the
tools you use and use them appropriately. sysmaster is flexible, but might put
the engine under stress.
After all, db2 only had monitors, and IBM rushed to implement db2stat (Hey,
Jeff, how's life?). What does that tell you?

--
Ciao,
Marco
__________________________________________________ ____________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Marco Greco wrote:
> Fernando Nunes wrote:
>> Marco Greco wrote:
>>> Dave wrote:
>>>> Hello, We have the following situation. Our 4GL developers have a need
>>>> to identify and save in a variable the current session isolation
>>>> level. If the isolation level is not "dirty read" they would set it
>>>> to dirty read. Then they would execute some lines of code. As a final
>>>> step they would set the isolation level back to it's original state.
>>>> Here is the code they were using to do this (yes we know it could be
>>>> one SQL statement, though that is what the developer used):
>>>>
>>>> select odb_sessionid
>>>> from sysmaster:sysopendb
>>>> where odb_sessionid = dbinfo('sessionid')
>>>> and odb_dbname in('bnh','bhi')
>>>> and odb_iscurrent = 'Y'
>>>>
>>>> select te.flags,
>>>> te.txt
>>>> from sysmaster:sysopendb op,
>>>> sysmaster:flags_text te,
>>>> sysmaster:syssessions se
>>>> where op.odb_isolation = te.flags
>>>> and te.tabname = 'sysopendb'
>>>> and op.odb_dbname in('bnh','bhi')
>>>> and se.sid = ?
>>>>
>>>> Here is the issue. When they put this code into production we were
>>>> seeing a lot of session - 'S' flag mutexes in onstat -u and it was
>>>> slowing the system down. This piece of code is used very often by
>>>> hundreds of users. The thing is that because the function where they
>>>> put this SQL is called from dozens of other places the isolation level
>>>> could be set to anything.
>>>>
>>>> We opened a case with Informix support and the bottom line they came
>>>> back with was,
>>>>
>>>> "I have discussed this with a person who works regularly with the
>>>> source code. He feels that this is what is going on;
>>>>
>>>> When that query is being run against the syssessions table, it is
>>>> placing a lock (AKA a Mutex) on the underlying memory structures that
>>>> the pseudo-table it was accessing. Being that it is accessing the
>>>> session information, it is putting a mutex on the sessions themselves.
>>>> Since you indicated that this code was running often, the session
>>>> mutex was being put in place to keep them from changing while this
>>>> read was occuring. This would hold up the actual user of those session
>>>> for the moment this was done. If this were run often, it could easily
>>>> bring the system to its knees."
>>>>
>>>> So the question is, how can our 4GL developers get the current
>>>> isolation level for the current session without causing all those
>>>> mutexes and locking the table??
>>>>
>>>> Any help would be greatly appreciated.
>>>>
>>>> David
>>>> Informix DBA
>>>> in4mixdba@gmail.com
>>> Quite simply the 4gl developer should keep track of the isolation level within
>>> the application. Create a function that is used to set the isolation level,
>>> and have it set a global or modular variable every time the isolation level is
>>> changed. use that function throughout the code. The rest he can work out on
>>> his own.
>>> And if he gives you the "we have 4 million lines of code" speach, tell him to
>>> learn awk to script the required code changes.
>> Hi Marco!
>> I can't disagree more with this...
>> In this scenario it may be an acceptable answer but when you go into more
>> complex environments (let's use the word SOA...) you may need to do a specific
>> action with a specific isolation level and then return to whatever you had
>> before. So, a nice and clean way to get the IL would be great!
>>
>> Regards
>>
>
> And a way there is! however what you should realize is that accessing shared
> memory structures, and in particular volatile ones such as statements,
> sessions, or threads, requires by definition a hefty amount of latching,
> otherwise the engine would crash, which I'm sure you would very much like to
> avoid.
> So while a sysmaster query done infrequently is quite allright, several
> hundred users accessing syssessions several hundreds times a second each is
> most definitely over the top, in particular when you have a cheap alternative
> readily available.
>
> The moral of the story is simple - know the strengths and limitations of the
> tools you use and use them appropriately. sysmaster is flexible, but might put
> the engine under stress.
> After all, db2 only had monitors, and IBM rushed to implement db2stat (Hey,
> Jeff, how's life?). What does that tell you?
>

A simple option into DBINFO? One byte in the session context? I don't know the
internals, but I would bet it wouldn't be hard...
I don't know if you recall, but we've talked recently because of a situation
where the costumer is seeing some of this symptoms... I'll go back to the
subject and investigate...

Regards.

--
Fernando Nunes
Portugal

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