The following registry variables are new or require changes:
Variable Name | Operating System | Values |
---|---|---|
Description | ||
DB2MAXFSCRSEARCH | All | Default=5
Values: -1, 1 to 33554 |
Specifies the number of free space control records to search when adding a record to a table. The default is to search five free space control records. 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 free space control records. | ||
DLFM_TSM_MGMTCLASS | AIX, Windows NT, Solaris | Default: the default TSM management class
Values: any valid TSM management class |
Specifies which TSM management class to use to archive and retrieve linked files. If there is no value set for this variable, the default TSM management class is used. | ||
DB2_CORRELATED_PREDICATES | All | Default=YES
Values: YES or NO |
The default for this variable is YES. When there are unique indexes on correlated columns in a join, and this registry variable is YES, the optimizer attempts to detect and compensate for correlation of join predicates. When this registry variable is YES, the optimizer uses the KEYCARD information of unique index statistics to detect cases of correlation, and dynamically adjusts the combined selectivities of the correlated predicates, thus obtaining a more accurate estimate of the join size and cost. | ||
DB2_VI_DEVICE | Windows NT | Default=null
Values: nic0 or VINIC |
Specifies the symbolic name of the device or Virtual Interface Provider Instance associated with the Network Interface Card (NIC). Independent hardware vendors (IHVs) each produce their own NIC. Only one (1) NIC is allowed per Windows NT machine; Multiple logical nodes on the same physical machine will share the same NIC. The symbolic device name "VINIC" must be in upper case and can only be used with Synfinity Interconnect. All other currently supported implementations use "nic0" as the symbolic device name. | ||
DB2_SELECTIVITY | ALL | Default=NO
Values: YES or NO |
This registry variable controls where the SELECTIVITY clause can be used. See the SQL Reference, Language Elements, Search Conditions for complete details on the SELECTIVITY clause.
When this registry variable is set to YES, the SELECTIVITY
clause can be specified when the predicate is a basic predicate where at least
one expression contains host variables.
| ||
DB2_UPDATE_PART_KEY | ALL | Default=YES
Values: YES or NO |
For FixPak 3 and later, the default value is YES. This registry variable specifies whether or not update of the partitioning key is permitted. | ||
DB2_BLOCK_ON_LOG_DISK_FULL | ALL | Default=NO
Values: YES or NO |
This DB2 registry variable can be set to prevent "disk full" errors from being generated when DB2 cannot create a new log file in the active log path.
Instead, DB2 attempts to create the log file every 5 minutes until it succeeds. After each attempt, DB2 writes a message to the db2diag.log file. The only way that you can confirm that your application is hanging because of a log disk full condition is to monitor the db2diag.log file.
Until the log file is successfully created, any user application
that attempts to update table data will not be able to commit transactions.
Read-only queries may not be directly affected; however, if a query needs
to access data that is locked by an update request, or a data page that is
fixed in the buffer pool by the updating application, read-only queries will
also appear to hang.
| ||
DB2_INDEX_2BYTEVARLEN | All | Default=NO
Values: YES or NO |
This registry variable allows columns
with a length greater than 255 bytes to be specified as part of an index
key. Indexes already created before turning this registry variable YES will
continue to have the 255 key limit restriction. Indexes created after turning
this registry variable YES will behave as a two-byte index even when the
registry variable is turned NO again.
Several SQL statements are affected by changes to this registry variable including CREATE TABLE, CREATE INDEX, and ALTER TABLE. For more information on these statements, refer to the changes documented for the SQL Reference. | ||
DB2_FORCE_FCM_BP | AIX | Default=NO
Values: YES or NO |
Specifies from where the fast communications manager (FCM) resources are allocated. The resources may be allocated from either the database manager shared memory segment or a separate one. With multiple logical nodes on the same machine, this registry variable should be used. On a partitioned database system with symmetric multi-processing (SMP) enabled, the setting of this registry variable has no effect on how communication takes place. In this case, communication is always through shared memory. However, it does affect the number of shared memory segments DB2 will use. | ||
DB2_AWE | Windows 2000 | Default=Null
Values: <entry>[; <entry>;...] where <entry>=<buffer pool ID>, <number of physical pages>, <number of address windows> |
Allows DB2 UDB on Windows 2000 to allocate
buffer pools that use up to 64 GB of memory. Windows 2000 must be configured
correctly to support Address Windowing Extensions (AWE) buffer pools. This
includes associating the "lock pages in memory"-right with the user on
Windows 2000 and setting this registry variable on DB2. In setting this variable
you need to know the buffer pool ID that is to be used for AWE support. You
also need to determine the number of physical pages to allocate and the number
of address windows.
For information on determining the number of physical pages to allocate and the number of address windows, see the section on "Managing the Database Buffer Pool" found in "Chapter 8. Operational Performance" earlier in this section.
| ||
DB2_STPROC_LOCKUP_FIRST | All | Default=NO
Values: YES or NO |
This registry variable has been renamed from DB2_DARI_LOOKUP_ALL. | ||
DB2MEMDISCLAIM | AIX | Default=YES
Values: YES or NO |
On AIX, memory used by DB2 processes may
have some associated paging space. This paging space may remain reserved,
even when the associated memory has been freed. The reservation of the paging
space depends on the AIX system's tunable virtual memory management allocation
policy. This registry variable controls whether DB2 agents explicitly request
that AIX disassociate the reserved paging space from the freed memory.
A setting of "YES" results in smaller paging space requirements, and possibly less disk activity from paging. A setting of "NO" results in greater paging space requirements, and possibly more disk activity from paging. In some situations, such as if paging space is plentiful, and if real memory is so plentiful that paging never occurs, then a setting of NO will provide a small performance improvement. | ||
DB2MEMMAXFREE | All | Default=8 388 608 bytes
Values: 0 to 232-1 bytes |
This registry variable controls the maximum amount of unused memory in bytes retained by DB2 processes. | ||
DB2_ANTIJOIN | All | Default=NO in a EEE environment
Default=YES in a non-EEE environment
Values: YES or NO |
For DB2 Universal Database EEE environments: When YES is specified, the optimizer will search for opportunities to transform NOT EXISTS subqueries into anti-joins which can be processed more efficiently by DB2. For non-EEE environments: When NO is specified, the optimizer will limit the opportunities to transform NOT EXISTS subqueries into anti-joins. | ||
NEWLOGPATH2 | UNIX | Default=NO
Values: YES or NO |
This parameter allows you to specify whether a secondary path should be used to implement dual logging. The path that will be used is generated by appending the character '2' to the current value of 'LOGPATH'. |
DB2DOMAINLIST | Windows NT | Default=Null
Values: one or more valid Windows NT domains (comma separating each) |
Defines one or more Windows
NT domains. Only users belonging to these domains will have their connection
or attachment requests accepted.
This registry variable should only be used under a pure Windows NT domain environment with DB2 servers and clients running DB2 Universal Database Version 7.1 (or later). | ||
DB2_LIKE_VARCHAR | All | Default=Y,N
Values: Y, N, S, floating point constant between 0 and 6.2 |
Controls the collection and
use of sub-element statistics. These are statistics about the content of data
in columns when the data has a structure in the form of a series of sub-fields
or sub-elements delimited by blanks.
This registry variable affects how the optimizer deals with a predicate of the form: COLUMN LIKE '%xxxxxx%' where the xxxxxx is any string of characters.
The syntax showing how this registry variable is used is:
db2set DB2_LIKE_VARCHAR=[Y|N|S|num1] [,Y|N|S|num2]where
| ||
DB2_PINNED_BP | AIX, HP-UX | Default=NO
Values: YES or NO |
This variable is used to hold
the database global memory (including buffer pools) associated with the database
in the main memory on some AIX operating systems. Keeping this database global
memory in the system main memory allows database performance to be more consistent.
If, for example, the buffer pool was swapped out of the system main memory then database performance would deteriorate. The reduction of disk I/O by having the buffer pools in system memory improves database performance. If you have other applications that require more of the main memory, you will want to allow the database global memory, depending on the system main memory requirements, to be swapped out of main memory.
When working with HP-UX in a 64-bit environment, in addition to modifying this registry variable, the DB2 instance group must be given the MLOCK privilege. This is done by having a user with root access rights do the following:
| ||
DB2_RR_TO_RS | All | Default=NO
Values: YES or NO |
Next key locking guarantees Repeatable Read (RR) isolation level by automatically locking the next key for all INSERT and DELETE statements and the next higher key value above the result set for SELECT statements. For UPDATE statements that alter key parts of an index, the original index key is deleted and the new key value is inserted. Next key locking is done on both the key insertion and key deletion. Next key locking is required to guarantee ANSI and SQL92 standard RR, and is the DB2 default.
If your application appears to stop or hang, you should examine snapshot information for your application. If the problem appears to be with next key locking, you can set the DB2_RR_TO_RS registry variable on based on two conditions. You can turn DB2_RR_TO_RS on if none of your applications rely on Repeatable Read (RR) behavior and if it is acceptable for scans to skip over uncommitted deletes. The skipping behavior affects the RR, Read Stability (RS), and Cursor Stability (CS) isolation levels. (There is no row locking for Uncommitted Read (UR) isolation level.)
When DB2_RR_TO_RS is on, RR behavior cannot be guaranteed for scans on user tables because next key locking is not done during index key insertion and deletion. Catalog tables are not affected by this option.
The other
change in behavior is that with DB2_RR_TO_RS on, scans will skip over rows
that have been deleted but not committed, even though the row may have qualified
for the scan.
|