Re: Informix Session Information

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