You can set performance variables to improve database processes, such as access plan optimizations, memory tuning operations, and operating resource policies.
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.
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.
The activation of the DB2ASSUMEUPDATE registry variable is effective on the db2start command.
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.
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).
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.
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.
This variable specifies whether or not the query optimizer uses optimization extensions to improve query performance. The ON, ENHANCED_MULTIPLE_DISTINCT, and IXOR values specify different optimization extensions. Use a comma-separated list when you want to use multiple values.
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)
In general, DB2_EXTENDED_OPTIMIZATION settings might not improve query performance in all environments. Testing should be done to determine individual query performance improvements.
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.
Memory access-intensive applications that use large amounts of virtual memory may 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 have to configure large pages on the operating system and the instance owner must possess the CAP_BYPASS_RAC_VMM and CAP_PROPAGATE capabilities.
On AIX 5L™, you can set this variable to FCM. FCM memory resides in its own memory set, so you must add the FCM keyword to the value of the DB2_LARGE_PAGE_MEM registry variable to enable large pages for FCM memory.
On Linux, there is an additional requirement for the availability of the libcap.so.1 library. This library must be installed for this option to work. If this option is turned on and the library is not on the system, the DB2 database disables the large kernel pages and continues to function as it would without them.
On Linux, to verify that large kernel pages are available, issue the following command:
cat ⁄proc⁄meminfo
If large kernel pages are available, the following three lines should appear (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 need to 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 for changes to take effect.)
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.
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).If only nonpartitioned RID indexes exist on the table, deferred index cleanup rollout is supported.
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.
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.
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.
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.
db2set DB2NTMEMSIZE=FCM:1073741824;APLD:268435456
db2set DB2NTMEMSIZE=QUE:67108864
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.
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.
For more information, please refer to the SetPriorityClass() API in the Win32 documentation.
Specify DB2NTWORKSET using the syntax DB2NTWORKSET=min, max, where min and max are expressed in megabytes.
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.
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:
db2iadm1 MLOCK
setprivgrp -f /etc/privgroup
db2set DB2_RCT_FEATURES=GROUPUPDATE=ON
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.
On POWER7 systems running AIX 6.1 Technology Level (TL) 5 or higher this variable can be set to AUTOMATIC. With this setting the DB2 database system automatically determines the hardware topology and assigns engine dispatchable units (EDUs) to the various hardware modules in such a way that memory can be more efficiently shared between multiple EDUs that need to access the same regions of memory. This setting is intended for larger POWER7 systems with 16 or more cores and can result in enhanced query performance on some workloads. It is best to run a performance analysis of the workload before and after setting this variable to AUTOMATIC to validate any performance improvements.
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>
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).
<RESOURECE_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>0x0F</RESOURCE>
<DBMEM_PERCENTAGE>50</DBMEM_PERCENTAGE>
<BUFFERPOOL_BINDING>
<BUFFERPOOL_ID>3</BUFFERPOOL_ID>
</BUFFERPOOL_BINDING>
</RESOURCE_BINDING>
</DATABASE_RESOURCE_POLICY>
</RESOURCE_POLICY>
You can set an operating system scheduling policy for DB2 on AIX, and for DB2 on Windows using the DB2NTPRICLASS registry variable.
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>
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_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.
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.
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.
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.
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.
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.
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.
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:
If these data types are encountered, an SQLCODE -804, SQLSTATE 07002 error is returned.
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 restored.