Thanks Art - I know that, their DBA knows that. But, as I explained some of users (app/dba users) are making some ad hoc queries. And when they realize it is lasting for a long time, they open OAT, locate their session (looking for query) and then terminate that session. This is simple explanation, there are some more events when they terminate their own session. And there is task for killing long lasting sessions (if I remember right). But sometimes they want session to last as long as it takes.
So, I know I can do it, DBA can do it - but then they would have one person assigned to killing sessions - since sometimes it is urgent to kill such a session and DBA is doing other stuff besides DBA and I have to jump through couple of vpn-s, log to vpn, ask for access, ....
The simplest way is that users kill their own session. They are not even logged on DB server - they use different frontend tools.
I think if HQ will not show these data they will have to turn sqltrace on for that users - but I miss that from OAT :)
Hrvoje
------------------------------
Hrvoje Zokovic
------------------------------
Original Message:
Sent: Thu April 22, 2021 02:12 PM
From: Art Kagel
Subject: HQ - Current SQL statement or Last parsed SQL statement
Hrvoje:
So, what the customer needs is the ability to locate sessions that have been inactive for a period of time so they can kill the session? Here:
select s.*, t.tid, current - dbinfo( 'utc_to_datetime', last_run_time ) idle_time
from syssessions as s, sysrstcb as r, systcblst as t
where s.sid = r.sid
and r.mttcb = t.address
and current - dbinfo( 'utc_to_datetime', last_run_time ) > 5.0;
You can join in sysmaster:syssqexplain to see the current SQL.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Thu April 22, 2021 12:20 PM
From: Hrvoje Zokovic
Subject: HQ - Current SQL statement or Last parsed SQL statement
Art, yes that is correct. And you predicted right - customer have users who are less active (they are doing some ad hoc queries) and they used OAT for finding their session so they can terminate it if necessary.
Hrvoje
------------------------------
Hrvoje Zokovic
Original Message:
Sent: Thu April 22, 2021 12:08 PM
From: Art Kagel
Subject: HQ - Current SQL statement or Last parsed SQL statement
Hrvoje:
If your site has that level of SQL traffic, then you are correct, SQL Trace isn't going to be particularly useful. An onstat -g ses or sql will show the "current" and "last" but that's an instantaneous picture and no better than the SQL Trace except that it will show the SQL for less active sessions better. Adding that capability to InformixHQ isn't going to help much, though I do agree that it should be there!
In this case you really need something like iWatch or SQL PowerTools, at least as long as most of your traffic is through network connections.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Thu April 22, 2021 11:58 AM
From: Hrvoje Zokovic
Subject: HQ - Current SQL statement or Last parsed SQL statement
Informix HQ does not display "Current SQL statement" or "Last parsed SQL statement" for session unless sql trace is turned on.
Is this bug or feature?
Some customers have 100.000s statements per second and turning sql trace on will keep just last 0,05 seconds of sql commands (unless you put 400GB for sql trace buffer - so you have to kill your server to monitor it - call me crazy but this is crazy)
Why HQ does not use sysmaster tables (as onstat -g sql does)?
Regards
Hrvoje
------------------------------
Hrvoje Zokovic
------------------------------
#Informix