instance_memory - Instance memory configuration parameter

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 (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 1,000,000.

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.

Possible values for instance_memory are:
  • AUTOMATIC- This value is the default value. 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.

    The AUTOMATIC setting allows Instance Memory usage to grow as needed. If STMM is enabled and tunes the overall database memory size, STMM tunes based on available system memory. This option indirectly determines the actual instance memory usage. The number of 4 KB pages is calculated, but applies only to a configuration, which has a license memory limit. In this case, the calculated instance_memory value is limited by the license memory limit.

  • 0 - Use this value only to reset a member-specific setting back to the global setting.
  • 1 - 100 - Specifies the instance_memory limit by calculating the percentage of available RAM divided by the number of local partitions. The in-memory value is updated at member startup time to reflect the calculated number of 4 KB pages. Used to set the Db2 percentage consumption of the total RAM on the workstation in Db2 instances with heterogeneous workstation hardware configurations.
  • 101 - system memory capacity - Specifies the memory limit as the number of 4 KB pages. This option also represents a tuning target if STMM is enabled.
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 being issued 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 the self-tuning of database_memory is enabled, STMM updates the configuration to achieve optimal performance while it monitors 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. More considerations are as follows:
  • If the 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 it maintains sufficient free instance memory. This behavior ensures that enough instance memory is available to satisfy volatile memory requirements. System memory is not monitored.
  • If the 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 it maintains 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.

Usage notes

  • The instance_memory configuration parameter can be configured for individual members by using the "for member" clause as part of the cfg update. If a value is specified for a member, that value applies only for that member. If no value is specified for a member, then the global value of instance_memory is used.

    The CLP output that is returned by db2 get dbm cfg shows what is applicable on the current member. Whether the member is using the global value or a per member override, the output shows only the currently in use value.

    If instance_memory is configured per-member on DPF or Db2 pureScale instances, then the deferred value for individual members is returned as the member_inst_mem configuration parameter name rather than through instance_memory for individual members.

    Example
    $ db2 "update dbm cfg member 1 using instance_memory 12345678 immediate"
          DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
    
    $ db2 "select name, current dbpartitionnum as member, varchar(VALUE, 20) VALUE, varchar(DEFERRED_VALUE,20) DEFERRED_VALUE 
          from SYSIBMADM.DBMCFG where NAME = 'instance_memory' or NAME = 'member_inst_mem'"
    
    NAME MEMBER VALUE DEFERRED_VALUE
    -------------------------------- ----------- -------------------- --------------------
    instance_memory 1 12345678 30914469
    member_inst_mem 1 12345678 12345678
    
    2 record(s) selected.
  • In a pureScale environment, if instance_memory is set to AUTOMATIC or as a percentage between the range 1 - 100 (inclusive), the computation takes into account of CF_MEM_SZ required by any CF residing in the same host as the member automatically. In addition, the effective instance memory limit is determined by deducting the restart light memory (rstrt_light_mem) from the instance_memory setting.

  • When instance memory is configured to use a percentage of system memory, the output of get dbm cfg with the show detail option will show the delayed and the ini-mem values as follows: the delayed value is the percentage that was used when the parameter was updated; this is the percentage that is used by db2start to calculate memory usage. The in-memory value will show that actual size of instance_memory currently in use, in pages.

    For example:
    <command output>
     $ db2 get dbm cfg show detail | grep -i instance_memory
    Global instance memory (% or 4KB) (INSTANCE_MEMORY) = 59385362     90
    </command output>