kill idle session ~ Answered: amber (solid confidence) — Multiple independent responders converged on the same working technique (dbinfo('UTC_TO_DATETIME', connected) against sysmaster:syssessions, plus the built-in idle_user_timeout task); never explicitly confirmed by the OP.

Advisory only.

HI, In the development environment, some user connect to database with ISQL, query the data and doesn't quit for a long time, and sometimes they hold the locks. I want to write a shell script to kill those session that is idle for 10 minutes. I check the syssessions tables, it has a integer column named connected, How to know the login timestamp of the session? thanks for your time.

If you have newer version of informix, you can use this: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.admin.doc/i ds_admin_1366.htm On 22.02.2017 05:58, CHUAN LU wrote: > HI, > > In the development environment, some user connect to database with ISQL, query > the data and doesn't quit for a long time, and sometimes they hold the locks. > > I want to write a shell script to kill those session that is idle for 10 > minutes. I check the syssessions tables, it has a integer column named > connected, > How to know the login timestamp of the session? > thanks for your time. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- *Ivan Zaviš* System & database administrator *T* +381 21 68 98 608 | *M* +381 69 846 99 08 *@*ivan.zavis@mi-system.co.rs <mailto:ivan.zavis@mi-system.co.rs> *M&I Systems, Co. Group* Bulevar vojvode Stepe 16, 21000 Novi Sad *T:* +381 21 68 98 602 *F:* +381 21 68 98 604 *@:* info@mi-system.co.rs *w:* www.mi-system.co.rs <http://www.facebook.com/pages/MI-Systems-Co/263409380366499>; <http://www.linkedin.com/company/m&i-systems-co.>; <http://www.youtube.com/misystemsco>; Odricanje od odgovornosti: Ovaj dokument namenjen je samo licima kojima je upucen i za pozivanje na isti od stane bilo kog lica, neophodna je naknadna pismena potvrda njegovog sadržaja. Shodno tome, M&I Systems, Co. Novi Sad odrice svaku odgovornost i ne prihvata bilo kakvu obavezu (ukljucujuci slucaj nepažnje) za posledice koje može pretrpeti bilo koje lice zbog cinjenja ili necinjenja na bazi takve informacije pre nego što takva lica prime dodatnu pismenu potvrdu. Ukoliko ste greškom primili ovu elektronsku poruku, uništite ili izbrišite istu sa vašeg racunara. Svako umnožavanje, širenje, kopiranje, obelodanjivanje, izmene, distribucija i/ili objavljivanje ove elektronske poruke je strogo zabranjeno. Sadržaj ove elektronske poruke ne predstavlja nužno stavove M&I Systems, Co. Novi Sad

select dbinfo('UTC=5FTO=5FDATETIME', connected) from sysmaster:syssessions = ...=20 is your friend. You might also want to have a look at the predefined terminate=5Fidle=5Fus= ers=20 task in sysadmin:ph=5Ftask table, or look this up in the docs under=20 'Automatically terminating idle connections'. HTH, Andreas From: "CHUAN LU" <luchuan@cn.ibm.com> To: ids@iiug.org Date: 22.02.2017 05:59 Subject: kill idle session [38666] Sent by: ids-bounces@iiug.org HI,=20 In the development environment, some user connect to database with ISQL,=20 query=20 the data and doesn't quit for a long time, and sometimes they hold the=20 locks.=20 I want to write a shell script to kill those session that is idle for 10=20 minutes. I check the syssessions tables, it has a integer column named=20 connected,=20 How to know the login timestamp of the session?=20 thanks for your time.=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

Lu: You should be aware that such a task already exists in the sysadmin database ph_task table. The function is idle_user_timeout(). The timeout period is set in the ph_threshold table and defaults to 60 seconds: > select * from ph_threshold where task_name = 'idle_user_timeout'; id 6 name IDLE TIMEOUT task_name idle_user_timeout value 60 value_type NUMERIC description Maximum amount of time in minutes for non-informix users to be idl e. 1 row(s) retrieved. You just have to enable the task and set its periodicity. As far as your actual question: > select * from syssessions where sid = 49989; sid 49989 username art uid 1000 pid 8002 hostname galadriel-ii tty /dev/pts/1 connected 1487762864 feprogram /opt/informix/infmx.12.10.FC8WE/bin/dbaccess pooladdr 1979437120 is_wlatch 0 is_wlock 0 is_wbuff 0 is_wckpt 0 is_wlogbuf 0 is_wtrans 0 is_monitor 0 is_incrit 0 state 524321 1 row(s) retrieved. > select dbinfo( 'utc_to_datetime', connected ) from syssessions where sid = 49989; (expression) 2017-02-22 06:27:44 1 row(s) retrieved. 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, Feb 21, 2017 at 11:58 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > HI, > > In the development environment, some user connect to database with ISQL, > query > the data and doesn't quit for a long time, and sometimes they hold the > locks. > > I want to write a shell script to kill those session that is idle for 10 > minutes. I check the syssessions tables, it has a integer column named > connected, > How to know the login timestamp of the session? > thanks for your time. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f403045cf4e442a0c105491ce161

hi Lu, Try this as alternative to API task [checked in IDS11.50]: SELECT s.sid, dbinfo('UTC_TO_DATETIME',s.connected) connect_tm, dbinfo('UTC_TO_DATETIME',t.last_run_time) last_run_tm, DBINFO('UTC_CURRENT') - t.last_run_time idle_tm FROM sysmaster:syssessions s, sysmaster:systcblst t, sysmaster:sysrstcb r WHERE t.tid = r.tid AND s.sid = r.sid ; It returns idle_time in seconds aurelio -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, February 22, 2017 12:35 PM To: ids@iiug.org Subject: Re: kill idle session [38673] Lu: You should be aware that such a task already exists in the sysadmin database ph_task table. The function is idle_user_timeout(). The timeout period is set in the ph_threshold table and defaults to 60 seconds: > select * from ph_threshold where task_name = 'idle_user_timeout'; id 6 name IDLE TIMEOUT task_name idle_user_timeout value 60 value_type NUMERIC description Maximum amount of time in minutes for non-informix users to be idl e. 1 row(s) retrieved. You just have to enable the task and set its periodicity. As far as your actual question: > select * from syssessions where sid = 49989; sid 49989 username art uid 1000 pid 8002 hostname galadriel-ii tty /dev/pts/1 connected 1487762864 feprogram /opt/informix/infmx.12.10.FC8WE/bin/dbaccess pooladdr 1979437120 is_wlatch 0 is_wlock 0 is_wbuff 0 is_wckpt 0 is_wlogbuf 0 is_wtrans 0 is_monitor 0 is_incrit 0 state 524321 1 row(s) retrieved. > select dbinfo( 'utc_to_datetime', connected ) from syssessions where > sid = 49989; (expression) 2017-02-22 06:27:44 1 row(s) retrieved. 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, Feb 21, 2017 at 11:58 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > HI, > > In the development environment, some user connect to database with > ISQL, query the data and doesn't quit for a long time, and sometimes > they hold the locks. > > I want to write a shell script to kill those session that is idle for > 10 minutes. I check the syssessions tables, it has a integer column > named connected, How to know the login timestamp of the session? > thanks for your time. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f403045cf4e442a0c105491ce161 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

database sysadmin; select sid, username, dbinfo('UTC_TO_DATETIME',connected) conection_time, current - dbinfo('UTC_TO_DATETIME',connected) connected_duration from syssessions order by 4 desc,2,3