aj wrote:
> DB2 LUW 8.1 fixpak 14
> Red Hat EL AS 4.4
>
> I'm trying to diagnose some nocturnal CPU pressure, and am trying to
> understand the dynamic statement cache as it applies to LUW. The only
> doc/redbooks I am finding are for Z/OS, which I am completely ignorant
> of.
>
> I am using only Java and JDBC in my applications. No static SQL.
>
> How does dynamic statement cache work in LUW 8.1? Is there a local
> statement cache? I think there is a global statement cache (based
> upon the "get snapshot for dynamic sql on <db>" command), but how do I
> control it? Is it always turned on? Can I change its size?
The package cache holds the compiled plans and its size is controlled
by the pckcachesz database config parameter. Not sure what you mean
by "local" vs. "global" statement cache (is this in relation to DPF?)
When another query is prepared, DB2 looks to see if the plan already
exists in the cache -- by using a byte-for-byte comparison of the SQL
statements. (i.e. "select * from t1" <> "select * FROM t1"). If
the statements match *exactly*, DB2 will use the existing plan.
Note, many things will invalidate some/all entries in the cache, like
table changes, index changes, runstats, and "FLUSH PACKAGE CACHE
DYNAMIC"
Every statement that is executed goes into the cache. I don't know
the specific algorithm for how the cache is maintained, but it's
almost certainly LRU-based (least-recently-used).
> If I prepare a statement in a java app, will the compiled version
> of the statement remain in the cache after I have closed the
> PreparedStatement? Will it remain after the java program completely
> exits?
Yes, and yes.
|
