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

PaulR wrote:[color=blue]
> 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?[/color]
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

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?

PaulR wrote:[color=blue]
> 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?
>[/color]
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