I recently wrote about using DB2 9.x’s Self Tuning Memory Manager (STMM) to automatically configure memory sizes.  If you’re running DB2 9.5 and 9.7, automatic configuration is available for a long list of parameters.  But what if you have to support 9.1, before several settings went automatic?

This question came to me yesterday from a co-worker who was getting database heap “out of memory” errors.  This was under DB2 9.1, where automatic wasn’t yet allowed for dbheap.  As I wrote earlier, several memory areas (like the log buffers) come out of dbheap, so if you increase one of these or set one to automatic, the dbheap needs to grow to accommodate it.  If you have to support 9.1, an automatic dbheap is not an option, so you may have to find a “high enough” fixed ceiling the hard way: trial and error.

You can get DB2 to help you come up with a number, a couple of ways:

• Run autoconfigure.  Use “apply none”; for example, “db2 autoconfigure apply none”.  See what it recommends.
• Run the system for awhile (under volume) on a DB2 9.7 install, with STMM enabled and dbheap set to automatic.  Then run “db2 get db cfg show detail” and see what it computed; it’ll be in parentheses after the word automatic.

These same approaches can also be used to determine good optional initial sizes to use along with  automatic settings.  STMM remembers and stores the results of prior self-tuning activities, so once your database has been running awhile under typical volumes (for at least a few 3 minute tuning cycles), the initial sizes don’t matter much.  But, in some cases, you may want to provide initial values so that a new database install is ready to bolt into action.