How do you determine session age?

Good morning, I hope this is a relatively simple question: How do you determine the age of an Informix session. Reason I'm asking, I use Cognos Impromptu reporting software against my Institution's IDS database server. Cognos is notorious for leaving sessions open indefinitely over ODBC connections. Because valuable system resources and memory is unnecessarily tied-up by these long-running sessions, I need to create a cron that will run @ night and kill-off these idle sessions. However, I am not able to find a way to get session age information from onstat -g ses or onstat -g sql. Does someone have a way to find out session age? Thanks for any insight. Sincerely, Jonathan B. Smaby Pomona College phone: (909) 621-8506 email: jonathan.smaby@pomona.edu -- "There is always a well-known solution to every human problem - neat, plausible, and wrong." - H.L. Mencken (1880-1956) ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

Hi, There is a table in sysmaster: { Session control blocks } create table sysscblst { Internal Use Only } ( sid integer, { session id } address int8, { address of session structure } currheap int8, { ptr to memory heap } poolp int8, { ptr to private session pool } breakflag integer, { stop current processing } urgent integer, { message from tbmode } killflag integer, { stop all processing } neterrno integer, { network error number } flags integer, { session flags } local smallint, { user is local if set } tlatchp int8, { latch protecting thread list } threadlist int8, { ptr to first thread in list } next int8, { next session in list } uid smallint, { user id } username char(32), { user name } gid smallint, { primary group id } nsuppgids integer, { number of supplementary gids } suppgidsp int8, { ptr to suppl'ry gids table } clienttype integer, { client type } pid integer, { process id of fe program } progname char(16), { fe program name } ttyin char(16), { tty name for users stdin } ttyout char(16), { tty name for users stdout } ttyerr char(16), { tty name for users stderr } cwd char(32), { users cwd } hostname char(16), { users host name } connected integer, { time that user connected } argc integer, { count of args sent } argvp int8, { ptr to arg table } envvarp int8, { ptr to env var table } numenvvars integer, { number of env vars } sizeenvtab integer, { size of env var table } sqscb int8, { ptr to sql control block } netscb int8, { ptr to net control block } class integer { VP class } ); This table has a few fields that are important to you: - sid (session ID) - username - hostname - connected "connected" in the UNIX timestamp of the connection. You can select DBINFO('utc_to_datetime', connected) to convert it to a friendly DATETIMEYEAR TO SECOND. One thing you should look is the timezone... I have scripts that use this and I keep getting confused about if this field is aware of daylight savings.... Please check, specially after a change in DST settings (March/October) where I live... Regards. On Fri, Aug 21, 2009 at 4:37 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Good morning, > > I hope this is a relatively simple question: How do you determine the age > of > an Informix session. Reason I'm asking, I use Cognos Impromptu reporting > software against my Institution's IDS database server. Cognos is notorious > for > leaving sessions open indefinitely over ODBC connections. Because valuable > system resources and memory is unnecessarily tied-up by these long-running > sessions, I need to create a cron that will run @ night and kill-off these > idle sessions. However, I am not able to find a way to get session age > information from onstat -g ses or onstat -g sql. > > Does someone have a way to find out session age? > > Thanks for any insight. > > Sincerely, > > Jonathan B. Smaby > Pomona College > phone: (909) 621-8506 > email: jonathan.smaby@pomona.edu > -- > "There is always a well-known solution to every human problem - neat, > plausible, and wrong." > - H.L. Mencken (1880-1956) > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd3568e9861b60471a9154b

select dbinfo('UTC_TO_DATETIME', connected), sid from sysmaster:sysscblst; You can also look at: https://www.ibm.com/developerworks/mydeveloperworks/blogs/idsteam/entry/terminat e_idle_users_with_the John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 08/21/2009 08:37:50 AM: > [image removed] > > How do you determine session age? [16764]: > > Jonathan Smaby > > to: > > ids > > 08/21/2009 08:38 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Good morning, > > I hope this is a relatively simple question: How do you determine the age of > an Informix session. Reason I'm asking, I use Cognos Impromptu reporting > software against my Institution's IDS database server. Cognos is > notorious for > leaving sessions open indefinitely over ODBC connections. Because valuable > system resources and memory is unnecessarily tied-up by these long-running > sessions, I need to create a cron that will run @ night and kill-off these > idle sessions. However, I am not able to find a way to get session age > information from onstat -g ses or onstat -g sql. > > Does someone have a way to find out session age? > > Thanks for any insight. > > Sincerely, > > Jonathan B. Smaby > Pomona College > phone: (909) 621-8506 > email: jonathan.smaby@pomona.edu > -- > "There is always a well-known solution to every human problem - neat, > plausible, and wrong." > - H.L. Mencken (1880-1956) > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Try with 'onstat -g ntt' -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan Smaby Sent: viernes, 21 de agosto de 2009 10:38 a.m. To: ids@iiug.org Subject: How do you determine session age? [16764] Good morning, I hope this is a relatively simple question: How do you determine the age of an Informix session. Reason I'm asking, I use Cognos Impromptu reporting software against my Institution's IDS database server. Cognos is notorious for leaving sessions open indefinitely over ODBC connections. Because valuable system resources and memory is unnecessarily tied-up by these long-running sessions, I need to create a cron that will run @ night and kill-off these idle sessions. However, I am not able to find a way to get session age information from onstat -g ses or onstat -g sql. Does someone have a way to find out session age? Thanks for any insight. Sincerely, Jonathan B. Smaby Pomona College phone: (909) 621-8506 email: jonathan.smaby@pomona.edu -- "There is always a well-known solution to every human problem - neat, plausible, and wrong." - H.L. Mencken (1880-1956) ------------------------------------------------------------- This message has been scanned by Postini anti-virus software. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.