DB2 Version 9.7 for Linux, UNIX, and Windows

Performance variables

DB2_ALLOCATION_SIZE

Operating system: All
Default: 128 KB, Range: 64 KB - 256 MB
Specifies the size of memory allocations for buffer pools.
The potential advantage of setting a higher value for this registry variable is fewer allocations will be required to reach a desired amount of memory for a buffer pool.

The potential cost of setting a higher value for this registry variable is wasted memory if the buffer pool is altered by a non-multiple of the allocation size. For example, if the value for DB2_ALLOCATION_SIZE is 8 MB and a buffer pool is reduced by 4 MB, this 4 MB will be wasted because an entire 8 MB segment cannot be freed.

Note: DB2_ALLOCATION_SIZE is deprecated and might be removed in a later release.

DB2_APM_PERFORMANCE

Operating system: All
Default: OFF, Values: ON or OFF
Set this variable to ON to enable performance-related changes in the access plan manager (APM) that affect the behavior of the query cache (package cache). These settings are not usually recommended for production systems. They introduce some limitations, such as the possibility of out-of-package cache errors or increased memory use, or both.
Setting DB2_APM_PERFORMANCE to ON also enables the NO PACKAGE LOCK mode. This mode allows the global query cache to operate without the use of package locks, which are internal system locks that protect cached package entries from being removed. The NO PACKAGE LOCK mode might result in minor performance improvements, but certain database operations are not allowed. These prohibited operations might include: operations that invalidate packages, operations that inoperate packages, and PRECOMPILE, BIND, and REBIND.

DB2ASSUMEUPDATE

Operating system: All
Default: OFF, Values: ON or OFF
When enabled, this variable allows the DB2® database system to assume that all fixed-length columns provided in an UPDATE statement are being changed. This eliminates the need for the DB2 database system to compare the existing column values to the new values to determine if the column is actually changing. Using this registry variable can cause additional logging and index maintenance when columns are provided for update (for example, in a SET clause) but are not actually being modified.
The activation of the DB2ASSUMEUPDATE registry variable is effective on the db2start command.

DB2_ASYNC_IO_MAXFILOP

Operating system: All
Default: The value of the maxfilop configuration parameter, Values: from the value of maxfilop to the value of max_int
DB2_ASYNC_IO_MAXFILOP is deprecated and might be removed in a later release. This variable is obsolete because of the shared file handle table maintained by the threaded database manager. For more information, see Shared file handle table
DB2_ASYNC_IO_MAXFILOP can still be set in Version 9.7, but it will have no effect. If you want to limit the number of file handles that can be open for each database, see the maxfilop configuration parameter.

DB2_AVOID_PREFETCH

Operating system: All
Default: OFF, Values: ON or OFF
Specifies whether prefetch should be used during crash recovery. If DB2_AVOID_PREFETCH =ON, prefetch is not used.

DB2_BACKUP_USE_DIO

Operating system: All
Default: OFF, Values: ON or OFF
Specifies whether or not backup images are cached by the operating system. The default behavior is to cache the image file. When DB2_BACKUP_USE_DIO is set to ON, the backup image file is directly written to disk, bypassing the file cache.

Setting this variable to ON might result in the operating system better utilizing memory resources because there is no benefit to caching the backup image file. This performance impact will have the largest benefit for Linux platforms. However, there may be a slight slowdown of the backup itself, so you should measure the change in backup performance when DB2_BACKUP_USE_DIO is set to ON.
Note: Changing the value of this registry variable does not affect the behavior of the backup that is already running. Changing the value will take effect when the next backup is run, and it does not require an instance restart.

DB2BPVARS

Operating system: As specified for each parameter
Default: Path
Two sets of parameters are available to tune buffer pools. One set of parameters, available only on Windows, specify that buffer pools should use scatter read for specific types of containers. The other set of parameters, available on all platforms, affect prefetching behavior.
Parameters are specified in an ASCII file, one parameter on each line, in the form parameter=value. For example, a file named bpvars.vars might contain the following lines:
```
  
   NO_NT_SCATTER = 1
   NUMPREFETCHQUEUES = 2
	   
```
Assuming that bpvars.vars is stored in F:\vars\, to set these variables issue the following command:
```
   db2set DB2BPVARS=F:\vars\bpvars.vars
```
Scatter-read parameters

The scatter-read parameters are recommended for systems with a large amount of sequential prefetching against the respective type of containers and for which you have already set DB2NTNOCACHE to ON. These parameters, available only on Windows platforms, are NT_SCATTER_DMSFILE, NT_SCATTER_DMSDEVICE, and NT_SCATTER_SMS. Specify the NO_NT_SCATTER parameter to explicitly disallow scatter read for any container. Specific parameters are used to turn scatter read on for all containers of the indicated type. For each of these parameters, the default is zero (or OFF); and the possible values include: zero (or OFF) and 1 (or ON).

Note: You can turn on scatter read only if DB2NTNOCACHE is set to ON to turn Windows file caching off. If DB2NTNOCACHE is set to OFF or not set, a warning message is written to the administration notification log if you attempt to turn on scatter read for any container, and scatter read remains disabled.

Prefetch-adjustment parameters

The prefetch-adjustment parameters are NUMPREFETCHQUEUES and PREFETCHQUEUESIZE. These parameters are available on all platforms and can be used to improve bufferpool data prefetching. For example, consider sequential prefetching in which the desired PREFETCHSIZE is divided into PREFETCHSIZE/EXTENTSIZE prefetch requests. In this case, requests are placed on prefetch queues from which I/O servers are dispatched to perform asynchronous I/O. By default, the DB2 database manager maintains one queue of size max(200,2*NUM_IOSERVERS ) for each each database partition. In some environments, performance improves with either more queues or queues of a different size, or both. The number of prefetch queues should be at most one half of the number of I/O servers. When you set these parameters, consider other parameters such as PREFETCHSIZE, EXTENTSIZE, NUM_IOSERVERS, and buffer pool size, and workload characteristics such as the number of current users.

If you think the default values are too small for your environment, first increase the values only slightly. For example, you might set NUMPREFETCHQUEUES=4 and PREFETCHQUEUESIZE=200. Make changes to these parameters in a controlled manner so that you can monitor and evaluate the effects of the change.

For NUMPREFETCHQUEUES, the default is 1, and the range of values is 1 to NUM_IOSERVERS. If you set NUMPREFETCHQUEUES to less than 1, it is adjusted to 1. If you set it greater than NUM_IOSERVERS, it is adjusted to NUM_IOSERVERS.

For PREFETCHQUEUESIZE, the default value is max(200,2*NUM_IOSERVERS). The range of values is 1 to 32767. If you set PREFETCHQUEUESIZE to less than 1, it is adjusted to the default. If set greater than 32 767, it is adjusted to 32 767.

Note: DB2BPVARS is deprecated and might be removed in a later release.

DB2CHKPTR

Operating system: All
Default: OFF, Values: ON or OFF
Specifies whether pointer checking for input is required.

DB2CHKSQLDA

Operating system: All
Default: ON, Values: ON or OFF
Specifies whether SQLDA checking for input is required.

DB2_EVALUNCOMMITTED

Operating system: All
Default: NO, Values: YES, NO
When enabled, this variable allows, where possible, scans to defer or avoid row locking until the data is known to satisfy predicate evaluation. With this variable enabled, predicate evaluation may occur on uncommitted data.
DB2_EVALUNCOMMITTED is only applicable when currently committed semantics will not help avoid lock contentions. When this variable is set and currently committed is applicable to a scan, deleted rows will not be skipped and predicate evaluate will not occur on uncommitted data; the currently committed version of the rows and data will be processed instead.

As well, DB2_EVALUNCOMMITTED is applicable only to statements using either Cursor Stability or Read Stability isolation levels. Furthermore, deleted rows are skipped unconditionally on table scan access while deleted keys are not skipped unless the registry variable DB2_SKIPDELETED is also set.

The activation of the DB2_EVALUNCOMMITTED registry variable is effective on the db2start command. The decision as to whether deferred locking is applicable is made at statement compile or bind time.

DB2_EXTENDED_IO_FEATURES

Operating system: AIX®
Default: OFF, Values: ON, OFF
Set this variable to ON to enable features that enhance I/O performance. This enhancement includes improving the hit rate of memory caches and reducing the latency on high priority I/O. These features are only available on certain combinations of software and hardware configuration; setting this variable to ON for other configurations will be ignored by either the DB2 database management system or by the operating system. The minimum configuration requirements are:
- Database version: DB2 V9.1
- RAW device must be used for database containers (container on file systems is not supported)
- Operating system: AIX 5.3 TL4
- Storage subsystem: Shark DS8000® supports all the enhanced I/O performance features. Refer to the Shark DS8000 documentation for setup and prerequisite information.
The default I/O priority settings for HIGH is 3, MEDIUM is 8, and LOW is 12; you can use the DB2_IO_PRIORITY_SETTING registry variable to change these settings.

DB2_EXTENDED_OPTIMIZATION

Operating system: All
Default: OFF, Values: ON, OFF, ENHANCED_MULTIPLE_DISTINCT, IXOR, or SNHD
This variable specifies whether or not the query optimizer uses optimization extensions to improve query performance. The ON, ENHANCED_MULTIPLE_DISTINCT, and SNHD values specify different optimization extensions. Use a comma-separated list when you want to use them in combination.
The ENHANCED_MULTIPLE_DISTINCT value might improve the performance of queries where multiple distinct aggregate operations are involved in one single select operation and where the ratio of processors to the number of database partitions is low (for example, the ratio is less than or equal to 1). This setting should be used in partitioned database environments without symmetric multiprocessors (SMPs).

The IXOR option (available starting in the DB2 Version 9.7 Fix Pack 2 release) specifies that the optimizer is to use the index ORing data access method.

If the SNHD value is specified, the optimizer determines a more efficient single-partition hash-directed partitioning strategy based on cost. Under this approach, operations that cannot be executed in a truly parallel manner are more aggressively optimized to execute on a single database partition other than the coordinator partition.

These optimization extensions might not improve query performance in all environments. Testing should be done to determine individual query performance improvements.

DB2_HASH_JOIN

Operating system: All
Default: YES, Values: YES or NO
Specifies hash join as a possible join method when compiling an access plan. DB2_HASH_JOIN must be tuned to get the best performance. Hash join performance is best if you can avoid hash loops and overflow to disk. To tune hash join performance, estimate the maximum amount of memory available for the sheapthres configuration parameter, and then tune the sortheap configuration parameter. Increase its value until you avoid as many hash loops and disk overflows as possible, but do not reach the limit specified by the sheapthres configuration parameter.
Note: DB2_HASH_JOIN is deprecated and might be removed in a future release.

DB2_IO_PRIORITY_SETTING

Operating system: AIX
Values: HIGH:#, MEDIUM:#, LOW:#, where # can be 1 - 15
This variable is used in combination with the DB2_EXTENDED_IO_FEATURES registry variable. This registry variable provides a means to override the default HIGH, MEDIUM, and LOW I/O priority settings for the DB2 database system, which are 3, 8, and 12, respectively. This registry variable must be set before the start of an instance; any modification requires an instance restart.
Note: Setting this registry variable alone does not enable the enhanced I/O features, DB2_EXTENDED_IO_FEATURES must be set to enable them. All system requirements for DB2_EXTENDED_IO_FEATURES also apply to this registry variable.

DB2_KEEP_AS_AND_DMS_CONTAINERS_OPEN

Operating system: All
Default: NO, Values: YES or NO
When you set this variable to ON, each DMS table space container has a file handle opened until the database is deactivated. Query performance might improve because the processing required to open the containers is eliminated. You should use this registry only in pure DMS environments, otherwise performance of queries against SMS table spaces might be affected negatively.

DB2_KEEPTABLELOCK

Operating system: All
Default: OFF, Values: ON, TRANSACTION, OFF, CONNECTION
When this variable is set to ON or TRANSACTION, this variable allows the DB2 database system to maintain the table lock when an Uncommitted Read or Cursor Stability isolation level is closed. The table lock that is kept is released at the end of the transaction, just as it would be released for Read Stability and Repeatable Read scans.
When this variable is set to CONNECTION, a table lock is released for an application until the application either rolls back the transaction or the connection is reset. The table lock continues to be held across commits and application requests to drop the table lock are ignored by the database. The table lock remains allocated to the application. Thus, when the application re-requests the table lock, the lock is already available.

For application workloads that can leverage this optimization, performance should improve. However, the workloads of other application running concurrently might be affected. Other applications might get blocked from accessing a given table resulting in poor concurrency. DB2 SQL catalog tables are not affected by this setting. The CONNECTION setting also includes the behavior described with the ON or TRANSACTION setting.

This registry variable is checked at statement compile or bind time.

DB2_LARGE_PAGE_MEM

Operating system: AIX, Linux, Windows Server
Default: NULL, Values: Use * to denote all applicable memory regions should use large/huge page memory, or a comma-separated list of specific memory regions that should use large/huge page memory. Available regions vary by operating system.
The DB setting is available on the following operating systems: AIX - large pages, Linux - huge pages, and Windows - large pages.
Other settings are available on AIX only: PRIVATE, DBMS, FCM, APPL, all of which enable large pages for the application memory region and DB:16 GB, which enables huge pages for database memory. When you are specifying the * setting on AIX, large pages are used for the database memory region.
When you are using large or huge pages for database memory (DB), dynamic decreasing of database memory is limited, and the db_mem_thresh setting is ignored. The self-tuning memory manager (STMM) does not adjust the overall size of the database area. However, STMM tunes the areas inside the database memory region subject to configuration. For more information, see the database_memory topic in the related links section.

Memory access-intensive applications that use large amounts of virtual memory might obtain performance improvements by using large or huge pages. To enable the DB2 database system to use them, you must first configure the operating system to use large or huge pages.

To enable large pages for agent private memory on 64-bit DB2 for AIX (the DB2_LARGE_PAGE_MEM=PRIVATE setting), you must configure large pages on the operating system and the instance owner must possess the CAP_BYPASS_RAC_VMM and CAP_PROPAGATE capabilities.

On Linux, to verify that huge kernel pages are available, issue the following command:
```
   cat /proc/meminfo
```
If huge kernel pages are available, the following three lines should be displayed (with different numbers depending on the amount of memory configured on your server):
```
   HugePages_Total:   200
   HugePages_Free:    200
   Hugepagesize:    16384 kB
```
If you do not see these lines, or if the HugePages_Total is 0, you must configure the operating system or kernel.

On Windows, the amount of large page memory that is available on the system is less than the total available memory. After the system has been running for some time, memory can become fragmented, and the amount of large page memory decreases. The DB2_ALLOCATION_SIZE registry variable should be set to a high value, such as 256MB, in order to achieve consistent performance allocating large memory pages on Windows. (Note that DB2_ALLOCATION_SIZE requires you to stop and restart the instance.)

DB2_LOGGER_NON_BUFFERED_IO

Operating system: All
Default: AUTOMATIC, Values: AUTOMATIC, ON, OFF
This variable allows you to control whether direct I/O (DIO) will be used on the log file system. When DB2_LOGGER_NON_BUFFERED_IO is set to AUTOMATIC, active log windows (namely, the primary log files) will be opened with DIO, and all other logger files will be buffered. When it is set to ON, all log file handles will be opened with DIO. When it is set to OFF, all log files handles will be buffered.

DB2MAXFSCRSEARCH

Operating system: All
Default: 5, Values: -1, 1 to 33554
Specifies the amount of free space control record (FSCRs) to search when adding a record to a table. The default is to search five FSCRs. Modifying this value allows you to balance insert speed with space reuse. Use large values to optimize for space reuse. Use small values to optimize for insert speed. Setting the value to -1 forces the database manager to search all FSCRs.

DB2_MAX_INACT_STMTS

Operating system: All
Default: Not set, Values: up to 4 000 000 000
This variable overrides the default limit on the number of inactive statements kept by any one application. You can choose a different value in order to increase or reduce the amount of system monitor heap used for inactive statement information. The default limit is 250.
The system monitor heap can become exhausted if an application contains an unusually high number of statements in a unit of work, or if there are many applications running concurrently.

DB2_MAX_NON_TABLE_LOCKS

Operating system: All
Default: YES, Values: See description
This variable defines the maximum number of NON table locks a transaction can have before it releases all of these locks. NON table locks are table locks that are kept in the hash table and transaction chain even when the transaction has finished using them. Because transactions often access the same table more than once, retaining locks and changing their state to NON can improve performance.
For best results, the recommended value for this variable is the maximum number of tables expected to be accessed by any connection. If no user-defined value is specified, the default value is as follows: If the locklist size is greater than or equal to
```
 SQLP_THRESHOLD_VAL_OF_LRG_LOCKLIST_SZ_FOR_MAX_NON_LOCKS 
```
(currently 8000), the default value is
```
SQLP_DEFAULT_MAX_NON_TABLE_LOCKS_LARGE
```
(currently 150). Otherwise, the default value is
```
SQLP_DEFAULT_MAX_NON_TABLE_LOCKS_SMALL
```
(currently 0).

DB2_MDC_ROLLOUT

Operating system: All
Default: IMMEDIATE, Values: IMMEDIATE, OFF, or DEFER
This variable enables a performance enhancement known as "rollout" for deletions from MDC tables. Rollout is a faster way of deleting rows in an MDC table, when entire cells (intersections of dimension values) are deleted in a search DELETE statement. The benefits are reduced logging and more efficient processing.
There are three possible outcomes of the variable setting:
- No rollout - if OFF is specified
- Immediate rollout - if IMMEDIATE is specified.
- Rollout with deferred index cleanup - if DEFER is specified
If the value is changed after startup, any new compilations of a statement will respect the new registry value setting. For statements that are in the package cache, no change in delete processing will be made until the statement is recompiled. The SET CURRENT MDC ROLLOUT MODE statement overrides the value of DB2_MDC_ROLLOUT at the application connection level.

In DB2 Version 9.7 and later releases, the DEFER value is not supported for range-partitioned tables with partitioned RID indexes. Only the OFF and IMMEDIATE values are supported. The cleanup rollout type is IMMEDIATE if the DB2_MDC_ROLLOUT registry variable is set to DEFER, or if the CURRENT MDC ROLLOUT MODE special register is set to DEFERRED to override the DB2_MDC_ROLLOUT setting. If only nonpartitioned RID indexes exist on the range-partitioned table, deferred index cleanup rollout is supported.

DB2MEMDISCLAIM

Operating system: ALL
Default: YES, Values: YES or NO
Memory used by DB2 database system processes might have some associated paging space. This paging space might remain reserved even when the associated memory has been freed. These conditions depend on the operating system's (tunable) virtual memory management allocation policy. The DB2MEMDISCLAIM registry variable controls whether DB2 agents explicitly request that the operating system disassociate the reserved paging space from the freed memory.
A DB2MEMDISCLAIM setting of YES results in smaller paging space requirements, and possibly less disk activity from paging. A DB2MEMDISCLAIM setting of NO results in larger paging space requirements, and possibly more disk activity from paging. In some situations, such as if paging space is plentiful and real memory is so plentiful that paging never occurs, a setting of NO provides a minor performance improvement.

DB2MEMMAXFREE

Operating system: All
Default: NULL, Values: 0 to 2³²-1 bytes
Specifies the maximum number of bytes of unused private memory that is retained by DB2 database system processes before unused memory is returned to the operating system.
If DB2MEMMAXFREE is not set, DB2 database system processes retain up to 20% of unused private memory (based on the amount of private memory currently consumed), before freeing memory back to the operating system.

Note: DB2MEMMAXFREE is deprecated and will be removed in a future release. This variable is no longer necessary because the database manager now uses a threaded engine model. Do not set this variable. Doing so will likely hurt performance and may lead to unexpected behavior.

DB2_MEM_TUNING_RANGE

Operating system: All
Default: NULL, Values: a sequence of percentages n, m where n=minfree and m=maxfree
The amount of physical memory that the DB2 instance leaves free is important because it dictates how much memory other applications running on the same machine are able to use. When self tuning of database shared memory is enabled, the amount of physical memory left free by a given instance depends on the need for memory by the instance (and its active databases). When an instance is in urgent need of additional memory, it will allocate memory until the free physical memory on the system reaches the percentage specified by minfree. When the instance is less in need of memory, it will maintain a larger amount of free physical memory, specified as a percentage by maxfree. As a result, it is a requirement that the value set for minfree must be less than the value of maxfree.
If this variable is not set, the DB2 database manager will calculate values for minfree and maxfree based on the amount of memory on the server. The setting of this variable has no effect unless you are running the self-tuning memory manager (STMM) and have database_memory set to AUTOMATIC.

DB2_MMAP_READ

Operating system: AIX
Default: OFF, Values: ON or OFF
This variable is used with DB2_MMAP_WRITE to allow the DB2 database system to use mmap as an alternative method of I/O.
When these variables are set to ON, data that is read to and written from the DB2 buffer pools bypasses the AIX memory cache and uses Memory Mapped I/O. If you have a relatively small DB2 buffer pool, and you cannot or choose not to increase the size of this buffer pool, you should take advantage of AIX memory caching by setting DB2_MMAP_READ and DB2_MMAP_WRITE to OFF.

DB2_MMAP_WRITE

Operating system: AIX
Default: OFF, Values: ON or OFF
This variable is used with DB2_MMAP_READ to allow the DB2 database system to use mmap as an alternative method of I/O.
When these variables are set to ON, data that is read to and written from the DB2 buffer pools bypasses the AIX memory cache and uses Memory Mapped I/O. If you have a relatively small DB2 buffer pool, and you cannot or choose not to increase the size of this buffer pool, you should take advantage of AIX memory caching by setting DB2_MMAP_READ and DB2_MMAP_WRITE to OFF.

DB2_NO_FORK_CHECK

Operating system: UNIX
Default: OFF, Values: ON or OFF
When this variable is enabled, the DB2 runtime client minimizes checks to determine if the current process is a result of a fork call. This can improve performance of DB2 applications that do not use the fork() API.

DB2NTMEMSIZE

Operating system: Windows
Default: (varies by memory segment)
Windows requires that all shared memory segments be reserved at DLL initialization time in order to guarantee matching addresses across processes. DB2NTMEMSIZE permits the user to override the DB2 defaults on Windows if necessary. In most situations, the default values should be sufficient. The memory segments, default sizes, and override options are:
1. Parallel FCM Buffers: default size is 512 MB on 32-bit platforms, 4.5 GB on 64-bit platforms; override option is FCM:<number of bytes>
2. Fenced Mode Communication: default size is 80 MB on 32-bit platforms, 512 MB on 64-bit platforms; override option is APLD:<number of bytes>
3. Message Query Memory: default size is 4 MB on 32-bit and 64-bit platforms; override option is QUE:<number of bytes>.
More than one segment might be overridden by separating the override options with a semicolon (;). For example, on a 32-bit version of DB2, to limit the FCM buffers to 1 GB, and the fenced stored procedures limit to 256 MB, use:
```
db2set DB2NTMEMSIZE=FCM:1073741824;APLD:268435456
```
To increase the message queue memory to 64 MB, use:
```
db2set DB2NTMEMSIZE=QUE:67108864
```

DB2NTNOCACHE

Operating system: Windows
Default: OFF, Values: ON or OFF
The DB2NTNOCACHE registry variable specifies whether the DB2 database system opens database files with a NOCACHE option. If DB2NTNOCACHE=ON, file system caching is eliminated. If DB2NTNOCACHE=OFF, the operating system caches DB2 files. This applies to all data except for files that contain long fields or LOBs. Eliminating system caching allows more memory to be available to the database so that the buffer pool or sort heap can be increased.
In Windows, files are cached when they are opened, which is the default behavior. One MB is reserved from a system pool for every 1 GB in the file. Use this registry variable to override the undocumented 192 MB limit for the cache. When the cache limit is reached, an out-of-resource error is given.

Note: You can achieve the same benefit for table space containers by using the CREATE TABLESPACE and ALTER TABLESPACE SQL statements.

DB2NTPRICLASS

Operating system: Windows
Default: NULL, Values: R, H, (any other value)
Sets the priority class for the DB2 instance (program DB2SYSCS.EXE). There are three priority classes:
- NORMAL_PRIORITY_CLASS (the default priority class)
- REALTIME_PRIORITY_CLASS (set by using R)
- HIGH_PRIORITY_CLASS (set by using H)
This variable is used with individual thread priorities (that you set using DB2PRIORITIES) to determine the absolute priority of DB2 threads relative to other threads in the system.

Note: DB2NTPRICLASS is deprecated and should only be used at the recommendation of service. Use DB2 service classes to adjust agent priority and prefetch priority. Care should be taken when using this variable. Misuse could adversely affect overall system performance.

For more information, see the SetPriorityClass() API in the Win32 documentation.

DB2NTWORKSET

Operating system: Windows
Default: 1,1
Used to modify the minimum and maximum working-set size available to the DB2 database manager. By default, when Windows is not in a paging situation, the working set of a process can grow as large as needed. However, when paging occurs, the maximum working set that a process can have is approximately 1 MB. DB2NTWORKSET allows you to override this default behavior.
Specify DB2NTWORKSET using the syntax DB2NTWORKSET=min, max, where min and max are expressed in megabytes.

DB2_OVERRIDE_BPF

Operating system: All
Default: Not set, Values: a positive numeric number of pages OR <entry>[;<entry>...] where <entry>=<buffer pool ID>,<number of pages>
This variable specifies the size of the buffer pool, in pages, to be created at database activation, rollforward recovery, or crash recovery. It is useful when memory constraints cause failures to occur during database activation, rollforward recovery, or crash recovery. The memory constraint could arise either in the rare case of a real memory shortage or, because of the attempt by the database manager to allocate a large buffer pool, in the case where there were inaccurately configured buffer pools. For example, when even a minimal buffer pool of 16 pages is not started by the database manager, try specifying a smaller number of pages using this environment variable. The value given to this variable overrides the current buffer pool size.
You can also use <entry>[;<entry>...] where <entry>=<buffer pool ID>,<number of pages> to temporarily change the size of all or a subset of the buffer pools so that they can start.

DB2_PINNED_BP

Operating system: AIX, HP-UX, Linux
Default: NO, Values: YES or NO
Setting this variable to YES causes DB2 to request that the Operating System pins DB2's Database Shared Memory. When configuring DB2 to pin Database Shared Memory, care should be taken to ensure that the system is not overcommitted, as the operating system will have reduced flexibility in managing memory.
On Linux, in addition to modifying this registry variable, the library, libcap.so.1 is also required.

Setting this variable to YES means that self tuning for database shared memory (activated by setting the database_memory configuration parameter to AUTOMATIC) cannot be enabled.
For AIX operating systems, to use database memory pinning with medium page size support (which is a default behavior), ensure that the instance owner has the CAP_BYPASS_RAC_VMM and CAP_PROPAGATE capabilities by logging on as root authority and issuing the following command:
```
chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE <instance_owner_user_id>
```
For HP-UX in a 64-bit environment, in addition to modifying this registry variable, the DB2 instance group must be given the MLOCK privilege. To do this, a user with root access rights performs the following actions:
1. Adds the DB2 instance group to the /etc/privgroup file. For example, if the DB2 instance group belongs to db2iadm1 group then the following line must be added to the /etc/privgroup file:
```
   db2iadm1 MLOCK
```
2. Issues the following command:
```
   setprivgrp -f /etc/privgroup
```

DB2PRIORITIES

Operating system: All
Values setting is platform dependent
Controls the priorities of DB2 processes and threads.

Note: DB2PRIORITIES is deprecated and should only be used at the recommendation of service. Use DB2 service classes to adjust agent priority and prefetch priority.

DB2_RCT_FEATURES

Operating system: All
Default: NULL. Values: GROUPUPDATE=[ON|OFF]. The default value for GROUPUPDATE is OFF.
This variable allows for optimized and reduced update processing for a searched UPDATE statement which targets multiple rows in an range clustered table when only equal predicates on the leading and subset of key sequence columns are specified. Logging is also reduced due to a single log record for all rows updated on a page, instead of a log record for each row updated.
Usage :
```
db2set DB2_RCT_FEATURES=GROUPUPDATE=ON
```
Note: It will not be possible to reverse migrate to older fix packs once this registry variable is enabled. In addition, group update will not be performed when the target table of the UPDATE statement uses DATA CAPTURE CHANGES, triggers, variable length columns, has any secondary indexes, or the update modifies any primary or foreign key columns.

DB2_RESOURCE_POLICY

Operating system: AIX 5 or higher, all Linux except zSeries (32-bit), Windows Server 2003 or higher
Default: Not set, Values: valid path to configuration file
Defines a resource policy which can be used to limit what operating system resources are used by the DB2 database or it contains rules for assigning specific operating system resources to specific DB2 database objects. For example, on AIX, Linux, or Windows operating systems, this registry variable can be used to limit the set of processors that the DB2 database system uses. The extent of resource control varies depending on the operating system.
On AIX NUMA and Linux NUMA enabled machines, a policy can be defined which specifies what resource sets the DB2 database system uses. When resource set binding is used, each individual DB2 process is bound to a particular resource set. This can be beneficial in some performance tuning scenarios.

You can set the registry variable to indicate the path to a configuration file which defines a policy for binding DB2 processes to operating system resources. The resource policy allows you to specify a set of operating system resources to restrict the DB2 database system. Each DB2 process is bound to a single resource of the set. Resource assignment occurs in a circular round robin fashion.

Sample configuration files:

Example 1: Bind all DB2 processes to either CPU 1 or 3.
```
<RESOURCE_POLICY>  	
	<GLOBAL_RESOURCE_POLICY>  
		<METHOD>CPU</METHOD> 
		<RESOURCE_BINDING> 
			<RESOURCE>1</RESOURCE>	
		</RESOURCE_BINDING>
		<RESOURCE_BINDING>
			<RESOURCE>3</RESOURCE>
		</RESOURCE_BINDING>
	</GLOBAL_RESOURCE_POLICY>
</RESOURCE_POLICY> 
```
Example 2: (AIX only) Bind DB2 processes to one of the following resource sets: sys/node.03.00000, sys/node.03.00001, sys/node.03.00002, sys/node.03.00003
```
<RESOURCE_POLICY>
	<GLOBAL_RESOURCE_POLICY> 
		<METHOD>RSET</METHOD> 
		<RESOURCE_BINDING> 
			<RESOURCE>sys/node.03.00000</RESOURCE> 
		</RESOURCE_BINDING> 
		<RESOURCE_BINDING> 
			<RESOURCE>sys/node.03.00001</RESOURCE> 
		</RESOURCE_BINDING> 
		<RESOURCE_BINDING> 
			<RESOURCE>sys/node.03.00002</RESOURCE> 
		</RESOURCE_BINDING> 
		<RESOURCE_BINDING> 
			<RESOURCE>sys/node.03.00003</RESOURCE> 
		</RESOURCE_BINDING> 
	</GLOBAL_RESOURCE_POLICY> 
</RESOURCE_POLICY>
```
Note: For AIX only, use of the RSET method requires CAP_NUMA_ATTACH and CAP_PROPAGATE capabilites.

Example 3: (Linux only) Bind all memory from bufferpool IDs 2 and 3 which are associated with the SAMPLE database to NUMA node 3. Also use 80 percent of the total database memory for the binding to NUMA node 3 and leave 20 percent to be striped across all nodes for non-bufferpool specific memory.
```
<RESOURCE_POLICY>
	<DATABASE_RESOURCE_POLICY>
		<DBNAME>sample</DBNAME>
		<METHOD>NODEMASK</METHOD>
		<RESOURCE_BINDING>
			<RESOURCE>3</RESOURCE>
			<DBMEM_PERCENTAGE>80</DBMEM_PERCENTAGE>
			<BUFFERPOOL_BINDING>
				<BUFFERPOOL_ID>2</BUFFERPOOL_ID>
				<BUFFERPOOL_ID>3</BUFFERPOOL_ID>
			</BUFFERPOOL_BINDING>
		</RESOURCE_BINDING>
	</DATABASE_RESOURCE_POLICY>
</RESOURCE_POLICY>
```
Example 4: (For Linux and Windows only) Define two distinct processor sets specified by CPU masks 0x0F and 0xF0. Bind DB2 processes and bufferpool ID 2 to processor set 0x0F and DB2 processes and bufferpool ID 3 to processor set 0xF0. For each processor set, use 50 percent of the total database memory for the binding.

This resource policy is useful when a mapping between processors and NUMA nodes is desired. An example of such a scenario is a system with 8 processors and 2 NUMA nodes where processors 0 to 3 belong to NUMA node 0 and processors 4 to 7 belong to NUMA node 1. This resource policy allows for processor binding while implicitly maintaining memory locality (a hybrid of CPU method and NODEMASK method).
```
<RESOURCE_POLICY>
	<DATABASE_RESOURCE_POLICY>
		<DBNAME>sample</DBNAME>
		<METHOD>CPUMASK</METHOD>
		<RESOURCE_BINDING>
			<RESOURCE>0x0F</RESOURCE>
			<DBMEM_PERCENTAGE>50</DBMEM_PERCENTAGE>
			<BUFFERPOOL_BINDING>
				<BUFFERPOOL_ID>2</BUFFERPOOL_ID>
			</BUFFERPOOL_BINDING>
		</RESOURCE_BINDING>
		<RESOURCE_BINDING>
			<RESOURCE>0xF0</RESOURCE>
			<DBMEM_PERCENTAGE>50</DBMEM_PERCENTAGE>
			<BUFFERPOOL_BINDING>
				<BUFFERPOOL_ID>3</BUFFERPOOL_ID>
			</BUFFERPOOL_BINDING>
		</RESOURCE_BINDING>
	</DATABASE_RESOURCE_POLICY>
</RESOURCE_POLICY>
```
The configuration file specified by the DB2_RESOURCE_POLICY registry variable accepts a SCHEDULING_POLICY element. You can use the SCHEDULING_POLICY element on some platforms to select
- The operating system scheduling policy used by the DB2 server
  You can set an operating system scheduling policy for DB2 on AIX, and for DB2 on Windows using the DB2NTPRICLASS registry variable.
- The operating system priorities used by individual DB2 server agents
Alternatively, you can use the registry variables DB2PRIORITIES and DB2NTPRICLASS to control the operating system scheduling policy and set DB2 agent priorities. However, the specification of a SCHEDULING_POLICY element in the resource policy configuration file provides a single place to specify both the scheduling policy and the associated agent priorities.

Example 1: Selection of the AIX SCHED_FIFO scheduling policy with a priority boost for the DB2 log writer and reader processes.
```
<RESOURCE_POLICY> 
	<SCHEDULING_POLICY>
		<POLICY_TYPE>SCHED_FIFO</POLICY_TYPE>
		<PRIORITY_VALUE>60</PRIORITY_VALUE>

		<EDU_PRIORITY>
			<EDU_NAME>db2loggr</EDU_NAME>
			<PRIORITY_VALUE>56</PRIORITY_VALUE>
		</EDU_PRIORITY>

		<EDU_PRIORITY>
			<EDU_NAME>db2loggw</EDU_NAME>
			<PRIORITY_VALUE>56</PRIORITY_VALUE>
		</EDU_PRIORITY>
	</SCHEDULING_POLICY>
</RESOURCE_POLICY>
```
Example 2: Replacement for DB2NTPRICLASS=H on Windows.
```
<RESOURCE_POLICY> 
	<SCHEDULING_POLICY>
		<POLICY_TYPE>HIGH_PRIORITY_CLASS</POLICY_TYPE>
	</SCHEDULING_POLICY>
</RESOURCE_POLICY>
```

DB2_SELUDI_COMM_BUFFER

Operating system: All
Default: OFF, Values: ON or OFF
This variable is used during the processing of blocking cursors over SELECT from UPDATE, INSERT, or DELETE (UDI) queries. When enabled, this registry variable prevents the result of a query from being stored in a temporary table. Instead, during the OPEN processing of a blocking cursor for a SELECT from UDI query, the DB2 database system attempts to buffer the entire result of the query directly into the communications buffer memory area.
If the communications buffer space is not large enough to hold the entire result of query, an SQLCODE -906 error is returned, and the transaction is rolled back. See the aslheapsz and rqrioblk database manager configuration parameters for information on adjusting the size of the communication buffer memory area for local and remote applications, respectively.

This registry variable is not supported when intrapartition parallelism is enabled.

DB2_SET_MAX_CONTAINER_SIZE

Operating system: All
Default: Not set, Values: -1, any positive integer greater than 65 536 bytes
This registry variable allows you to limit the size of individual containers for automatic storage table spaces with the AutoResize feature enabled.
If the value is set to -1, there will be no limit to the size of a container.

Note: Although you can specify DB2_SET_MAX_CONTAINER_SIZE in bytes, kilobytes, or megabytes, db2set indicates its value in bytes.

DB2_SKIPDELETED

Operating system: All
Default: OFF, Values: ON or OFF
When enabled, this variable allows statements using either Cursor Stability or Read Stability isolation levels to unconditionally skip deleted keys during index access and deleted rows during table access. With DB2_EVALUNCOMMITTED enabled, deleted rows are automatically skipped, but uncommitted pseudo-deleted keys in indexes are not skipped unless DB2_SKIPDELETED is also enabled.
DB2_SKIPDELETED is only applicable when currently committed semantics will not help avoid lock contentions. When this variable is set and currently committed is applicable to a scan, deleted rows will not be skipped; their currently committed version will be processed instead

This registry variable does not impact the behavior of cursors on the DB2 catalog tables.

This registry variable is activated with the db2start command.

DB2_SKIPINSERTED

Operating system: All
Default: OFF, Values: ON or OFF
When the DB2_SKIPINSERTED registry variable is enabled, it allows statements using either Cursor Stability or Read Stability isolation levels to skip uncommitted inserted rows as if they had not been inserted. This registry variable does not impact the behavior of cursors on the DB2 catalog tables. This registry variable is activated at database startup, while the decision to skip uncommitted inserted rows is made at statement compile or bind time.
This registry variable has no effect if currently committed semantics are being used. That is, even if DB2_SKIPINSERTED is set to OFF and currently committed behavior is enabled, uncommitted inserted rows are still skipped.

Note: Skip inserted behavior is not compatible with tables that have pending rollout cleanup. As a result, scanners might wait for locks on a RID only to discover that the RID is part of a rolled out block.

DB2_SMS_TRUNC_TMPTABLE_THRESH

Operating system: All
Default=-2, Values: -2, -1, or 0 to n, where n=the number of extents per temporary table in the SMS table space container that are to be maintained
This variable specifies a minimum file size threshold at which the file representing a temporary table is maintained in SMS table spaces.
Starting in DB2 V9.7 Fix Pack 2, the default setting for this variable is -2, which means that there will not be any unnecessary file system access for any spilled SMS temporary objects whose size is less than or equal to 1 extent * number of containers. Temporary objects that are larger than this are truncated to 0 extent.

When this variable is set to 0, which was the default setting prior to DB2 V9.7 Fix Pack 2, no special threshold handling is done. Instead, once a temporary table is no longer needed, that file is truncated to 0 extent. When the value of this variable is greater than 0, a larger file is maintained. Objects larger than the threshold will be truncated to the threshold size. This reduces some of the system overhead involved in dropping and recreating the file each time a temporary table is used.

If this variable is set to -1, the file is not truncated and the file is allowed to grow indefinitely, restricted only by system resources.

DB2_SORT_AFTER_TQ

Operating system: All
Default: NO, Values: YES or NO
Specifies how the optimizer works with directed table queues in a partitioned database environment when the receiving end requires the data to be sorted and the number of receiving nodes is equal to the number of sending nodes.
When DB2_SORT_AFTER_TQ=NO, the optimizer tends to sort at the sending end and merge the rows at the receiving end.

When DB2_SORT_AFTER_TQ=YES, the optimizer tends to transmit the rows unsorted, not merge at the receiving end, and sort the rows at the receiving end after receiving all the rows.

DB2_SQLWORKSPACE_CACHE

Operating system: All
Default: 30, Values: 10 - 2000
This variable allows you to control the amount of caching of previously used sections in the SQL Workspace.
The SQL Workspace contains allocations, in the form of sections, for the execution of SQL. Each SQL statement (static or dynamic) that is being executed on behalf of an application must maintain a unique copy of the section in the SQL Workspace for the duration of execution of that statement. Once the execution of the statement is complete, the section becomes inactive and the memory allocations associated with an inactive section can either be freed, or they can remain cached in the SQL Workspace. When a new execution of the same SQL statement occurs from any connection, it may find a cached copy of the section in the SQL Workspace left from a previous execution, thus saving the costs associated with allocating and initializing a new copy of the section. In such a manner, the SQL Workspace contains both active sections-corresponding to currently executing SQL-and cached sections that are not currently executing.

The value for this registry variable specifies the percentage of memory allocations that are allowed to remain cached in the SQL Workspace. This caching is expressed as a percentage of the memory allocations for active sections. Thus, for example, a value of 50 would mean that the SQL workspace contains all of the active (currently executing) sections and up to 50% more of previously executed cached sections that can be reused. You would adjust the setting for DB2_SQLWORKSPACE_CACHE based on how much of the SQL workspace you want to make available for reuse. For example, increasing the size of this variable, can result in some performance improvements for OLTP workloads. On the other hand, a higher setting also means that there is an increase in the size of the application shared heap. Note that if the appl_memory database configuration parameter is not set to AUTOMATIC, the size of the SQL Workspace may also be limited by the appl_memory and the SQL Workspace may not provide as much caching as the DB2_SQLWORKSPACE_CACHE setting might allow for; you might want to consider increasing appl_memory (or setting it to AUTOMATIC) in such a case. This registry variable is not dynamic

DB2_TRUST_MDC_BLOCK_FULL_HINT

Operating system: All
Default: OFF, Values: ON or OFF
When you insert records into an MDC table, DB2 searches the composite block index for blocks that have the same dimension values as the new record that is being inserted. Those blocks are then checked to determine whether they have enough free space for the new record. For any block checked that does not have enough free space, DB2 sets the Full_Block bit in the composite block index for that block. If the list of blocks for a specified dimension value is long and most of those blocks are full, then a significant amount of time is spent searching.
When the DB2_TRUST_MDC_BLOCK_FULL_HINT variable is set, DB2 skips searching for free space in any block that is marked with the Full_Block bit in the composite block index. This Full_Block bit is only a hint as the bit is only cleared when the entire block is removed and when the composite block index is rebuilt by using the REORG command. The trade-off is that some free space might be wasted if deletes are run that partially empties blocks as oppose to fully emptying them with rollout delete. For more information about rollout deletes, see "Rollout deletion" in the "Optimization strategies for MDC tables" topic.

DB2_TRUSTED_BINDIN

Operating system: All
Default: OFF, Values: OFF, ON, or CHECK
When DB2_TRUSTED_BINDIN is enabled, it speeds up the execution of query statements containing host variables within an embedded unfenced stored procedure.
When this variable is enabled, there is no conversion from the external SQLDA format to an internal DB2 format during the binding of SQL and XQuery statements contained within an embedded unfenced stored procedure. This will speed up the processing of the embedded SQL and XQuery statements.

The following data types are not supported in embedded unfenced stored procedures when this variable is enabled:
- SQL_TYP_DATE
- SQL_TYP_TIME
- SQL_TYP_STAMP
- SQL_TYP_CGSTR
- SQL_TYP_BLOB
- SQL_TYP_CLOB
- SQL_TYP_DBCLOB
- SQL_TYP_CSTR
- SQL_TYP_LSTR
- SQL_TYP_BLOB_LOCATOR
- SQL_TYP_CLOB_LOCATOR
- SQL_TYP_DCLOB_LOCATOR
- SQL_TYP_BLOB_FILE
- SQL_TYP_CLOB_FILE
- SQL_TYP_DCLOB_FILE
- SQL_TYP_BLOB_FILE_OBSOLETE
- SQL_TYP_CLOB_FILE_OBSOLETE
- SQL_TYP_DCLOB_FILE_OBSOLETE
If these data types are encountered, an SQLCODE -804, SQLSTATE 07002 error is returned.

Note: The data type and length of the input host variable must match the internal data type and length of the corresponding element exactly. For host variables, this requirement will always be met. However, for parameter markers, care must be taken to ensure that matching data types are used. The CHECK option can be used to ensure that the data types and lengths match for all input host variables, but this option negates most of the performance improvements.

Note: DB2_TRUSTED_BINDIN is deprecated and will be removed in a later release.

DB2_USE_ALTERNATE_PAGE_CLEANING

Operating system: All
Default: Not set, Values: ON or OFF
This variable specifies whether a DB2 database uses the alternate method of page cleaning algorithms or the default method of page cleaning. When this variable is set to ON, the DB2 system writes changed pages to disk, keeping ahead of LSN_GAP and proactively finding victims. Doing this allows the page cleaners to better utilize available disk I/O bandwidth. When this variable is set to ON, the chngpgs_thresh database configuration parameter is no longer relevant because it does not control page cleaner activity.

DB2_USE_FAST_PREALLOCATION

Operating system: AIX, Linux and Solaris on VeritasVxFS, JFS2, GPFS™, or ext4 (Linux only) file systems
Default: ON for Veritas VxFS, JFS2 and GPFS, Values: ON or OFF
Allows the fast preallocation file system feature to reserve table space, and speed up the process of creating or altering large table spaces and database restore operations. This speed improvement is implemented at a small delta cost of performing actual space allocation during runtime when rows are inserted.
To disable fast preallocation, set DB2_USE_FAST_PREALLOCATION to OFF. This might improve runtime performance, at the cost of slower table space creation and database restore times, on some operating systems, especially AIX, when there is a large volume of inserts and selects on same table space. Any change in this setting applies to new increases in tablespace file size or new files only. If you want to disable fast preallocation and avoid having leftover preallocated space from previous increases or additions, you need to perform a restore of that tablespace.

DB2_USE_IOCP

Operating system: AIX 5.3 TL9 SP2, AIX 6.1 TL2, or AIX 7.1 or later.
Default: ON, Values: OFF or ON
Configure IOCP before enabling this registry variable.
This variable enables the use of AIX I/O completion ports (IOCP) when submitting and collecting asynchronous I/O (AIO) requests. This feature is used to enhance performance in a non-uniform memory access (NUMA) environment by avoiding remote memory access.