Is there way to get idle session?
The poster asked how to find sessions idle for ~4 hours and kill them. Art Kagel pointed to the Scheduler's idle_user_timeout task (disabled by default), enabled/tuned via updates to ph_task (tk_enable, tk_frequency) and ph_threshold. Fernando Nunes showed the alternative of checking net_last_read/net_last_write in sysmaster:sysnetworkio (or onstat -g ntt), and John Miller warned that network times can wrongly flag long-running queries as idle. Since the poster is on 11.50, which lacks the task, Art suggested rolling your own using the task's query (admin('onmode','z',sid) over sysrstcb/systcblst/sysscblst with last_run_time), adding user filters — and upgrading.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Is there any way to get all the idle session i..e about 4 hours after that kill those session Thank you very much --001a1135f1441adb0a052ea44435
There is a task manager task, idle_user_timeout, that is no enabled by default, that will watch for and kill idle tasks. See the section "Automatically terminating idle connections" in the Administrator's Guide PDF manual or in the online InfoCenter. You can enable the task with: UPDATE ph_task SET tk_enable = t WHERE tk_name = idle_user_timeout; And configure how frequently it polls for idle sessions and how long it will let them go before terminating them with: UPDATE ph_task SET tk_frequency = INTERVAL (5) MINUTE TO MINUTE WHERE tk_name = idle_user_timeout; UPDATE ph_threshold SET value = 5 WHERE task_name = idle_user_timeout; Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Tue, Mar 22, 2016 at 10:45 AM, medkba <medkba@gmail.com> wrote: > Hi, > > Is there any way to get all the idle session i..e about 4 hours after that > kill those session > > Thank you very much > > --001a1135f1441adb0a052ea44435 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013a1102d8ea52052ea474cc
Depends on your version how easy it is.
If you have this:
{ network IO Information }
create table informix.sysnetworkio
(
net_id int, { Net
ID }
sid int, { session
id }
net_netscb int8, { address of
netscb }
net_client_type int, { client
type }
net_client_name char(12), { client protocal
name }
net_read_cnt int8, { number of read
operations }
net_read_bytes int8, { # of bytes txfr to
server }
net_write_cnt int8, { number of write
operations }
net_write_bytes int8, { # of bytes txfr to
client }
net_open_time int, { time connection was
made }
net_last_read int, { time of last network
read }
net_last_write int, { time of last network
write }
net_state int, { state of network
connection }
net_options int, { sqlhost
options }
net_prot_id int, { id for protocal
name }
net_protocol char(10), { prtocal
name }
net_server_fd int, { poll server
fd }
net_poll_thread int { poll thread
id }
);
on your sysmaster, just check the net_last_read/net_last_write. It should
be a Unix time stamp which you can convert to a DATETIME YEAR TO SECOND
with DBINFO().
Be aware of the daylight saving time difference.
New versions will already have a task to do this:
idle_user_timeout
If you have an old version you may need to get this from:
castelo@primary:informix-> onstat -g ntt
IBM Informix Dynamic Server Version 12.10.FC6 -- On-Line -- Up 4 days
21:33:06 -- 287728 Kbytes
global network information:
#netscb connects read write q-free q-limits q-exceed
alloc/max
10/ 15 47 1250 1287 5/ 6 135/ 10 0/
0 6/ 6
Individual thread network information (times):
netscb thread name sid open read write
address
45f8ec78 sqlexec 1868 15:58:34 15:58:34 15:58:34
"sid", "read" and "write"
Regards
On Tue, Mar 22, 2016 at 2:45 PM, medkba <medkba@gmail.com> wrote:
> Hi,
>
> Is there any way to get all the idle session i..e about 4 hours after that
> kill those session
>
> Thank you very much
>
> --001a1135f1441adb0a052ea44435
>
>
>
>
*******************************************************************************
> 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...
--089e013cbd32a9e266052ea47619
Version 11.50 FC9
Thank you
On Tue, Mar 22, 2016 at 11:00 PM, Fernando Nunes <domusonline@gmail.com>
wrote:
> Depends on your version how easy it is.
> If you have this:
>
> { network IO Information }
>
> create table informix.sysnetworkio>
> (
>
> net_id int, { Net
> ID }
>
> sid int, { session
> id }
>
> net_netscb int8, { address of
> netscb }
>
> net_client_type int, { client
> type }
>
> net_client_name char(12), { client protocal
> name }
>
> net_read_cnt int8, { number of read
> operations }
>
> net_read_bytes int8, { # of bytes txfr to
> server }
>
> net_write_cnt int8, { number of write
> operations }
>
> net_write_bytes int8, { # of bytes txfr to
> client }
>
> net_open_time int, { time connection was
> made }
>
> net_last_read int, { time of last network
> read }
>
> net_last_write int, { time of last network
> write }
>
> net_state int, { state of network
> connection }
>
> net_options int, { sqlhost
> options }
>
> net_prot_id int, { id for protocal
> name }
>
> net_protocol char(10), { prtocal
> name }
>
> net_server_fd int, { poll server
> fd }
>
> net_poll_thread int { poll thread
> id }
>
> );
>
> on your sysmaster, just check the net_last_read/net_last_write. It should
> be a Unix time stamp which you can convert to a DATETIME YEAR TO SECOND
> with DBINFO().
> Be aware of the daylight saving time difference.
>
> New versions will already have a task to do this:
>
> idle_user_timeout
>
> If you have an old version you may need to get this from:
>
> castelo@primary:informix-> onstat -g ntt
>
> IBM Informix Dynamic Server Version 12.10.FC6 -- On-Line -- Up 4 days
> 21:33:06 -- 287728 Kbytes>
> global network information:
> #netscb connects read write q-free q-limits q-exceed
> alloc/max
> 10/ 15 47 1250 1287 5/ 6 135/ 10 0/
> 0 6/ 6
>
> Individual thread network information (times):
>
> netscb thread name sid open read write
> address
>
> 45f8ec78 sqlexec 1868 15:58:34 15:58:34 15:58:34
>
> "sid", "read" and "write"
>
> Regards
>
> On Tue, Mar 22, 2016 at 2:45 PM, medkba <medkba@gmail.com> wrote:
>
> > Hi,
> >
> > Is there any way to get all the idle session i..e about 4 hours after
> that
> > kill those session
> >
> > Thank you very much
> >
> > --001a1135f1441adb0a052ea44435
> >
> >
> >
> >
>
>
*******************************************************************************
> > 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...
>
> --089e013cbd32a9e266052ea47619
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1141b24efa542c052ea48aa3
One thing to consider when just doing network traffic to determine idle
time is
that a user can send to the server a large query and the engine works on
this
query for several hours without sending a message to the server. I would
not
call this session idle, but if you just look at network time they you
would.
I believe the idle=5Fsession task looks at the
CURRENT - DBINFO("utc=5Fto=5Fdatetime",last=5Frun=5Ftime) > time=5Fallowed
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/22/2016 08:00:11 AM:
> From: "Fernando Nunes" <domusonline@gmail.com>
> To: ids@iiug.org
> Date: 03/22/2016 08:02 AM
> Subject: Re: Is there way to get idle session? [36815]
> Sent by: ids-bounces@iiug.org
>
> Depends on your version how easy it is.
> If you have this:
>
> { network IO Information }
>
> create table informix.sysnetworkio>
> (
>
> net=5Fid int, { Net
> ID }
>
> sid int, { session
> id }
>
> net=5Fnetscb int8, { address of
> netscb }
>
> net=5Fclient=5Ftype int, { client
> type }
>
> net=5Fclient=5Fname char(12), { client protocal
> name }
>
> net=5Fread=5Fcnt int8, { number of read
> operations }
>
> net=5Fread=5Fbytes int8, { # of bytes txfr to
> server }
>
> net=5Fwrite=5Fcnt int8, { number of write
> operations }
>
> net=5Fwrite=5Fbytes int8, { # of bytes txfr to
> client }
>
> net=5Fopen=5Ftime int, { time connection was
> made }
>
> net=5Flast=5Fread int, { time of last network
> read }
>
> net=5Flast=5Fwrite int, { time of last network
> write }
>
> net=5Fstate int, { state of network
> connection }
>
> net=5Foptions int, { sqlhost
> options }
>
> net=5Fprot=5Fid int, { id for protocal
> name }
>
> net=5Fprotocol char(10), { prtocal
> name }
>
> net=5Fserver=5Ffd int, { poll server
> fd }
>
> net=5Fpoll=5Fthread int { poll thread
> id }
>
> );
>
> on your sysmaster, just check the net=5Flast=5Fread/net=5Flast=5Fwrite. I=
t should
> be a Unix time stamp which you can convert to a DATETIME YEAR TO SECOND
> with DBINFO().
> Be aware of the daylight saving time difference.
>
> New versions will already have a task to do this:
>
> idle=5Fuser=5Ftimeout
>
> If you have an old version you may need to get this from:
>
> castelo@primary:informix-> onstat -g ntt
>
> IBM Informix Dynamic Server Version 12.10.FC6 -- On-Line -- Up 4 days
> 21:33:06 -- 287728 Kbytes>
> global network information:
> #netscb connects read write q-free q-limits q-exceed
> alloc/max
> 10/ 15 47 1250 1287 5/ 6 135/ 10 0/
> 0 6/ 6
>
> Individual thread network information (times):
>
> netscb thread name sid open read write
> address
>
> 45f8ec78 sqlexec 1868 15:58:34 15:58:34 15:58:34
>
> "sid", "read" and "write"
>
> Regards
>
> On Tue, Mar 22, 2016 at 2:45 PM, medkba <medkba@gmail.com> wrote:
>
> > Hi,
> >
> > Is there any way to get all the idle session i..e about 4 hours after
that
> > kill those session
> >
> > Thank you very much
> >
> > --001a1135f1441adb0a052ea44435
> >
> >
> >
> >
>
***************************************************************************=
****
> > 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...
>
> --089e013cbd32a9e266052ea47619
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
True. And very well reminded. In most OLTP systemsthis shouldn't be an
issue, but kiiling a long query is not intended.
The sysadmin task does basically this:
SELECT admin("onmode","z",A.sid), A.username, A.sid, hostname
INTO rc, sys_username, sys_sid, sys_hostname
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) > time_allowed
UNITS MINUTE
AND LOWER(A.username) NOT IN( "informix", "root")
(taken from 12.10.FC6)
Regards
On Tue, Mar 22, 2016 at 3:13 PM, John Miller iii <miller3@us.ibm.com> wrote:
> One thing to consider when just doing network traffic to determine idle
> time is
> that a user can send to the server a large query and the engine works on
> this
> query for several hours without sending a message to the server. I would
> not
> call this session idle, but if you just look at network time they you
> would.
>
> I believe the idle=5Fsession task looks at the
>
> CURRENT - DBINFO("utc=5Fto=5Fdatetime",last=5Frun=5Ftime) > time=5Fallowed
>
> John F. Miller III
> STSM, Lead Architect
> miller3@us.ibm.com
> 503-747-1366
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/22/2016 08:00:11 AM:
>
> > From: "Fernando Nunes" <domusonline@gmail.com>
> > To: ids@iiug.org
> > Date: 03/22/2016 08:02 AM
> > Subject: Re: Is there way to get idle session? [36815]
> > Sent by: ids-bounces@iiug.org
> >
> > Depends on your version how easy it is.
> > If you have this:
> >
> > { network IO Information }
> >
> > create table informix.sysnetworkio> >
> > (
> >
> > net=5Fid int, { Net
> > ID }
> >
> > sid int, { session
> > id }
> >
> > net=5Fnetscb int8, { address of
> > netscb }
> >
> > net=5Fclient=5Ftype int, { client
> > type }
> >
> > net=5Fclient=5Fname char(12), { client protocal
> > name }
> >
> > net=5Fread=5Fcnt int8, { number of read
> > operations }
> >
> > net=5Fread=5Fbytes int8, { # of bytes txfr to
> > server }
> >
> > net=5Fwrite=5Fcnt int8, { number of write
> > operations }
> >
> > net=5Fwrite=5Fbytes int8, { # of bytes txfr to
> > client }
> >
> > net=5Fopen=5Ftime int, { time connection was
> > made }
> >
> > net=5Flast=5Fread int, { time of last network
> > read }
> >
> > net=5Flast=5Fwrite int, { time of last network
> > write }
> >
> > net=5Fstate int, { state of network
> > connection }
> >
> > net=5Foptions int, { sqlhost
> > options }
> >
> > net=5Fprot=5Fid int, { id for protocal
> > name }
> >
> > net=5Fprotocol char(10), { prtocal
> > name }
> >
> > net=5Fserver=5Ffd int, { poll server
> > fd }
> >
> > net=5Fpoll=5Fthread int { poll thread
> > id }
> >
> > );
> >
> > on your sysmaster, just check the net=5Flast=5Fread/net=5Flast=5Fwrite.
> I=
> t should
>
> > be a Unix time stamp which you can convert to a DATETIME YEAR TO SECOND
> > with DBINFO().
> > Be aware of the daylight saving time difference.
> >
> > New versions will already have a task to do this:
> >
> > idle=5Fuser=5Ftimeout
> >
> > If you have an old version you may need to get this from:
> >
> > castelo@primary:informix-> onstat -g ntt
> >
> > IBM Informix Dynamic Server Version 12.10.FC6 -- On-Line -- Up 4 days
> > 21:33:06 -- 287728 Kbytes> >
> > global network information:
> > #netscb connects read write q-free q-limits q-exceed
> > alloc/max
> > 10/ 15 47 1250 1287 5/ 6 135/ 10 0/
> > 0 6/ 6
> >
> > Individual thread network information (times):
> >
> > netscb thread name sid open read write
> > address
> >
> > 45f8ec78 sqlexec 1868 15:58:34 15:58:34 15:58:34
> >
> > "sid", "read" and "write"
> >
> > Regards
> >
> > On Tue, Mar 22, 2016 at 2:45 PM, medkba <medkba@gmail.com> wrote:
> >
> > > Hi,
> > >
> > > Is there any way to get all the idle session i..e about 4 hours after
> that
> > > kill those session
> > >
> > > Thank you very much
> > >
> > > --001a1135f1441adb0a052ea44435
> > >
> > >
> > >
> > >
> >
>
> ***************************************************************************=
> ****
>
> > > 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...
> >
> > --089e013cbd32a9e266052ea47619
> >
> >
> >
>
> ***************************************************************************=
> ****
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> 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...
--001a113f2a0c830c4e052ea4e614
Art, Some of the users are require to exclude as well. I couldn't find this task idle_user_timeout inside ph_task Thank you On Tue, Mar 22, 2016 at 10:59 PM, Art Kagel <art.kagel@gmail.com> wrote: > There is a task manager task, idle_user_timeout, that is no enabled by > default, that will watch for and kill idle tasks. See the section > "Automatically terminating idle connections" in the Administrator's Guide > PDF manual or in the online InfoCenter. You can enable the task with: > > UPDATE ph_task > SET tk_enable = t > WHERE tk_name = idle_user_timeout; > > And configure how frequently it polls for idle sessions and how long it > will let them go before terminating them with: > > UPDATE ph_task > SET tk_frequency = INTERVAL (5) MINUTE TO MINUTE > WHERE tk_name = idle_user_timeout; > > UPDATE ph_threshold > SET value = 5 > WHERE task_name = idle_user_timeout; > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. 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 Tue, Mar 22, 2016 at 10:45 AM, medkba <medkba@gmail.com> wrote: > > > Hi, > > > > Is there any way to get all the idle session i..e about 4 hours after > that > > kill those session > > > > Thank you very much > > > > --001a1135f1441adb0a052ea44435 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e013a1102d8ea52052ea474cc > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1141b24e309ed7052ea5cb14
You can't fine the procedure because you are running v11.50 (you really should upgrade to v12.10 BTW) which doesn't have it. However, see again the posts from Fernando and John for how to do it yourself. Of course you can add filters on userid and/or session #. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Tue, Mar 22, 2016 at 12:35 PM, medkba <medkba@gmail.com> wrote: > Art, > > Some of the users are require to exclude as well. I couldn't find this > task idle_user_timeout inside ph_task > > Thank you > > On Tue, Mar 22, 2016 at 10:59 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > There is a task manager task, idle_user_timeout, that is no enabled by > > default, that will watch for and kill idle tasks. See the section > > "Automatically terminating idle connections" in the Administrator's Guide > > PDF manual or in the online InfoCenter. You can enable the task with: > > > > UPDATE ph_task > > SET tk_enable = t > > WHERE tk_name = idle_user_timeout; > > > > And configure how frequently it polls for idle sessions and how long it > > will let them go before terminating them with: > > > > UPDATE ph_task > > SET tk_frequency = INTERVAL (5) MINUTE TO MINUTE > > WHERE tk_name = idle_user_timeout; > > > > UPDATE ph_threshold > > SET value = 5 > > WHERE task_name = idle_user_timeout; > > > > Art > > > > Art S. Kagel, President and Principal Consultant > > ASK Database Management > > www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. 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 Tue, Mar 22, 2016 at 10:45 AM, medkba <medkba@gmail.com> wrote: > > > > > Hi, > > > > > > Is there any way to get all the idle session i..e about 4 hours after > > that > > > kill those session > > > > > > Thank you very much > > > > > > --001a1135f1441adb0a052ea44435 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --089e013a1102d8ea52052ea474cc > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1141b24e309ed7052ea5cb14 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ee4fcce3799052ea74dfe