This parameter specifies the maximum
amount of memory that can be allocated for a database partition if
you are using DB2® database products
with memory usage restrictions or if you set it to a specific value.
Otherwise, the AUTOMATIC setting allows instance
memory to grow as needed.
- Configuration type
- Database manager
- Applies to
-
- Database server with local and remote clients
- Database server with local clients
- Partitioned database server with local and remote clients
- Parameter type
- Configurable online
- Default [range]
- AUTOMATIC[0 - system memory capacity]
The
DB2 license memory limit of the installed product further restricts
the maximum value. 32-bit instances are also restricted to a maximum
of 1000000.
- Unit of measure
- Pages (4 KB)
- When allocated
- Not applicable
- When freed
- Not applicable
The default value of instance_memory is AUTOMATIC.The AUTOMATIC setting results in
a value being computed at database partition activation. The computed
value ranges between 75 percent and 95 percent of the system memory
capacity on the system - the larger the system, the higher the percentage.
For DB2 database products with
memory usage restrictions, the computed value is also limited by the
maximum allowed by the product license. For database partition servers
with multiple logical database partitions, this computed value is
divided by the number of logical database partitions.
Starting with Version 9.7 Fix Pack 1
and Version 9.5 Fix Pack 5, the computed value for the AUTOMATIC setting
does not enforce a limit on memory allocated across the instance for DB2 database products without memory
usage restrictions. For Version 9.7 and Version 9.5 Fix Pack 4 or
earlier, the computed value for the AUTOMATIC setting
represents a limit for all DB2 database
products.
- Updating instance_memory dynamically
- Dynamic updates to instance_memory require
an instance attachment. See the ATTACH command
for details.
- For DB2 database products
with memory usage restrictions, dynamic updates to instance_memory must
indicate a value less than any license limit or AUTOMATIC. Otherwise,
the update fails and the SQL5130N error message is returned.
- Dynamic updates to instance_memory must indicate
a value less than the amount of system memory capacity or AUTOMATIC
. Otherwise, the update is deferred until the next db2start is
issued and the SQL1362W warning message is returned.
- Dynamic updates to instance_memory must indicate
a value larger than the current amount of in-use instance memory.
Otherwise, the update is deferred until the instance is restarted,
and the SQL1362W warning message is returned. The amount of in-use
instance memory can be determined by subtracting the Cached memory value
from Current usage value
in the output of the db2pd -dbptnmem command. The
minimum value would be the highest in-use instance memory across all
database partitions.
- If instance_memory is set to a value greater
than the amount of system memory capacity, the next db2start command
that you issue will fail and return the SQL1220N error message.
- If instance_memory is dynamically updated
to AUTOMATIC, the value is recalculated immediately.
- Restriction for instances in partitioned database environments
- You should not use of a specific value for instance_memory in partitioned database environments.
Using a specific value for instance_memory is
not recommended in partitioned database environments because
the instance_memory is a database manager configuration
parameter and it is not possible to specify different values for different
database partitions. This makes it difficult to establish a setting
suitable for all database partitions because they might have different
memory requirements.
- Controlling DB2 Memory consumption:
DB2 memory
consumption varies depending on workload and configuration. In addition
to this, self-tuning of database_memory becomes
a factor if it is enabled. Self-tuning of database_memory is
enabled when database_memory is set to AUTOMATIC
and the self-tuning memory manager (STMM) is active.
If the
instance is running on a DB2 database
product without memory usage restrictions and instance_memory is
set to AUTOMATIC, an instance_memory limit
is not enforced. The database manager allocates system memory as needed.
If self-tuning of database_memory is enabled,
STMM updates the configuration to achieve optimal performance while
monitoring available system memory. This monitoring of available memory
ensures that system memory is not over-committed
If the instance
is running on a DB2 database
product with memory usage restrictions or
instance_memory is
set to a specific value, an
instance_memory limit
is enforced. The database manager allocates system memory up to this
limit, the application can receive memory allocation errors when this
limit is reached. Additional consideration are as follows:
- If self-tuning of database_memory is enabled
and instance_memory is set to a specific value,
STMM updates the configuration to achieve optimal performance while
maintaining sufficient free instance memory. This ensures that enough
instance memory is available to satisfy volatile memory requirements.
System memory is not monitored.
- If self-tuning of database_memory is enabled
and instance_memory is set to AUTOMATIC,
this is the case where an instance_memory limit
is enforced for DB2 database
product with memory usage restrictions, STMM updates the configuration
to achieve optimal performance while monitoring available system memory
and maintaining sufficient free instance memory.
- Monitoring Instance Memory usage
Use the db2pd -dbptnmem command to show
details on instance memory usage.
Use
the new ADMIN_GET_MEM_USAGE table function to get the total instance
memory consumption by a DB2 instance
for a specific database partition, or for all database partitions.
This table function also returns the current upper bound value.
When
fast communication manager (FCM) shared memory is allocated, each
local database partition's share of the overall FCM shared memory
size for the system is accounted for in that database partition's instance_memory usage.