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
------------------------------
Original Message:
Sent: Tue January 23, 2024 03:18 PM
From: Art Kagel
Subject: Find currently executed/running sessions
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
Original Message:
Sent: Tue January 23, 2024 07:55 AM
From: Stefan Wilfling
Subject: Find currently executed/running sessions
Hi!
In this case I can say, that alll "cond wait*" is "idle" for JDBC-Connections?
------------------------------
Kind Regards
Stefan
Original Message:
Sent: Tue January 23, 2024 07:07 AM
From: Art Kagel
Subject: Find currently executed/running sessions
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
Original Message:
Sent: Tue January 23, 2024 04:14 AM
From: Stefan Wilfling
Subject: Find currently executed/running sessions
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
Original Message:
Sent: Sun January 21, 2024 09:58 AM
From: David Williams
Subject: Find currently executed/running sessions
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
Original Message:
Sent: Fri January 19, 2024 10:36 AM
From: Stefan Wilfling
Subject: 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 DESC
That 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):
| state | statedesc | statedetail |
| 0 | running | running |
| 1 | ready | ready |
| 1 | ready | running |
| 4 | cond wait | cond wait <unknown> |
| 4 | cond wait | cond wait await_MC1 |
| 4 | cond wait | cond wait await_MC10 |
| 4 | cond wait | cond wait await_MC98 |
| 4 | cond wait | cond wait backend:0 |
| 4 | cond wait | cond wait bp_cond |
| 4 | cond wait | cond wait defunct |
| 4 | cond wait | cond wait netnorm |
| 4 | cond wait | cond wait ReadAhead |
| 4 | cond wait | cond wait smx pipe1 |
| 5 | terminated | terminated |
| 7 | sleeping | IO Idle |
| 7 | sleeping | IO Wait |
| 7 | sleeping | sleeping forever |
| 7 | sleeping | sleeping secs: 0 |
| 7 | sleeping | sleeping secs: 1 |
| 7 | sleeping | sleeping secs: -1 |
| 7 | sleeping | sleeping secs: 10 |
How can I identify all sessions actively interacting with the database server (not idle)?
------------------------------
Thanks for your response
Stefan
------------------------------