What in validates Dynamic SQL Packages in Package Cache ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PaulR - 23 May 2006 09:57 GMT

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

Reply to this Message

Serge Rielau - 23 May 2006 12:08 GMT

> Hi,
>
[quoted text clipped - 16 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

PaulR - 23 May 2006 16:44 GMT

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?

Reply to this Message

Serge Rielau - 24 May 2006 09:43 GMT

> Thanks Serge,
>
[quoted text clipped - 3 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message