PCKG Cache

Gopalan Venkatramani

PCKG Cache


DB2 LUW 10.5 fp 7

So I was been reading through the forums and tech articles that flush package is not a advisable to be executed on production database. So then I ended up with an discussion that sql query in the package cache don't have to produce access plans so any updates to the runstats will not be taken into consideration. So this is my question If queries in package cache doesn't creates a access plan how it will get updates to the updated statistics. Let me know if you can understand the question. 




Paul Bird

RE: PCKG Cache
(in response to Gopalan Venkatramani)


I am not sure I understand your question or the scenario that you are trying to understand but I can provide some background...


The relevant dynamic SQL entries in the package cache will be affected when you execute runstats (or update statistics manually) so that the next new request will automatically force a compilation. Similarly if you create an index for a table etc.


For example:

  1. I issue "SELECT * FROM T1"; this causes an entry for this statement to be compiled and put into the package cache.
  2. I execute the exact same statement again; there is no compilation this time and I would end up using the cached entry. 
  3. You then issue RUNSTATS on T1; part of the runstats logic will "invalidate" (i.e. mark it as no longer available for reuse) any cached dynamic SQL statement which is dependent on T1. This includes the cached "SELECT * FROM T1"
  4. I issue the same statement a third time; this time a new compilation occurs and a new entry is put into the package cache.


There are a few caveats and other factors that can affect the above but the basic philosophy is that the package cache will automatically keep dynamic SQL entries synchronized with the effects of statistics and DDL updates so that new requests for them always reflect the current environment.


FYI, the reason that FLUSH PACKAGE CACHE is not recommended for production systems is that it will invalidate all the cached dynamic SQL entries at once causing a massive surge in compilations which will hit resources and affect throughput.


Hope this helps,