Session Idle Time
Hi, We are running Informix 11.70.FC7 on Solaris. Does anyone have a SQL that will display how long each session has been idle? Thank You, --Dave --001a1134d03428c92a05084c6343
Hi,
database sysmaster;
SELECT s.sid, s.username, q.odb_dbname database, s.hostname,
dbinfo('UTC_TO_DATETIME',s.connected) conection_time,
dbinfo('UTC_TO_DATETIME',t.last_run_time) last_run_time,current - dbinfo('UTC_TO_DATETIME',t.last_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 7
HTH
Stuart
---
Ardenta Limited is ISO/IEC 20000-1 certified. We are proud to be named IT
Supplier Of The Year 2014 and 2013 at eGR B2B Awards.
Ardenta Limited is a company registered in England and Wales. Registered
number: 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
Middlesex, TW16 6RT.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Informix
DBA
Sent: 20 November 2014 15:58
To: ids@iiug.org
Subject: Session Idle Time [34202]
Hi,
We are running Informix 11.70.FC7 on Solaris. Does anyone have a SQL that
will display how long each session has been idle?
Thank You,
--Dave
--001a1134d03428c92a05084c6343
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Stuart,
Yes thank you that is wonderful. I found another similar version that
comes back instantly. We have an issue today where when we run onstat -u
we get the following error, "Changing data structure forced command
termination." We also have an older script that kills sessions that are
idle for more than 45 minutes. Though it doesn't work it onstat -u doesn't
work. Thank you for your SQL, certainly a help.
Here is another similar version:
select 'EXECUTE FUNCTION task("onmode","z","' || A.sid ||'");', a.sid,
a.username, hostname,
CURRENT - DBINFO("utc_to_datetime",last_run_time)
FROM sysmaster:sysrstcb A , sysmaster:systcblst B,
sysmaster:sysscblst C
WHERE A.tid = B.tid
AND C.sid = A.sid
AND lower(name) in ("sqlexec")
AND CURRENT - DBINFO("utc_to_datetime",last_run_time) > 45 UNITS MINUTE
AND lower(A.username) NOT IN( "informix", "root");
--David
On Thu, Nov 20, 2014 at 11:24 AM, Stuart Brooks <stuart.brooks@ardenta.com>
wrote:
> Hi,
>
> database sysmaster;>
> SELECT s.sid, s.username, q.odb_dbname database, s.hostname,
> dbinfo('UTC_TO_DATETIME',s.connected) conection_time,
> dbinfo('UTC_TO_DATETIME',t.last_run_time) last_run_time,> current - dbinfo('UTC_TO_DATETIME',t.last_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 7
>
> HTH
> Stuart
>
> ---
>
> Ardenta Limited is ISO/IEC 20000-1 certified. We are proud to be named IT
> Supplier Of The Year 2014 and 2013 at eGR B2B Awards.
>
> Ardenta Limited is a company registered in England and Wales. Registered
> number: 4181041. Registered office: Saxon House, Downside, Sunbury on
> Thames,
> Middlesex, TW16 6RT.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Informix
> DBA
> Sent: 20 November 2014 15:58
> To: ids@iiug.org
> Subject: Session Idle Time [34202]
>
> Hi,
>
> We are running Informix 11.70.FC7 on Solaris. Does anyone have a SQL that
> will display how long each session has been idle?
>
> Thank You,
>
> --Dave
>
> --001a1134d03428c92a05084c6343
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7beb986e5b89b905084cdf4c