Open SQL Statements

Hello, How can we identify that a particular session has how many open SPL routines or statements? I mean, how can we identify number of routines that are just open and doing noting? Whats the procedure to close these open statements appropriately? Do we need to check all sessions one by one and calculate the # of open statements or there is some other technique to monitor open statements of all sessions? Regards, Anees

There is nothing inherently wrong with a session having open statements, even many such. I wrote a middleware server while at Bloomberg that prepared about 10,000 statements at startup so that they were available to client programs already optimized and ready to run. This is a common Informix development technique for statements that are reused frequently in an application. Now, if these are one time use statements in a long running application that the developers have simply neglected to close and free, well, that's a horse of a different color! Then you'll have to track down the culprits by examining the syssessions table to see what sessions have unexpected numbers of SQL statements still open on the session then go to the source to determine if the behavior is reasonable. If not, beat the offending developers with a large handful of partially cooked linguine and make them fix it! Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Fri, Jun 5, 2009 at 10:29 AM, ANEES AHMAD <aanees@i2cinc.com> wrote: > Hello, > > How can we identify that a particular session has how many open SPL > routines > or statements? I mean, how can we identify number of routines that are just > open and doing noting? > Whats the procedure to close these open statements appropriately? > Do we need to check all sessions one by one and calculate the # of open > statements or there is some other technique to monitor open statements of > all > sessions? > > Regards, > Anees > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5a7239ae6eb046b9bc40d

Take a look at onstat -g stm {optional session id} This will show you each prepared statement and how much memory it consu= mes. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) |------------> | From: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |"ANEES AHMAD" <aanees@i2cinc.com> = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | To: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids@iiug.org = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Date: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |06/05/2009 07:30 AM = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Subject: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |Open SQL Statements [15949] = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Sent by: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids-bounces@iiug.org = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| Hello, How can we identify that a particular session has how many open SPL routines or statements? I mean, how can we identify number of routines that are = just open and doing noting? Whats the procedure to close these open statements appropriately? Do we need to check all sessions one by one and calculate the # of open= statements or there is some other technique to monitor open statements = of all sessions? Regards, Anees ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Art, Can syssessions table provide us the information, that how much statements are open? I think this table does not contain such information. Secondly as asked earlier, how can we differentiate among required open and non-required open statements .i.e. how can we be sure that an open statement must need to be closed. Regards, Anees

As to your second question, that's an application question. Only your developers can answer it. As to your first question, from the syssessions record you can find all of the prepared statements assigned to each session and determine if those statements have cursors attached to them and whether they are currently open or closed. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Jun 8, 2009 at 8:29 AM, ANEES AHMAD <aanees@i2cinc.com> wrote: > Art, > > Can syssessions table provide us the information, that how much statements > are > open? I think this table does not contain such information. > Secondly as asked earlier, how can we differentiate among required open and > non-required open statements .i.e. how can we be sure that an open > statement > must need to be closed. > > Regards, > Anees > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b184bd57fa046bd5f864