"aj" <ronald@[EMAIL PROTECTED]
> wrote in message
news:NeqdnYcK99d1j4vVnZ2dnUVZ_oesnZ2d@[EMAIL PROTECTED]
> 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?
>
> 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?
>
> Any help appreciated.
>
> aj
Package cache is global (for the database) and packages are not flushed
out
when a connection that created the package is closed. They can be used by
any application connection. If DB2 finds an (absolutely) identical SQL
statements already in cache, it will use that access plan and will not
have
to calculate a new one. The text of the SQL must be identical, including
spaces, etc.
It is best to use prepared statements with parameter makers ("?") if the
predicates change from one execution to the next, so that DB2 can reuse
the
package in cache. If you use literals in the predicate, each statement
will
be different, and little reuse will be possible.
The size is controlled by the PCKCACHESZ parm in the database
configuration
(get db config for db-name). You can monitor package cache overflow in the
application snapshot.
|
