You can use DB2® variables
to control and maintain your database. When you set variables you
can control areas such as the default administration server, the default
client path, and the ability to commit changes that are made to data
when you exit an application.
- DB2ADMINSERVER
-
- Operating system: Windows and UNIX
- Default: NULL
- Specifies the DB2 Administration
Server.
- DB2_ATS_ENABLE
-
- Operating system: All
- Default: NULL, Values: YES/TRUE/ON/1 or NO/FALSE/OFF/0
- This variable controls whether the administrative task scheduler
is running. The administrative task scheduler is disabled by default.
When the scheduler is disabled, you can use the built-in procedures
and views to define and modify tasks but the scheduler will not execute
the tasks.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2AUTH
-
- Operating system: All
- Default: Not set. Values: DISABLE_CHGPASS, OSAUTHDB, SQLADM_NO_RUNSTATS_REORG, TRUSTEDCLIENT_DATAENC, TRUSTEDCLIENT_SRVRENC, JCC_ENFORCE_SECMEC, DISABLE_SYSMON_CONNECT
- This variable allows you to tune the behavior of user authentication.
Valid values are as follows:
- ALLOW_LOCAL_FALLBACK:
This value allows the DB2 server
to fall back to using SERVER authentication for local implicit connects
or attaches when the server is configured to use Generic Security
Service (GSS) plugins. When ALLOW_LOCAL_FALLBACK is
enabled, for local implicit connects, the userid and password plugin
specified by the srvcon_pw_plugindatabase manager
configuration parameter is used for authenticating the user, instead
of using the specified GSS authentication, such as KERBEROS, KRB_SERVER_ENCRYPT, GSSPLUGIN,
or GSS_SERVER_ENCRYPT.
A local
implicit connect is created when you issue a connect to a local database
(note that
local means only IPC, not TCP/IP) without
providing a userid or password. DB2 uses
the userid of the current session or process for the userid of the
connect. The password plugins provided by DB2 assume that a userid retrieved from the operating
system has already been authenticated by the operating system and,
therefore, password validation is not necessary.
Note: If you provide
a userid and password, it is not considered a local implicit connect
and the ALLOW_LOCAL_FALLBACK option does not apply.
The
password plugin DB2 uses is
determined by the srvcon_pw_plugin database
manager configuration parameter. If the srvcon_pw_plugin parameter
is set to IBMLDAPauthserver, the IBMLDAPauthserver
plugin processes the local implicit connect. If the srvcon_pw_plugin parameter
is set to a custom security plugin, the custom plugin processes the
local implicit connect. If the srvcon_pw_plugin parameter
is not set, the default plugin (IBMOSauthserver) processes the local
implicit connect. The security plugins provided by your DB2 database product always allow a local implicit
connect because they assume that the user has been validated by the
OS.
- DISABLE_CHGPASS: This value disables the ability
to change the password from the client.
- OSAUTHDB: This value instructs the DB2 database manager to use the authentication
and group setting for a user on the AIX® operating
system. Transparent LDAP support has also been extended to the Linux, HP-UX and Solaris operating
systems. The LDAP server can be any one of the following:
- IBM® Tivoli® Directory Server (ITDS)
- Microsoft Active Directory
(MSAD)
- Sun One Directory Server
- SQLADM_NO_RUNSTATS_REORG:
This value, introduced in DB2 Version
9.7 Fix Pack 5, disables the ability of users with SQLADM authority
to perform a reorg or runstats operation.
- TRUSTEDCLIENT_DATAENC: This
value forces untrusted clients to use DATA_ENCRYPT.
This value is not applicable to a DB2 Connect™ gateway.
- TRUSTEDCLIENT_SRVRENC: This
value forces untrusted clients to use SERVER_ENCRYPT.
This value is not applicable to a DB2
Connect gateway.
- JCC_ENFORCE_SECMEC:
This value, introduced in Fix Pack 4, forces the DB2 server to not accept CLEAR_TEXT_PASSWORD_SECURITY
security mechanisms when the authentication value is set to SERVER_ENCRYPT.
- DISABLE_SYSMON_CONNECT:
This value removes the implicit privilege to connect to the database
from SYSMON.
- You
can set multiple values for DB2AUTH using the db2set command.
You must separate each value with a comma. For example, if you want DB2AUTH to
have both DISABLE_CHGPASS and OSAUTHDB enabled,
use the command:
db2set DB2AUTH=DISABLE_CHGPASS,OSAUTHDB
Note: You
cannot enable TRUSTEDCLIENT_SRVRENC and TRUSTEDCLIENT_DATAENC at
the same time.
- DB2_BCKP_INCLUDE_LOGS_WARNING
-
- DB2_BCKP_PAGE_VERIFICATION
-
- Operating system: All
- Default: FALSE, Values: FALSE, TRUE
- Specifies whether DMS and AS page validation occurs during a backup.
- DB2CLIINIPATH
-
- Operating system: All
- Default: NULL
- Used to override the default path of the 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.
- DB2_COMMIT_ON_EXIT
-
- Operating system: UNIX
- Default: OFF, Values: OFF/NO/0 or ON/YES/1
- On UNIX operating systems,
prior to DB2 UDB Version 8, DB2 committed any remaining in-flight
transactions on successful application exit. In DB2 UDB Version 8, the behavior was changed so
that in-flight transactions were rolled back on exit. This registry
variable allows users with embedded SQL applications which depend
on the earlier behavior to continue to enable it in DB2 Version 9. This registry variable does not
affect JDBC, CLI,
and ODBC applications.
Note that this registry variable is deprecated,
and the commit-on-exit behavior will no longer be supported in future
release. Users should determine whether any of their applications
developed prior to DB2 Version
9 continue to depend on this functionality, and add the appropriate
explicit COMMIT or ROLLBACK statements to the application as required.
If the registry variable is turned on, care should be taken not to
implement new applications which fail to explicitly COMMIT before
exit.
Most users should leave this registry variable at the
default setting.
- DB2_COMMON_APP_DATA_PATH
-
- DB2_COMPATIBILITY_VECTOR
-
- Operating system: All
- Default: NULL, Values: NULL or 00 to FFF
- The DB2_COMPATIBILITY_VECTOR registry variable
is used to enable one or more DB2 compatibility
features introduced since DB2 Version 9.5.
These features ease the task of migrating applications written for
other relational database vendors to DB2 Version 9.5 or
later.
- DB2_COMPATIBILITY_VECTOR is represented
as a hexadecimal value, and each bit in the variable enables one of
the DB2 compatibility features
as outlined in the DB2_COMPATIBILITY_VECTOR values table. To enable
all of the supported compatibility features, set the registry variable
to the value ORA (which is equivalent to the hexadecimal value FFF).
This is the recommended setting.
- DB2CONNECT_DISCONNECT_ON_INTERRUPT
-
- Operating system: All
- Default: NO, Values: YES/TRUE/1 or NO/FALSE/0
- When set to YES (TRUE or 1),
this variable specifies that the connection to a Version 8 (or higher) DB2 Universal Database™ z/OS® server should be broken immediately
when an interrupt occurs. You can use this variable in the following
configurations:
- If you are running a DB2 client
with a Version 8 (or higher) DB2 UDB z/OS server, set DB2CONNECT_DISCONNECT_ON_INTERRUPT to YES on
the client.
- If you are running a DB2 client
through a DB2 Connect gateway
to a Version 8 (or higher) DB2 UDB z/OS server, set DB2CONNECT_DISCONNECT_ON_INTERRUPT to YES on
the gateway.
- DB2_CREATE_DB_ON_PATH
-
- Operating system: Windows
- Default: NULL, Values: YES or NO
- Set this registry variable to YES to enable support
for the use of a path (as well as a drive) as a database path. The
setting of DB2_CREATE_DB_ON_PATH is checked when
a database is created, when the database manager configuration parameter dftdbpath is
set, and when a database is restored. The fully qualified database
path can be up to 215 characters in length.
If DB2_CREATE_DB_ON_PATH is
not set (or is set to NO) and you specify a path
for the database path when creating or restoring a database, error SQL1052N is
returned.
If DB2_CREATE_DB_ON_PATH is not
set (or is set to NO) and you update the dftdbpath database
manager configuration parameter, error SQL5136N is
returned.
CAUTION:
If path support is used to create
new databases, applications written prior to DB2 Version 9.1 using the
db2DbDirGetNextEntry() API
or an older version of it, might not work correctly. Please refer
to
http://www.ibm.com/software/data/db2/support/db2_9/ for
details on various scenarios and the proper course of action.
- DB2_DDL_SOFT_INVAL
-
- Operating system: All
- Default: ON, Values: ON or OFF
- Enables soft invalidation of applicable database objects when
they are dropped or altered.
When
DB2_DDL_SOFT_INVAL is
set to
ON, any DDL operation, such as drop, alter,
or detach, can start without waiting for transactions referencing
the same objects to finish. Current executions dependant on the objects
will continue with the original object definition, while new executions
will utilize the changed object. This allows for better concurrency
when issuing DDL statements.
Note: The new soft invalidation capabilities
only apply to dynamic packages. Any objects with static packages will
still require a hard invalidation.
- DB2_DISABLE_FLUSH_LOG
-
- Operating system: All
- Default: OFF, Values: ON or OFF
- Specifies whether to disable closing the active log file when
the online backup is completed.
When
an online backup completes, the last active log file is truncated,
closed, and made available to be archived. This ensures that your
online backup has a complete set of archived logs available for recovery.
You might want to disable closing the last active log file if you
are concerned that you are wasting log space.
You might also
want to disable closing the last active log file if you find you are
receiving log full messages a short time after the completion of the
online 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. During the short interval between these two
events, you may receive log full messages.
During any backup
which includes logs, this registry variable is ignored, since the
active log file must be truncated and closed in order for the backup
to include the logs.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2_DISPATCHER_PEEKTIMEOUT
-
- Operating system: All
- Default: 1, Values: 0 to 32767 seconds; 0 denotes
that timeout is immediate
- DB2_DISPATCHER_PEEKTIMEOUT allows you to
adjust the time, in seconds, that a dispatcher waits for a client's
connection request before handing the client off to an agent. In most
cases, you should not need to adjust this registry variable. This
registry variable only affects instances that have DB2 Connect connection concentrator enabled.
This
registry variable and the DB2_SERVER_CONTIMEOUT registry
variable both configure the handling of a new client during connect
time. If there are many slow clients connecting to an instance, the
dispatcher may be held up for up to 1 second to timeout each client,
causing the dispatcher to become a bottle neck, if many clients are
connecting simultaneously. If an instance with multiple active databases
is experiencing very slow connection times, DB2_DISPATCHER_PEEKTIMEOUT may
be lowered to 0. Lowering DB2_DISPATCHER_PEEKTIMEOUT causes
the dispatcher to only look into the client's connect request if it
is already there; the dispatcher will not wait for the connect request
to arrive. If an invalid value is set, the default value is used.
This registry variable is not dynamic.
- DB2_DJ_INI
-
- DB2_DMU_DEFAULT
-
- Operating system: All
- Default: NULL, Values: : IMPLICITLYHIDDENMISSING, IMPLICITLYHIDDENINCLUDE
- This variable allows you to set the default behavior of whether
implicitly hidden columns are included when the column list is omitted
by the load, import, ingest, and export utilities. Valid values are
as follows:
- NULL
- It means that no default behavior is specified. If the table has
implicitly hidden columns, the column list must be explicitly specified
or the hidden column options must be specified by the utilities. Otherwise
an error occurs.
- IMPLICITLYHIDDENMISSING
- The utilities assume that the implicitly hidden columns are not
included by default unless the column list or the hidden column options
are specified.
- IMPLICITLYHIDDENINCLUDE
- The utilities assume that the implicitly hidden columns are included
by default, when neither the column list nor the hidden column options
are specified.
Consider the following examples of how the setting
for
DB2_DMU_DEFAULT affects the result of a load
operation:
- DB2_DOCHOST
-
- Operating system: All
- Default: Not set (but DB2 will
still try to access the Information Center from the IBM website), Values: http://hostname where hostname=
valid host name or IP address
- Specifies the host name on which the DB2 Information Center is
installed. This variable can be automatically set during the installation
of the DB2 Information
Center if the automatic configuration option is selected in
the DB2 Setup wizard.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2_DOCPORT
-
- Operating system: All
- Default: NULL, Values: any valid port number
- Specifies the port number through which the DB2 help system serves the DB2 documentation. This variable can be automatically
set during the installation of the DB2 Information Center if
the automatic configuration option is selected in the DB2 Setup wizard.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2DSDRIVER_CFG_PATH
-
- DB2DSDRIVER_CLIENT_HOSTNAME
-
- Operating system: All
- Default: NULL
- Used to override the default client host name of the (db2dsdriver.cfg)
configuration file. This variable forces CLI to
pick the client host name entry from the automatic client reroute
section of db2dsdriver.cfg file.
- DB2_ENABLE_AUTOCONFIG_DEFAULT
-
- Operating system: All
- Default: NULL, Values: YES or NO
- This variable controls whether the Configuration Advisor is run
automatically at database creation. If DB2_ENABLE_AUTOCONFIG_DEFAULT is
not set (null), the effect is the same as if the variable was set
to YES and the Configuration Advisor is run at database
creation. You do not need to restart the instance after you set this
variable. If you execute the AUTOCONFIGURE command
or run CREATE DB AUTOCONFIGURE, these commands
override the setting of DB2_ENABLE_AUTOCONFIG_DEFAULT.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2_ENABLE_LDAP
-
- Operating system: 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_EVMON_EVENT_LIST_SIZE
-
Note: If activity records cannot be allocated from the monitor
heap, they will be dropped. To prevent this from happening, set the mon_heap_sz configuration
parameter to AUTOMATIC. If you have mon_heap_sz set
to a specific value, ensure that DB2_EVMON_EVENT_LIST_SIZE is
set to a smaller value. These actions, however, cannot guarantee that
activity records will not be dropped, as the monitor heap is also
used for tracking other monitor elements.
- DB2_EVMON_STMT_FILTER
-
Note: Refer to the sqlmon.h header file
for definitions of database system monitor constants.
- DB2_EXTSECURITY
-
- Operating system: Windows
- Default: YES, Values: YES or NO
- Prevents unauthorized access to DB2 by
locking by locking DB2 objects
(system files, directories, and IPC objects). To avoid potential problems,
this registry variable should not be turned off. If DB2_EXTSECURITY is
not set, its value is interpreted as YES on DB2 database sever products and NO on
clients.
- DB2_FALLBACK
-
- Operating system: Windows
- 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 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_FMP_COMM_HEAPSZ
-
- Operating system: Windows, UNIX
- Default:
20 MB, or enough space to run 10 fenced routines (whichever is larger).
- This variable specifies, in 4 KB pages, the size of the pool used
for fenced routine invocations, such as stored procedure or user-defined
function calls. The space used by each fenced routine is twice the
value of the aslheapsz configuration parameter.
On UNIX, the underlying shared memory
allocation is rounded up to a multiple of 256 MB by default, and the
heap is allowed to grow into the pre-allocated shared memory as required.
If the value is set explicitly, the underlying shared memory allocation
is not rounded up, and the FMP heap is limited to the specified size.
Pre-allocated shared memory does not count towards instance memory
usage, but it does contribute towards the virtual memory consumption
(swap on Solaris and HP-UX, configured virtual limits on Linux, Solaris, and HP-UX)
If
you are running a large number of fenced routines on your system,
you might need to increase the value of this variable. If you are
running a very small number of fenced routines, you can reduce it
to conserve virtual memory consumption.
Setting this value
to 0 means that no set is created, and as a result
no fenced routines can be invoked. It also means that the health monitor
and the automatic database maintenance functionality (such as automatic
backups, statistics collection, and REORG) will be disabled since
this functionality relies on the fenced routine infrastructure.
If you are running SAS in-database analytics
(enabled by setting the DB2_SAS_SETTINGS registry
variable), the memory for connections to the SAS embedded process
(EP) is also allocated from the FMP heap. Guidelines for fenced routines
apply when the heap is adjusted to accommodate connections running
queries that include in-database analytics. As a general rule, you
can expect the FMP heap memory requirements to increase by 120 KB.
If, however, you specify the COMM_BUFFER_SZ option
for the DB2_SAS_SETTINGS registry variable, the
FMP heap memory requirements increase by twice the value of the COMM_BUFFER_SZ option
multiplied by the number of concurrent SAS queries that you want to
support.
- DB2_GRP_LOOKUP
-
- Operating system: Windows
- Default: NULL, Values: LOCAL, DOMAIN, TOKEN, TOKENLOCAL, TOKENDOMAIN
- This variable specifies which Windows security
mechanism is used to enumerate the groups to which a user belongs.
- DB2_HADR_BUF_SIZE
-
- Operating system: All
- Default: 2*logbufsz
- This variable specifies the standby log receiving buffer size
in unit of log pages. If not set, DB2 will
use two times the primary logbufsz configuration
parameter value for the standby receiving buffer size. The maximum
size that can be specified is 4 GB. This variable should be set in
the standby instance. It is ignored by the primary database.
If
HADR synchronization mode (the hadr_syncmode database
configuration parameter) is set to ASYNC, during
peer state, a slow standby might cause the send operation on the primary
to stall and therefore block transaction processing on the primary.
A larger than default log-receiving buffer can be configured on a
standby database to allow it to hold more unprocessed log data. This
may allow for brief periods where the primary generates log data faster
than the standby can consume it, without blocking transaction processing
at the primary.
Note: A larger log receiving buffer size can help
absorb peak transaction loads on the primary database, but the buffer
will still fill up if the average replay rate on the standby database
is slower than the log rate on the primary database.
- DB2_HADR_NO_IP_CHECK
-
- DB2_HADR_PEER_WAIT_LIMIT
-
- Operating system: All
- Default: 0 (meaning no limit) Values: 0 to
max unsigned 32 bit integer, inclusive
When the
DB2_HADR_PEER_WAIT_LIMIT registry
variable is set, the HADR primary database will break out of peer
state if logging on the primary database has been blocked for the
specified number of seconds because of log replication to the standby.
When this limit is reached, the primary database will break the connection
to the standby database. If the peer window is disabled, the primary
database will enter disconnected state and logging resumes. If the
peer window is enabled, the primary database will enter disconnected
peer state, in which logging continues to be blocked. The primary
database leaves disconnected peer state upon re-connection or peer
window expiration. Logging resumes once the primary leaves disconnected
peer state.
Note: If
you set DB2_HADR_PEER_WAIT_LIMIT, use a minimum
value of 10 to avoid triggering false alarms.
This
parameter has no effect on a standby database, but it is recommended
that the same value be used on both primary and standby databases.
Invalid values (not a number or negative numbers) will be interpreted
as 0, meaning no limit. This parameter is static.
Database instance needs to be restarted to make this parameter effective.
- DB2_HADR_ROS
-
- Operating system: All
- Default: OFF Values: OFF or ON
- This variable enables the HADR reads on standby feature. When DB2_HADR_ROS is
enabled on the HADR standby database, the standby accepts client connections
and allows read-only queries to run on it. DB2_HADR_ROS is
a static registry variable, so it requires the DB2 instance to be restarted for a changed setting
to take effect.
- DB2_HADR_SORCVBUF
-
- Operating system: All
- Default: Operating system TCP socket receive buffer size, Values: 1024 to 4294967295
- This variable specifies the operating system (OS) TCP socket receive
buffer size for the HADR connection, which allows users to customize
the HADR TCP/IP behavior distinctly from other connections. Some operating
systems will automatically round or silently cap the user specified
value. The actual buffer size used for the HADR connection is logged
in the db2diag log files. Consult your operating
system network tuning guide for the optimal setting for this parameter
based on your network traffic. This variable should be used in conjunction
with DB2_HADR_SOSNDBUF.
- DB2_HADR_SOSNDBUF
-
- Operating system: All
- Default: Operating system TCP socket send buffer size, Values: 1024 to 4294967295
- This variable specifies the operating system (OS) TCP socket send
buffer size for the HADR connection, which allows users to customize
the HADR TCP/IP behavior distinctly from other connections. Some operating
systems will automatically round or silently cap the user specified
value. The actual buffer size used for the HADR connection is logged
in the db2diag log files. Consult your operating
system network tuning guide for the optimal setting for this parameter
based on your network traffic. This variable should be used in conjunction
with DB2_HADR_SORCVBUF.
- DB2_HISTORY_FILTER
-
- Operating system: All
- Default: NULL, Values: NULL, G, L, Q, T, U
This variable specifies operations that are not to modify the
history file. You can use the
DB2_HISTORY_FILTER registry
variable to reduce potential contention on the history file by filtering
out operations. Specify which operations that cannot modify the history
file using a comma-separated list:
db2set DB2_HISTORY_FILTER=T, L
Possible
values for
DB2_HISTORY_FILTER are:
- G: Reorg operations
- L: Load operations
- Q: Quiesce operations
- T: Alter table space operations
- U: Unload operations
- DB2_INDEX_PCTFREE_DEFAULT
-
- Operating system: All
- Default: Not set, Values: 0 to 99
This registry variable specifies what percentage of each index
page to leave as free space when building the index. The setting for DB2_INDEX_PCTFREE_DEFAULT is
overridden if you explicitly specify the PCTFREE clause on the CREATE
INDEX statement. The registry variable does not affect the LEVEL2
PCTFREE clause on the CREATE INDEX statement.
The registry variable
does not apply at database upgrade time, even if the indexes are re-created
during the upgrade. It only applies to a new installation or once
the upgrade is complete. This registry variable is dynamic; you can
set it or unset it without having to stop and start instance.
If DB2_WORKLOAD is
set to SAP, DB2_INDEX_PCTFREE_DEFAULT will
be set to 0.
- DB2LDAP_BASEDN
-
- Operating system: All
- Default: NULL, Values: Any valid base distinguished
domain name.
- When this is set, the LDAP objects for DB2 will be stored in the LDAP directory under
CN=System
CN=IBM
CN=DB2
under the base distinguished name specified. When
this is set for the Microsoft Active
Directory Server, ensure that CN=DB2, CN=IBM, and CN=System are defined
under this distinguished name.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2LDAPCACHE
-
- Operating system: 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 IMMEDIATE ALL
This
command updates and removes incorrect entries from the database directory
and the node directory.
- DB2LDAP_CLIENT_PROVIDER
-
- Operating system: Windows
- 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
-
- Operating system: All
- Default:Null, Values: base_domain_name[:port_number],
or base_domain_name:SSL:636 when
using an SSL enabled LDAP host
- Specifies the host name and optional port number of the location
for the LDAP directory where base_domain_name is
the TCP/IP host name, and [:port_number] is the
port number.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2LDAP_KEEP_CONNECTION
-
- DB2LDAP_SEARCH_SCOPE
-
- Operating system: All
- Default: DOMAIN, Values: LOCAL, DOMAIN,
or GLOBAL
- Specifies the search scope for information found in database 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.
- DB2_LIMIT_FENCED_GROUP
-
- Operating system: Windows
- Default: NULL, Values: ON or OFF
- If you have Extended Security enabled, you can restrict the operating
system's privileges of the fenced mode process (db2fmp)
to the privileges assigned to the DB2USERS group by setting this registry
variable to ON and by adding the DB2 service account (the user name that runs
the DB2 service) to the DB2USERS
group.
Note: If LocalSystem is being used as the DB2 service account, setting DB2_LIMIT_FENCED_GROUP will
have no effect.
You can grant additional operating system
privileges to the db2fmp process by adding the DB2 service account to an operating
system group that holds those additional privileges.
- DB2_LOAD_COPY_NO_OVERRIDE
-
- Operating system: All
- Default: NONRECOVERABLE, Values: COPY
YES or NONRECOVERABLE
- This variable will convert any LOAD COPY
NO to either LOAD COPY YES or NONRECOVERABLE,
depending on the value of the variable. This variable is applicable
to HADR primary databases as well as to standard (non-HADR) databases;
it is ignored on an HADR standby database. On an HADR primary database,
if this variable is not set, LOAD COPY
NO is converted to LOAD NONRECOVERABLE.
The value of this variable either specifies a nonrecoverable load
or the copy destination, using the same syntax as a COPY
YES clause.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2LOADREC
-
- Operating system: 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.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2LOCK_TO_RB
-
- Operating system: 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 to be rolled back. Any other setting results in
transaction rollback.
- DB2_MAP_XML_AS_CLOB_FOR_DLC
-
Note: DB2_MAP_XML_AS_CLOB_FOR_DLC is deprecated
and will be removed in a future release. This variable is no longer
necessary because most existing DB2 applications
that access XML values do so with an XML capable client.
- DB2_MAX_LOB_BLOCK_SIZE
-
- Operating system: All
- Default: 0 (no limit), Values: 0 to 21487483647
- Sets the maximum amount of LOB or XML data to be returned in a
block. This is not a hard maximum; if this maximum is reached on the
server during data retrieval, the server finishes writing out the
current row before generating a reply for the command, such as FETCH,
to the client.
- DB2_MEMORY_PROTECT
-
- DB2_MIN_IDLE_RESOURCES
-
- Operating system: Linux
- Default: OFF, Values: OFF or ON
- This variable specifies that an activated database is to use minimal
processing resources when it is idle. This might be useful in some
virtual Linux environments
(for example, zVM) where the small resource savings can help the host
virtual machine monitor schedule its CPU and memory resources across
all its virtual machines more efficiently.
- DB2_NCHAR_SUPPORT
-
- DB2NOEXITLIST
-
- Operating system: All
- Default: OFF, Values: ON or OFF
- This variable indicates that DB2 should
not load an exit list handler and that it should not perform a commit
when the application exits, regardless of the setting of the DB2_COMMIT_ON_EXIT registry
variable.
When DB2NOEXITLIST is turned off
and DB2_COMMIT_ON_EXIT is turned on, any in-flight
transactions for embedded SQL applications are automatically committed.
It is recommended to explicitly add COMMIT or ROLLBACK statements
when an application exits.
Applications that dynamically load
and unload the DB2 library before
the application terminates might crash when calling the DB2 exit handler. This crash might happen because
the application attempts to call a function that does not exist in
memory. To avoid this situation, set the DB2NOEXITLIST registry
variable.
- DB2_NUM_CKPW_DAEMONS
-
- Operating system: Linux and UNIX
- Default: 3, Values: 1[:FORK] to 100[:FORK]
- You can use the DB2_NUM_CKPW_DAEMONS registry
variable to start a configurable number of check password daemons.
The daemons are created during db2start and handle
check password requests when the default IBMOSauthserver security
plugin is in use. Increasing the setting for DB2_NUM_CKPW_DAEMONS can
decrease the time required to establish a database connection, but
this is only effective in scenarios where many connections are being
made simultaneously and where authentication is expensive.
DB2_NUM_CKPW_DAEMONS can
be set to a value between 1 and 100. The database manager will create
the number of daemons specified by DB2_NUM_CKPW_DAEMONS.
Each daemon can handle check password requests directly.
An
optional FORK parameter can be added to enable the
check password daemons to explicitly spawn an external check password
program (db2ckpw) to handle check password requests.
This is similar to setting DB2_NUM_CKPW_DAEMONS to
zero in previous releases. In FORK mode, each check
password daemon will spawn the check password program for each request
to check a password. The daemons in FORK mode are
started as the instance owner.
If DB2_NUM_CKPW_DAEMONS is
set to zero, the effective value is set to 3:FORK,
where 3 check password daemons are started in FORK mode.
- DB2_OPTSTATS_LOG
-
- Operating system: All
- Default: Not set (see details below), Values: OFF, ON {NUM | SIZE | NAME | DIR}
- DB2_OPTSTATS_LOG specifies the attributes
of the statistics event logging files which are used to monitor and
analyze statistics collection related activities. When DB2_OPTSTATS_LOG is
not set or set to ON, statistics event logging is
enabled, allowing you to monitor system performance and keep a history
for better problem determination. Log records are written to the first
log file until it is full. Subsequent records are written to the next
available log file. If the maximum number of files is reached, the
oldest log file will be overwritten with the new records. If system
resource consumption is of great concern to you, disable this registry
variable by setting it to OFF.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
When statistics event logging is explicitly enabled (set to
ON),
there are a number of options you can modify:
- NUM: the maximum number of rotating log files.
Default: 5, Values 1 - 15
- SIZE:
the maximum size of rotating log files. (The size of each rotating
file is SIZE/NUM.) Default = 15
Mb, Values 1 Mb - 4096 Mb
- NAME: the base name for rotating log files. Default: db2optstats.number.log,
for instance db2optstats.0.log, db2optstats.1.log,
etc.
- DIR: the base directory for rotating log files.
Default: diagpath/events
You can specify a value for as many of these options as you want,
just ensure that
ON is the first value when you want
to enable statistics logging. For instance, to enable statistics logging
with maximum of 6 log files, a maximum file size of 25 Mb, a base
file name of
mystatslog, and the directory
mystats,
issue the following command:
db2set DB2_OPTSTATS_LOG=ON,NUM=6,SIZE=25,NAME=mystatslog,DIR=mystats
- DB2REMOTEPREG
-
- Operating system: Windows
- Default: NULL, Values: Any valid Windows computer name
- Specifies the remote computer name that contains the Win32 registry
list of DB2 instance profiles
and DB2 instances. The value
for DB2REMOTEPREG must be set only once after
the DB2 database product is
installed, and must not be changed after it is set. Use this variable
with extreme caution.
- In a partitioned database environment, you can use the DB2REMOTEPREG registry
variable to configure a computer that is not the instance owner to
use the values of registry variables on the instance-owning computer.
See Setting variables at the instance level in a partitioned database environment for more information about
when to use this variable.
When the DB2 database
manager reads the registry variables on Windows operating systems, it reads the DB2REMOTEPREG value
first. If the DB2REMOTEPREG variable is set,
the database manager opens the registry on the remote computer that
is specified by the DB2REMOTEPREG variable. Subsequent
reading and updating of the registry variables is redirected to the
specified remote computer.
For a computer that is not the instance
owner to access the remote registry, the Remote Registry Service must
be running on the target computer. Also, the user logon account and
all DB2 service logon accounts
must have sufficient access to the remote registry. To use the DB2REMOTEPREG variable,
you must operate in a Windows domain
environment so that you can grant the required registry access to
the domain account.
- Do not use DB2REMOTEPREG in a Microsoft Cluster Server environment.
- DB2_RESOLVE_CALL_CONFLICT
-
- Operating system: AIX, HP-UX,
Solaris, Linux, Windows
- Default: YES, Values: YES, NO
When routines called
by triggers attempt to access tables that have been modified by other
statements or routines in the body of the trigger, this can break
nested SQL statement rules. Setting DB2_RESOLVE_CALL_CONFLICT enforces
that all modifications to tables are completed in compliance with
the SQL standard rules for triggers before executing the CALL statement.
You
must stop the instance before you reset DB2_RESOLVE_CALL_CONFLICT and
then restart it. Then rebind any packages which cause invocation of
triggers. To rebind SQL Procedures use: CALL SYSPROC.REBIND_ROUTINE_PACKAGE
('P','procedureschema.procedurename','CONSERVATIVE');
You
need to be aware that DB2_RESOLVE_CALL_CONFLICT can
have a performance impact. Setting DB2_RESOLVE_CALL_CONFLICT to YES causes
the DB2 database manager to
resolve all potential read and write conflicts through the injection
of temporary tables, as needed. Typically, the impact is small because
at most one temporary table is injected. This has a small effect in
an OLTP environment because only one (or a small number of) rows are
being modified by the triggering statement. Typically, when following
the general recommendation to use SMS (system managed space) for temporary
table spaces, the performance impact from setting DB2_RESOLVE_CALL_CONFLICT is
expected to be low.
Changes to this variable
can take effect immediately for all future compiled SQL statements
if the db2set command is issued with the -immediate parameter.
You do not need to restart the instance.
- DB2_RESTRICT_DDF
-
- DB2ROUTINE_DEBUG
-
- Operating system: AIX and Windows
- Default: OFF, Values: ON or 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.
Note: DB2ROUTINE_DEBUG is deprecated and
will be removed in a future release. This stored procedure debugger
has been replaced by the Unified Debugger.
- DB2_SAS_SETTINGS
-
- Operating system: All
- Default: Not set. Values: ENABLE_SAS_EP, LIBRARY_PATH, COMM_BUFFER_SZ, COMM_TIMEOUT, RESTART_RETRIES, DIAGPATH, DIAGLEVEL
- This variable is the primary point of configuration for in-database
analytics with the SAS embedded process (EP). All options except for
the ENABLE_SAS_EP option are configurable online.
- ENABLE_SAS_EP
- If you set this option to TRUE, the SAS EP starts automatically
when you issue the db2start command. The default
for this option is FALSE.
- LIBRARY_PATH
- The fully qualified path from which to load the SAS EP library
the next time that the SAS EP process starts. If you do not specify
a path, the DB2 database manager
looks for the SAS EP library under the sqllib/function/sas directory.
For security reasons, you should install the SAS EP library in a location
where unauthorized users cannot modify or replace the file. Choose
one of the following options:
- Ensure that the library path and the SAS EP library file are owned
and can be written to only by the instance owner.
- Place the file in a directory, such as sqllib/function,
that has the sticky bit set.
Only a user with SYSADM authority can configure the library path
by using the db2set command.
- COMM_BUFFER_SZ
- An integer value specifying the amount of shared memory buffer,
in 4 KB pages, to use for communication sessions between the DB2 data server and the SAS EP.
The valid range of values for this parameter is 1 - 32767. The default
value is 15. Communications buffers are allocated from the FMP communications
heap. For more information, see DB2_FMP_COMM_HEAPSZ.
- COMM_TIMEOUT
- A timeout value that the DB2 database
manager uses to determine whether the SAS EP should be considered
unresponsive when exchanging control messages. If this value is reached,
the database manager kills the SAS EP so that it can be spawned again.
The default is 300 seconds.
- RESTART_RETRIES
- An integer value specifying the number of times that the DB2 database manager attempts to
respawn the SAS EP after detecting that it has terminated abnormally.
The valid range of values for this parameter is 0 - 100. The default
value is 10. After the retry count has been reached, the database
manager waits for 15 minutes before attempting the operation again.
- DIAGPATH
- A fully qualified path specifying the location of diagnostic logs
for the SAS EP. The default value is the value of the diagpath database
manager configuration parameter.
- DIAGLEVEL
- An integer value specifying the minimum severity level of messages
that are captured in the SAS diagnostic logs. The valid values for
this option are as follows:
- 1
- Severe
- 2
- Error
- 3
- Warning
- 4
- Informational
The default value is the value of the diaglevel database
manager configuration parameter.
- MEMSIZE
- An integer value specifying the maximum amount of memory, in 4
KB pages, that the SAS EP can consume on a particular host. The valid
range of values for this option is 1 - 4294967295. If there are multiple
logical partitions, the value that is applied to each partition is
divided by the number of logical partitions on the corresponding host.
The default value is 20% of the value of the instance_memory database
manager configuration parameter. If you set the instance_memory parameter
to a fixed value, ensure that this value takes the additional memory
requirements for the SAS EP into account.
Example:db2set DB2_SAS_SETTINGS="ENABLE_SAS_EP:TRUE;
LIBRARY_PATH:/home/instowner/sqllib/function/sas"
- DB2SATELLITEID
-
- Operating system: All
- Default: NULL, Values: a valid satellite ID declared
in the Satellite Control Database
- Specifies the satellite ID that is passed to the satellite control
server when a satellite synchronizes. If a value is not specified
for this variable, the logon ID is used as the satellite ID.
- DB2_SERVER_CONTIMEOUT
-
- Operating system: All
- Default: 180, Values: 0 to 32767 seconds
- This registry variable and the DB2_DISPATCHER_PEEKTIMEOUT registry
variable both configure the handling of a new client during connect
time. DB2_SERVER_CONTIMEOUT allows you to adjust
the time, in seconds, that an agent waits for a client's connection
request before terminating the connection. In most cases, you should
not need to adjust this registry variable, but if DB2 clients are constantly being timed out by
the server at connect time, you can set a higher value for DB2_SERVER_CONTIMEOUT to
extend the timeout period. If an invalid value is set, the default
value is used. This registry variable is not dynamic.
- DB2_SERVER_ENCALG
-
- Operating system: All
- Default: NULL, Values: AES_CMP or AES_ONLY
Note: DB2_SERVER_ENCALG is
deprecated in Version
9.7 and
might be removed in a future release.
If the DB2_SERVER_ENCALG registry
variable is set when you upgrade your instances to DB2 Version
9.7,
the alternate_auth_enc configuration parameter
is set to AES_ONLY or AES_CMP according
to the setting of DB2_SERVER_ENCALG. Thereafter,
to specify the encryption algorithm for encrypting user IDs and passwords,
update the alternate_auth_enc configuration parameter.
If the alternate_auth_enc configuration parameter
is set, its value takes precedence over the DB2_SERVER_ENCALG registry
variable value.
- DB2SORT
-
- Operating system: All, server only
- Default: NULL
- This variable 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.
- DB2_STANDBY_ISO
-
- Operating system: All
- Default: NULL, Values: UR or OFF
- This variable coerces the isolation level requested by applications
and statements running on an active HADR standby database to Uncommitted
Read (UR). When DB2_STANDBY_ISO is set to UR,
isolation levels higher than UR are coerced to UR with no warning
returned. If the HADR standby takes over as the HADR primary, this
variable will have no effect.
- DB2STMM
-
- Operating system: UNIX
- The registry variable controls a set of parameters which allow
you to modify certain characteristics of the self tuning memory manager
(STMM).
- Parameters:
- GLOBAL_BENEFIT_SEGMENT_COMPATIBLE
- Default: Not set, Values: YES, NO
The GLOBAL_BENEFIT_SEGMENT_COMPATIBLE parameter
only has a functional impact if the database_memory configuration
parameter is set to AUTOMATIC for a database.
This
parameter influences the permission settings of the STMM shared memory
segment. It should only be set to YES on systems
with multiple instances, where some of the instances are downlevel
and have database_memory set to AUTOMATIC,
in order to mitigate downlevel compatibility issues that impact the
tuning of a database's overall database memory usage. A downlevel
instance would be an instance belonging to any of the following DB2 releases and fix pack levels: DB2 V9.1 at all fix pack levels, DB2 V9.5 fix pack 7 and earlier,
and DB2 V9.7 fix pack 4 and
earlier.
For instances that are non-root DB2 installations, you should set this variable
only if you want all instances on the system make use of the same
STMM shared memory segment. Leaving this variable unset or set to NO will
cause a non-root instance to use its own instance-specific STMM shared
memory segment, which may impact the tuning of overall database memory
usage for any databases with database_memory set
to AUTOMATIC.
This registry variable is read
once, during the DB2 instance
startup. Note that you need to set this parameter across all the upgraded
(that is, non-downlevel) instances and once set, you need to restart
all upgraded instances.
- GLOBAL_BENEFIT_SEGMENT_UNIQUE
- Default: Not set, Values: YES, NO
The GLOBAL_BENEFIT_SEGMENT_UNIQUE parameter
only has a functional impact if the database_memory configuration
parameter is set to AUTOMATIC for a database.
This
parameter specifies that each upgraded (that is, non-downlevel) instance
is to make use of its own instance-specific STMM shared memory segment.
The means that each instance tunes overall database memory usage for
any of the databases belonging to it, independent of the tuning of
overall database memory usage of databases belonging to the other
instances on the system.
You should only consider setting this
parameter to YES if the instance_memory configuration
parameter is not set to AUTOMATIC for all
instances on a system.
This registry variable is read once,
during the DB2 instance startup.
Note that this parameter needs to be set across all the upgraded instances
and, once set, it requires that you restart all upgraded instances.
- DB2_TRUNCATE_REUSESTORAGE
-
- Operating system: All
- Default: NULL (not set), Values: IMPORT, import
- You can use this variable to resolve lock contention between the IMPORT with REPLACE command
and the BACKUP ... ONLINE command. In some situations,
online backup and truncate operations are unable to execute concurrently.
When this occurs, you can set DB2_TRUNCATE_REUSESTORAGE to IMPORT or import,
and physical truncation of the object, including data, indexes, long
fields, large objects and block maps (for multidimensional clustering
tables), is skipped and only logical truncation is performed. That
is, the IMPORT with REPLACE command
empties the table, causing the object's logical size to decrease,
but the storage on disk remains allocated.
This registry variable
is dynamic; you can set it or unset it without having to stop and
start instance. You can set DB2_TRUNCATE_REUSESTORAGE before
an online backup starts and then unset it after online backup completes.
For multi-partitioned environments, the registry variable will only
be active on the nodes on which the variable is set. DB2_TRUNCATE_REUSESTORAGE is
only effective on DMS permanent objects.
In SAP environments,
when DB2_WORKLOAD=SAP is
set, the default value of this registry variable is IMPORT.
- Changes to this
variable will take effect immediately for all future compiled SQL
statements. There is no need to restart the instance or to issue the db2set command
with the -immediate parameter.
- DB2_UTIL_MSGPATH
-
- Operating system: All
- Default: instanceName/tmp directory
- The DB2_UTIL_MSGPATH registry variable is
used in conjunction with the SYSPROC.ADMIN_CMD procedure, the SYSPROC.ADMIN_REMOVE_MSGS
procedure, and the SYSPROC.ADMIN_GET_MSGS UDF. It applies on the instance
level. DB2_UTIL_MSGPATH can be set to indicate
a directory path on the server where the fenced user ID can read,
write and delete files. This directory must be accessible from all
coordinator partitions, and must have sufficient space to contain
utility message files.
If this path is not set, the instanceName/tmp directory
is used as the default (note that instanceName/tmp is
cleaned up when DB2 is uninstalled).
If
this path is not set when the ALTOBJ procedure is run, a temporary
message file is created in the ~sqllib/tmp directory.
If
this path is changed, the files that existed in the directory pointed
to by the previous setting are not automatically moved or deleted.
If you want to retrieve the contents of the messages created under
the old path, you must manually move these messages (which are prefixed
with the utility name and suffixed with the user ID) to the new directory
to which DB2_UTIL_MSGPATH points. The next utility
message file is created, read, and cleaned up in the new location.
The
files under the DB2_UTIL_MSGPATH directory are
utility specific, not transaction dependent. They are not part of
the backup image. The files under the DB2_UTIL_MSGPATH directory
are user managed; that means a user can delete the message files
using the SYSPROC.ADMIN_REMOVE_MSGS procedure. These files are not
cleaned up by uninstalling DB2.
- DB2_XBSA_LIBRARY
-
- Operating system: AIX, HP-UX,
Solaris, and Windows
- Default: NULL, Values: Any valid path and file.
- Points to the vendor-supplied XBSA library. On AIX, the setting must include the shared object
if it is not named shr.o. HP-UX, Solaris, and Windows do not require the shared
object name. For example, to use Legato's NetWorker Business Suite
Module for DB2, the registry
variable must be set as follows:
db2set DB2_XSBA_LIBRARY="/usr/lib/libxdb2.a(bsashr10.o)"
The
XBSA interface can be invoked through the BACKUP DATABASE or
the RESTORE DATABASE commands. For example:
db2 backup db sample use XBSA
db2 restore db sample use XBSA
- DB2_XSLT_ALLOWED_PATH
-
- Operating system: All
- Default: NULL or NONE, Values: ALL or
a list of valid URIs , separated by a whitespace
- This registry variable controls how the DB2 instance refers to the external entities
defined inside of an XSLT stylesheet.