IBM Books

Administration Guide


Appendix E. DB2 Registry and Environment Variables

The following is a list of DB2 registry variables and environment variables that you may need to know about to get up and running. Each has a brief description; some may not apply to your environment.

You can view a list of all supported registry variables by using:

   db2set -lr

You can change the value for a variable for the current session by using:

   db2set registry_variable_name=new_value

To update environment variables, the set command must be used and then the system rebooted.

The values for the changed registry variables must be set before the DB2START command is issued. Refer to Administration Guide, Design and Implementation for more information on changing and using registry variables.
Parameter Operating System Values Description
General
DB2ACCOUNT All Default=null The accounting string that is sent to the remote host. Refer to the DB2 Connect User's Guide for details.
DB2BIDI All Default=NO

Values: YES or NO

This variable enables bidirectional support and the db2codepage variable is used to declare the code page to be used. Refer to the Administration Guide, Design and Implementation in the National Language Support appendix for additional information on bidirectional support.
DB2CODEPAGE All Default: derived from the language ID, as specified by the operating system. Specifies the code page of the data presented to DB2 for database client application. The user should not set db2codepage unless explicitly stated in DB2 documents, or asked to do so by DB2 service. Setting db2codepage to a value not supported by the operating system can produce unexpected results. Normally, you do not need to set db2codepage because DB2 automatically derives the code page information from the operating system.
DB2COUNTRY All Default: derived from the language ID, as specified by the operating system. Specifies the country code of the client application, which influences date and time formats.
DB2DBDFT All Default=null Specifies the database alias name of the database to be used for implicit connects. If an application has no database connection but SQL statements are issued, an implicit connect will be made provided the DB2DBDFT environment variable has been defined with a default database.
DB2DBMSADDR Windows 32-bit operating systems Default= 0x20000000 for Windows NT, 0x90000000 for Windows 95

Value: 0x20000000 to 0xB0000000 in increments of 0x10000

Specifies the default database manager shared memory address in hexadecimal format. If db2start fails due to a shared memory address collision, this registry variable can be modified to force the database manager instance to allocate its shared memory at a different address.
DB2DISCOVERYTIME OS/2 and Windows 32-bit operating systems Default=40 seconds,

Minimum=20 seconds

Specifies the amount of time that SEARCH discovery will search for DB2 systems.
DB2DMNBCKCTLR Windows NT Default=null If DB2 is installed on a backup domain controller, setting this parmeter to YES allows DB2 to use the security database on the backup domain controller. This reduces LAN traffic.
Note:A backup domain controller shadows the security database on the primary domain controller.
DB2INCLUDE All Default=current directory Specifies a path to be used during the processing of the SQL INCLUDE text-file statement during DB2 PREP processing. It provides a list of directories where the INCLUDE file might be found. Refer to the Application Development Guide for descriptions of how db2include is used in the different precompiled languages.
DB2INSTDEF OS/2 and Windows 32-bit operating systems Default=DB2 Sets the value to be used if DB2INSTANCE is not defined.
DB2INSTOWNER Windows NT Default=null The registry variable created in the DB2 profile registry when the instance is first created. This variable is set to the name of the instance-owning machine.
DB2_LIC_STAT_SIZE All Default=null

Range: 0 to 32767

The registry variable is used to determine the maximum size (in MBs) of the file containing the license statistics for the system. A value of zero turns the license statistic gathering off. If the variable is not recognized or not defined, the variable defaults to unlimited. The statistics are displayed using the license center.
DB2NBDISCOVERRCVBUFS All Default=16 buffers,

Minimum=16 buffers

This variable is used for NetBIOS search discovery. The variable specifies the number of concurrent discovery responses that can be received by a client. If the client receives more concurrent responses than are specified by this variable, then the excess responses are discarded by the NetBIOS layer. The default is sixteen (16) NetBIOS receive buffers. If a number less than the default value is chosen, then the default is used.
DB2OPTIONS All except Windows 3.1 and Macintosh Default=null Sets command line processor options.
DB2SLOGON Windows 3.x Default=null,

Values: YES or NO

Enables a secure logon in DB2 for Windows 3.x. If db2slogon=YES DB2 does not write user IDs and passwords to a file, but instead uses a segment of memory to maintain them. When db2slogon is enabled, the user must logon each time Windows 3.x is started.
DB2TIMEOUT Windows 3.x and Macintosh Default=(not set) Used to control the timeout period for Windows 3.x and Macintosh clients during long SQL queries. After the timeout period has expired a dialog box pops up asking if the query should be interrupted or allowed to continue. The minimum value for this variable is 30 seconds. If db2timeout is set to a value between 1 and 30, the default minimum value will be used. If db2timeout is set to a value of 0, or a negative value, the timeout feature is disabled. This feature is disabled by default.
DB2TRACENAME Windows 3.x and Macintosh Default= DB2WIN.TRC (on Windows 3.x), DB2MAC.TRC (on Macintosh) On Windows 3.x and Macintosh, specifies the name of the file where trace information is stored. The default for each system is saved in your current instance directory (for example, \sqllib\db2). We strongly recommend that you specify the full path name when naming the trace file.
DB2TRACEON Windows 3.x and Macintosh Default=NO

Values: YES or NO

On Windows 3.x and Macintosh, turns trace on to provide information to IBM in case of a problem. (It is not recommended that you turn trace on unless you encounter a problem you cannot resolve.) Refer to the Troubleshooting Guide for information on using the trace facility with clients.
DB2TRCFLUSH Windows 3.x and Macintosh Default=NO

Values: YES or NO

On Windows 3.x and Macintosh, db2trcflush can be used in conjunction with db2traceon=YES. Setting db2trcflush=YES will cause each trace record to be written immediately into the trace file. This will slow down your DB2 system considerably, so the default setting is db2trcflush=NO. This setting is useful in cases where an application hangs the system and requires the system to be rebooted. Setting this keyword guarantees that the trace file and trace entries are not lost by the reboot.
DB2TRCSYSERR Windows 3.x and Macintosh Default=1

Values: 1-32767

Specifies the number of system errors to trace before the client turns off tracing. The default value traces one system error, after which, trace is turned off.
DB2YIELD Windows 3.x Default=NO

Values: YES or NO

Specifies the behavior of the Windows 3.x client while communicating with a remote server. When set to NO, the client will not yield the CPU to other Windows 3.x applications, and the Windows environment is halted while the client application is communicating with the remote server. You must wait for the communications operation to complete before you can resume any other tasks. When set to YES, your system functions as normal. It is recommended that you try to run your application with db2yield=YES. If your system crashes, you will need to set db2yield=NO. For application development, ensure your application is written to accept and handle Windows messages while waiting for a communications operation to complete.
System Environment
DB2CONNECT_IN_APP_PROCESS All Default=YES

Values: YES or NO

When setting this variable to NO, local DB2 Connect clients on a DB2 Connect Enterprise Edition machine are forced to run within an agent. Some advantages of running within an agent are that local clients are able to be monitored and that they can use SYSPLEX support.
DB2ENVLIST UNIX Default: null Lists specific variable names for either stored procedures or user-defined functions. By default, the db2start command filters out all user environment variables except those prefixed with DB2 or db2. If specific registry variables must be passed to either stored procedures or user-defined functions, you can list the variable names in the db2envlist registry variable. Separate each variable name by one or more spaces. DB2 constructs its own PATH and LIBPATH, so if PATH or LIBPATH is specified in db2envlist, the actual value of the variable name is appended to the end of the DB2-constructed value.
DB2INSTANCE All Default=db2instdef on OS/2 and Windows 32-bit operating systems. The environment variable used to specify the instance that is active by default. On UNIX, users must specify a value for DB2INSTANCE.
DB2INSTPROF OS/2, Windows 3.x, and Windows 32-bit operating systems Default: null The environment variable used to specify the location of the instance directory on OS/2, Windows 3.x, and Windows 32-bit operating systems, if different than DB2PATH.
DB2LIBPATH UNIX Default: null Specifies the value or LIBPATH in the db2libpath registry variable. The value of LIBPATH cannot be inherited between parent and child processes if the user ID has changed. Since the db2start executable is owned by root, DB2 cannot inherit the LIBPATH settings of end users. If you list the variable name, LIBPATH, in the db2envlist registry variable, you must also specify the value of LIBPATH in the db2libpath registry value. The db2start executable then reads the value of db2libpath and append this value to the end of the DB2-constructed LIBPATH.
DB2NODE All
Used to specify the target logical node of a DB2 Extended Enterprise Edition database partition server that you want to connect to. If this variable is not set, the target logical node defaults to the logical node which is defined with port 0 on the machine.
DB2PATH OS/2, Windows 3.x, and Windows 32-bit operating systems Default: (varies by operating system) The environment variable used to specify the directory where the product is installed on OS/2, Windows 3.x, and Windows 32-bit operating systems.
Communications
DB2CHECKCLIENTINTERVAL AIX, server only Default=0

Values: A numeric value greater than zero.

Used to verify the status of APPC client connections. Permits early detection of client termination, rather than waiting until after the completion of the query. When set to zero, no check will be made. When set to a numerical value greater than zero, the value represents DB2 internal work units. For guidance, the following check frequency values are given: Low frequency use 300; medium frequency use 100; high frequency use 50. Checking more frequently for client status while executing a database request lengthens the time taken to complete the queries. If the DB2 workload is heavy (that is, it involves many internal requests), then setting DB2CHECKCLIENTINTERVAL to a low value has a greater impact on performance than in a situation where the workload is light and most of the time DB2 is waiting.
DB2COMM All, server only Default=null

Values: any combination of APPC, IPXSPX, NETBIOS, NPIPE, TCPIP

Specifies the communication managers that are started when the database manager is started. If this is not set, no DB2 communications managers are started at the server.
DB2_FORCE_NLS_CACHE AIX, HP_UX, Solaris Default=FALSE

Values: TRUE or FALSE

Used to eliminate the chance of lock contention in multi-threaded applications. When this registry variable is "TRUE", the code page and country code information is saved the first time a thread accesses it. From that point, the cached information is used for any other thread that requests this information. This eliminates lock contention and results in a performance benefit in certain situations. This setting should not be used if the application changes locale settings between connections. It is likely not needed in such a situation anyway, since multi-threaded applications typically do not change their locale settings because it is not "thread-safe" to do so.
DB2NBADAPTERS OS/2 and Windows NT, server only Default=0

Range: 0-15,

Multiple values should be separated by commas

Used to specify which local adapters to use for DB2 NetBIOS LAN communications. Each local adapter is specified using its logical adapter number.
DB2NBCHECKUPTIME OS/2 and Windows NT, server only Default=1 minute

Values: 1-720

Specifies the time interval between each invocation of the NetBIOS protocol checkup procedure. Checkup time is specified in minutes.

Lower values will ensure that the NetBIOS protocol checkup runs more often, freeing up memory and other system resources left when unexpected agent/session termination occurs.

DB2NBINTRLISTENS OS/2 and Windows NT, server only Default=1

Values: 1-10

Multiple values should be separated by commas

Specifies the number of NetBIOS listen send commands (NCBs) that will be asynchronously issued in readiness for remote client interrupts. This flexibility is provided for "interrupt active" environments to ensure that interrupt calls from remote clients will be able to establish connections when servers are busy servicing other remote interrupts.

Setting db2nbintrlistens to a lower value will conserve NetBIOS sessions and NCBs at the server. However, in an environment where client interrupts are common, you may need to set db2nbintrlistens to a higher value in order to be responsive to interrupting clients.
Note:Values specified are position sensitive; they relate to the corresponding value positions for db2nbadapters.

DB2NBRECVBUFFSIZE OS/2 and Windows NT, server only Default=4096 bytes

Range: 4096-65536

Specifies the size of the DB2 NetBIOS protocol receive buffers. These buffers are assigned to the NetBIOS receive NCBs. Lower values conserve server memory, while higher values may be required when client data transfers are larger.
DB2NBBRECVNCBS OS/2 and Windows NT, server only Default=10

Range: 1-99

Specifies the number of NetBIOS "receive_any" commands (NCBs) that the server will issue and maintain during operation. This value may be adjusted depending on the number of remote clients to which your server is connected. Lower values will conserve server resources.
Note:Each adapter in use can have its own unique receive NCB value specified by db2nbbrecvncbs. The values specified are position sensitive; they relate to the corresponding value positions for db2nbadapters.
DB2NBRESOURCES OS/2 and Windows NT server only Default=null Specifies the number of NetBIOS resources to allocate for DB2 use in a multi-context environment. This variable is restricted to multi-context client operation.
DB2NBSENDNCBS OS/2 and Windows NT, server only Default=6

Range: 1-720

Specifies the number of send NetBIOS commands (NCBs) that the server will reserve for use. This value may be adjusted depending on the number of remote clients your server is connected to. Setting db2nbsendncbs to a lower value will conserve server resources. However, you may need to set it to a higher value to prevent the server from waiting to send to a remote client when all other send commands are in use.
DB2NBSESSIONS OS/2 and Windows NT, server only Default=null

Range: 5-254

Specifies the number of sessions that DB2 should request to be reserved for DB2 use. The value of db2nbsessions can be set to request a specific session for each adapter specified using db2nbadapters.
Note:Values specified are position sensitive; they relate to the corresponding value positions for db2nbadapters.
DB2NBXTRANCBS OS/2 and Windows NT, server only Default=5 per adapter

Range: 5-254

Specifies the number of "extra" NetBIOS commands (NCBs) the server will need to reserve when the db2start command is issued. The value of db2nbxtrancbs can be set to request a specific session for each adapter specified using db2nbadapters.
DB2NETREQ Windows 3.x Default=3

Range: 0-25

Specifies the number of NetBIOS requests that can be run concurrently on Windows 3.x clients. The higher you set this value, the more memory below the 1MB level will be used. When the concurrent number of requests to use NetBIOS services reaches the number you have set, subsequent incoming requests for NetBIOS services are held in a queue and become active as the current requests complete. If you enter 0 (zero) for db2netreq, the Windows database client issues NetBIOS calls in synchronous mode using the NetBIOS wait option. In this mode, the database client allows only the current NetBIOS request to be active and does not process another one until the current request has completed. This can affect other application programs. The 0 value is provided for backwards compatibility only. It is strongly recommended that 0 not be used.
DB2RETRY OS/2 and Windows NT Default=0

Range: 0-20 000

The number of times DB2 attempts to restart the APPC listener. If the SNA subsystem at the server/gateway is down, this profile variable, in conjunction with db2retrytime, can be used to automatically restart the APPC listener without disrupting client communications using other protocols. In such a scenario, it is no longer necessary to stop and restart DB2 to reinstate the APPC client communications.
DB2RETRYTIME OS/2 and Windows NT Default=1 minute

Range: 0-7 200 minutes

In increments of one minute, the number of minutes that DB2 allows between performing successive retries to start the APPC listener. If the SNA subsystem at the server/gateway is down, this profile variable, in conjunction with db2retry, can be used to automatically restart the APPC listener without disrupting client communications using other protocols. In such a scenario, it is no longer necessary to stop and restart DB2 to reinstate the APPC client communications.
DB2SERVICETPINSTANCE OS/2 and Windows NT Default=null Used to support incoming APPC connections from DB2 workstation V.1 clients or from the DB2 MVS database. When the db2start command is invoked, the instance specified will start the APPC listeners for the following TP names:
DB2INTERRUPT
x'07'68
x'07'6SN
DB2SOSNDBUF Windows 95 and Windows NT Default=32767 Specifies the value of TCP/IP send buffers on Windows 95 and Windows NT operating systems.
DB2SYSPLEX_SERVER OS/2, Windows NT, and UNIX Default=null Specifies whether SYSPLEX exploitation when connected to DB2 for OS/390 is enabled. If this registry variable is not set (which is the default), or is set to a non-zero value, exploitation is enabled. If this registry variable is set to zero (0), exploitation is disabled. When set to zero, SYSPLEX exploitation is disabled for the gateway regardless of how the DCS database catalog entry has been specified. For more information see the Command Reference and the CATALOG DCS DATABASE command.

DB2_VI_ENABLE Windows NT Default=OFF

Values: ON or OFF

Specifies whether to use the Virtual Interface Architecture (VIA) communication protocol or not. If this registry variable is "ON", then FCM will use VI for inter-node communication. If this registry variable is "OFF", then FCM will use TCP/IP for inter-node communication.
Note:The value of this registry variable must be the same across all the database partitions in the instance.
DB2_VI_VIPL Windows NT Default= vipl.dll Specifies the name of the Virtual Interface Provider Library (VIPL) that will be used by DB2. In order to load the library successfully, the library name used in this registry variable must be in the PATH user environment variable. The currenly supported implementations all use the same library name.
DB2_VI_DEVICE Windows NT Default=null

Values: nic0

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 currenly supported implementations all use the same symbolic name.
DCE Directories
DB2DIRPATHNAME OS/2, UNIX, and Windows 32-bit operating systems Default=null Specifies a temporary override of the DIR_PATH_NAME parameter value in the database manager configuration file. If a directory server is used and the target of a CONNECT statement or ATTACH command is not explicitly cataloged, then the target is concatenated with DB2DIRPATHNAME (if specified) to form the fully qualified DCE name.
Note:The db2dirpathname variable has no effect on the instance's global name, which is always identified by the database manager configuration parameters DIR_PATH_NAME and DIR_OBJ_NAME.
DB2CLIENTADPT OS/2 and Windows 32-bit operating systems Default=null

Range: 0-15

Specifies the client adapter number for NETBIOS protocol on OS/2 and Windows 32-bit operating systems. The db2clientadpt value overrides the DFT_CLIENT_ADPT parameter value in the database manager configuration file.
DB2CLIENTCOMM OS/2, UNIX, and Windows 32-bit operating systems Default=null Specifies a temporary override of the DFT_CLIENT_COMM parameter value in the database manager configuration file. If both DFT_CLIENT_COMM and db2clientcomm are not specified, then the first protocol found in the object is used. If either one or both of them are specified, then only the first matching protocol will be used. In either case, no retry is attempted if the first connect fails.
DB2ROUTE OS/2, UNIX, and Windows 32-bit operating systems Default=null Specifies the name of the Routing Information Object the client uses when it connects to a database with a different database protocol. The db2route value overrides the ROUTE_OBJ_NAME parameter value in the database manager configuration file.
Command Line Processor
DB2BQTIME All Default=1 second

Maximum value: 1 second

Specifies the amount of time the command line processor front end will sleep before checking if the back end process is active and establishing a connection to it.
DB2BQTRY All Default=60 retries

Minimum value: 0 retries

Specifies the number of times the command line processor front end process tries to determine whether the back end process is already active. It works in conjunction with db2bqtime.
DB2IQTIME All Default=5 seconds

Minimum value: 1 second

Specifies the amount of time the command line processor back end process waits on the input queue for the front end process to pass commands.
DB2OPTIONS All Default=null Sets command line processor options.
DB2RQTIME All Default=5 seconds

Minimum value: 1 second

Specifies the amount of time the command line processor back end process waits for a request from the front end process.
MPP Configuration
DB2ATLD_PORTS DB2 UDB EEE on AIX, Solaris, and Windows NT Default= 6000:6063

Value: num1:num2 where both are between 1 and 65535, and num1<=num2

Specifies the range of port numbers used for the AutoLoader utility's internal TCPIP communication. If not set, AutoLoader uses the internal default port range 6000:6063. When you have other applications using the AutoLoader default port range, this variable can be used to select an alternate port range.
DB2ATLD_PWFILE DB2 UDB EEE on AIX, Solaris, and Windows NT Default=null

Value: a file path expression

Specifies a path to a file that contains a password used during AutoLoader authentication. If not set, AutoLoader either extracts the password from its configuration file or prompts you interactively. Using this variable will address password security concerns and allows the separation of AutoLoader configuration information from authentication information.
DB2CHGPWD_EEE DB2 UDB EEE on AIX and Windows NT Default=null

Values: YES or NO

Specifies whether you are allowing other users to change passwords on AIX or Windows NT EEE systems. You must ensure that the passwords for all partitions or nodes are maintained centrally using either a Windows NT domain controller on Windows NT, or NIS on AIX. If not maintained centrally, passwords may not be consistent across all partitions or nodes. This could result in a password only being changed at the database partition to which the user connects to make the change.
DB2_FORCE_FCM_BP AIX Default=NO

Values: YES or NO

This registry variable is applicable to DB2 UDB EEE for AIX when using multiple logical partitions. When DB2START is issued, DB2 allocates the FCM buffers from the database global memory or, if there is not enough room there, from a separate shared memory segment which is used by all FCM daemons (for that instance) on the same physical machine. Which it chooses is largely dependent on the number of FCM buffers to be created (which, in turn, is determined by the FCM_NUM_BUFFERS database manager configuration parameter). If this registry variable is set to YES, the FCM buffers are always created in a separate memory segment. When the FCM buffers are created in a separate memory segment, the communication between FCM daemons of different logical partitions on the same physical node occurs through shared memory. Otherwise, FCM daemons on the same node communicate through UNIX Sockets. The advantage of communicating through shared memory in this way is that it is faster. The disadvantage is that there is one fewer shared memory segments available for other uses, most notably database buffer pools. This reduces the maximum size of database buffer pools.
DB2NODE All Default=null

Values: 1-999

Specifies which node of the MPP server instance you want to connect or attach to.
DB2PORTRANGE Windows NT Values: nnnn:nnnn This value is set to the TCP/IP port range used by FCM so that any additional partitions created on another machine will also have the same port range.
SQL Compilier
DB2_CORRELATED_PREDICATES All Default=OFF

Values: ON or OFF

When there are unique indexes on correlated columns in a join, and this registry variable is ON, the optimizer attempts to detect and compensate for correlation of join predicates. When this registry variable is ON, 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_HASH_JOIN All Default=NO

Values: YES or NO

Specifies hash join as a possible join method when compiling an access plan.
DB2_LIKE_VARCHAR All Default=NO

Values: YES, NO, or a floating point constant

Specifies how the optimizer works with a predicate of the form
   COLUMN LIKE '%XXXXXX%'

where the xxxxxx is any string of characters.

For all predicates, the optimizer has to estimate how many rows match the predicate. For LIKE predicates with leading and trailing % characters, the optimizer assumes that the COLUMN being matched has a structure of a series of elements concatenated together to form the entire column. The optimizer then estimates the length of each element based on the length of the string enclosed in the % characters.

DB2_NEW_CORR_SQ_FF All Default=OFF

Values: ON or OFF

Affects the selectivity value computed by the SQL optimizer for certain subquery predicates when it is set to "ON". If can be used to improve the accuracy of the selectivity value of equality subquery predicates that use the MIN or MAX aggregate function in the SELECT list of the subquery. For example:
SELECT * FROM T WHERE 
T.COL = (SELECT MIN(T.COL) 
FROM T WHERE ...)
DB2_PRED_FACTORIZE All Default=NO

Value: YES or NO

Specifies whether the optimizer will search for opportunities to extract additional predicates from disjuncts. In some circumstances, the additional predicates can alter the estimated cardinality of the intermediate and final result sets. With the following query:
SELECT n1.empno, 
       n1.lastname
  FROM employee n1, 
       employee n2
  WHERE 
   ((n1.lastname='SMITH'
  AND n2.lastname='JONES')
  OR (n1.lastname='JONES'
  AND n2.lastname='SMITH'))

the optimizer can generate the following additional predicates:

SELECT n1.empno, 
       n1.lastname
  FROM employee n1, 
       employee n2
  WHERE n1.lastname IN 
   ('SMITH', 'JONES')
  AND n2.lastname IN 
   ('SMITH', 'JONES')
  AND 
   ((n1.lastname='SMITH'
  AND n2.lastname='JONES')
  OR (n1.lastname='JONES'
  AND n2.lastname='SMITH'))
Performance
DB2_AVOID_PREFETCH All Default=OFF,

Values: ON or OFF

Specifies whether or not prefetch should be used during crash recovery. If db2_avoid_prefetch=ON, prefetch is not used.
DB2_BINSORT AIX Default=NO

Values: YES or NO

Enables a new sort algorithm that reduces the CPU time and elapsed time of sorts. This new algorithm extends the extremely efficient integer sorting technique of DB2 UDB to all sort datatypes such as BIGINT, CHAR, VARCHAR, FLOAT, and DECIMAL, as well as combinations of these datatypes. To enable this new algorithm, use the following command:
db2set DB2_BINSORT = yes
DB2BPVARS Windows NT Default=path Specifies the path to a file containing parameters used when tuning buffer pools. The currently supported parameters are: NT_SCATTER_DMSFILE, NT_SCATTER_DMSDEVICE, and NT_SCATTER_SMS.

For each of these parameters, the default is zero (or OFF); and the possible values include: zero (or OFF) and 1 (or ON). Each parameter is used to turn scatter read on for the respective type of containers. Each can only be enabled (turned ON) if DB2NTNOCACHE is set to ON in the registry. A warning message is written to the db2diag.log if DB2NTNOCACHE is set to OFF (or not set), and scatter read remains disabled. The parameters are recommended for systems with a large amount of sequential prefetching against the respective type of containers and you have already decided to use DB2NTNOCACHE set to OFF.

An example of how to set the path to the file is shown:

db2set DB2BPVARS = 
   f:\BPVARSFILE

The content of the file is any of these parameters in the form:

parameter=value
DB2CHKPTR All Default=OFF,

Values: ON or OFF

Specifies whether or not pointer checking for input is required.
DB2_DARI_LOOKUP_ALL All Default=OFF,

Values: ON or OFF

Specifies whether or not the UDB server will perform a catalog lookup for ALL DARIs and stored procedures before looking in the function subdirectory of the sqllib subdirectory; and in the unfenced subdirectory of the function subdirectory of the sqllib subdirectory.
Note:For stored procedures of PARAMETER TYPE DB2DARI that are located in the directories mentioned above, setting this value to "ON" will degrade performance since the catalog lookup will be performed possibly on another node in an EEE configuration) before the function directories are searched.
DB2MEMDISCLAIM AIX Default=null

Values: YES or NO

Depending on the workload being executed and the pool agents configuration, you may run into a situation where the committed memory for each DB2 agent will stay above 32 MB even when the agent is idle. This behavior is expected and usually results in good performance as the memory is kep for fast re-use. However, on a memory constrained system, this may not be a desirable side effect. To avoid this condition, issue the following:
db2set DB2MEMDISCLAIM = yes

Disclaiming memory tells the AIX operating system to stop paging the area so that it no longer occupies any real storage. Setting DB2MEMDISCLAIM to "YES" tells DB2 UDB to disclaim some or all memory once freed, depending on DB2MEMMAXFREE. This ensures that the memory is made readily available for other processes as soon as it is freed. See also DB2MEMMAXFREE.

DB2MEMMAXFREE AIX Default=null

Values: 4000000 to 256000000

Specifies the amount of free memory that is retained by each DB2 agent. You may set this variable to a value between 4 and 256 MB. We recommend that if you use this feature, you specify a value of 8 MB:
db2set DB2MEMMAXFREE 
   = 8000000

See also DB2MEMDISCLAIM.

DB2_MMAP_READ AIX Default=ON ,

Values: ON or OFF

Used in conjunction with db2_mmap_write to allow DB2 to use mmap as an alternate method of I/O. In most environments, mmap should be used to avoid operating system locks when multiple processes are writing to different sections of the same file. However, perhaps you migrated from Parallel Edition V1.2 where the default was OFF allowing AIX chaching of DB2 data read from JFS filesystems into memory (outside the buffer pool). If you want the comparable performance with DB2 UDB, you can either increase the size of the buffer pool, or change db2_mmap_read and db2_mmap_write to OFF.
DB2_MMAP_WRITE AIX Default=ON

Values: ON or OFF

Used in conjunction with db2_mmap_read to allow DB2 to use mmap as an alternate method of I/O. In most environments, mmap should be used to avoid operating system locks when multiple processes are writing to different sections of the same file. However, perhaps you migrated from Parallel Edition V1.2 where the default was OFF allowing AIX caching of DB2 data read from JFS filesystems into memory (outside the buffer pool). If you want the comparable performance with DB2 UDB, you can either increase the size of the buffer pool, or change db2_mmap_read and db2_mmap_write to OFF.
DB2_NO_PKG_LOCK All Default=OFF

Values: ON or OFF

Allows the Global SQL Cache to operate without the use of package locks to protect cached package entries. (Package locks are internal system locks.) To improve performance (because acquiring and freeing locks takes time), you can now choose to work in a "no package lock" mode. In this mode, certain database operations are not allowed. These operations may include: operations that invalidate packages, operations that inoperate packages, and operations that directly change a package.
DB2NTMEMSIZE Windows NT Default=(varies by memory segment) Windows NT requires that all shared memory segments be reserved at DLL initialization time in order to guarantee matching addresses across processes. DB2NTMEMSIZE has been introduced to permit the user to override the DB2 defaults on Windows NT if necessary. In most situations, the default values should be sufficient. The memory segments, default sizes, and override options are: 1) Database Kernel: default size is 16777216 (16 MB); override option is DBMS:<number of bytes>. 2) Parallel FCM Buffers: default size is 22020096 (21 MB); override option is FCM:<number of bytes>. 3) Database Admin GUI: default size is 33554432 (32 MB); override option is DBAT:<number of bytes>. 4) Fenced Stored Procedures: default size is 16777216 (16 MB); override option is APLD:<number of bytes>. More than one segment may be overridden by separating the override options with a semi-colon (;). For example, to limit the database kernel to approximately 256K, and the FCM buffers to approximately 64 MB, use:
db2set DB2NTMEMSIZE=
DBMS:256000;FCM:64000000
DB2NTNOCACHE Windows NT Default=OFF

Value: ON or OFF

Specifies whether or not DB2 will open database files with a NOCACHE option. If db2ntnocache=ON, file system caching is eliminated. If db2ntnocache=OFF, the operating system caches DB2 files. This applies to all data except for files that contain LONG FIELDS or LOBS. Eliminating system caching allows more memory to be available to the database so that the buffer pool or sortheap can be increased.
DB2NTPRICLASS Windows NT Default=null

Value: 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: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 Windows NT Default=1,1 Used to modify the minimum and maximum working set size available to DB2. By default, when Windows NT is not in a paging situation, a process's working set 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 for DB2 using the syntax db2ntworkset=min,max, where min and max are expressed in megabytes.

DB2_OVERRIDE_BPF All Default=not set

Values: a positive numeric number of pages

Specifies the size of the buffer pool, in pages, to be created at database activation, or first connection, time. It is useful when failures occur during database activation or first connection resulting from memory constraints. Should even a minimal buffer pool of 16 pages not be brought up by the database manager, then the user can try again after specifying a smaller number of pages using this environment variable. The memory constraint could arise either because of a real memory shortage (which is rare); or, because of the attempt by the database manager to allocate large, inaccurately configured buffer pools. This value, if set, will override the current buffer pool size.
DB2PRIORITIES All Values setting is platform dependent. Controls the priorities of DB2 processes and threads.
DB2_RR_TO_RS All Default=NO

Values: YES or NO

When set to YES, the RR isolation level is, effectively, downgraded to RS for user tables. RR semantics are no longer provided in the database manager instance. If your applications do not require RR semantics, this registry variable can be used to reduce the next-key lock contention problems that can sometimes occur under RR.
DB2_SORT_AFTER_TQ All Default=NO

Values: YES or NO

Specifies how the optimizer works with directed table queues in a partitioned database 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.

Data Links Manager
DLFM_BACKUP_DIR_NAME AIX, Windows NT Default: null

Values: ADSM or any valid path

Specifies the backup device to use.
DLFM_ENABLE_STPROC AIX, Windows NT Default: NO

Values: YES or NO

Specifies whether a stored procedure is used to link groups of files.
DLFM_GC_MODE AIX, Windows NT Default: PASSIVE

Values: SLEEP, PASSIVE, or ACTIVE

Specifies the control of garbage file collection on the Data Links server. When set to SLEEP, no garbage collection occurs. When set to PASSIVE, garbage collection runs only if no other transactions are running. When set to ACTIVE, garbage collection runs even if other transactions are running.
DLFM_INSTALL_PATH AIX, Windows NT Default

On AIX: /usr/lpp/ db2_06_00 /adm

On NT: DB2PATH /bin

Range: any valid path

Specifies the path where the data links executables are installed.
DLFM_LOG_LEVEL AIX, Windows NT Default: LOG_INFO

Values: LOG_CRIT, LOG_DEBUG, LOG_ERR, LOG_INFO, LOG_NOTICE, LOG_WARNING

Specifies the level of diagnostic information to be recorded.
DLFM_PORT All except Windows 3.n Default: 50100

Values: any valid port number

Specifies the port number used to communicate with the Data Links servers running the DB2 Data Links Manager. This environment variable is only used when a table contains a "DATALINKS" column.
Miscellaneous
DB2ADMINSERVER OS/2, Windows 95, Windows NT, and UNIX Default=null Specifies which DB2 instance is set up as the DB2 Administration Server.
DB2CLIINIPATH All Default=null Used to override the default path of the DB2 CLI/ODBC configuration file (db2cli.ini) and specify a different location on the client. The value specified must be a valid path on the client system.

DB2DEFPREP All Default=NO

Values: ALL, YES, or NO

Simulates the runtime behavior of the DEFERRED_PREPARE precompile option for applications that were precompiled prior to this option becoming available. For example, if a DB2 v2.1.1 or earlier application were run in a DB2 v2.1.2 or later environment, db2defprep could be used to indicate the desired 'deferred prepare' behavior.
DB2_DJ_COMM All Default=null

Values include: libdrda.a, libsqlnet.a, libnet8.a, libdrda.dll, libsqlnet.dll, libnet8.dll, and so on.

Specifies the wrapper libraries that are loaded when the database manager is started. Specifying this variable reduces the run-time cost of loading frequently used wrappers. Other values for other operating systems are supported (the .dll extension is for the Windows NT operating system; the .a extension is for the AIX operating system). Library names vary by protocol and operating system. This variable is not available unless the database manager parameter federated is set to YES.
DB2DMNBCKCTLR Windows NT Default=null

Values: ? or a domain name

If you know the name of the domain for which DB2 server is the backup domain controller, set db2dmnbckctlr=DOMAIN_NAME. The DOMAIN_NAME must be in upper case. To have DB2 determine the domain for which the local machine is a backup domain controller, set db2dmnbckctlr=?. If the db2dmnbckctlr profile variable is not set or is set to blank, DB2 performs authentication at the primary domain controller.
Note:DB2 does not use an existing backup domain controller by default because a backup domain controller can get out of synchronization with the primary domain controller, causing a security exposure. Getting out of synchronization can occur when the primary domain controller's security database is updated but the changes are not propagated to a backup domain controller. This could occur if there are network latencies or if the computer browser service is not operational.
DB2_FALLBACK Windows NT Default=OFF

Values: ON or OFF

This variable allows you to force all database connections off during the fallback processing. It is used in conjunction with the failover support in the Windows NT environment with Microsoft Cluster Server (MSCS). If DB2_FALLBACK is not set or is set to OFF, and a database connection exists during the fall back, the DB2 resource cannot be brought offline. This will mean the fallback processing will fail.
DB2_FORCE_TRUNCATION All Default=NO

Values: YES or NO

Used during restart recovery. If set to "NO", it will halt restart recovery if it is determined that a bad page is stopping the restart recovery too soon (that is, all active logs have not been read). This is usually caused by a bad page in one of the logs. The user can set this variable to "YES" to signal restart recovery that it should continue processing as if the end of logs was reached. After setting the variable to "YES", logs not read during restart recovery are overwritten when the database becomes active again. The default is "NO", which is not to proceed if a bad page is not found. Use this variable only under the direction from IBM Service personnel.
DB2_GRP_LOOKUP Windows NT Default=null

Values: LOCAL, DOMAIN

This variable is used to tell DB2 where to validate user accounts and perform group member lookup. Set the variable to LOCAL to force DB2 to always enumerate groups and validate user accounts on the DB2 server. Set the variable to DOMAIN to force DB2 to always enumerate groups and validate user accounts on the user account's Windows NT domain.
DB2LDAP_SEARCH_SCOPE All Default= DOMAIN

Values: LOCAL, DOMAIN, GLOBAL

Specifies the search scope for information found in partitions or domains in the Lightweight Directory Access Protocol (LDAP). "LOCAL" disables searching in the LDAP directory. "DOMAIN" only searches in LDAP for the current directory partition. "GLOBAL" searches in LDAP in all directory partitions until the object is found.
DB2LOADREC All Default=null Used to override the location of the load copy during roll forward. If the user has changed the physical location of the load copy, db2loadrec must be set before issuing the roll forward.
DB2LOCK_TO_RB All Default=null

Values: Statement

Specifies whether lock timeouts cause the entire transaction to be rolled-back, or only the current statement. If db2lock_to_rb is set to STATEMENT, locked timeouts cause only the current statement is rolled back. Any other setting results in transaction rollback.
DB2NOEXITLIST All Default=OFF

Values: ON or OFF

If defined, this variable indicates to DB2 not to install an exit list handler in applications and not to perform a COMMIT. Normally, DB2 installs a process exit list handler in applications and the exit list handler performs a COMMIT operation if the application ends normally.

For applications that dynamically load the DB2 library and unload it before the application terminates, the invocation of the exit list handler fails because the handler routine is no longer loaded in the application. If your application operates in this way, you should set the DB2NOEXITLIST variable and ensure your application explicitly invokes all required COMMITs.

DB2NTREMOTEPREG Windows 95 and Windows NT Default=null

Value: Any valid Windows 95 or Windows NT machine name

Specifies the remote machine name that contains the Win32 registry list of DB2 instance profiles and DB2 instances. The value for db2remotepreg should only be set once after DB2 is installed, and should not be modified. Use this variable with extreme caution.
DB2ROUTINE_DEBUG AIX and Windows NT Default=OFF

Values: ON, OFF

Specifies whether to enable the debug capability for Java stored procedures. If you are not debugging Java stored procedures, use the default, OFF. There is a performance impact to enable debugging. Refer to Application Development Guide for more information about debugging Java stored procdures.
DB2SORCVBUF Windows 95 and Windows NT Default=32767 Specifies the value of TCP/IP receive buffers on Windows 95 and Windows NT operating systems.
DB2SORT All, server only Default=null Specifies the location of a library to be loaded at runtime by the LOAD utility. The library contains the entry point for functions used in sorting indexing data. Use db2sort to exploit vendor-supplied sorting products for use with the LOAD utility in generating table indexes. The path supplied must be relative to the database server.
DB2SYSTEM Windows NT, Windows 95, OS/2, and UNIX Default=null Specifies the name that is used by your users and database administrators to identify the DB2 server system. If possible, this name should be unique within your network.

This name is displayed in the system level of the Control Center's object tree to aid administrators in the identification of server systems that can be administered from the Control Center.

When using the 'Search the Network' function of the Client Configuration Assistant, DB2 discovery returns this name and it is displayed at the system level in the resulting object tree. This name aids users in identifying the system that contains the database they wish to access. A value for db2system is set at installation time as follows:

On Windows NT, or Windows 95, the setup program sets it equal to the computer name specified for the Windows system.
On OS/2, the user is prompted to enter the DB2SYSTEM name during the installation process.
On UNIX systems, it is set equal to the UNIX system's TCP/IP hostname.
DB2UPMPR OS/2 Default=ON

Values: ON or OFF

Specifies whether or not the UPM logon screen will display on the screen when the user enters the wrong user ID or password on OS/2.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]