Find currently executed/running sessions

Hi! We have a select to get the currently running statements: SELECT syssqlstat.sqs_dbname ,ROUND (systcblst.cpu_time, 1) ,syssqlstat.sqs_statement ,systcblst.state ,systcblst.statedetail ,systcblst.tid ,systcblst.name ,sysopendb.odb_dbname ,syssessions.feprogram FROM sysmaster:syssessions syssessions ,sysmaster:systcblst systcblst ,sysmaster:sysrstcb sysrstcb ,sysmaster:syssqlstat syssqlstat ,sysmaster:sysopendb sysopendb WHERE systcblst.tid = sysrstcb.tid AND syssessions.sid = sysrstcb.sid AND syssessions.sid = syssqlstat.sqs_sessionid AND BITAND (sysrstcb.flags, '0x435fede') = 0 AND sysrstcb.flags < '0x20000000' AND syssessions.sid <> DBINFO ('sessionid') AND syssessions.sid = sysopendb.odb_sessionid AND sysopendb.odb_iscurrent = 'Y' AND systcblst.statedesc = "running" ORDER BY systcblst.cpu_time DESCThat works good, but today we found out, that IO-Waiting-Statements are not shown (there was a problem at the storage). I attempted to gather information about the state columns in systcblst but did not find any documentation (but many dead links at ibm...). What I found out (doing many selects und grouping theme): How can I identify all sessions actively interacting with the database server (not idle)? ------------------------------ Thanks for your response Stefan ------------------------------

Hi, This is not documented. See this for idle sessions, perhaps exclude them? https://www.ibm.com/support/pages/system-monitor-interface-query-find-idle-time-sessions Regards, David. ------------------------------ David Williams ------------------------------

Small bug in this script ... systcblst doesn't have a column named "run_time", but it's "last_run_time". SELECT s.sid, s.username, q.odb_dbname database, s.pid, -- t.tid, hex(t.address) address, t.state, 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 ------------------------------ Thanks! Mark Scranton The Mark Scranton Group mark@markscranton.com ------------------------------

Hi! Thank, good Idea. I works good, but I get many "cond wait netnorm"-Sessions. ("cond wait netnorm" - description found I here) I think this are JDBC-Sessions, where the last (java.sql) Statement aren´t closed? ------------------------------ Kind Regards Stefan ------------------------------

Netnorm status is the main loop waiting for the client to send a request. So, an idle session connected via network (as opposed to a shared memory connection). Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi! In this case I can say, that alll "cond wait*" is "idle" for JDBC-Connections? ------------------------------ Kind Regards Stefan ------------------------------

Cond wait usually means the server is waiting for a locked "condition" held by another session to be released. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Art, instead of "waiting on a locked condition", which is kind of unduly conflating locks (latches, mutexes) with conditions, how about "waiting on a condition to arrive" which could be either an event to occur, e.g. new input from a session's client (signified by "cond wait netnorm/sm_read", for tcp or ipc connected clients), or an operation to complete, e.g. the blocking section of a checkpoint ("cond wait cp") In either case, the waiting thread would sleep indefinitely, but not without first having registered itself with the condition it's waiting on. The party (thread) eventually fulfilling the condition will then go and wake all waiters on this particular condition. To make things a little more confusing, the condition names displayed by various onstat options are not the conditions' identifiers, but rather serve as a hint for the human observer regarding their particular purpose. You can guess this from the fact that "onstat -g con" shows multiple conditions carrying the same name: e.g. each (tcp connected) SQL session would have its own 'netnorm' condition. The actual identifying part is a condition's address (a condition is a certain memory structure, with, among others, a numeric ID (purpose?), a name (informative only), a waiter list and a latch protecting the condition). Bottom line, for this discussion: a pure "cond wait" state is not an indicator for whether a thread/session currently is active, i.e. having work to do, or not. You'd have to know all possible condition names (impossible) or, as an approximation, you'd take the well known 'idle' conditions (netnorm, sm_read) and consider all others as 'busy' indicators, at least for regular session threads. HTH, Andreas ------------------------------ Andreas Legner ------------------------------