Memory allocation

Memory allocation and deallocation occurs at various times. Memory might be allocated to a particular memory area when a specific event occurs (for example, when an application connects), or it might be reallocated in response to a configuration change.

Figure 1 shows the different memory areas that the database manager allocates for various uses and the configuration parameters that enable you to control the size of these memory areas. Note that in a partitioned database environment, each database partition has its own database manager shared memory set.

Figure 1. Types of memory allocated by the database manager
The relationship between different memory areas that the database manager allocates
Memory is allocated by the database manager whenever one of the following events occurs:
When the database manager starts (db2start)
Database manager shared memory (also known as instance shared memory) remains allocated until the database manager stops (db2stop). This area contains information that the database manager uses to manage activity across all database connections. Db2® automatically controls the size of the database manager shared memory.
When a database is activated or connected to for the first time
Database global memory is used across all applications that connect to the database. The size of the database global memory is specified by the database_memory database configuration parameter. By default, this parameter is set to automatic, allowing Db2 to calculate the initial amount of memory allocated for the database and to automatically configure the database memory size during run time based on the needs of the database.
The following memory areas can be dynamically adjusted:
  • Buffer pools (using the ALTER BUFFERPOOL statement)
  • Database heap (including log buffers)
  • Utility heap
  • Package cache
  • Catalog cache
  • Lock list
The sortheap, sheapthres_shr, and sheapthres configuration parameters are also dynamically updatable. The only restriction is that sheapthres cannot be dynamically changed from 0 to a value that is greater than zero, or vice versa.

Shared sort operations are performed by default, and the amount of database shared memory that can be used by sort memory consumers at any one time is determined by the value of the sheapthres_shr database configuration parameter. Private sort operations are performed only if intrapartition parallelism, database partitioning, and the connection concentrator are all disabled, and the sheapthres database manager configuration parameter is set to a non-zero value.

When an application connects to a database
Each application has its own application heap, part of the application global memory. You can limit the amount of memory that any one application can allocate by using the applheapsz database configuration parameter, or limit overall application memory consumption by using the appl_memory database configuration parameter.
When an agent is created
Agent private memory is allocated for an agent when that agent is assigned as the result of a connect request or a new SQL request in a partitioned database environment. Agent private memory contains memory that is used only by this specific agent. If private sort operations have been enabled, the private sort heap is allocated from agent private memory.
The following configuration parameters limit the amount of memory that is allocated for each type of memory area. Note that in a partitioned database environment, this memory is allocated on each database partition.
numdb
This database manager configuration parameter specifies the maximum number of concurrent active databases that different applications can use. Because each database has its own global memory area, the amount of memory that can be allocated increases if you increase the value of this parameter.
maxappls
This database configuration parameter specifies the maximum number of applications that can simultaneously connect to a specific database. The value of this parameter affects the amount of memory that can be allocated for both agent private memory and application global memory for that database.
max_connections
This database manager configuration parameter limits the number of database connections or instance attachments that can access the data server at any one time.
max_coordagents
This database manager configuration parameter limits the number of database manager coordinating agents that can exist simultaneously across all active databases in an instance (and per database partition in a partitioned database environment). Together with maxappls and max_connections, this parameter limits the amount of memory that is allocated for agent private memory and application global memory.

. You can use the ADMIN_GET_MEM_USAGE table function to determine the total memory consumption for the entire instance or for just a single database partition. Use the MON_GET_MEMORY_SET and MON_GET_MEMORY_POOL table functions to examine the current memory usage at the instance, database, or application level.

On UNIX and Linux® operating systems, although the ipcs command can be used to list all the shared memory segments, it does not accurately reflect the amount of resources consumed.