FLUSH PACKAGE CACHE DYNAMIC

[Damir]

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

[Patrick Finnegan]

On Nov 11, 10:56*am, "Damir" <[e-mail]:

* * 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 .....................

[Damir]

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