Configuring memory and memory heaps

With the simplified memory configuration feature, you can configure memory and memory heaps required by the Db2® data server by using the default AUTOMATIC setting for most memory-related configuration parameters, thereby, requiring much less tuning.

The simplified memory configuration feature provides the following benefits:
  • You can use a single parameter, instance_memory, to specify all of the memory that the database manager is allowed to allocate from its private and shared memory heaps. Also, you can use the appl_memory configuration parameter to control the maximum amount of application memory that is allocated by Db2 database agents to service application requests.
  • You are not required to manually tune parameters used solely for functional memory.
  • You can use the db2mtrk command to monitor heap usage and the ADMIN_GET_MEM_USAGE table function to query overall memory consumption.
  • The default Db2 configuration requires much less tuning, a benefit for new instances that you create.
The following table lists the memory configuration parameters whose values default to the AUTOMATIC setting. These parameters can also be configured dynamically, if necessary. Note that the meaning of the AUTOMATIC setting differs with each parameter, as described in the rightmost column.
Table 1. Memory configuration parameters whose values default to AUTOMATIC
Configuration parameter name Description Meaning of the AUTOMATIC setting
appl_memory Controls the maximum amount of application memory that is allocated by Db2 database agents to service application requests. If an instance_memory limit is enforced, the AUTOMATIC setting allows all application memory requests as long as the total amount of memory allocated by the database partition is within the instance_memory limit. Otherwise, it allows request as long as there are system resources available.
applheapsz Starting with Version 9.5, this parameter refers to the total amount of application memory that can be consumed by the entire application. For partitioned database environments, Concentrator, or SMP configurations, this means that you might need to increase the applheapsz value used in previous releases unless you use the AUTOMATIC setting. The AUTOMATIC setting allows the application heap size to increase. as needed. A limit might be enforced if there is an appl_memory limit or an instance_memory limit.
database_memory Specifies the amount of shared memory that is reserved for the database shared memory region. When enabled, the memory tuner determines the overall memory requirements for the database and increases or decreases the amount of memory allocated for database shared memory depending on the current database requirements. Starting with Version 9.5, AUTOMATIC is the default setting for all Db2 server products.
dbheap Determines the maximum memory used by the database heap. The AUTOMATIC setting allows the database heap to increase as needed. A limit might be enforced if there is a database_memory limit or an instance_memory limit.
instance_memory If you are using a Db2 database products with memory usage restrictions or if you set this parameter to a specific value, this parameter specifies the maximum amount of memory that can be allocated for a database partition. The AUTOMATIC setting allows the overall memory consumed by the entire database manager instance to grow as needed, and STMM ensures that sufficient system memory is available to prevent memory overcommitment. For Db2 database products with memory usage restrictions, the AUTOMATIC setting enforces a limit based on the lower of a computed value (75-95% of RAM) and the allowable memory usage under the license. See instance_memory for details on when it is enforced as a limit.
mon_heap_sz Determines the amount of the memory, in pages, to allocate for database system monitor data. The AUTOMATIC setting allows the monitor heap to increase as needed. A limit might be enforced if there is an instance_memory limit.
stat_heap_sz Indicates the maximum size of the heap used in collecting statistics using the RUNSTATS command. The AUTOMATIC setting allows the statistics heap size to increase as needed. A limit might be enforced if there is an appl_memory limit or an instance_memory limit.
stmtheap Specifies the size of the statement heap which is used as a work space for the SQL or XQuery compiler to compile an SQL or XQuery statement. The AUTOMATIC setting allows the statement heap to increase as needed. A limit might be enforced if there is an appl_memory limit or an instance_memory limit.
Note: The DBMCFG and DBCFG administrative views retrieve database manager configuration parameter information for the currently connected database for all database partitions. For the mon_heap_sz, stmtheap, and stat_heap_sz configuration parameters, the DEFERRED_VALUE column on this view does not persist across database activations. That is, when you issue the get dbm cfg show detail or get db cfg show detail command, the output from the query shows updated (in memory) values.
The following table shows whether configuration parameters are set to the default AUTOMATIC value during instance upgrade or creation and during database upgrade or creation.
Table 2. Configuration parameters set to AUTOMATIC during instance and database upgrade and creation
Configuration parameters Set to AUTOMATIC upon instance upgrade or creation Set to AUTOMATIC upon database upgrade Set to AUTOMATIC upon database creation
applheapsz1   X X
dbheap   X X
instance_memory X    
mon_heap_sz1 X    
stat_heap_sz1   X X
stmtheap1     X
As part of the move to simplified memory configuration, the following elements have been deprecated:
  • Configuration parameters appgroup_mem_sz, groupheap_ratio, and app_ctl_heap_sz. These configuration parameters are replaced with the new appl_memory configuration parameter.
  • The -p parameter of the db2mtrk memory tracker command. This option, which lists private agent memory heaps, is replaced with the -a parameter, which lists all application memory consumption.