What in validates Dynamic SQL Packages in Package Cache ?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • PaulR
    #1

    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

    Tags: None
    • Serge Rielau
      #2
      Re: What in validates Dynamic SQL Packages in Package Cache ?

      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

        Comment

        • PaulR
          #3
          Re: What in validates Dynamic SQL Packages in Package Cache ?

          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?

            Comment

            • Serge Rielau
              #4
              Re: What in validates Dynamic SQL Packages in Package Cache ?

              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

                Comment

                Previous template Next
                Working...