DB2 Express C 10.5.4 – Memory Usage for Package Cache

db2

The testing environment (Win64) uses a express c – which does pretty well. Now when testing a java batch it runs out of private memory exhausting all available memory (14.8 GB) to package cache. As a first action I put on STMM because I thought this would help. Now, because it did not work out, I disabled the STMM and still something is eating up the memory. The production environment is on DB2 10.1 WSE with 64GB available. I am afraid that one day our applications will run into the same situation on this machine.

In db2diag.log everything started with sqlcode -101 "statement too long".
The output of db2mtrk is:

Speicher wird überwacht am 2015/10/30 um 06:59:18.

Speicher für Instanz

   other       fcmbp       monh        
   57,0M       832,0K      1,8M        

Speicher für Datenbank: EASY    

   utilh       pckcacheh   other       catcacheh   bph (5)     bph (4)     
   64,0K       14,8G       192,0K      2,7M        64,9M       64,9M       

   bph (3)     bph (1)     bph (S32K)  bph (S16K)  bph (S8K)   bph (S4K)   
   82,1M       80,7M       1,8M        1,5M        1,4M        1,3M        

   shsorth     lockh       dbh         apph (1149) apph (1148) apph (1147) 
   2,3M        46,6M       107,4M      128,0K      128,0K      128,0K      

   apph (1146) apph (1145) apph (1144) apph (1143) apph (1142) apph (1141) 
   128,0K      128,0K      128,0K      128,0K      128,0K      128,0K

My setting from get db cfg:

 Speicher mit automatischer Leistungsoptimierung                       (SELF_TUNING_MEM)    = OFF
 Größe des gemeinsamen Datenbankspeichers (4 KB)                       (DATABASE_MEMORY)    = AUTOMATIC(180832)
 Schwellenwert für Datenbankspeicher                                   (DB_MEM_THRESH)      = 100
 Max. Speicher für Sperrenliste (4 KB)                                 (LOCKLIST)           = AUTOMATIC(11200)
 Anzahl der Sperrenlisten pro Anwend. (in )                           (MAXLOCKS)           = AUTOMATIC(97)
 Größe des Paketcache  (4 KB)                                          (PCKCACHESZ)         = 16384
 Sortierspeicherschwelle für gemeinsame Sortierungen (4 KB)            (SHEAPTHRES_SHR)     = AUTOMATIC(5025)
 Zwischenspeicher für Sortierlisten (4 KB)                             (SORTHEAP)           = AUTOMATIC(1005)

If someone has a clue, an idea or hint for me – I would be very glad to hear from you.

Best Answer

Many applications or persistence frameworks generate new statements for each interaction with the database. The DB2 statement concentrator can help save system resources by mapping similar statements to one and reusing access plans and thereby share resources. Usually this help to cut query compilation time and reduce required system resources.

The package cache size can either be automatically set (self-tuning memory manager) or manually specified. In your case it seemed manually setting made more sense as the cache was hoping to reuse statements. If it is set to a fixed value, cache entries would need to be removed for new statements. However, the rest of your system memory could have been assigned to a bigger bufferpool.

Related Question