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]

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

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

[Damir]

> db2 reset monitor all
> db2 connect to yourdb user youruser using password
> db2 flush package cache dynamic
> db2 connect reset
> db2 get snapshot .....................

I have tried that too, but it didn't work:

>db2 "select NUM_EXECUTIONS, NUM_COMPILATIONS, ROWS_READ,
>substr(STMT_TEXT,1,80) as stmt_text from SYSIBMADM.SNAPDYN_SQL order by
>rows_read desc fetch first 1 rows only"

NUM_EXECUTIONS NUM_COMPILATIONS ROWS_READ STMT_TEXT
-------------------- -------------------- -------------------- -------------------------------------
881 1 1393853832 SELECT
... FROM ...
1 record(s) selected.

>db2 reset monitor for db ppzprod
DB20000I The RESET MONITOR command completed successfully.

>db2 FLUSH PACKAGE CACHE DYNAMIC
DB20000I The SQL command completed successfully.

>db2 "select NUM_EXECUTIONS, NUM_COMPILATIONS, ROWS_READ,
>substr(STMT_TEXT,1,80) as stmt_text from SYSIBMADM.SNAPDYN_SQL order by
>rows_read desc fetch first 1 rows only"

NUM_EXECUTIONS NUM_COMPILATIONS ROWS_READ STMT_TEXT
-------------------- -------------------- -------------------- -------------------------------------
881 0 1393853832 SELECT
... FROM ...

1 record(s) selected.

>db2 "SELECT ... FROM ..." (one more execution of the same
>statement...)
...
>db2 "select NUM_EXECUTIONS, NUM_COMPILATIONS, ROWS_READ,
>substr(STMT_TEXT,1,80) as stmt_text from SYSIBMADM.SNAPDYN_SQL order by
>rows_read desc fetch first 1 rows only"

NUM_EXECUTIONS NUM_COMPILATIONS ROWS_READ STMT_TEXT
-------------------- -------------------- -------------------- -------------------------------------
882 1 1393857333
SELECT ... FROM ...

1 record(s) selected.

What am I missing here?

Regards,
Damir

[Cherry]

Flush package cache will not flush the packages that are currently in
use by any application.
So, make sure you terminate your connection with db and then do the
flush.

[Patrick Finnegan]

You are missing the "db2 connect reset" statement that I specified in
the example.

[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

[Ian]

There are two things going on here.

1) FLUSH PACKAGE CACHE only invalidates entries in the package cache --
it does not actually remove them. So, it will reset only the number
of compilations, and the best/worst prepare time.

2) There is a hole in the documentation.

The snapshot views (like SNAPDYN_SQL) and table functions (UDFs) they
are built on are not affected by the RESET MONITOR command. RESET
MONITOR only affects your local instance attachment. The UDF makes
its own instance attachment to take the snapshot, so it sees snapshot
monitor data since the instance was started (or the database was
activated, if you're looking at database-level snapshots).

Also related to this, the snapshot views also depend on the DFT_MON_%
database manager confguration variables. If these variables are not
ON, then the snapshot views return 0 for all of the entries. The
UPDATE MONITOR SWITCHES statement has no effect on these monitor
elements.