Guys,

We have been experiencing package cache overflow on DB2 V9.5 FP 4

A suggestion from IBM that I should flush pacakage cache occasionally.

I tried, did not see any difference.

Am I doing some thing wrong. Still getting same number of SQLs. Also sum of all statements' size is about the same.

I am not clear about this. Please help.

Regards

DBFinder

I don't know why they would suggest you flush package cache if you have overflow. Either increase the package cache size, or reduce the number of unique SQL statments (using statements prepared with parameter markers instead of literals in the predicate will accomplish this if you using java apps).

When you flush package cache it flushes the access plan so that the next identical SQL statement will be recompiled, but it does not necessarily remove all the information.

Also, any statements that are in use by applications obviously will not be flushed.

But it "probably" does force the next user of that indentical SQL statement to recompile the access plan.

When I do a flush package cache and then a Snapshot for Dynamic SQL, I see lots of SQL statements with 0 compiles and 0 number of executions, so that is how I know it keeps some stuff in the package cache, even if it forces the next user of that SQL to recompile. Obviously, it could be flushed completely out of memory if an active package needed it and there was no free package cache memory available.

I just ran some tests on DB2 9.1. It looks like, after the dynamic cache is flushed, the last statement for each connection remains in the cache (even if it has completed and committed before the flush). Once a connection is reset, its last statement is also flushed. However, if another connection is made before that, and it uses the statement remaining in the cache, that statement won't be flushed after the first connection terminates. The snapshot indicates that by increasing the number of statement executions. However, it does force recompilation as the number of compilations is reset to 0.

I used db2pd -dynamic to verify that.

Thanks Friends,

What I think after discussions, that IBM software support has advised wrong. The flush package cache is not emptied. That suggestion was irrelevant.

Thanks

DBFinder

Flush package cache dynamic does force recompile of all new SQL statements. That is what it is designed to do. Whether some package information remains in memory has nothing to do with whether the cached access plan will be used next time, or whether a recompile is forced.

The reason why the IBM advice is questionable, is that if you don't have enough memory for package cache, forcing a recompile for all subsequent SQL will not make things better. When package cache is full and a new SQL statement comes in, then DB2 forces an old package out of memory to make room for the new one, sort of the same way bufferpools work. So even if flush package cache dynamic did remove all traces of all packages from package cache memory, it is still not a good to issue that statement as some sort of cure for not having package cache enough memory (or not using prepared statements).

The only thing worse than package cache overflow is wiping out the entire package cache memory (unless you need to do that for some reason such as a starting a new snapshot or because you want to force DB2 to select a new access plan for all SQL statements using new runstats, etc).

Well, the advised this as a local fix to Package Cache Overflow problem. Since Dec 1,2009 we had 2 database crashes due to Package Cache Overflow.
I did have doubt on this, so I called them back to double check if the local fix is right, since I did not see any difference.

Here is their last communication regarding that PMR.

My worry is/was whether after upgrade to FP5, the problem will go away or not ??

The problem may go away, but the local fix is not relevant, I am sure.

Thanks

DBFinder

According to IBM (per your note above) there is a memory leak in Package cache that is fixed in Fixpack 5 (I presume V9.5?). If that is what they say, then I have no reason to doubt them and in that case, the temporary fix may help keep from your database crashing. But it would not necessarily help if there was no memory leak, and besides, package cache overflow is not necessarily something to be avoided at all costs.

I would not go by your query as to determine whether the temporary fix is helping, since you cannot tell if the actual access plan is still in memory just by the number of packages in cache from your query.

Yes, I agree.

Actually there is no other choice for us. We had upgraded from V8. 9.5.0 then 9.5.3 then 9.5.4 and now 9.5.5 next week. Each fixpack had some issue. Package Cache Overflow appeared with 9.5.4.

When they told us to flush the package cache occasionally, we still had another crash (second one). They had said that flush helps in such case which actually proved to be untrue in our case.
May be package cache overflow is not the root cause. Well, let us see how it goes.

Thanks for your time.

DBFinder