Hi all,

I am looking for a way to extract from our db2 udb database (v. 9.5) the queries executed during the last 1-2 days.
Reading on this forum I found a link to a "DB2 Monitor" tool that shows them, but what I need is to extract last the queries throught a view (I didn't find one that shows the last days history) or a clp command.

Any suggestion?

Thanks in advance,

Alessandro.

you can start event monitoring
see for detail
Collecting information about database system events
or use the get snapshot with dynamic sql option
or use sysibmadm administrative views ..
all detail at the same place

Hi przytula_guy,

Thanks for your answer. I'd like to avoid to start an event monitor because it's our production database and I must follow a long iter.
About the remaining solutions I already tried the views of sysibmadm schema but what I found was only views that shows just heavy query or cpu expensive query, executing queries, but nothing that lists all the queries of the last two/three days.

So I tried

get snapshot for dynamic sql on <mydatabase>

but I get the following error and I don't know how to solve:

How can I solve it?

Thanks,

Alessandro.

I executed this
db2 get snapshot for dynamic sql on clsm <-- in db2inst1 := instance owner
and no problem
are you local in the instance owner...

I tried to execute the "get snapshot" statement directly on the server and it runs.
Via the clp on my local machine I tried the one you suggested:

but I suppose I misunderstood your suggestion because it doesn't run, I get an error about "IN" clause no expected in that position.

Looking at the result of the command (on the server) it seems the same result I get with:

Isn't it?

yes - get snapshots can be produced with dynamic admin views doing similar operation as get snapshot

Ok thanks, when I saw it for the first time I was snared by the "snapshot_timestamp" field..
My problem is now that the view contains only data related to the executed query. Is there a way to link it to the user that launches it, the ip of its machine, and the real time the query was executed?

with event monitor you get this - similar to get snapshot for application agentid xxx

DUOW correlation token = 10.104.116.63.40191.110908065504
Application name = db2jcc_application
Application ID = 10.104.116.63.40191.110908065504
UOW start timestamp = 09/08/2011 08:55:04.942245
UOW stop timestamp = 09/08/2011 08:55:05.768879
UOW completion status = Committed - Commit Statement