Hi all,
OS-AIX
DB2 - V8, FixPak7
I am trying to find the longest running query using the command ..
get snapshot for dynamic sql on <db_name>
The above command is listing all the quries on the db but I need to find out the longest running query..
Thanks in advance..

You could use the snapshot functions in a query. Something like this:

select * from table(snapshot_dyn_sql('MYDB',-1)) as x order by total_exec_time desc fetch first 5 rows only

Andy

Hi,
Continuing the very old post instead of creating a new. As recommended, we only enable switches as and when required. If asked to kill the longest running query so as to bring down the CPU utilization, I will first turn on the monitor switch for statements and timestamp (on by default). But this will not capture data for work in execution. How do I find the longest running query in such case?

Thanks,
db2cap

Uhm...
?

How's the "long running" part related to CPU utilization though?

DB2 - V8, FixPak7
not all sysibmadm views are available if any ....

przytula_guy: caught the point...
Actually we still have some of our databases on V8 which unfortunately does not have those easy to manage administrative views.

n_i, you did not got the pitch of my point actually. Even if I use SYSIBMADM.LONG_RUNNING_SQL view, will turning on the monitor switches immediately start gathering the data which otherwise is not collected for the running queries?

Kindly refer the below post for better understanding:
Statement text in LONG_RUNNING_SQL