版次注意事項


8.8 Appendix A. DB2 Registry and Environment Variables

The following registry variables are new or require changes:

|8.8.1 Table of New and Changed Registry Variables


|

|Table 6. Registry Variables
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.

Note:
If AWE support is enabled, extended storage (ESTORE) cannot be used for any of the buffer pools in the database. The buffer pools referenced by this variable must already exist in SYSIBM.SYSBUFFERPOOLS.
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
  • The term preceding the comma, or the only term to the right of the predicate, means the following but only for columns that do not have positive sub-element statistics:
    • S - The optimizer estimates the length of each element in a series of elements concatenated together to form a column based on the length of the string enclosed in the % characters.
    • Y - The default. Use a default value of 1.9 for the algorithm parameter. Use a variable-length sub-element algorithm with the algorithm parameter.
    • N - Use a fix-length sub-element algorithm.
    • num1 - Use the value of num1 as the algorithm parameter with the variable length sub-element algorithm.
  • The term following the comma means the following:
    • N - The default. Do not collect of use sub-element statistics.
    • Y - Collect sub-element statistics. Use a variable-length sub-element algorithm that uses the collected statistics together with the 1.9 default value for the algorithm parameter in the case of columns with positive sub-element statistics.
    • num2 - Collect sub-element statistics. Use a variable-length sub-element algorithm that uses the collected statistics together with the value of num2 as the algorithm parameter in the case of columns with positive sub-element statistics.
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:

  1. Add 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. Issue the following command:

    setprivgrp -f /etc/privgroup

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.


[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]