RE: How long has a session been connected?

We run the following sysmaster query instead of "onstat -g ses". It provides similar information, but includes the start time for each user session along with other information. #!/usr/bin/ksh # # Script: sessions.sh # PURPOSE: Show active Informix sessions with user, database, start time # USAGE: sessions.sh # # SAMPLE DISPLAY: # # sid user database tty start_time_pdt pid # 266 rbernste sysmaster 1998-08-29 14:59 28352 export DELIMIDENT=1 TIMEZONE=`date +%Z` # Get time zone from UNIX $INFORMIXDIR/bin/dbaccess sysmaster 2>/dev/null <<-EOF! SET PDQPRIORITY OFF; SELECT LPAD(sid,9,' ') " sid ", -- Informix session id username AS "User", -- User name sqs_dbname[1,16] AS "Database", -- Database name tty, -- Workstation name EXTEND (dbinfo('UTC_TO_DATETIME',connected), YEAR TO MINUTE) AS "Start_Time_$TIMEZONE", LPAD(pid,6,' ') AS " pid" -- UNIX process id FROM syssessions, syssqlstat WHERE sid = sqs_sessionid AND sid != dbinfo('sessionid') -- Exclude this session ORDER BY 5 DESC, 1; EOF! exit 0 -----Original Message----- From: Jacob Salomon [mailto:JSalomon@bn.com] Sent: Wednesday, June 21, 2000 08:33 To: informix-list@iiug.org Subject: How long has a session been connected? Hi Family. In sysmaster:syssessions, there is a column named "connected", which is commented (in syssessions.sql) to be the time when the session connected to the server. Unfortunately, this is an integer-type column, the same type of number as other "timestamps" and does not give me a clue how many minutes (or hours) a session has been connected. My motivation is a suspicion that people are opening ODBC sessions and forgetting about them. Is there any way to determine: - The length of time a session has been connected to the server? A time-stamp of the connect time will be fine. - The time of the most recent databse activity of s specified session. Good heavens! Even onstat -g ses <session id> does not give me this information! Thanks. -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

** 674: Procedure (lpad) not found. ** regards from ottmar goedecke hdi informationsverarbeitung datanbankadministration podbielskistr. 396 D-30659 hannover tel 0511/645-4451, fax 0511/645-114451 e-mail: goedecke@hdi.de Bernstein, Rick <rbernste@alarismed.com> schrieb im Newsbeitrag: 8iqtbb$or1$1@news.xmission.com... > > We run the following sysmaster query instead of "onstat -g ses". > It provides similar information, but includes the start time for > each user session along with other information. > > > #!/usr/bin/ksh > # > # Script: sessions.sh > # PURPOSE: Show active Informix sessions with user, database, start > time > # USAGE: sessions.sh > # > # SAMPLE DISPLAY: > # > # sid user database tty start_time_pdt > pid > # 266 rbernste sysmaster 1998-08-29 14:59 > 28352 > > export DELIMIDENT=1 > TIMEZONE=`date +%Z` # Get time zone from UNIX > > $INFORMIXDIR/bin/dbaccess sysmaster 2>/dev/null <<-EOF! > SET PDQPRIORITY OFF;> SELECT LPAD(sid,9,' ') " sid ", -- Informix session > id > username AS "User", -- User name > sqs_dbname[1,16] AS "Database", -- Database name > tty, -- Workstation name > EXTEND (dbinfo('UTC_TO_DATETIME',connected), YEAR TO MINUTE) > AS "Start_Time_$TIMEZONE", > LPAD(pid,6,' ') AS " pid" -- UNIX process id > FROM syssessions, syssqlstat > WHERE sid = sqs_sessionid > AND sid != dbinfo('sessionid') -- Exclude this > session > ORDER BY 5 DESC, 1; > EOF! > > exit 0 > > -----Original Message----- > From: Jacob Salomon [mailto:JSalomon@bn.com] > Sent: Wednesday, June 21, 2000 08:33 > To: informix-list@iiug.org > Subject: How long has a session been connected? > > > Hi Family. > > In sysmaster:syssessions, there is a column named "connected", which is > commented (in syssessions.sql) to be the time when the session > connected to the server. Unfortunately, this is an integer-type > column, the same type of number as other "timestamps" and does not give > me a clue how many minutes (or hours) a session has been connected. > > My motivation is a suspicion that people are opening ODBC sessions and > forgetting about them. > > Is there any way to determine: > - The length of time a session has been connected to the server? A > time-stamp of the connect time will be fine. > - The time of the most recent databse activity of s specified session. > > Good heavens! Even onstat -g ses <session id> does not give me this > information! > > Thanks. > -- > +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ > |------------------- Bulletin Board Announcement ----------------------| > | Congregants will please note that the bowl at the back of the church | > | bearing the sign "For the Sick" is for monetary contributions only. | > +----------------------------------------------------------------------+ > > > Sent via Deja.com http://www.deja.com/ > Before you buy.