DB2 Version 10.1 for Linux, UNIX, and Windows

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_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, Values: ON or OFF
  • This variable specifies whether DB2 will process all log files 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
    • 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, Solaris, 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.
    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.
Related concepts:
DB2 registry and environment variables