By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,602 Members | 1,507 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,602 IT Pros & Developers. It's quick & easy.

What in validates Dynamic SQL Packages in Package Cache ?

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.