You set general registry variables to control database
behaviors, such as the time interval between consecutive connection
retries. Some registry variables apply only to specific operating
system environments.
- DB2ACCOUNT
-
- Operating system: All
- Default: NULL
- This variable defines the accounting string that is sent to the
remote host. Refer to the DB2
Connect User's Guide for
details.
- DB2BIDI
-
- Operating system: 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.
- DB2_CAPTURE_LOCKTIMEOUT
-
- Operating system: All
- Default: NULL, Values: ON or NULL
- This variable specifies to log descriptive information about
lock timeouts at the time that they occur. The logged information
identifies: the key applications involved in the lock contention that
resulted in the lock timeout, the details about what these applications
were running at the time of the lock timeout, and the details about
the lock causing the contention. Information is captured for both
the lock requestor (the application that received the lock timeout
error) and the current lock owner. A text report is written and stored
in a file for each lock timeout.
The files are created using the
following naming convention: db2locktimeout.par.AGENTID.yyyy-mm-dd-hh-mm-ss,
where par is the database partition number; AGENTID is
the Agent ID; yyyy-mm-dd-hh-mm-ss is the timestamp
consisting of the year, month, day, hour, minute and second. In non-partitioned
database environments, par is set to 0.
The
location of the file is based on the value set in the diagpath database
configuration parameter. If diagpath is not set,
then the file is located in one of the following directories:
- In Windows environments:
- If you do not set the DB2INSTPROF environment
variable, information is written to x:\SQLLIB\DB2INSTANCE,
where x is the drive reference, SQLLIB is
the directory that you specified for the DB2PATH registry
variable, and DB2INSTANCE is the name of the instance
owner.
- If you set the DB2INSTPROF environment variable,
information is written to x:\DB2INSTPROF\DB2INSTANCE,
where x is the drive reference, DB2INSTPROF is
the name of the instance profile directory, and DB2INSTANCE is
the name of the instance owner.
- If you set the DB2INSTPROF environment variable
to a new location, you must ensure that it contains the appropriate
files and folders to run the instance. This may require you to copy
all of the files and folders from the previous location to the new
location.
- In Linux and UNIX environments: information is written to INSTHOME/sqllib/db2dump,
where INSTHOME is the home directory of the instance.
Delete lock timeout report files when you no longer need them.
Because the report files are in the same location as other diagnostics
logs, the DB2® system could shutdown
if the directory is allowed to get full. If you need to keep some
lock timeout report files, move them to a directory or folder different
than where the DB2 logs are
stored.
- 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.
Important: This variable is deprecated
and might be removed in a future release because there are new methods
to collect lock timeout events using the CREATE EVENT MONITOR FOR
LOCKING statement.
- DB2CODEPAGE
-
- Operating system: All
- Default: derived from the language ID, as specified by the operating
system.
- This variable 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.
Note: Because Windows does not report a Unicode
code page (in the Windows regional
settings) instead of the ANSII code page, a Windows application will not behave as a
Unicode client. To override this behavior, set the DB2CODEPAGE registry
variable to 1208 (for the Unicode code page) to
cause the application to behave as a Unicode application.
- DB2_COLLECT_TS_REC_INFO
-
- Operating system: All
- Default: ON; OFF for
HADR databases, Values: ON or OFF
- This variable specifies whether all log files are to be processed
when rolling forward a table space, regardless of whether the log
files contain log records that affect the table space. To skip the
log files known not to contain any log records affecting the table
space, set this variable to ON. DB2_COLLECT_TS_REC_INFO must
be set before the log files are created and used so that the information
required for skipping log files is collected.
- DB2_CONNRETRIES_INTERVAL
-
- Operating system: All
- Default: Not set, Values: an integer number of seconds
- This variable specifies the sleep time between consecutive connection
retries, in seconds, for the automatic client reroute feature. You
can use this variable in conjunction with DB2_MAX_CLIENT
CONNRETRIES to configure the retry behavior for
automatic client reroute.
If DB2_MAX_CLIENT_CONNRETRIES is
set, but DB2_CONNRETRIES_INTERVAL is not, DB2_CONNRETRIES_INTERVAL defaults
to 30. If DB2_MAX_CLIENT_CONNRETRIES is
not set, but DB2_CONNRETRIES_INTERVAL is set, DB2_MAX_CLIENT_CONNRETRIES defaults
to 10. If neither DB2_MAX_CLIENT_CONNRETRIES nor DB2_CONNRETRIES_INTERVAL is
set, the automatic client reroute feature reverts to its default behavior
of retrying the connection to a database repeatedly for up to 10 minutes.
- DB2CONSOLECP
-
- Operating system: Windows
- Default: NULL, Values: all valid code page values
- Specifies the code page for displaying DB2 message text. When specified, this value
overrides the operating system code page setting.
- DB2DBDFT
-
- Operating system: All
- Default: NULL
- This variable specifies the database alias name of the database
to be used for implicit connects. If an application has no database
connection but SQL or XQuery statements are issued, an implicit connect
will be made if the DB2DBDFT environment variable
has been defined with a default database.
- DB2DISCOVERYTIME
-
- Operating system: Windows
- Default: 40 seconds, Minimum: 20 seconds
- This variable specifies the amount of time that SEARCH discovery
will search for DB2 systems.
- DB2_ENFORCE_MEMBER_SYNTAX
-
- Operating system: All
- Default: OFF , Values: OFF or ON
- This variable allows you to control whether or not the syntax
for SQL statements, DB2 commands,
and APIs will be checked for the correct usage of the database partition
keywords to determine whether the MEMBER keyword must be used instead.
In a DB2 pureScale® environment,
the default behavior is to tolerate the usage of keywords specific
to database partitions, such as DBPARTITIONNUM or DATABASE PARTITION,
even when the operation is targeting a DB2 member.
However, when DB2_ENFORCE_MEMBER_SYNTAX is set
to ON, the MEMBER keyword must be specified correctly,
otherwise SQL1538N is returned. The setting of this variable is ignored
and has no effect outside of a DB2 pureScale environment.
- DB2_EXPRESSION_RULES
-
- Operating system: All
- Default: Empty, Values: RAISE_ERROR_PERMIT_SKIP or RAISE_ERROR_PERMIT_DROP
- The settings for the DB2_EXPRESSION_RULES registry
variable control how the DB2 Optimizer
determines the access plan for queries which involve a RAISE_ERROR
function. The default behaviour of the RAISE_ERROR function is that
no filtering may be pushed beyond the expression containing this function.
This can result in no predicates being applied during the table accesses
which can lead to excessive computation of expressions, excessive
locking and poor query performance.
In certain cases this behaviour
is too strict, depending on the particular business requirements of
the application, it may not matter if predicates and joins are applied
before the application of RAISE_ERROR. For example in the context
of a row level security implementation, there is typically an expression
of the form:
CASE WHEN <conditions for validatin access to this row>
THEN NULL
ELSE RAISE_ERROR(...)
END
The application may only be concerned with validating
access to the rows which are selected by the query and not in validating
access to every row in the table. Thus predicates could be applied
in the base table access and the expression containing the RAISE_ERROR
only needs to executed after all the filtering is performed. In this
case a value of
DB2_EXPRESSION_RULES=
RAISE_ERROR_PERMIT_SKIP may
be appropriate.
Another alternative is in the context of COLUMN
LEVEL security. In this case there are typically expressions of the
form:
CASE WHEN <conditions for validating access to this row and column>
THEN <table.column>
ELSE RAISE_ERROR(...)
END
In this case the application may only want errors
to be raised if the user attempts to receive the data for a particular
row and column contains a value that the user is not allowed to retrieve.
In this case a setting of
DB2_EXPRESSION_RULES=
RAISE_ERROR_PERMIT_DROP will
only cause the expression containing the RAISE_ERROR function to be
evaluated if the particular column is used by a predicate or a column
function, or if it is returned as output from the query.
- DB2FODC
- Operating system: All
- Default: The concatenation of all FODC parameters (see following
list)
- for Linux and UNIX: "CORELIMIT=val DUMPCORE=ON
DUMPDIR=diagpath"
- for Windows: "DUMPDIR=diagpath"
Note that the parameters are separated by spaces.
- This registry variable controls a set of troubleshooting-related
parameters used in First Occurrence Data Collection (FODC). Use DB2FODC to
control different aspects of data collection in outage situations. The DB2FODC registry
variable should be set at the instance level only.
This registry
variable is read once, during the DB2 instance
startup. To perform updates to the FODC parameters online, use
db2pdcfg tool.
Use the
DB2FODC registry variable to sustain
the configuration across reboots. You do not need to specify all of
the parameters, nor do you need to specify them in a particular order.
The default value is assigned to any parameter that is not specified.
For example, if you don't want the core files dumped, but you do want
the other parameters' default behaviors, you would issue the command:
db2set DB2FODC="DUMPCORE=OFF"
Parameters:- CORELIMIT
-
- COS
- Operating system: All
- Default: ON, Values: ON or OFF
- This option specifies if the db2cos script
is enabled or not. You can use the following parameters with this
parameter:
- COS_SLEEP
- Default: 3, Values: 0 to unlimited
- This option specifies the amount of time to sleep in seconds between
checking the size of the output file generated.
- COS_TIMEOUT
- Default: 30, Values: 0 to unlimited
- This option specifies the amount of time to wait in seconds before
the script is finished.
- COS_COUNT
- Default: 255, Values: 0 to 255
- This option specifies the number of times to execute db2cos during
a database manager trap.
- COS_SQLO_SIG_DUMP
- Default: ON, Values: ON or OFF
- This option specifies if db2cos is enabled
when the SQLO_SIG_DUMP signal is received.
- DUMPCORE
-
- DUMPDIR
- Operating system: All
- Default: diagpath directory, or the default
diagnostic directory if diagpath is not defined,
Values: path to directory
- This option specifies the absolute path name of the directory
for core file creation.
- FODCPATH
- Operating system: All
- Default: path defined by the DIAGPATH database
manager configuration parameter, Values: fodc_path_name
- This option specifies the absolute path name of where the FODC
package is to be directed. The fodc_path_name must
be an existing directory and must be writable by the member or members
for which it is set for and by the fmp processes running on those
members.
- SERVICELEVEL
- Operating system: All
- Default: AUTOMATIC ulimit setting, Values: AUTOMATIC, BASIC,
or FULL
- This option specifies how data is collected during panics, traps,
or errors that might indicate data corruption. DB2 is designed to
generate diagnostics that are appropriate to the configuration and
problem context. For example, when a trap can be sustained, only
the minimum essential diagnostics are generated in order to rollback
the transaction and respond to the application as soon as possible,
releasing resources which other applications may be waiting on. When
a trap cannot be sustained, diagnostics such as db2cos data collection
scripts and core dumps may be limited in favor of availability in DB2 pureScale
configurations. The default behaviour for generating diagnostics
is represented by the SERVICELEVEL setting of AUTOMATIC.
The following
option are supported for this parameter:
- AUTOMATIC
- This setting specifies that the effective SERVICELEVEL
setting (that is, BASIC or FULL)
is to be chosen at runtime, for the members,
and at start time, for the CF process.
At present, the only times that BASIC is chosen are
for DB2 pureScale environments
that have multiple members and
for trap resilience.
- BASIC
- This SERVICELEVEL setting specifies that a minimal
amount of FODC data is to be dumped. Core dump processing is disabled
by default (but can be overridden by the COREDUMP setting), diagnostics
are restricted to the affected thread only, and callout scripts are
disabled.
- FULL
- This SERVICELEVEL setting specifies that the maximum
amount of FODC data is to be dumped. This includes core dumps, any
associated components dumps, and the invocation of the callout scripts.
In addition, there is no attempt to sustain traps.
- DB2_FORCE_APP_ON_MAX_LOG
-
- Operating system: All
- Default: TRUE, Values: TRUE or FALSE
- Specifies what happens when the max_log configuration
parameter value is exceeded. If set to TRUE, the
application is forced off the database and the unit of work is rolled
back.
If FALSE, the current statement fails.
The application can still commit the work completed by previous statements
in the unit of work, or it can roll back the work completed to undo
the unit of work.
Note: This DB2 registry
variable affects the ability of the import utility to recover from
log full situations. If DB2_FORCE_APP_ON_MAX_LOG is
set to TRUE and you issue an IMPORT command
with the COMMITCOUNT command option, the import
utility will not be able to perform a commit in order to avoid running
out of active log space. When the import utility encounters an SQL0964C
(Transaction Log Full), it will be forced off the database and the
current unit of work will be rolled back.
- 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.
- DB2GRAPHICUNICODESERVER
-
- Operating system: All
- Default: OFF, Values: ON or OFF
- This registry variable is used to accommodate existing applications
written to insert graphic data into a Unicode database. Its use is
only needed for applications that specifically send sqldbchar (graphic)
data in Unicode instead of the code page of the client. (sqldbchar
is a supported SQL data type in C and C++ that can hold a single double-byte
character.) When set to ON, you are telling the database
that graphic data is coming in Unicode, and the application expects
to receive graphic data in Unicode.
- DB2INCLUDE
-
- Operating system: All
- Default: Current directory
- Specifies a path to be used during the processing of the SQL INCLUDE
text-file statement during DB PREP processing.
It provides a list of directories where the INCLUDE file might be
found. Refer to Developing Embedded SQL Applications for
descriptions of how DB2INCLUDE is used in the
different precompiled languages.
- DB2INSTDEF
-
- Operating system: All
- Default: DB2 on Windows, and the last instance that is created
on UNIX.
- This variable sets the value to be used if DB2INSTANCE is
not defined.
- DB2INSTOWNER
-
- Operating system: Windows
- 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
-
- Operating system: All
- Default: NULL, Range: 0 to 32767
- This variable determines 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.
- DB2LOCALE
-
- Operating system: All
- Default: NO, Values: YES or NO
- This variable specifies whether the default "C" locale of a process
is restored to the default "C" locale after calling DB2 and whether to restore the process locale
back to the original 'C' after calling a DB2 function.
If the original locale was not 'C', then this registry variable is
ignored.
- DB2_MAX_CLIENT_CONNRETRIES
-
- Operating system: All
- Default: Not set, Values: an integer number of maximum times to
retry the connection
- This variable specifies the maximum number of connection retries
that the automatic client reroute feature will attempt. You can use
this variable in conjunction with DB2_CONNRETRIES_INTERVAL to
configure the retry behavior for automatic client reroute.
If DB2_MAX_CLIENT_CONNRETRIES is
set, but DB2_CONNRETRIES_INTERVAL is not, DB2_CONNRETRIES_INTERVAL defaults
to 30. If DB2_MAX_CLIENT_CONNRETRIESis
not set, but DB2_CONNRETRIES_INTERVAL is set, DB2_MAX_CLIENT_CONNRETRIES defaults
to 10. If neither DB2_MAX_CLIENT_CONNRETRIES nor DB2_CONNRETRIES_INTERVAL is
set, the automatic client reroute feature reverts to its default behavior
of retrying the connection to a database repeatedly for up to 10 minutes.
- DB2_MAX_GLOBAL_SNAPSHOT_SIZE
-
- Operating system: All
- Default: Not set, Values: 0 to the maximum size of a snapshot.
- This variable specifies the number of bytes a snapshot or snapshot
estimate can be. You can use this variable to prevent large global
snapshots from causing memory usage spikes which can cause performance
degradation and system hangs.
By
default, DB2_MAX_GLOBAL_SNAPSHOT_SIZE is not
set, which means an effective limit of the maximum size of a snapshot
(2 GB less 512 bytes). This variable is dynamic and only applies to
partitioned database environments.
- DB2_OBJECT_TABLE_ENTRIES
-
- Operating system: All
- Default: 0, Values: 0-65532
The
actual maximum value possible on your system depends on the page size
and extent size, but it cannot exceed 65532.
- This variable specifies the expected number of objects in a table
space. If you know that a large number of objects (for example, 1000
or more) will be created in a DMS table space, you should set this
registry variable to the approximate number before creating the table
space. This will reserve contiguous storage for object metadata during
table space creation. Reserving contiguous storage reduces the chance
that an online backup will block operations which update entries in
the metadata (for example, CREATE INDEX, IMPORT REPLACE).
It will also make resizing the table space easier because the metadata
will be stored at the start of the table space.
If the initial
size of the table space is not large enough to reserve the contiguous
storage, the table space creation will continue without the additional
space reserved.
- DB2_SRVLSTLOG_LEVEL
-
- Operating system: All
- Default: 1, Values: 0-4
Specifies the logging level for server list events that pertain
to workload balancing (WLB) and automatic client reroute (ACR). You
can use this information (usually under the guidance of IBM® service) to gather problem determination
data. All entries that are logged are informational. Valid values
for this registry variable are as follows:
- 0: Nothing is logged
- 1: Only messages of high importance are logged.
- 2: Only messages of medium and high importance are logged.
- 3: Only messages of low, medium, and high importance are logged.
- 4: All messages are logged.
The diagpath database manager configuration
parameter specifies where the server list log files are to be stored.
These log files are circular and use the following naming convention:
db2srvlst.0.log, db2srvlst.1.log,
db2srvlst.N.log. Changes
to DB2_SRVLSTLOG_LEVEL require the client application
to be restarted before the new value takes effect.
- DB2_SYSTEM_MONITOR_SETTINGS
-
- Operating system: All
- 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.
- The registry variable controls a set of parameters
which allow you to modify the behavior of various aspects of DB2 monitoring. Separate each parameter
by a semicolon, as in the following example:
db2set DB2_SYSTEM_MONITOR_SETTINGS=OLD_CPU_USAGE:TRUE;
DISABLE_CPU_USAGE:TRUE
Every time you set DB2_SYSTEM_MONITOR_SETTINGS,
each parameter must be set explicitly. Any parameter that you do not
specify when setting this variable reverts back to its default value.
So in the following example:db2set DB2_SYSTEM_MONITOR_SETTINGS=DISABLE_CPU_USAGE:TRUE
OLD_CPU_USAGE will
be restored to its default setting.Note: Currently, this registry
variable only has settings for Linux;
additional settings for other operating systems will be added in future
releases.
- Parameters:
- OLD_CPU_USAGE
- Operating system: Linux
- Values: TRUE/ON, FALSE/OFF
- Default value on RHEL4 and SLES9: TRUE (Note: a setting of FALSE
for OLD_CPU_USAGE will be ignored-only the old behavior
will be used.)
- Default value on RHEL5, SLES10, and others: FALSE
- This parameter controls how the instance obtains CPU usage times
on Linux platforms. If set
to TRUE, the older method of getting CPU usage time is used. This
method returns both system and user CPU usage times, but consumes
more CPU in doing so (that is, it has a higher overhead). If set to
FALSE, the newer method of getting CPU usage is used. This method
returns only the user CPU usage value, but is faster because it has
less overhead.
- DISABLE_CPU_USAGE
- Operating system: Linux
- Values: TRUE/ON, FALSE/OFF
- Default value on RHEL4 and SLES9: TRUE
- Default value on RHEL5, SLES10, and others:
FALSE
- This parameter allows you to determine whether CPU usage is read
or not. When DISABLE_CPU_USAGE is enabled (set to
TRUE), CPU usage is not read, allowing you to avoid the overhead
that can sometimes occur during the retrieval of CPU usage.
- DB2TERRITORY
-
- Operating system: All
- Default: derived from the language ID, as specified by the operating
system.
- This variable specifies the region, or territory code of the client
application, which influences date and time formats.
- DB2_VIEW_REOPT_VALUES
-
- Operating system: All
- Default: NO, Values: YES, NO
- This variable enables all users to store the cached values of
a reoptimized SQL or XQuery statement in the EXPLAIN_PREDICATE table
when the statement is explained. When this variable is set to NO,
only DBADM is allowed to save these values in the EXPLAIN_PREDICATE
table.