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.
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.
|
