Hello All,

We have a Production DB2 Database running in AIX (8.1) Fixpak 11. Is there anyway i can find which user runs which Dynamic sql ? I could see 1000's of dynamic SQL's executed at a time in the server. Also Please let me know is there anyway to find the SQL that uses more CPU time.

Thanks & regards,
Senthil.T

with db2 get snapshot you can see the userid that has ben identified to db2
alse with another db2 get snapshot command .. dynamic sql... you can see the cpu usage by executed query in the dynamic sql cache.

You could try using Spotlight from Quest Central for DB2.

Thanks

Spotlight from Quest Central for DB2, is very useful. I hope this is just like a Query patroller.
As per policy we were not allowed to install any of additional products in our environments.
Dynamic SQL snapshot does not have the UserID's under which it runs, also it has only USER CPU and SYSTEM CPU..

Please let me know how can i see the dynamic SQL Cache..

Thanks & regards,
Senthil.T

this command will show user and connectid
C:\Documents and Settings\GUY>db2 get snapshot for application agentid 127

Application Snapshot

Application handle = 127
Application status = Connect Completed
Status change time = Not Collected
Application code page = 1252
Application country/region code = 0
DUOW correlation token = *LOCAL.DB2.070323125333
Application name = db2bp.exe
Application ID = *LOCAL.DB2.070323125333
Sequence number = 0001
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =

Connection request start timestamp = 23/03/2007 13:53:33.797228
Connect request completion timestamp = 23/03/2007 13:53:33.798058
Application idle time = Not Collected
CONNECT Authorization ID = DB2ADMIN
Client login ID = GUY
Configuration NNAME of client = DBSS_PC
Client database manager product ID = SQL08025

db2 get snapshot for dynamic sql on db2nt
will list the dynamic sql cache

Thanks Guy Przytula.

But the dynamic SQL snapshot doesn't have the UserID. Eventhough the application snapshot gives the UserID, Dynamic SQL shows a number of queries being executed, and where i could see the CPU time consumed by a single query also.

Number of executions = 1
Number of compilations = 1
Worst preparation time (ms) = 0
Best preparation time (ms) = 0
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 0
Internal rows updated = 0
Rows written = 0
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 0.000000
Total user cpu time (sec.ms) = 0.000000
Total system cpu time (sec.ms) = 0.000000
Statement text = CALL REORGCHK_IX_STATS ('T', '"PSEPM "."PS_WFA_WKFRCE1_T7"')

The only thing that i could not find here is the USER ID under which the query is being executed. Please let me know if there is any other way to get the User ID info.

Thanks & regards,
Senthil.T

"db2 get snapshot for application agentid xxx"
should give you some user information, I get these 3 values on my 8.1 FP 14 instance:

CONNECT Authorization ID
Client login ID
Configuration NNAME of client

Thankyou for the informations