instance_memory - Instance memory configuration parameter

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 (requires an instance attachment)

Configurable by member in a DB2 pureScale® environment and in partitioned database environments

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 the instance_memory parameter is AUTOMATIC. The AUTOMATIC setting results in a value that is 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 that is 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.

Updating the instance_memory parameter dynamically

• Dynamic updates to the instance_memory parameter 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 the instance_memory parameter 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 the instance_memory parameter 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 the instance_memory parameter is set to a value greater than the amount of system memory capacity, the next db2start command that you issue fails, and return the SQL1220N error message.
• If the instance_memory parameter is dynamically updated to AUTOMATIC, the value is recalculated immediately.

Suggestions for using fixed instance memory limits with a multi-member instance

Specifying a member number allows a different limit to be set on the specified member. Otherwise, the global value applies to all members. Since each member might have different memory requirements, fixed instance memory limits must be set carefully for each member. The following factors might be considered:

• Only fixed limits can be set when you are updating a member.
• When fixed instance memory limits are set in DB2 pureScale environments, ensure that STMM is configured to run independently on each member.
• It is suggested that you do not use STMM in a partitioned database environment with fixed instance memory limits.
• When the member clause is used, the instance_memory parameter value cannot be set to AUTOMATIC at the same time.

You can update a member's instance memory setting back to a global value by specifying the following values:

update dbm cfg member n using instance_memory 0

where n is the member number.

Controlling DB2 Memory consumption:

DB2 memory consumption varies depending on workload and configuration. In addition to this factor, self-tuning of the database_memory becomes a factor if it is enabled. Self-tuning of the 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 the instance_memory parameter 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. The 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 considerations are as follows:

• If self-tuning of database_memory is enabled and the instance_memory parameter is set to a specific value, STMM updates the configuration to achieve optimal performance while maintaining sufficient free instance memory. This behavior 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 the instance_memory parameter is set to AUTOMATIC, a instance_memory parameter 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 Tivoli® Storage FlashCopy® Manager shared memory is allocated, each local database partition's share of the overall shared memory size for the system is accounted for in that database partition's instance_memory usage.