Syssessions connected

I am attempting to write something to cleanup all the disconnected sessions on my 11.10 server automaticly. 9.4 never had this problem but my problem now is, the sysmaster-syssessions.connected variable. Should show some sort of time yet it is an integer variable. I found it fully undocumented in the admin ref manual. All admin ref says is that it is "connected integer Time that user connected to the database server". What time format? What is this variable saying. All entries look like this: 1219588344 Being such a large number even for new users I can assume that it is not the number of seconds connected. Maybe it is the time OF the connection, well that would be in datatime format? So what is this thing? Any thoughts or pointers welcome. Thanks,,,, Tim Ertl 413-442-9000 x6211

Tim, This integer looks like it may Unix long time, number of seconds since Jan 1, 1970. David -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Tim Ertl Sent: Friday, September 19, 2008 11:39 AM To: ids@iiug.org Subject: Syssessions connected [13437] I am attempting to write something to cleanup all the disconnected sessions on my 11.10 server automaticly. 9.4 never had this problem but my problem now is, the sysmaster-syssessions.connected variable. Should show some sort of time yet it is an integer variable. I found it fully undocumented in the admin ref manual. All admin ref says is that it is "connected integer Time that user connected to the database server". What time format? What is this variable saying. All entries look like this: 1219588344 Being such a large number even for new users I can assume that it is not the number of seconds connected. Maybe it is the time OF the connection, well that would be in datatime format? So what is this thing? Any thoughts or pointers welcome. Thanks,,,, Tim Ertl 413-442-9000 x6211 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Something like this will help you to find old sessions... dbaccess sysmaster - 2>/dev/null << ! set isolation to dirty read; SELECT sid, username[1,20] username, hostname, DBINFO("UTC_TO_DATETIME", connected) as login_time FROM syssessions ORDER BY 4 ! Link, David A escreveu: Tim, This integer looks like it may Unix long time, number of seconds since Jan 1, 1970. David -----Original Message----- From: [1]ids-bounces@iiug.org [[2]mailto:ids-bounces@iiug.org] On Behalf Of Tim Ertl Sent: Friday, September 19, 2008 11:39 AM To: [3]ids@iiug.org Subject: Syssessions connected [13437] I am attempting to write something to cleanup all the disconnected sessions on my 11.10 server automaticly. 9.4 never had this problem but my problem now is, the sysmaster-syssessions.connected variable. Should show some sort of time yet it is an integer variable. I found it fully undocumented in the admin ref manual. All admin ref says is that it is "connected integer Time that user connected to the database server". What time format? What is this variable saying. All entries look like this: 1219588344 Being such a large number even for new users I can assume that it is not the number of seconds connected. Maybe it is the time OF the connection, well that would be in datatime format? So what is this thing? Any thoughts or pointers welcome. Thanks,,,, Tim Ertl 413-442-9000 x6211 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. References 1. mailto:ids-bounces@iiug.org 2. mailto:ids-bounces@iiug.org 3. mailto:ids@iiug.org

Here's one way to do it. The column is actually a timestamp although its not defined that way. The DBINFO function can do the translation. select a.sid session ,substr(a.username,1,12) as user ,substr(b.hostname,1,16) as host ,CURRENT - DBINFO("UTC_TO_DATETIME", connected) as duration from sysrstcb a, sysscblst b where a.sid =b.sid order by duration desc ; Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "Tim Ertl" <tim@lmrgroup.com> To: ids@iiug.org Date: 09/19/2008 12:40 PM Subject: Syssessions connected [13437] I am attempting to write something to cleanup all the disconnected sessions on my 11.10 server automaticly. 9.4 never had this problem but my problem now is, the sysmaster-syssessions.connected variable. Should show some sort of time yet it is an integer variable. I found it fully undocumented in the admin ref manual. All admin ref says is that it is "connected integer Time that user connected to the database server". What time format? What is this variable saying. All entries look like this: 1219588344 Being such a large number even for new users I can assume that it is not the number of seconds connected. Maybe it is the time OF the connection, well that would be in datatime format? So what is this thing? Any thoughts or pointers welcome. Thanks,,,, Tim Ertl 413-442-9000 x6211 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

An aside, make sure someone didn't turn off keepalive in the sqlhosts file on the server and on the clients (k=0 option in the connection string disables keepalive checking). Art On Fri, Sep 19, 2008 at 12:38 PM, Tim Ertl <tim@lmrgroup.com> wrote: > I am attempting to write something to cleanup all the disconnected sessions > on my 11.10 server automaticly. 9.4 never had this problem but my problem > now is, the sysmaster-syssessions.connected variable. > > Should show some sort of time yet it is an integer variable. I found it > fully undocumented in the admin ref manual. All admin ref says is that it > is > "connected integer Time that user connected to the database server". What > time format? What is this variable saying. All entries look like this: > > 1219588344 > > Being such a large number even for new users I can assume that it is not > the > number of seconds connected. Maybe it is the time OF the connection, well > that would be in datatime format? So what is this thing? > > Any thoughts or pointers welcome. > > Thanks,,,, > > Tim Ertl > > 413-442-9000 x6211 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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.

Tim: I wrote a deveoper works article on how to use the database scheduler and the SQL cmd api to do this exact task. Check out the following. I have copied the top of the article below. John F. Miller III STSM, Support Architect miller3@us.ibm.com IBM Informix Dynamic Server (IDS) http://www.ibm.com/developerworks/blogs/page/idsteam?entry=3Dterminate_= idle_users_with_the Terminate Idle Users with the Database Admin System The Database Admin System is a framework that can simplify many tasks f= or DBAs, application developers and end users. In addition, these tasks ca= n be seamlessly integrated into a graphical admin system, such as, the OpenA= dmin Tool for IDS. We will examine how a DBA can take advantage of the Database Admin Syst= em to solve a real life problem. The problem we are going to explore is to= how to remove users who have been idle for more than a specified length of time, only during work hours. Prior to the database admin system a DBA would utilize several different operating system tools, such as, shell scripting and cron. In addition, if this is pre-packaged system these n= ew scripts and cron entries will have to be integrated into an installed script. Lastly this needs to be portable across all supported platforms= . If you are to utilize the database Admin system you only have to add a = few lines to your schema file and you are done. Since this is only SQL you = will have the advantage of being portable across different flavors of UNIX a= nd Windows. The components we are going to utilize are: Database Scheduler Alert System User Configurable Thresholds SQL Admin API We are going to break the above problem into three separate parts. 1. Creating a tunable threshold for the idle time out 2. Develop a stored procedure to terminate the idle users 3. Schedule this procedure to run at regular intervals = "Tim Ertl" = <tim@lmrgroup.com = > = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Syssessions connected [13437] = 09/19/2008 09:38 = AM = = = Please respond to = ids@iiug.org = = = I am attempting to write something to cleanup all the disconnected sess= ions on my 11.10 server automaticly. 9.4 never had this problem but my probl= em now is, the sysmaster-syssessions.connected variable. Should show some sort of time yet it is an integer variable. I found it= fully undocumented in the admin ref manual. All admin ref says is that = it is "connected integer Time that user connected to the database server". Wh= at time format? What is this variable saying. All entries look like this: 1219588344 Being such a large number even for new users I can assume that it is no= t the number of seconds connected. Maybe it is the time OF the connection, we= ll that would be in datatime format? So what is this thing? Any thoughts or pointers welcome. Thanks,,,, Tim Ertl 413-442-9000 x6211 ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Great info, thanks everyone for the suggestions but alas, it looks like I am re0-inventing the wheel (thanks Jim) AND I am not solving the root problem (thanks ART). So Art, KEEP ALIVE, where can I read more about this? It is my guess your thoughts are to avoid the problem to start with. Currently my sqlhosts file has no 5th argument or a k=0 on the client or server end. Actually most of these programs run directly on the server. Thanks,,, Tim Ertl 413-442-9000 x6211 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Friday, September 19, 2008 2:08 PM To: ids@iiug.org Subject: Re: Syssessions connected [13442] An aside, make sure someone didn't turn off keepalive in the sqlhosts file on the server and on the clients (k=0 option in the connection string disables keepalive checking). Art On Fri, Sep 19, 2008 at 12:38 PM, Tim Ertl <tim@lmrgroup.com> wrote: > I am attempting to write something to cleanup all the disconnected sessions > on my 11.10 server automaticly. 9.4 never had this problem but my problem > now is, the sysmaster-syssessions.connected variable. > > Should show some sort of time yet it is an integer variable. I found it > fully undocumented in the admin ref manual. All admin ref says is that it > is > "connected integer Time that user connected to the database server". What > time format? What is this variable saying. All entries look like this: > > 1219588344 > > Being such a large number even for new users I can assume that it is not > the > number of seconds connected. Maybe it is the time OF the connection, well > that would be in datatime format? So what is this thing? > > Any thoughts or pointers welcome. > > Thanks,,,, > > Tim Ertl > > 413-442-9000 x6211 > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

The default for the keepalive option is supposed to be k=1, but I've seen setting k=1 explicitely fix this problem and the default in the ODBC/ESQL. library may NOT be k=1 on the client side (but the server side is more important anyway). This is documented in the Administrator's Guide in the section on the SQLHOSTS file. You don't mention your plaform, if it is Solaris, IB the default timeout on dead connections is very high (on the order of hours), so you may have to adjust the kernel's keepalive timeout down to something more reasonable like 15mins. Art On Fri, Sep 19, 2008 at 3:12 PM, Tim Ertl <tim@lmrgroup.com> wrote: > Great info, thanks everyone for the suggestions but alas, it looks like I > am > re0-inventing the wheel (thanks Jim) AND I am not solving the root problem > (thanks ART). > > So Art, KEEP ALIVE, where can I read more about this? It is my guess your > thoughts are to avoid the problem to start with. > > Currently my sqlhosts file has no 5th argument or a k=0 on the client or > server end. Actually most of these programs run directly on the server. > > Thanks,,, > > Tim Ertl > 413-442-9000 x6211 > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Friday, September 19, 2008 2:08 PM > To: ids@iiug.org > Subject: Re: Syssessions connected [13442] > > An aside, make sure someone didn't turn off keepalive in the sqlhosts file > on the server and on the clients (k=0 option in the connection string > disables keepalive checking). > > Art > > On Fri, Sep 19, 2008 at 12:38 PM, Tim Ertl <tim@lmrgroup.com> wrote: > > > I am attempting to write something to cleanup all the disconnected > sessions > > on my 11.10 server automaticly. 9.4 never had this problem but my problem > > now is, the sysmaster-syssessions.connected variable. > > > > Should show some sort of time yet it is an integer variable. I found it > > fully undocumented in the admin ref manual. All admin ref says is that it > > is > > "connected integer Time that user connected to the database server". What > > time format? What is this variable saying. All entries look like this: > > > > 1219588344 > > > > Being such a large number even for new users I can assume that it is not > > the > > number of seconds connected. Maybe it is the time OF the connection, well > > that would be in datatime format? So what is this thing? > > > > Any thoughts or pointers welcome. > > > > Thanks,,,, > > > > Tim Ertl > > > > 413-442-9000 x6211 > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > 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. > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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.

Thanks Art, I will check this out. I am running Solaris but I have stuff over 30 days old. Amazing what a difference it makes when I cleaned up. Thanks again! Tim Ertl 413-442-9000 x6211 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Friday, September 19, 2008 4:43 PM To: ids@iiug.org Subject: Re: Syssessions connected [13446] The default for the keepalive option is supposed to be k=1, but I've seen setting k=1 explicitely fix this problem and the default in the ODBC/ESQL. library may NOT be k=1 on the client side (but the server side is more important anyway). This is documented in the Administrator's Guide in the section on the SQLHOSTS file. You don't mention your plaform, if it is Solaris, IB the default timeout on dead connections is very high (on the order of hours), so you may have to adjust the kernel's keepalive timeout down to something more reasonable like 15mins. Art On Fri, Sep 19, 2008 at 3:12 PM, Tim Ertl <tim@lmrgroup.com> wrote: > Great info, thanks everyone for the suggestions but alas, it looks like I > am > re0-inventing the wheel (thanks Jim) AND I am not solving the root problem > (thanks ART). > > So Art, KEEP ALIVE, where can I read more about this? It is my guess your > thoughts are to avoid the problem to start with. > > Currently my sqlhosts file has no 5th argument or a k=0 on the client or > server end. Actually most of these programs run directly on the server. > > Thanks,,, > > Tim Ertl > 413-442-9000 x6211 > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Friday, September 19, 2008 2:08 PM > To: ids@iiug.org > Subject: Re: Syssessions connected [13442] > > An aside, make sure someone didn't turn off keepalive in the sqlhosts file > on the server and on the clients (k=0 option in the connection string > disables keepalive checking). > > Art > > On Fri, Sep 19, 2008 at 12:38 PM, Tim Ertl <tim@lmrgroup.com> wrote: > > > I am attempting to write something to cleanup all the disconnected > sessions > > on my 11.10 server automaticly. 9.4 never had this problem but my problem > > now is, the sysmaster-syssessions.connected variable. > > > > Should show some sort of time yet it is an integer variable. I found it > > fully undocumented in the admin ref manual. All admin ref says is that it > > is > > "connected integer Time that user connected to the database server". What > > time format? What is this variable saying. All entries look like this: > > > > 1219588344 > > > > Being such a large number even for new users I can assume that it is not > > the > > number of seconds connected. Maybe it is the time OF the connection, well > > that would be in datatime format? So what is this thing? > > > > Any thoughts or pointers welcome. > > > > Thanks,,,, > > > > Tim Ertl > > > > 413-442-9000 x6211 > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > 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. > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.