469,626 Members | 1,369 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

What in validates Dynamic SQL Packages in Package Cache ?

Hi,

I am trying to understand what invalidates Dynamic Packages in the
Package Cache.

By monitoring the Size of the Package Cache, it appears the following
does

1. Performing a Runstats on all Tables reduces the Used Package Cache
from some 200+MB to about 30MB ??

2. Performing a Refresh of an MQT appears to cause some packages to be
recompiled?

This is causing us pain as we have some Dynamic packages that take
considerable time (10secs +) to compile and so it is imperative we hold
them in the package cache.

Can anybody inform us what will invalidate Dynamic Packages - and if
our observations are correct?

Many Thanks.
Paul

May 23 '06 #1
3 2557
PaulR wrote:
Hi,

I am trying to understand what invalidates Dynamic Packages in the
Package Cache.

By monitoring the Size of the Package Cache, it appears the following
does

1. Performing a Runstats on all Tables reduces the Used Package Cache
from some 200+MB to about 30MB ??

2. Performing a Refresh of an MQT appears to cause some packages to be
recompiled?

This is causing us pain as we have some Dynamic packages that take
considerable time (10secs +) to compile and so it is imperative we hold
them in the package cache.

Can anybody inform us what will invalidate Dynamic Packages - and if
our observations are correct?

Whenever is an action is performed on an object upon which a statement
in the cache depends these statements are invalidated.
If you do runstats on ALL tables, then yes, nearly all statements will
be invalidated because DB2 presumes you're going to want to pick up new
plans.

I suppose you could round robin your runstats...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 23 '06 #2
Thanks Serge,

Guess that's working as designed then - is there a option anywhere to
disable using new Stats. for existing statements?

And I guess the same goes for statements referencing MQTs after a
Refresh?

May 23 '06 #3
PaulR wrote:
Thanks Serge,

Guess that's working as designed then - is there a option anywhere to
disable using new Stats. for existing statements?

And I guess the same goes for statements referencing MQTs after a
Refresh?

That kind of goes against the whole idea. Can you reason this out for
me. I need to understand the pain point (compile cpu load, what if,
....?) (Perhaps offline).

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

92 posts views Thread by Reed L. O'Brien | last post: by
4 posts views Thread by Kay Schluehr | last post: by
14 posts views Thread by Ina Schmitz | last post: by
3 posts views Thread by aj | last post: by
2 posts views Thread by Damir | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.