how i can get which query is taking the most time for exceution ?????

or the highest cost of a query executing ???????????????????

is there any way?

I am checking ...

Pawan,  you can use these consults :

db2 "select TOTAL_EXEC_TIME/NUM_EXECUTIONS
as EXEC_TIME, TOTAL_EXEC_TIME, NUM_EXECUTIONS, substr (STMT_TEXT, 1,5000)
as statement from table(SNAPSHOT_DYN_SQL('<dbname>', -1)) as snap_dyn_sql
where NUM_EXECUTIONS > 1 order by 1 desc, TOTAL_EXEC_TIME desc"
> 1_slowdyn



db2 "select TOTAL_EXEC_TIME/NUM_EXECUTIONS
as EXEC_TIME, TOTAL_EXEC_TIME, NUM_EXECUTIONS, substr (STMT_TEXT, 1,5000)
as statement from table(SNAPSHOT_DYN_SQL('<dbname>', -1)) as snap_dyn_sql
where NUM_EXECUTIONS > 1 order by NUM_EXECUTIONS desc,1 desc" >
2_mostexecuteddyn



db2 create event monitor e for statements
write to table

 

db2 set event monitor e state=1



$ # wait long enough to catch a good
sample of activity.



db2 set event monitor e state=0



db2 "select rows_read, time(STOP_TIME)
- time(START_TIME) as TimeSecs, substr(cast(STMT_TEXT as varchar(5000)),1,5000)
from stmt_e order by rows_read desc, 2 desc fetch first 100 rows only"
> 3_evallrowsread



db2 "select rows_read, time(STOP_TIME)
- time(START_TIME) as TimeSecs, substr(cast(STMT_TEXT as varchar(5000)),1,5000)
from stmt_e order by 2 desc, rows_read desc fetch first 200 rows only"
> 4_evallexectime



db2 "select rows_read, time(STOP_TIME)
- time(START_TIME) as TimeSecs, substr(cast(s.text as varchar(5000)),1,5000)
from stmt_e e, syscat.statements s where e.stmt_type = 2 and e.package_name
= s.pkgname order by rows_read desc, 2 desc fetch first 100 rows only"
> 5_evprowsread



db2 "select rows_read, time(STOP_TIME)
- time(START_TIME) as TimeSecs, substr(cast(s.text as varchar(5000)),1,5000)
from stmt_e e, syscat.statements s where e.stmt_type = 2 and e.package_name
= s.pkgname order by 2 desc, rows_read desc fetch first 100 rows only"
> 6_evpexectime

Please, if these consults help you, post here your comments.

Thanks!

yes ..thanks buddy..