Re: Informix Session Information

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