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

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.