General registry variables

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_BYPASS_DEFAULT_ISOLATION_APPS
  • Operation system: All
  • Default: NULL, Values: one or more applications, separated by a colon or semicolon. Application names are case-sensitive.
  • This dynamic registry variable identifies connections that will not have their initial value set to the value specified by DB2_DEFAULT_ISOLATION_VALUE. These connections will behave in the traditional manner, i.e. initially using the package isolation levels until such time that a SET ISOLATION statement is issued.
  • The connections are identified by matching the program name supplied to DB2 when the connection is established to any of the values in the provided list.

Usage Example:

A DBA can decide to allow certain applications to bypass a new default isolation setting using DB2_DEFAULT_ISOLATION_VALUE so that package isolation values will not be ignored. The administrator is able to identify the applications by the application name supplied to DB2.

Using db2set, the DB2_BYPASS_DEFAULT_ISOLATION_APPS registry variable can be set to allow any connection with a program ID of “APPN” or “APPM” to not have the value of its CURRENT ISOLATION register set to UR when the connection is established:
db2set DB2_BYPASS_DEFAULT_ISOLATION_APPS=“APPN:APPM”
DB2_BYPASS_DEFAULT_ISOLATION_GROUPS
  • Operating system: All
  • Default: NULL, Values: one or more group authorization IDs, separated by a colon or a semicolon. Each group authorization ID should be specified in uppercase.
  • This variable identifies connections that will not have their initial value set to the value specified by DB2_DEFAULT_ISOLATION_VALUE. These connections will behave in the traditional manner (that is, initially using the package isolation levels until such time that a SET ISOLATION statement is issued).
  • The connections are identified by matching any of the group authorization IDs associated with current value of SESSION_USER special register at connection time with any of the values in the provided list.

Usage Example:

A DBA can allow certain applications to bypass a new default isolation level set using the DB2_DEFAULT_ISOLATION_VALUE registry variable such that package isolation values will not be ignored.

The administrator is able to identify the applications by the group authorization ID in use.

Using db2set, the DB2_BYPASS_DEFAULT_ISOLATION_GROUPS registry variable can be set to allow any connection with a session group authorization IDs of “DB2GROUP” to not have the value of its CURRENT ISOLATION register set to UR when the connection is established:
db2set DB2_BYPASS_DEFAULT_ISOLATION_GROUPS=“DB2GROUP”
DB2_BYPASS_DEFAULT_ISOLATION_USERS
  • Operating system: All
  • Default: NULL, Values: One or more user authorization IDs, separated by a colon or a semicolon. Each user authorization ID should be specified in uppercase.
  • This dynamic registry variable identifies connections that will not have their initial value set to the value specified by DB2_DEFAULT_ISOLATION_VALUE. These connections will behave in the traditional manner i.e. initially using the package isolation levels until such time that a SET ISOLATION statement is issued.
  • The connections are identified by matching the DB2 authorization ID present in the SESSION_USER special register at connection time with a value in the provided list.

Usage Example:

A DBA can allow certain applications to bypass a default isolation level set using the DB2_DEFAULT_ISOLATION_VALUE registry variable such that package isolation values will not be ignored.

The administrator is able to identify the applications by the user authorization ID in use.

Using db2set, the DB2_BYPASS_DEFAULT_ISOLATION_USERS registry variable can be set to allow any connection with a session authorization ID of “SVINC”, “TRICE” or “KLOW” to not have the value of its CURRENT ISOLATION register set to UR when the connection is established:
db2set DB2_BYPASS_DEFAULT_ISOLATION_USERS=“SVINCE:TRICE:KLOW”
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 that is presented to Db2 for a database client application. Do not set this variable unless explicitly instructed to do so by the Db2 documentation or by an IBM support representative. Setting this variable to a value that is not supported by the operating system can produce unexpected results. Normally, you do not need to set this variable because Db2 automatically derives the code page information from the operating system.
    Note: Windows, in its regional settings, reports the ANSI code page. Therefore, when running a Windows application that is to behave as a Unicode client, set the DB2CODEPAGE registry variable to 1208, which is the value for the Unicode code page.
DB2_COLLECT_TS_REC_INFO
  • Operating system: All
  • Default: ON; OFF for HADR databases, Values: ON or OFF
  • This variable enables tracking of affected table spaces in each log file as they are being produced. This information can benefit a later table space recovery via the ROLLFORWARD command, where only the log files that contain log records affecting the table space are processed. This can significantly reduce recovery time, especially for the table spaces that are not being changed frequently. For more information, see "Rolling forward changes in a table space".
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.
DB2_DEFAULT_ISOLATION_VALUE
  • Operating system: All
  • Default: NULL, Values: a valid isolation level value. The value can be one from the following set:
    • UR (Uncommitted Read)
    • CS (Cursor Stability)
    • RR (Repeatable Read)
    • RS (Read Stability)

    It can also be set to two blank spaces.

This variable causes the initial value of the CURRENT ISOLATION special register in any subsequent new connection to be set to the specified value in the same manner as is done by the SET ISOLATION statement and with the same consequences. This value can be overwritten within a connection through use of the SET ISOLATION statement.

The setting of this registry variable will result in package isolation values being ignored for any dynamic SQL prepared within those connections until and unless a SET ISOLATION RESET statement is issued.

Usage Notes:

DB2 is active and the database is currently being used by a number of applications.

Using db2set, the database administrator dynamically sets the new registry variable to the UR isolation value:

db2set DB2_DEFAULT_ISOLATION_VALUE=UR
This action automatically sets the CURRENT ISOLATION special register to UR for all new connections and causes package isolation values to be ignored for any dynamic SQL prepared within those connections.
DB2_DEFAULT_ISOLATION_VALUE can also be reset to " " (two blanks) :

db2set DB2_DEFAULT_ISOLATION_VALUE=" "

Restrictions and Limitations:

The following restrictions and limitations have been identified for this registry variable:
  • The DB2_DEFAULT_ISOLATION_VALUE registry variable affects all connections on all databases under the instance.
  • The DB2_DEFAULT_ISOLATION_VALUE does not affect the isolation level used by static SQL statements, including those compiled at runtime as part of incremental bind and reoptimization processing.
  • The DB2_DEFAULT_ISOLATION_VALUE registry variable acts as an automatic setting of the CURRENT ISOLATION special register. Its value can be overridden in the normal way by any of the following:
    • Use of the statement isolation clause
    • The SQL Compiler (in order to maintain statement integrity)
  • The DB2_DEFAULT_ISOLATION_VALUE overrides the use of the CHANGE ISOLATION LEVEL command in CLP and effectively prevents isolation changes in ODBC and JCC applications. For CLP, it is possible to regain use of the CHANGE ISOLATION LEVEL command by issuing a SET CURRENT ISOLATION RESET immediately after the connection to the database is established.
  • Changes to the value of DB2_DEFAULT_ISOLATION_VALUE will only be picked up by connections established after the change has been made.
  • In a partitioned database the value of the DB2_DEFAULT_ISOLATION_VALUE dynamic registry variable only has an effect at coordinator partitions because this is where dynamic SQL compilation occurs
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 behavior 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 behavior 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
    • Operating system: Linux and UNIX
    • Default: Current® ulimit setting, Values: 0 to unlimited
    • This option specifies the maximum size, in bytes, of core files created. This value overrides the current core file size limit setting. Consideration should be given to the available file system space because core files can be quite large. The size is dependent on the Db2 configuration and the state of the process at the time the problem occurs.

      If CORELIMIT is set, Db2 will use this value to override the current user core limit (ulimit) setting to generate the core file.

      If CORELIMIT is not set, Db2 will set the core file size to the value equal to the current ulimit setting.
      Note: Any changes to the user core limit or CORELIMIT are not effective until the next recycling of the Db2 instance.
    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
    • Operating system: Linux, AIX®
    • Default: AUTO, Values: AUTO, ON, or OFF
    • This option specifies if core file generation is to take place. Core files, which are used for problem determination and are created in the diagpath directory, contain the entire process image of the terminating Db2 process. However, whether or not an actual core file dump occurs depends on the current ulimit setting and value of the CORELIMIT parameter. Some operating systems also have configuration settings for core dumps, which may dictate the behavior of application core dumping. The AUTO setting causes a core file to be generated if a trap cannot be sustained when the DB2RESILIENCE registry variable is set to ON. The DUMPCORE=ON setting always generates a core file by overriding the DB2RESILIENCE registry variable setting.

      The recommended method for disabling core file dumps is to set DUMPCORE to OFF.

    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. For FODC packages that are not generated due to an instance-wide event, the FODC package will be directed to the DIAGPATH regardless of the FODCPATH setting.
    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 behavior 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.
DB2_WLM_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.
  • Parameters:
    ENABLE_HWMS
    • Default: TRUE
    • Values: TRUE, FALSE
    • The collection of HWM values can impact scalability on large transactional systems. If you set this option to FALSE, some HWM values will no longer be maintained
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: Not set
  • 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.
  • If not explicitly set, SQL INCLUDE ext-file statement will search text-file in the current directory.
DB2INSTDEF
  • Operating system: Windows
  • Default: Db2 on Windows
  • This variable sets the value to be used if DB2INSTANCE is not defined. Support for UNIX has been deprecated for this release.
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.
    Note: A locale is a unique combination of language, country, region, and code . The "C" locale assumes that allchar data types are 1 byte and that their value is always less than 256 characters. You change or restore a "C" locale for a specific application by calling the setlocale function.
    setlocale (LC_ALL, "C");
    You can also change the locale to another region. For example, "en-US"
    setlocale( LC_ALL, "en-US" );
    You can query for the name of the current locale, using NULL.
    current_locale = setlocale (LC_ALL, NULL);
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 two 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's two parameter settings only affect Linux; additional settings for other operating systems may 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.
DB2_ONLINERECOVERY
DB2_ONLINERECOVERY_WITH_UR_ACCESS
  • Operating system: All
  • Default: YES (NO when DB2_WORKLOAD=SAP), Values: YES, NO
  • If the database is configured to allow for connectivity during the backward phase of crash recovery (when DB2_ONLINERECOVERY is set to YES), then this variable controls table accessibility during crash recovery. If this variable is set to YES, then the tables that are involved in crash recovery might be accessible to SQL queries with UR isolation level. If this variable is set to NO, then the tables involved in crash recovery will not be accessible to SQL queries. Tables that are not involved in crash recovery will always be accessible to SQL queries in any isolation level. For more information, see "Database accessibility during backward phase of crash recovery or HADR takeover".
  • Changes to this variable do not require the database instance to be restarted.