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.
- 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
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.
- 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.
- 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.
- This database manager configuration parameter limits the number of database connections or instance attachments that can access the data server at any one time.
- 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 memory tracker, invoked by the db2mtrk command, to view the current allocation of memory within the instance. You can also 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. You can use the db2mtrk command as an alternative to ipcs.