There are DB2 LUW configuration parameters that can be set to AUTOMATIC and managed by the DB2 automatic memory manager (STMM). One advantage of this is that DB2 will automatically adjust the memory it uses to get the work done based on the characteristics of the current work load and it will continue to adjust itself dynamically as processing continues. Below is a list of configuration parameters for DB2 V9.5 that can be set to AUTOMATIC.
Here is a list of database manager (instance) configuration values for a partitioned environment that can be set to AUTOMATIC. Note that the SHEAPTHRES can be set to 0 and this combined with other database level configuration parameters can result in DB2 sort memory being managed by the automatic memory manager.
db2 attach to db2inst1
db2 get dbm cfg show detail
Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(4096)
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(889708)
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(0)
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(2000)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(4096)
No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
Sort heap threshold (4KB) (SHEAPTHRES) = 0
Here is a list of database configuration values that can be set to AUTOMATIC. Note that the CATALOGCACHE_SZ may be set to -1 and that this results in it’s memory size being derived by the formula (MAXAPPLS*4). If MAXAPPLS is set to AUTOMATIC then the databases catalog cache memory size is indirectly managed by DB2’s automatic memory manager.
db2 connect to db2db1
db2 get db cfg for db2db1
Size of database shared memory (4KB) (DATABASE_MEMORY) = CALCULATED
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(12097)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(3465)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(152076)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(3041)
Database heap (4KB) (DBHEAP) = AUTOMATIC(20000)
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(4096)
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(2000)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(6928)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(6600)
Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(1)
Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(18)
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC
Max number of active applications (MAXAPPLS) = AUTOMATIC(430)
Average number of active applications (AVG_APPLS) = AUTOMATIC(1)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Change self tuning memory to off and STMM is off. If you do not change the configuration values then they are set to the last value STMM used.
Self tuning memory (SELF_TUNING_MEM) = ON (Active)
LINUX Configuration Tip: If you want to use the DB2 LUW Automatic Memory Manager feature on a LINUX server then the maximum segment size (SHMMAX) should be configured to the same size as the maximum total shared memory (SHMALL) and the maximum total shared memory (SHMALL) should be configured to the total physical memory on the server.
DB2 Configuration Tip: If the maximum segment size is less than the maximum total shared memory for LINUX then you should avoid using AUTOMATIC on configuration parameters that manage the amount of shared memory that DB2 will use or you will experience problems if DB2 exceeds these limits. The same is true for the maximum total shared memory if it is set to something smaller than the maximum physical memory on the server. The SHMMAX and SHMALL LINUX configuration values limit the amount of shared memory DB2 can use on a LINUX server. If DB2 is configured to exceed these limits or if the DB2 automatic memory manager adjusts DB2 shared memory so that it exceeds these limits then LINUX will stop the DB2 threads when these limits are exceeded.
Here is the formula to use when configuring DB2 to run within the max segment memory size of an operating system like LINUX.
(util_heap_sz * 4096) +
(dbheap * 4096) +
(catalogcache_sz * 4096) +
(locklist * 4096) +
(pckcachesz * 4096) +
(bufferpool_1 * bufferpool_page_size) +
(bufferpool_n * bufferpool_page_size)
<= LINUX maximum segment size (SHMMAX)
You can determine the current values of the DB2 database utility heap size, database heap, catalog cache, lock list and package cache by connecting to the database and running the db2 get database configuration command with the show detail option.
db2 connect to db2db1
db2 get db cfg for db2db1 show detail
You can determine the current values for the DB2 bufferpools in a database by running the SQL select statement listed below. If you see a -2 value then the bufferpool is configured to have it’s memory managed by DB2’s automatic memory manager. Note, that size in bytes of memory for a bufferpool may be calculated by multiplying page size times the number of pages (PAGESIZE * NPAGES).
db2 -v "select substr(bpname,1,15) as bpname,pagesize,npages from syscat.bufferpools order by bpname"
select substr(bpname,1,15) as bpname,pagesize,npages from syscat.bufferpools order by bpname
BPNAME PAGESIZE NPAGES
--------------- ----------- -----------
BPTBL32K 32768 40960
BPTMP04K 4096 1000
BPTMP08K 8192 1000
BPTMP16K 16384 1000
BPTMP32K 32768 4395
IBMDEFAULTBP 16384 19000
In order to determine how a LINUX server is configured run the command ipcs –l.
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 262144 SHMMAX
max total shared memory (kbytes) = 8388608 SHMALL
min seg size (bytes) = 1
------ Semaphore Limits --------
max number of arrays = 1024
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767
------ Messages: Limits --------
max queues system wide = 1024
max size of message (bytes) = 65535
default max size of queue (bytes) = 65536
If you multiply the SHMMAX size times 1024 (Kbytes) then you will have the number of bytes available for a segment on the LINUX server. If this matches the SHMALL value and if the SHMALL value matches the total physical memory on the server then you are free to use AUTOMATIC for DB2 LUW database configuration values for utility heap size, database heap, catalog cache, lock list, package cache and bufferpools. If SHMMAX is less than SHMALL or if SHMALL is less than the physical memory available on the LINUX server then you should configure utility heap size, database heap, catalog cache, lock list, package cache and bufferpools so that the total bytes of shared memory they use is equal to or less than the LINUX segment maximum limit of shared memory limit, which ever is smaller.
You can set up a spreadsheet with formulas that will help you make sure that your DB2 LUW database configuration and bufferpool configuration will be equal to or less than the max seg size limit on the LINUX server. Here is an example of one way you could do this.