Hi,
I want to flush Dynamic Sql CACHE completely in db2 9.1.2
I used command "db2 FLUSH PACKAGE CACHE DYNAMIC".
But it lefts some queries in cache. These are fixed queries.

It creats problem for me to find out top 10 queries with high hit ratio, using table function snap_get_dyn_sql_v91().
These queries come in top 10 most of the time.

Is there any way to flush dynamic sql cache completely.

Lekh Raj

My guess is why the package cache did not flush totally is that those statements were active at the time of the flush command. Things you can do to try to fix this situation:

A) quiesce the db/instance, then flush when db is consistant
B) db2stop and db2start

I am not sure about A, but B is guaranteed to work.

Andy

Try doing:
db2 reset monitor all

Then only select the SQL statements with number of executions greater than zero.

Thanks ARWinner and Marcus_A for reply.
I cannot restart db/instance everytime. "reset monitor all" sounds good, but I want to reset dynamic sql counters only, not all.
Will check if "reset monitor all" doesn't affect other counters.


Lekh Raj