Kill inactive persistent sessions
A user on IDS 10 (Linux) asked whether Informix can automatically time out and kill persistent sessions left idle by a buggy application. The consensus: there's no built-in idle timeout in IDS 10, so you must script it. Suggestions included watching the last-activity columns of 'onstat -g ntt' and killing with 'onmode -z <sid>', and a sysmaster query (joining syssessions, systcblst, sysrstcb, sysopendb with dbinfo('UTC_TO_DATETIME',t.run_time)) to compute idle time; one poster shared a shell loop that kills sessions idle over 24 hours. IBM's John Miller noted IDS 11 can terminate idle users via a server-side task controlled by OpenAdmin Tool/SQL.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi list! I'm having some problems with inactive persistent sessions. I have some app that IS NOT working properly and it's keeping open sessions in Informix. I know I have to fix that app. But while the app is fixed, is there some way of setting a timeout of persistent connections in Informix? I want sessions that don't have activity in 5 minutes to be killed. Thanks in advice and Regards, Emiliano S. Romero This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
Version of Informix? OS? Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Emiliano Romero Sent: Thursday, June 05, 2008 1:50 PM To: ids@iiug.org Subject: Kill inactive persistent sessions [12333] Hi list! I'm having some problems with inactive persistent sessions. I have some app that IS NOT working properly and it's keeping open sessions in Informix. I know I have to fix that app. But while the app is fixed, is there some way of setting a timeout of persistent connections in Informix? I want sessions that don't have activity in 5 minutes to be killed. Thanks in advice and Regards, Emiliano S. Romero This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Sorry, IBM Informix Dynamic Server Version 10.00.FC6 running in a Linux Red Hat Regards -----Mensaje original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Robert Roussey(IT) Enviado el: Jueves, 05 de Junio de 2008 03:05 p.m. Para: ids@iiug.org Asunto: RE: Kill inactive persistent sessions [12334] Version of Informix? OS? Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Emiliano Romero Sent: Thursday, June 05, 2008 1:50 PM To: ids@iiug.org Subject: Kill inactive persistent sessions [12333] Hi list! I'm having some problems with inactive persistent sessions. I have some app that IS NOT working properly and it's keeping open sessions in Informix. I know I have to fix that app. But while the app is fixed, is there some way of setting a timeout of persistent connections in Informix? I want sessions that don't have activity in 5 minutes to be killed. Thanks in advice and Regards, Emiliano S. Romero This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. __________ Información de NOD32, revisión 3154 (20080603) __________ Este mensaje ha sido analizado con NOD32 antivirus system http://www.nod32.com This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
Perhaps you may have to script this out by utilizing onstat -g ntt
for example:
onstat -g ntt | grep 2932
34d56018 sqlexec 2932 13:32:03 11:31:12 11:31:12
----date
Thu Jun 5 15:20:18 EDT 2008
then monitor the last 2 columns for a certain period of time; if this doesn't
change, the session is pretty much idle and can safely be killed with onmode
-z 2932.
----- Original Message ----
From: Emiliano Romero <eromero@sitrack.com>
To: ids@iiug.org
Sent: Thursday, June 5, 2008 1:50:18 PM
Subject: Kill inactive persistent sessions [12333]
Hi list! I'm having some problems with inactive persistent sessions. I have
some app that IS NOT working properly and it's keeping open sessions in
Informix. I know I have to fix that app. But while the app is fixed, is
there some way of setting a timeout of persistent connections in Informix? I
want sessions that don't have activity in 5 minutes to be killed.
Thanks in advice and Regards,
Emiliano S. Romero
This message is for the designated recipient only and may contain privileged,
proprietary, or otherwise private information.
If you have received it in error, please notify the sender immediately and
delete the original. Any other use of the email by you is prohibited.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hello. Taking advantage of this thread, I'd like to ask
¿does it work this command for a local dbaccess started process?
Thanks.
--Nestor
El jue, 05-06-2008 a las 15:29 -0400, Kern Doe escribió:
> Perhaps you may have to script this out by utilizing onstat -g ntt
> for example:
> onstat -g ntt | grep 2932
> 34d56018 sqlexec 2932 13:32:03 11:31:12 11:31:12
> ----> date
> Thu Jun 5 15:20:18 EDT 2008
>
> then monitor the last 2 columns for a certain period of time; if this doesn't
> change, the session is pretty much idle and can safely be killed with onmode
> -z 2932.
>
> ----- Original Message ----
> From: Emiliano Romero <eromero@sitrack.com>
> To: ids@iiug.org
> Sent: Thursday, June 5, 2008 1:50:18 PM
> Subject: Kill inactive persistent sessions [12333]
>
> Hi list! I'm having some problems with inactive persistent sessions. I have
> some app that IS NOT working properly and it's keeping open sessions in
> Informix. I know I have to fix that app. But while the app is fixed, is
> there some way of setting a timeout of persistent connections in Informix? I
> want sessions that don't have activity in 5 minutes to be killed.
>
> Thanks in advice and Regards,
>
> Emiliano S. Romero
>
> This message is for the designated recipient only and may contain privileged,
> proprietary, or otherwise private information.
> If you have received it in error, please notify the sender immediately and
> delete the original. Any other use of the email by you is prohibited.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Yes it should work for all threads. Many times, users connect to the database
via dbaccess then leave the sessions open; and onstat -g ntt should tell
whether they are active or not. In some cases, there are running away backup
threads, this should also help me to know.
----- Original Message ----
From: nestor gomez munoz <nestorgm@prodigy.net.mx>
To: ids@iiug.org
Sent: Thursday, June 5, 2008 5:04:06 PM
Subject: Re: Kill inactive persistent sessions [12337]
Hello. Taking advantage of this thread, I'd like to ask
¿does it work this command for a local dbaccess started process?
Thanks.
--Nestor
El jue, 05-06-2008 a las 15:29 -0400, Kern Doe escribió:
> Perhaps you may have to script this out by utilizing onstat -g ntt
> for example:
> onstat -g ntt | grep 2932
> 34d56018 sqlexec 2932 13:32:03 11:31:12 11:31:12
> ----> date
> Thu Jun 5 15:20:18 EDT 2008
>
> then monitor the last 2 columns for a certain period of time; if this
doesn't
> change, the session is pretty much idle and can safely be killed with onmode
> -z 2932.
>
> ----- Original Message ----
> From: Emiliano Romero <eromero@sitrack.com>
> To: ids@iiug.org
> Sent: Thursday, June 5, 2008 1:50:18 PM
> Subject: Kill inactive persistent sessions [12333]
>
> Hi list! I'm having some problems with inactive persistent sessions. I have
> some app that IS NOT working properly and it's keeping open sessions in
> Informix. I know I have to fix that app. But while the app is fixed, is
> there some way of setting a timeout of persistent connections in Informix? I
> want sessions that don't have activity in 5 minutes to be killed.
>
> Thanks in advice and Regards,
>
> Emiliano S. Romero
>
> This message is for the designated recipient only and may contain
privileged,
> proprietary, or otherwise private information.
> If you have received it in error, please notify the sender immediately and
> delete the original. Any other use of the email by you is prohibited.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I think I spotted this SQL on www.informixfaq.com. Worked fine with IDS 10.
Goes against the sysmaster db.
set isolation to dirty read;SELECT -- s.sid as sid,
s.username[1,10] as user,
q.odb_dbname[1,10] database,
s.tty[1,12] as tty,
-- dbinfo('UTC_TO_DATETIME',s.connected) con_time,
dbinfo('UTC_TO_DATETIME',t.run_time) last_run,
current - dbinfo('UTC_TO_DATETIME',t.run_time) idle_time
FROM syssessions s,
systcblst t,
sysrstcb r,
sysopendb q
WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.odb_sessionid
ORDER BY 5 DESC
Bob Roussey
Unix / Informix Administration
Spirit Airlines
Robert.Roussey@SpiritAir.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Kern Doe
Sent: Thursday, June 05, 2008 5:14 PM
To: ids@iiug.org
Subject: Re: Kill inactive persistent sessions [12338]
Yes it should work for all threads. Many times, users connect to the database
via dbaccess then leave the sessions open; and onstat -g ntt should tell
whether they are active or not. In some cases, there are running away backup
threads, this should also help me to know.
----- Original Message ----
From: nestor gomez munoz <nestorgm@prodigy.net.mx>
To: ids@iiug.org
Sent: Thursday, June 5, 2008 5:04:06 PM
Subject: Re: Kill inactive persistent sessions [12337]
Hello. Taking advantage of this thread, I'd like to ask
¿does it work this command for a local dbaccess started process?
Thanks.
--Nestor
El jue, 05-06-2008 a las 15:29 -0400, Kern Doe escribió:
> Perhaps you may have to script this out by utilizing onstat -g ntt
> for example:
> onstat -g ntt | grep 2932
> 34d56018 sqlexec 2932 13:32:03 11:31:12 11:31:12
> ----> date
> Thu Jun 5 15:20:18 EDT 2008
>
> then monitor the last 2 columns for a certain period of time; if this
doesn't
> change, the session is pretty much idle and can safely be killed with onmode
> -z 2932.
>
> ----- Original Message ----
> From: Emiliano Romero <eromero@sitrack.com>
> To: ids@iiug.org
> Sent: Thursday, June 5, 2008 1:50:18 PM
> Subject: Kill inactive persistent sessions [12333]
>
> Hi list! I'm having some problems with inactive persistent sessions. I have
> some app that IS NOT working properly and it's keeping open sessions in
> Informix. I know I have to fix that app. But while the app is fixed, is
> there some way of setting a timeout of persistent connections in Informix? I
> want sessions that don't have activity in 5 minutes to be killed.
>
> Thanks in advice and Regards,
>
> Emiliano S. Romero
>
> This message is for the designated recipient only and may contain
privileged,
> proprietary, or otherwise private information.
> If you have received it in error, please notify the sender immediately and
> delete the original. Any other use of the email by you is prohibited.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi all,
This is useful stuff - we have a lot of automatic processes that connect to
the database and sometimes fail to drop the session when they are done.
Here is the script for killing sessions older than 24 hours based on the SQL
that Robert Roussey posted. Actually mine has a few user filters specific to
my site. I am running on Informix 10 FC5
for i in $(dbaccess sysmaster 2>/dev/null << EOF|tail +4
set isolation to dirty read;
SELECT s.sid as sid
FROM syssessions s,
systcblst t,
sysrstcb r,
sysopendb q
WHERE t.tid = r.tid AND s.sid = r.sid
AND s.sid = q.odb_sessionid
AND (current year to day -
dbinfo('UTC_TO_DATETIME',t.run_time))::char(10)::integer > 0EOF
)
do
echo onmode -z $i
onmode -z $idone
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Robert
Roussey(IT)
Sent: 06 June 2008 04:46 PM
To: ids@iiug.org
Subject: RE: Kill inactive persistent sessions [12344]
I think I spotted this SQL on www.informixfaq.com. Worked fine with IDS 10.
Goes against the sysmaster db.
set isolation to dirty read;SELECT -- s.sid as sid,
s.username[1,10] as user,
q.odb_dbname[1,10] database,
s.tty[1,12] as tty,
-- dbinfo('UTC_TO_DATETIME',s.connected) con_time,
dbinfo('UTC_TO_DATETIME',t.run_time) last_run,
current - dbinfo('UTC_TO_DATETIME',t.run_time) idle_time FROM syssessions s,
systcblst t,
sysrstcb r,
sysopendb q
WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.odb_sessionid ORDER BY 5
DESC
Bob Roussey
Unix / Informix Administration
Spirit Airlines
Robert.Roussey@SpiritAir.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Kern
Doe
Sent: Thursday, June 05, 2008 5:14 PM
To: ids@iiug.org
Subject: Re: Kill inactive persistent sessions [12338]
Yes it should work for all threads. Many times, users connect to the
database via dbaccess then leave the sessions open; and onstat -g ntt should
tell whether they are active or not. In some cases, there are running away
backup threads, this should also help me to know.
----- Original Message ----
From: nestor gomez munoz <nestorgm@prodigy.net.mx>
To: ids@iiug.org
Sent: Thursday, June 5, 2008 5:04:06 PM
Subject: Re: Kill inactive persistent sessions [12337]
Hello. Taking advantage of this thread, I'd like to ask ¿does it work this
command for a local dbaccess started process?
Thanks.
--Nestor
El jue, 05-06-2008 a las 15:29 -0400, Kern Doe escribió:
> Perhaps you may have to script this out by utilizing onstat -g ntt for
> example:
> onstat -g ntt | grep 2932
> 34d56018 sqlexec 2932 13:32:03 11:31:12 11:31:12
> ----> date
> Thu Jun 5 15:20:18 EDT 2008
>
> then monitor the last 2 columns for a certain period of time; if this
doesn't
> change, the session is pretty much idle and can safely be killed with
> onmode -z 2932.>
> ----- Original Message ----
> From: Emiliano Romero <eromero@sitrack.com>
> To: ids@iiug.org
> Sent: Thursday, June 5, 2008 1:50:18 PM
> Subject: Kill inactive persistent sessions [12333]
>
> Hi list! I'm having some problems with inactive persistent sessions. I
> have some app that IS NOT working properly and it's keeping open
> sessions in Informix. I know I have to fix that app. But while the app
> is fixed, is there some way of setting a timeout of persistent
> connections in Informix? I want sessions that don't have activity in 5
minutes to be killed.
>
> Thanks in advice and Regards,
>
> Emiliano S. Romero
>
> This message is for the designated recipient only and may contain
privileged,
> proprietary, or otherwise private information.
> If you have received it in error, please notify the sender immediately
> and delete the original. Any other use of the email by you is prohibited.
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Might want to check this blog out. It explains how to kill idle users =
with
version
11 and have it run inside the database server and be control by the
graphical
OpenAdmin Tool or direct SQL.
http://www-128.ibm.com/developerworks/blogs/page/idsteam?entry=3Dtermin=
ate_idle_users_with_the
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
=
"Mark Tyrer" =
<mark.tyrer@rtt.c =
o.za> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
RE: Kill inactive persistent =
06/09/2008 02:35 sessions [12346] =
AM =
=
=
Please respond to =
ids@iiug.org =
=
=
Hi all,
This is useful stuff - we have a lot of automatic processes that connec=
t to
the database and sometimes fail to drop the session when they are done.=
Here is the script for killing sessions older than 24 hours based on th=
e
SQL
that Robert Roussey posted. Actually mine has a few user filters specif=
ic
to
my site. I am running on Informix 10 FC5
for i in $(dbaccess sysmaster 2>/dev/null << EOF|tail +4
set isolation to dirty read;
SELECT s.sid as sid
FROM syssessions s,
systcblst t,
sysrstcb r,
sysopendb q
WHERE t.tid =3D r.tid AND s.sid =3D r.sid
AND s.sid =3D q.odb_sessionid
AND (current year to day -
dbinfo('UTC_TO_DATETIME',t.run_time))::char(10)::integer > 0EOF
)
do
echo onmode -z $i
onmode -z $idone
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Robert
Roussey(IT)
Sent: 06 June 2008 04:46 PM
To: ids@iiug.org
Subject: RE: Kill inactive persistent sessions [12344]
I think I spotted this SQL on www.informixfaq.com. Worked fine with IDS=
10.
Goes against the sysmaster db.
set isolation to dirty read;SELECT -- s.sid as sid,
s.username[1,10] as user,
q.odb_dbname[1,10] database,
s.tty[1,12] as tty,
-- dbinfo('UTC_TO_DATETIME',s.connected) con_time,
dbinfo('UTC_TO_DATETIME',t.run_time) last_run,
current - dbinfo('UTC_TO_DATETIME',t.run_time) idle_time FROM syssessio=
ns
s,
systcblst t,
sysrstcb r,
sysopendb q
WHERE t.tid =3D r.tid AND s.sid =3D r.sid AND s.sid =3D q.odb_sessionid=
ORDER BY
5
DESC
Bob Roussey
Unix / Informix Administration
Spirit Airlines
Robert.Roussey@SpiritAir.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of K=
ern
Doe
Sent: Thursday, June 05, 2008 5:14 PM
To: ids@iiug.org
Subject: Re: Kill inactive persistent sessions [12338]
Yes it should work for all threads. Many times, users connect to the
database via dbaccess then leave the sessions open; and onstat -g ntt
should
tell whether they are active or not. In some cases, there are running a=
way
backup threads, this should also help me to know.
----- Original Message ----
From: nestor gomez munoz <nestorgm@prodigy.net.mx>
To: ids@iiug.org
Sent: Thursday, June 5, 2008 5:04:06 PM
Subject: Re: Kill inactive persistent sessions [12337]
Hello. Taking advantage of this thread, I'd like to ask =BFdoes it work=
this
command for a local dbaccess started process?
Thanks.
--Nestor
El jue, 05-06-2008 a las 15:29 -0400, Kern Doe escribi=F3:
> Perhaps you may have to script this out by utilizing onstat -g ntt fo=
r
> example:
> onstat -g ntt | grep 2932
> 34d56018 sqlexec 2932 13:32:03 11:31:12 11:31:12
> ----> date
> Thu Jun 5 15:20:18 EDT 2008
>
> then monitor the last 2 columns for a certain period of time; if this=
doesn't
> change, the session is pretty much idle and can safely be killed with=
> onmode -z 2932.>
> ----- Original Message ----
> From: Emiliano Romero <eromero@sitrack.com>
> To: ids@iiug.org
> Sent: Thursday, June 5, 2008 1:50:18 PM
> Subject: Kill inactive persistent sessions [12333]
>
> Hi list! I'm having some problems with inactive persistent sessions. =
I
> have some app that IS NOT working properly and it's keeping open
> sessions in Informix. I know I have to fix that app. But while the ap=
p
> is fixed, is there some way of setting a timeout of persistent
> connections in Informix? I want sessions that don't have activity in =
5
minutes to be killed.
>
> Thanks in advice and Regards,
>
> Emiliano S. Romero
>
> This message is for the designated recipient only and may contain
privileged,
> proprietary, or otherwise private information.
> If you have received it in error, please notify the sender immediatel=
y
> and delete the original. Any other use of the email by you is prohibi=
ted.
>
>
>
***********************************************************************=
*****
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
***********************************************************************=
*****
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
***********************************************************************=
*****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
***********************************************************************=
*****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
***********************************************************************=
*****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=