Performance variables

You can set performance variables to improve database processes, such as access plan optimizations, memory tuning operations, and operating resource policies.

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 may 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 somewhat improved performance, 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_AVOID_LOCK_ESCALATION
  • Operating system: All
  • Default=OFF (ON when DB2_WORKLOAD=SAP), Values: ON or OFF
  • When the DB2_AVOID_LOCK_ESCALATION registry variable is ON, lock escalation will not be performed. Instead, SQL0912N is returned to the application that requested the lock that would normally result in lock escalation. The application is able to either COMMIT or ROLLBACK which will free the locks held by this application. This variable can be updated online without restarting the instance.
  • This variable is available in Version 11.1.2.2 and newer releases.
  • Changes to this variable do not require the database instance to be restarted.
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 and load copy 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 or load copy 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 or load copy image file. This performance impact will have the largest benefit for Linux® platforms. However, there may be a slight slowdown of the backup or load itself, so you should measure the change in backup or load 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 or load that is already running. Changing the value will take effect when the next backup or load 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.
    Important: This performance variable is deprecated and might be removed in a future release. For more information, see Some registry and environment variables have changed.
    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 line:
         NO_NT_SCATTER = 1
    
    Assuming that bpvars.vars is stored in F:\vars\, to set these variables you execute 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 theNO_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.
DB2CHKPTR
  • Operating system: All
  • Default=OFF, Values: ON or OFF
  • Specifies whether or not pointer checking for input is required.
DB2CHKSQLDA
  • Operating system: All
  • Default=ON, Values: ON or OFF
  • Specifies whether or not SQLDA checking for input is required.
DB2_DEFER_MEMORY_COMMIT
  • Operating system: Unix, Linux
  • Default=OFF, Values: OFF or ON
  • When this variable is ON, initialization of buffer pool and lock list memory is performed asynchronously in the background. This asynchronous initialization allows database activation to complete and the database to be available for application connections sooner. During the short time-period after database activation while this memory is being initialized, database activities might observe a small performance impact.
  • This variable does not apply to the Windows operating system, as the existing behavior is already similar to the ON setting.
  • Changes to this variable require the database instance to be restarted before taking effect.
  • This variable is available in Db2 V11.1 MP4 FP6 and later versions.
DB2_EVALUNCOMMITTED
  • Operating system: All
  • Default: NO (YES when DB2_WORKLOAD=SAP), 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. Only scans that are not Currently Committed (CC) applicable considers these variables.

    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 for index scans 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_EXTEND_COL_UNIQUE_INDEX_ACCESS
  • Operating system: All
  • Default=OFF, Values: ON, OFF
  • This variable lets you control whether a modification state index is created when a unique or primary key constraint is created on a column-organized table. When this registry variable is set to ON, the modification state index, if it does not already exist, is generated when creating an enforced primary key or unique constraint. The modification state index is needed for certain index access plans to be chosen.
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 as well as 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)
    • 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, MEDIUM, and LOW are 3, 8, and 12, respectively; 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 OPT_SORTHEAP_EXCEPT_COL value
  • This variable specifies whether the query optimizer uses optimization extensions to help improve query performance. The values specify different optimization extensions. To specify multiple values, use a comma-separated list.

    The default behavior (specified by the OFF or IXOR value) is for the optimizer to extend the index ORing data access method to include OR predicates that reference any indexed column even when non-indexed column predicates are present. For example, consider the following two index definitions:
       INDEX IX2:  dept    ASC
       INDEX IX3:  job     ASC
    The following predicates can be satisfied by using these two indexes when the IXOR option is set:
       WHERE
            dept = :hv1 OR
            (job = :hv2 AND
            years >= :hv3)

    You can use the OPT_SORTHEAP_EXCEPT_COL value option to override the value of the sortheap database configuration parameter. The override value affects query optimization only and does not determine the amount of actual memory that is available at run time. If the query accesses a column-organized table, this override value is ignored to allow the query compiler to use the current value of the sortheap database configuration parameter.

    One usage of the OPT_SORTHEAP_EXCEPT_COL is for shadow tables. Shadow tables facilitate BLU Acceleration® for analytical queries in OLTP environment. Shadow tables are column-organized tables. The requirements for sort heap memory are higher than you would normally have for databases in OLTP environments. To increase the sort heap memory without affecting existing access plans for OLTP queries, add OPT_SORTHEAP_EXCEPT_COL to DB2_EXTENDED_OPTIMIZATION to override the value of the sortheap database configuration parameter.

    DB2_EXTENDED_OPTIMIZATION settings might not improve query performance in all environments. You should test to determine individual query performance improvements.

    Important:
    • The ENHANCED_MULTIPLE_DISTINCT and IXOR values are deprecated as of Version 10.1 and might be removed in a future release. Removing the ENHANCED_MULTIPLE_DISTINCT option makes new enhancements that improve the performance of multiple distinct queries available. The IXOR value is redundant because it specifies the default behavior. For more details, see Registry variables with changed behaviors.
    • The ENHANCED_MULTIPLE_DISTINCT value takes effect dynamically only if it was enabled when the instance was last started.
DB2_IO_PRIORITY_SETTING
  • Operating system: AIX
  • Values: HIGH:#,MEDIUM:#,LOW:#, where # can be 1 to 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 prior to the start of an instance; any modification requires an instance restart. Note that 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 overhead 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 impacted 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 executing concurrently might be impacted. Other applications might get blocked from accessing a given table resulting in poor concurrency. Db2 SQL catalog tables are not impacted 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 that should use large page memory, or a comma-separated list of specific memory regions that should use large page memory. Available regions vary by operating system.
  • Db2 uses the term "large pages" generically, whereas operating systems use the terms "large" and "huge" to reference both specific and various larger hardware-dependent page sizes. A "large" or "huge" OS page size is not the same size on all OS/hardware platforms, and these terms may refer to multiple page sizes on a given OS/hardware platform.
  • The DB setting applies to the DATABASE_MEMORY region and enables the following page sizes: AIX - large (16MB) pages, Linux x64 - huge (2MB) pages, and Windows - large (2MB) pages.- The DB:16GB setting is available on AIX only, which enables huge (16GB) pages for the DATABASE_MEMORY region.
  • The PRIVATE, DBMS, FCM, and APPL (APPL_MEMORY) settings are available on AIX only, each of which enables large (16MB) pages for the applicable memory region.
  • The * setting enables OS large/huge page memory for all of the available regions as indicated above. On AIX, large (16MB) pages will be enabled for database memory as opposed to huge (16GB) pages.

    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 theDb2 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 256 MB, 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, or 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 33 554
  • Specifies the number 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 a very high number of statements in a unit of work, or if there are a large number of applications executing 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 table, deferred index cleanup rollout is supported.

  • Changes to this variable will take effect immediately for all future compiled SQL statements. There is no need to restart the instance or to issue the db2set command with the -immediate parameter.
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. Whether or not this is so depends 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.

DB2_MEM_TUNING_RANGE
  • Operating system: All
  • Default =NULL, Values: a sequence of percentages n, m where n=minfree and m=maxfree and n < m
  • If this variable is not set, the Db2 database manager calculates values for minfree and maxfree based on the amount of memory on the server. In limited instance_memory environments, the Db2 database manager calculates values for minfree and maxfree based on the instance_memory setting. The setting of this variable has no effect unless the self-tuning memory manager (STMM) is enabled and database_memory is set to AUTOMATIC.

    The minfree and maxfree settings represent the amount of instance memory, system memory or both that the STMM attempts to leave as a buffer. This buffer is critical to satisfying volatile memory requirements while avoiding memory over-commitment on the system or exhausting instance memory. In addition, the minfree-maxfree range is used to balance memory demands across multiple databases. In a single database that is tuned by STMM, the target free system or instance memory is always minfree. In a multiple database environment, the STMM tuner for the database with the highest memory demands targets the minfree value, while the STMM tuners for databases with lower demands have varying higher free memory targets (up to the maxfree value). The default minfree, maxfree settings are as follows:
    Table 1. Default minfree, maxfree settings
    Instance or System Memory Size minfree (%) maxfree(%)
    1 GB 7.8 33
    2 GB 7.4 29
    4 GB 7.0 25
    8 GB 6.7 22
    16 GB 6.4 19
    32 GB 6.2 17
    64 GB 6.0 15
    128 GB 5.8 13
    256 GB 5.7 12
    512 GB 5.6 11
    1 TB 5.5 10

    Starting with Version 10.5, a 5% extra buffer is added which is included in the values in the preceding table. This extra buffer is to accommodate the volatility of memory demands in a wider range of environments while maintaining the resilience expected of automatically tuned STMM environments. However, the additional 5% buffer is available to newly activating databases to minimize detuning (scaling back) upon activation. If a Version 10.5 or later STMM tuner detects the presence of STMM tuners from previous releases (that are competing with them for system memory), then the additional 5% buffer is removed from the calculation for the databases that are running on Version 10.5 or later. This removal of the additional 5% buffer, is to avoid biasing memory allocation towards the databases that are running on previous releases (which would tend to have lower free memory targets).

    Performance gains might be achieved by reducing the minfree, maxfree settings in an STMM environment. However, care must be taken to ensure that volatility in memory requirements does not result in paging or memory exhaustion.

    Changes to this variable take effect immediately for all STMM tuning operations. There is no need to restart the instance or to run the db2set command with the -immediate parameter.

  • Changes to this variable will take effect immediately for all future compiled SQL statements. There is no need to restart the instance or to issue the db2set command with the -immediate parameter.
DB2_MMAP_READ
  • Operating system: AIX
  • Default=OFF, Values: ON or OFF
  • This variable is used in conjunction with DB2_MMAP_WRITE to allow the Db2 database system to use mmap as an alternate method of I/O.

    When these variables are set to ON, data is read into and written from the Db2 buffer pools using memory mapped I/O, and subsequently removed from the file system cache. This avoids double-caching of Db2 data. However, the recommended method to bypass the file system cache is to specify the NO FILE SYSTEM CACHING clause at the table space level, and to leave these variables at the default setting of OFF.

DB2_MMAP_WRITE
  • Operating system: AIX
  • Default=OFF, Values: ON or OFF
  • This variable is used in conjunction with DB2_MMAP_READ to allow the Db2 database system to use mmap as an alternate method of I/O.

    When these variables are set to ON, data is read into and written from the Db2 buffer pools using memory mapped I/O, and subsequently removed from the file system cache. This avoids double-caching of Db2 data. However, the recommended method to bypass the file system cache is to specify the NO FILE SYSTEM CACHING clause at the table space level, and to leave these variables at the default setting of 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 may 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 is set to ON, file system caching is eliminated. If DB2NTNOCACHE is set to 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.

  • Changes to this variable will take effect immediately for all future compiled SQL statements. There is no need to restart the instance or to issue the db2set command with the -immediate parameter.
Note: For table space containers, using the NO FILE SYSTEM CACHING clause with the ALTER TABLESPACE or CREATE TABLESPACE statement reports the same benefit as setting DB2NTNOCACHE to ON.
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 in conjunction with individual thread priorities (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, please refer to 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 brought up 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 up.

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 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
DB2_REDUCE_FLUSHING_DURING_BACKUP
  • Operating system: All
  • Default: OFF, Values: ON | OFF
  • At the beginning of an online backup operation, changed pages within the bufferpool must be persisted to table space storage, which can increase the duration of the backup operation in configurations with very large bufferpools. This variable specifies whether online backup performs reduced flushing of changed pages within the bufferpool. When reduced flushing is performed, the duration of the rollforward operation which is required after the restoration of an online backup may increase.
  • This variable is available in Version 11.1.4.4 and newer releases.
  • Changes to this variable do not require the database instance to be restarted.
  • Changes to this variable will have no effect on an online backup operation which is already running at the time of the variable change.
DB2_RESOURCE_POLICY
  • Operating system: AIX, Linux, Windows
  • Default=Not set, Values: valid path to configuration file (AIX, Linux, Windows) or AUTOMATIC (AIX, Linux, Windows)
  • Defines a resource policy that can be used to control what operating system resources are used by the Db2 database, or contains rules for assigning specific operating system resources to specific Db2 database objects. The extent of resource control varies depending on the operating system.

    On AIX systems with POWER7® or newer processors, or any Linux or Windows system, this variable can be set to AUTOMATIC. When the AUTOMATIC option is specified, the Db2 database system automatically determines the hardware topology and creates a resource group for every set of related resources (processors and memory) that are visible in the hardware topology. Depending on the hardware topology, there may be one or more resource groups, each containing one or more resources.

    The AUTOMATIC setting enables processor affinity, whereby the Db2 database system assigns engine dispatchable units (EDUs) to specific resource groups and resources. In addition, the AUTOMATIC setting also determines whether to enable memory affinitization, whereby EDUs attempt to allocate local memory during processing, to improve memory affinity. The assignment of EDUs to resources occurs in a circular round robin fashion, and assumes that each resource is equivalent. On systems using POWER7 and newer processors, use of the AUTOMATIC setting is not recommended when the distribution of resources is skewed and/or subject to change, such as in systems with multiple LPARs. On POWER® systems running AIX, resource allocation can be determined by using the lssrad -av command. On POWER systems running Linux, resource allocation can be determined by using the numactl -H command.

    This setting is intended for systems that expose multiple resource groups and/or resources in their hardware topology, and can enhance query performance for some workloads. To validate any performance improvements, it is recommended to run a performance analysis of the workload before and after you set the DB2_RESOURCE_POLICY variable to AUTOMATIC.

    You can also set the registry variable to indicate the path to a configuration file that defines a policy for binding Db2 EDUs to specific resource groups and resources. This is useful in cases where the desired configuration cannot be obtained using the AUTOMATIC setting.

    Note: For AIX only, use of DB2_RESOURCE_POLICY=AUTOMATIC or the use of a configuration file that uses the RSET or SRAD methods, requires the instance account to have CAP_NUMA_ATTACH and CAP_PROPAGATE capabilities. For more information, see the AIX documentation for the chuser command.
    Note: For AIX only, use of the SRAD resource type is not supported when the PowerVM® Dynamic Platform Optimizer is enabled.

    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> 

    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 (that is, 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>

    Example 5: (AIX operating systems only) You can manually enable resource group awareness by specifying resource groups in the resource policy file. Use the RESOURCE_GROUP element to specify the resources that belong to a particular resource group. The defined resource groups do not have to align with NUMA boundaries. The RESOURCE_GROUP column of the ENV_GET_DB2_EDU_SYSTEM_RESOURCES table function identifies the resource group with which an EDU is associated.

    Define two resource groups, each of which contains four scheduler resource affinity domains (SRADs):
    <RESOURCE_POLICY>
      <GLOBAL_RESOURCE_POLICY>
    
        <METHOD>SRAD</METHOD>
    
        <RESOURCE_GROUP>
          <RESOURCE_GROUP_NAME>TESTGROUP1</RESOURCE_GROUP_NAME>
    
          <RESOURCE_BINDING>
            <RESOURCE>0</RESOURCE>
          </RESOURCE_BINDING>
    
          <RESOURCE_BINDING>
            <RESOURCE>1</RESOURCE>
          </RESOURCE_BINDING>
    
          <RESOURCE_BINDING>
            <RESOURCE>2</RESOURCE>
          </RESOURCE_BINDING>
    
          <RESOURCE_BINDING>
            <RESOURCE>3</RESOURCE>
          </RESOURCE_BINDING>
    
        </RESOURCE_GROUP>
    
        <RESOURCE_GROUP>
          <RESOURCE_GROUP_NAME>TESTGROUP2</RESOURCE_GROUP_NAME>
    
          <RESOURCE_BINDING>
            <RESOURCE>4</RESOURCE>
          </RESOURCE_BINDING>
    
          <RESOURCE_BINDING>
            <RESOURCE>5</RESOURCE>
          </RESOURCE_BINDING>
    
          <RESOURCE_BINDING>
            <RESOURCE>6</RESOURCE>
          </RESOURCE_BINDING>
    
          <RESOURCE_BINDING>
            <RESOURCE>7</RESOURCE>
          </RESOURCE_BINDING>
    
        </RESOURCE_GROUP>
    
      </GLOBAL_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_FIFO2 scheduling policy with a priority boost for the Db2 log writer and reader processes.

    	<RESOURCE_POLICY> 
    		<SCHEDULING_POLICY>
    			<POLICY_TYPE>SCHED_FIFO2</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.
    Note: If the communications buffer space is not large enough to hold the entire result of query, an SQLCODE -906 error is issued, 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.

    Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

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.
    Note: Although you can specify DB2_SET_MAX_CONTAINER_SIZE in bytes, kilobytes, or megabytes, db2set indicates its value in bytes.
  • If the value is set to -1, there will be no limit to the size of a container.
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. Only scans that are not Currently Committed (CC) applicable considers these variables.

    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_SMP_INDEX_CREATE
  • Operating system: All
  • Default=Not set, Values: 2 to 1000
  • This dynamic registry variable overrides the default number of agents used to scan and sort the index data when building or rebuilding an index. This registry variable is only checked when the index manager component determines that parallelism is warranted. That decision is based on many considerations, including table size and whether multiple processors are present.

    DB2_SMP_INDEX_CREATE has an effect only when it is set to a non-zero value. When increasing the number of agents used to scan and sort the index data, it is important to ensure that the database configuration parameters sortheap and sheapthres_shr are set appropriately. The more memory available for sorting (specified by the sheapthres_shr parameter), the less likely sorting the index data requires writing out temporary results to a system temporary tablespace. If the sort does not spill to disk, it is much faster. Furthermore, to ensure that each agent participating in the sort gets an equal amount of memory, the sortheap parameter should be set to a value no greater than sheapthres_shr/n, where n is the number of agents used to scan and sort the index table.

DB2_SMS_TRUNC_TMPTABLE_THRESH
  • Operating system: All
  • Default=-2, Values: -2, -1, 0to 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.

    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, 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.

    Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

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: 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_ASYNC_FOR_MIRRORLOG
  • Operating system: Unix
  • Default: OFF, Values: ON | OFF
  • This variable may improve the performance of log writing for databases configured with a mirrored log path (ie. via the MIRRORLOGPATH database configuration parameter) by asynchronously writing log data to both the active log path and the mirrored log path in parallel. Log write performance can be monitored using the log write time monitor element.
  • This variable is available in Version 11.1.4.4 and newer releases.
  • Changes to this variable require the database instance to be restarted.
  • Db2 does not allow this environment variable to be turned on in Windows.
  • It is not recommended to enable this variable in Solaris environments, as degradation in mirrorlog performance may be observed.
DB2_USE_BUFFERED_READ_FOR_ACTIVE_LOG
  • Operating system: All
  • Default=NO, Values: YES or NO
  • This variable specifies whether to allow a significant performance improvement for rollback processing of large units of work, through the autonomous use of buffered I/O when reading transaction log file data.
  • This variable is available in Version 11.1.4.4 and newer releases.
  • Changes to this variable do not require the database instance to be restarted.
DB2_USE_FAST_PREALLOCATION
  • Operating system: AIX and Linux on Veritas VxFS, JFS2, GPFS, ext4 (Linux only) and xFS (Linux only) file systems
  • Default: ON for Veritas VxFS, JFS2, GPFS, ext4 and xFS, 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. Note that once fast preallocation is disabled, the database has to be restarted.

DB2_USE_FAST_LOG_PREALLOCATION
  • Operating system: AIX and Linux on Veritas VxFS, JFS2, GPFS, ext4 (Linux only) and xFS (Linux only) file systems
  • Default: OFF, ON under DB2_WORKLOAD=SAP, Values: ON or OFF
  • Allows the fast preallocation file system feature to reserve space for log files, and speed up the process of creating or altering large log files, if the underlying file system supports this feature. This speed improvement is implemented at a small delta cost of performing actual space allocation during runtime when log records get written to such preallocated log files.

    To enable fast preallocation for logs, set DB2_USE_FAST_LOG_PREALLOCATION to ON.

DB2_USE_IOCP
  • Operating system: AIX
  • Default=ON, Values: ON or OFF
  • This variables 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.
DB2_4K_DEVICE_SUPPORT
  • Operating system: All
  • Default=OFF, Values: ON or OFF
  • Set this variable to ON to enable support for storage devices that use a 4K sector size. This setting adjusts the memory layout of data structures and the parameter of disk I/O operations to be compatible with the requirements of this type of storage. If you enable this setting in an environment configured with storage devices that use a 512-byte sector size, performance may be degraded.
  • This variable is available starting in Version 11.1.4.4 as a technical preview, and not yet supported in production environments until further notice.
  • Changes to this variable require the database instance to be restarted.
  • The following restrictions and limitations currently apply when 4 KB sector support is enabled:
    • The use of database managed (DMS) table spaces that are configured for direct disk (raw) access is not supported.
    • The use of system managed (SMS) table spaces that are configured with NO FILESYSTEM CACHING is not supported.
    • There may be a performance penalty accessing large object (LOB) data, depending on workload characteristics.
    • There may be a performance penalty when restoring backup images or processing load copy files that were created prior to enabling of 4 KB sector support.
    • Backup images and load copy files will be slightly larger in size.
    • This feature is not supported for pureScale® instances.