Hi
We were having a discussion on package cache. One of our dev dbs
complained regularly of a package cache overflow and I started digging
into apps code since we were using good cache size(8000) 4 k pages. I
found this in one SP

while (i<100000)
do
prepare st1 from txt;
execute st1 using v_var1;
set i = i+1;
end while;

I assumed that since the statement was prepared inside the loop, it
would take a million prepares for this loop to complete. This would
use up so much package cache was what I thought. I then read that I
was wrong since db2 prepares only 'distinct' sqls. Since this was the
same I thought it shouldn't fill the package cache.
I also found this from db2top



ConnTime..: 13:28:56.841 UOW Start.:
13:28:56.997 Appl name.: Toad.exe DB2 user..:
DB2INST1
OS user...: ASRINIVASAN Agent id..:
25527 Coord DBP.: 0 Coord id..: 270077
Client pid: 2800 Hash
joins: 0 Hash loops: 0 HJoin
ovf.: 0
SQL Stmts.: 86,641
Sorts.....: 0 Sort time.: 0.000 Sorts
ovf.: 0
Rows Read.: 21 Rows
Sel..: 2 Read/Sel..: 10 Rows
Wrtn.: 86,650
Rows Ins..: 86,636 Rows
Upd..: 0 Rows Del..: 0 Locks
held: 86,644
Trans.....: 12 Open
Curs.: 0 Rem Cursor: 0
Memory....: 256.0K
Dyn. SQL..: 173282 Static
SQL: 6 Cpu Time..: 6.393923
AvgCpuStmt: 0.000

Please see the 'dyn sql;' and 'sql stmts' values - they are very very
high. Would that mean it really is adding each stmts to the package
cache? Would preparing the sql once above the loop solve this??

Please help.

On Sep 9, 2:31*pm, Arun Srini <arunro... (AT) gmail (DOT) com> wrote:
The best way to monitor Package Cache is

MON_GET_PKG_CACHE_STMT_DETAILS

http://publib.boulder.ibm.com/infoce.../r0056569.html

If u are not using V9.7 then u have to manually run the SNAPSHOTS and
identify the SQL which is causing this issue.
Once u know the SQL ..then it is easy to see why it is taking that
much of Cache...explains..tuning...etc

Cheers
Shashi Mannepalli