Administration Guide

Appendix D. 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: Planning for more information on changing and using registry variables.

Table 62. General Registry Variables

Variable Name Operating System Values
Description
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: Planning 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.
DB2_DISABLE_FLUSH_LOG All Default= OFF
Value: ON or OFF
Specifies if you wish to disable the inclusion of the last active log file in any on-line backups.
When an on-line backup completes, the last active log file is truncated, closed, and archived as part of the backup. This provides you with a complete back up including all of the logs required for the restoring of that backup.
You may wish to disable the inclusion of the last active log file if you are concerned that you are wasting portions of the Log Sequence Number (LSN) space. Each time an active log file is truncated, the LSN is incremented by an amount proportional to the space truncated. If you perform a very large number of on-line backups each day, you may wish to disable the inclusion of the last active log file.
You may also wish to disable the inclusion of the last active log file if you find you are receiving log full messages a short time after the completion of the on-line backup. When a log file is truncated, the reserved active log space is incremented by the amount proportional to the size of the truncated log. The active log space is freed once the truncated log file is reclaimed. The reclamation occurs a short time after the log file becomes inactive. It is during the short interval in-between that you may receive log full messages.
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.
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.

Variable Name	Operating System	Values
Description
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: Planning 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.
DB2_DISABLE_FLUSH_LOG	All	Default= OFF Value: ON or OFF
Specifies if you wish to disable the inclusion of the last active log file in any on-line backups. When an on-line backup completes, the last active log file is truncated, closed, and archived as part of the backup. This provides you with a complete back up including all of the logs required for the restoring of that backup. You may wish to disable the inclusion of the last active log file if you are concerned that you are wasting portions of the Log Sequence Number (LSN) space. Each time an active log file is truncated, the LSN is incremented by an amount proportional to the space truncated. If you perform a very large number of on-line backups each day, you may wish to disable the inclusion of the last active log file. You may also wish to disable the inclusion of the last active log file if you find you are receiving log full messages a short time after the completion of the on-line backup. When a log file is truncated, the reserved active log space is incremented by the amount proportional to the size of the truncated log. The active log space is freed once the truncated log file is reclaimed. The reclamation occurs a short time after the log file becomes inactive. It is during the short interval in-between that you may receive log full messages.
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.
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.

Table 63. System Environment Variables

Variable Name Operating System Values
Description
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.
DB2DOMAINLIST Windows NT server only Default=null
Values: A list of Windows NT domain names separated by commas (",").
This variable is effective only when CLIENT authentication is set in the Database Manager configuration; and is needed if a single signon from Windows NT desktop is required in a Windows NT domain environment. The list defines the domains which the userID will be authenticated against.
Note: When this variable is set together with CLIENT authentication specified, only connection requests from DB2 Universal Database for Windows NT clients Version 7 (or later) are allowed.

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 Default: null
Values: 1 to 999
Used to specify the target logical node of a DB2 Extended Enterprise Edition database partition server that you want to attach to or 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.
DB2_PARALLEL_IO All Default: null
Values: * (meaning every table space) or a comma-separated list of more than one defined table space
While reading or writing data from and to table space containers, DB2 may use parallel I/O if the number of containers in the database is greater than one. To force parallel I/O for a single container, use this registry variable. After setting the registry variable, issue a DB2STOP and then enter DB2START to allow the changes to take effect.
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.
DB2_STRIPED_CONTAINERS All Default: null
Values: ON, null
When using RAID devices for table space containers, it is suggested that the table space be created with an extent size that is equal to, or a multiple of, the RAID stripe size. However, because of the one-page container tag, the extents will not line up with the RAID stripes, and it may be necessary during an I/O request to access more physical disks than would be optimal.
When using DMS table space containers this problem is avoided by allocating the tag its own (full) extent. This avoids the problem but does require an extra extent of overhead within the container.
After setting this registry variable, issue a DB2STOP and then enter DB2START to allow the changes to take effect.

Table 64. Communications Variables

Variable Name Operating System Values
Description
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 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.

DB2TCPCONNMGRS All Default=1 on serial machines; square root of the number of processors rounded up to a maximum of eight connection managers on symmetric multiprocessor machines.
Values: 1 to 8
The default number of connection managers is created if the registry variable is not set. If the registry variable is set, the value assigned here overrides the default value. The number of TCP/IP connection managers specifed up to a maximum of 8 is created. If less than one is specified then DB2TCPCONNMGRS is set to a value of one and a warning is logged that the value is out of range. If greater than eight is specified then DB2TCPCONNMGRS is set to a value of eight and a warning is logged that the value is out of range. Values between one and eight are used as given. When there is greater than one connection manager created, connection throughput should improve when multiple client connections are received simultaneously. There may be additional TCP/IP connection manager process (on UNIX) or threads (on OS/2 and Windows operating systems) if the user is running on a SMP machine, or has modified the DB2TCPCONNMGRS registry variable. Additional processes or threads require additional storage.
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.

Table 65. DCE Directory Variables

Variable Name Operating System Values
Description
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.

Table 66. Command Line Variables

Variable Name Operating System Values
Description
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.
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.

Variable Name	Operating System	Values
Description
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.
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.

Table 67. MPP Configuration Variables

Variable Name Operating System Values
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. In order to modify this global registry variable, you must be at the root directory and on the DAS instance.
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.
DB2_NUM_FAILOVER_NODES DB2 UDB on AIX, Solaris, and Windows NT Default: 2
Values: 0 to the number of logical nodes
Specifies the number of nodes that can be used as failover nodes in a high availability environment. With high availability, if a node fails, then the node can be restarted as a second logical node on a different host. The number used with this variable determines how much memory is reserved for FCM resources for failover nodes.
For example, host A has two logical nodes: 1 and 2; and host B has two logical nodes: 3 and 4. Assume DB2_NUM_FAILOVER_NODES is set to 2. During DB2START, both host A and host B will reserve enough memory for FCM so that up to four logical nodes could be managed. Then if one host fails, the logical nodes for the failing host could be restarted on the other host.
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.

Variable Name	Operating System	Values
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. In order to modify this global registry variable, you must be at the root directory and on the DAS instance.
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.
DB2_NUM_FAILOVER_NODES	DB2 UDB on AIX, Solaris, and Windows NT	Default: 2 Values: 0 to the number of logical nodes
Specifies the number of nodes that can be used as failover nodes in a high availability environment. With high availability, if a node fails, then the node can be restarted as a second logical node on a different host. The number used with this variable determines how much memory is reserved for FCM resources for failover nodes. For example, host A has two logical nodes: 1 and 2; and host B has two logical nodes: 3 and 4. Assume DB2_NUM_FAILOVER_NODES is set to 2. During DB2START, both host A and host B will reserve enough memory for FCM so that up to four logical nodes could be managed. Then if one host fails, the logical nodes for the failing host could be restarted on the other host.
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.

Table 68. SQL Compiler Variables

Variable Name Operating System Values
Description
DB2_ANTIJOIN All Default=NO
Values: YES or NO
This registry variable is applicable to DB2 Universal Database EEE environments. If specifies whether the optimizer will search for opportunities to transform "NOT EXISTS" subqueries into anti-joins which can be processed more efficiently by DB2.
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 between 0 and 6.001
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'))

Variable Name	Operating System	Values
Description
DB2_ANTIJOIN	All	Default=NO Values: YES or NO
This registry variable is applicable to DB2 Universal Database EEE environments. If specifies whether the optimizer will search for opportunities to transform "NOT EXISTS" subqueries into anti-joins which can be processed more efficiently by DB2.
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 between 0 and 6.001
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'))

Table 69. Performance Variables

Variable Name Operating System Values
Description
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=YES
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.

DB2_EXTENDED_OPTIMIZATION All Default=OFF
Values: ON or OFF
Specifies whether or not the query optimizer uses optimization extensions to improve query performance. The extensions may not improve query performance in all environments. Testing should be done to determine individual query performance improvements.
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 kept 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 the value given with DB2MEMMAXFREE. If DB2MEMMAXFREE is null, then all of the memory is disclaimed once freed. If DB2MEMMAXFREE is given a value, then only some of the memory is disclaimed once freed (up to the value given in DB2MEMMAXFREE). This ensures that the memory is made readily available for other processes as soon as it is freed. See also DB2MEMMAXFREE. These two registry variables work together.
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. These two registry variables work together.
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.

Table 70. Data Links Variables

Variable Name Operating System Values
Description
DLFM_BACKUP_DIR_NAME AIX, Windows NT Default: null
Values: TSM or any valid path
Specifies the backup device to use. If you change the setting of this registry variable between TSM and a path at run-time, the archived files are not moved. Only new backups are place in the new location. Previously archived files are not moved.
DLFM_BACKUP_LOCAL_MP AIX, Windows NT Default: null
Values: any valid path to the local mount point in the DFS system
Specifies the fully qualified path to a mount point in the DFS system. When a path is given, it is used instead of the path given with DLFM_BACKUP_DIR_NAME.
DLFM_BACKUP_TARGET AIX, Windows NT Default: null
Values: LOCAL, TSM, XBSA
Specifies the type of backup used.
DLFM_BACKUP_TARGET_LIBRARY AIX, Windows NT Default: null
Values: any valid path to the DLL or shared library name
Specifies the fully qualified path to the DLL or shared library. This library is loaded using the libdfmxbsa.a library.
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_FS_ENVIRONMENT AIX, Windows NT Default: NATIVE
Values: NATIVE or DFS
Specifies the environment in which Data Links servers operate. NATIVE indicates that the Data Links server is in a single machine situation where the server can take over files on its own machine. DFS indicates that the Data Links server is in a distributed filesystem (DFS) environment where the server can take over files throughout the filesystem. Mixing DFS filesets and native filesystems is not allowed.
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.

Variable Name	Operating System	Values
Description
DLFM_BACKUP_DIR_NAME	AIX, Windows NT	Default: null Values: TSM or any valid path
Specifies the backup device to use. If you change the setting of this registry variable between TSM and a path at run-time, the archived files are not moved. Only new backups are place in the new location. Previously archived files are not moved.
DLFM_BACKUP_LOCAL_MP	AIX, Windows NT	Default: null Values: any valid path to the local mount point in the DFS system
Specifies the fully qualified path to a mount point in the DFS system. When a path is given, it is used instead of the path given with DLFM_BACKUP_DIR_NAME.
DLFM_BACKUP_TARGET	AIX, Windows NT	Default: null Values: LOCAL, TSM, XBSA
Specifies the type of backup used.
DLFM_BACKUP_TARGET_LIBRARY	AIX, Windows NT	Default: null Values: any valid path to the DLL or shared library name
Specifies the fully qualified path to the DLL or shared library. This library is loaded using the libdfmxbsa.a library.
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_FS_ENVIRONMENT	AIX, Windows NT	Default: NATIVE Values: NATIVE or DFS
Specifies the environment in which Data Links servers operate. NATIVE indicates that the Data Links server is in a single machine situation where the server can take over files on its own machine. DFS indicates that the Data Links server is in a distributed filesystem (DFS) environment where the server can take over files throughout the filesystem. Mixing DFS filesets and native filesystems is not allowed.
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.

Table 71. Miscellaneous Variables

Variable Name Operating System Values
Description
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_ENABLE_LDAP All Default=NO
Values: YES or NO
Specifies whether or not the Lightweight Directory Access Protocol (LDAP) is used. LDAP is an access method to directory services.
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 Windows NT domain to which the user account belongs.
DB2LDAP_BASEDN All Default=null
Values: Any valid base domain name.
Specifies the base domain name for the LDAP directory.
DB2LDAPCACHE All Default=YES
Values: YES or NO
Specifies that the LDAP cache is to be enabled. This cache is used to catalog the database, node, and DCS directories on the local machine.
To ensure that you have the latest entries in the cache, do the following:
REFRESH LDAP DB DIR REFRESH LDAP NODE DIR

These commands update and remove incorrect entries from the database directory and the node directory.
DB2LDAP_CLIENT_PROVIDER Windows 95/98/NT/2000 only Default=null (Microsoft, if available, is used; otherwise IBM is used.)
Values: IBM or Microsoft
When running in a Windows environment, DB2 supports using either Microsoft LDAP clients or IBM LDAP clients to access the LDAP directory. This registry variable is used to explicitly select the LDAP client to be used by DB2.
Note: To display the current value of this registry variable, use the db2set command:
db2set DB2LDAP_CLIENT_PROVIDER

DB2LDAPHOST All Default=null
Values: Any valid hostname.
Specifies the hostname of the location for the LDAP directory.
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.
DB2REMOTEPREG 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 ]