By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,567 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,567 IT Pros & Developers. It's quick & easy.

FLUSH PACKAGE CACHE DYNAMIC

P: n/a
Hello!
I have noticed that after (sucessfully) executing the command:
FLUSH PACKAGE CACHE DYNAMIC
the dynamic SQL statement cache is not completely cleared (some of the
dynamic SQL statement elements are not reset).

For example, looking at the dynamic SQL snapshot I see a certain SQL
statement that has been executed a number of times and has read a (big)
number of rows.
Then I execute the "FLUSH PACKAGE CACHE DYNAMIC" command.
Then I execute the same SQL statement one more time.
Now, when I issue another dynamic SQL snapshot I expect for this SQL
statement the number of executions to be one, and the number of rows read to
be equal to the number of rows read by *only* that last statement
invocation.
But this doesn't happen. Instead, the number of executions remains as before
the FLUSH command, increased by one, and the number of rows read is
increased by the number of rows read by the last invocation of the
statement.

Is this the expected result of the FLUSH command?
If so, how can I reset the NUM_EXECUTIONS and ROWS_READ (and to that matter
all other) elements of the dynamic SQL cache?

Regards,
Damir
Nov 11 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Nov 11, 10:56*am, "Damir" <damirwil...@yahoo.comwrote:
* * Hello!
I have noticed that after (sucessfully) executing the command:
* * FLUSH PACKAGE CACHE DYNAMIC
the dynamic SQL statement cache is not completely cleared (some of the
dynamic SQL statement elements are not reset).

For example, looking at the dynamic SQL snapshot I see a certain SQL
statement that has been executed a number of times and has read a (big)
number of rows.
Then I execute the "FLUSH PACKAGE CACHE DYNAMIC" command.
Then I execute the same SQL statement one more time.
Now, when I issue another dynamic SQL snapshot I expect for this SQL
statement the number of executions to be one, and the number of rows readto
be equal to the number of rows read by *only* that last statement
invocation.
But this doesn't happen. Instead, the number of executions remains as before
the FLUSH command, increased by one, and the number of rows read is
increased by the number of rows read by the last invocation of the
statement.

Is this the expected result of the FLUSH command?
If so, how can I reset the NUM_EXECUTIONS and ROWS_READ (and to that matter
all other) elements of the dynamic SQL cache?

Regards,
* * Damir


db2 reset monitor all
db2 connect to yourdb user youruser using password
db2 flush package cache dynamic
db2 connect reset

db2 get snapshot .....................


Nov 11 '08 #2

P: n/a
You are missing the "db2 connect reset" statement that I specified in
the example.
Hmm... supposing that I have a WebSphere Application Server that holds open
(many) connections to the database (that keep executing the aforementioned
queries), and I cannot reset these connections (because it is a production
site), in that case it is impossible to reset the counters?
(in spite of all said, I can reset - for example - the table counters (i.e.
rows read, overflows, etc.) ... but not the dynSQL ones ...)

Regards,
Damir
Nov 12 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.