Miscellaneous variables

• Operating system: Windows and UNIX
• Default: NULL
• Specifies the Db2 Administration Server.

• Operating system: All
• Default: Not set
• Values: UTL_DIR_DBAUTH, EXTERNAL_ROUTINE_DBAUTH, NOT_FENCED_ROUTINE_DBAUTH
• Use this registry variable to control the behavior of certain GRANT, REVOKE, and Data Definition Language (DDL) statements. Multiple values can be specified with a comma separating the values. This registry variable is dynamic and can be set or unset without having to stop and start the instance.
• This registry variable affects how the current GRANT, REVOKE, or DDL statement behaves when it is issued. It does not affect privileges or authorities that have already been granted.

For Db2 11.1.4.6 security special build 40997 and later

• EXTERNAL_ROUTINE_DBAUTH: Set this value for the GRANT statement of CREATE_EXTERNAL_ROUTINE on the database, to include the SECADM or ACCESSCTRL authority in addition to the SYSADM authority.
• NOT_FENCED_ROUTINE_DBAUTH: Set this value for the GRANT statement of CREATE_NOT_FENCED_ROUTINE on the database, to include the SECADM or ACCESSCTRL authority in addition to the SYSADM authority.
• UTL_DIR_DBAUTH: Set this value for the GRANT statement of EXECUTE on the module UTL_DIR, to include the SECADM or ACCESSCTRL authority in addition to the SYSADM authority.

For Db2 11.1.4.7 security special build 41268 and later

• EXTERNAL_ROUTINE_DBADM: When this value is set, the DBADM authority implicitly includes the CREATE_EXTERNAL_ROUTINE database authority.
• NOT_FENCED_ROUTINE_DBADM When this value is set, the DBADM authority implicitly includes the CREATE_NOT_FENCED_ROUTINE database authority.
• UTL_DIR_DATAACCESS: When this value is set, users with the DATAACCESS authority have implicit EXECUTE privilege on SYSIBMADM.
• UTL_DIR UTL_DIR_SQLADM_GRANT: When this value is set, SQLADM and DBADM are granted EXECUTE privilege on UTL_DIR during a CREATE DATABASE, migration, or db2updv operation. When running the db2updv command, this registry variable must be set on the system from which the db2updv command is run.

• 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 enable this variable will take effect immediately. Changes to disable this variable will require an instance restart.

• Operating system: All
• Default: Not set. Values: DISABLE_CHGPASS, OSAUTHDB, SQLADM_NO_RUNSTATS_REORG, TRUSTEDCLIENT_DATAENC, TRUSTEDCLIENT_SRVRENC, DISABLE_SYSMON_CONNECT, JCC_ENFORCE_SECMEC, JCC_NOENFORCE_SECMEC_MSG, JCC_NOENFORCE_SECMEC_NOMSG.
• 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 enable Transparent LDAP support using Pluggable Authentication Modules (PAM) on Linux®, or Loadable Authentication Modules (LAM) on AIX®.
    Note: The OSAUTHDB option only has an effect when the default password authentication plugin is in use. It does not have any effect when the Db2 LDAP plugins or any other plugins are in use.
  • 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 Db2 10.5.0.4, forces the Db2 server to not accept CLEAR_TEXT_PASSWORD_SECURITY security mechanisms when the authentication value is set to SERVER_ENCRYPT. Cannot be set together with either JCC_NOENFORCE_SECMEC_MSG or JCC_NOENFORCE_SECMEC_NOMSG settings.
  • JCC_NOENFORCE_SECMEC_MSG: This value, introduced in Db2 11.1.3.3, allows the Db2 server to accept CLEAR_TEXT_PASSWORD_SECURITY security mechanisms when the authentication value is set to SERVER_ENCRYPT, and prints a warning in the db2diag.log regarding the associated risk of sending the password in the clear. If set, the following message is printed on every connect: Connection accepted as SERVER ( JCC_NOENFORCE_SECMEC_MSG ). Cannot be set together with either JCC_ENFORCE_SECMEC or JCC_NOENFORCE_SECMEC_NOMSG settings.
  • JCC_NOENFORCE_SECMEC_NOMSG: This value, introduced in Db2 11.1.3.3, allows the Db2 server to accept CLEAR_TEXT_PASSWORD_SECURITY security mechanisms when the authentication value is set to SERVER_ENCRYPT, but does not print a warning in the db2diag.log regarding the associated risk of sending the password in the clear. Cannot be set together with either JCC_ENFORCE_SECMEC or JCC_NOENFORCE_SECMEC_NOMSG settings. This value represents the default behavior of Db2 since Db2 10.5.0.4.
  • 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.

• Operating system: All
• Default: COMPRESS, Values: COMPRESS, NX842
• Specifies whether backups are compressed with the common Db2 compression or with NX842 compression. NX842 compression is only available on AIX (see Hardware accelerated backup and log file compression for details).

• Operating system: All
• Default: FALSE, Values: FALSE, TRUE
• Specifies whether online backups which fail to include all of the necessary log files should still be allowed to complete successfully. By default, online backups that do not explicitly specify either the INCLUDE LOGS or the EXCLUDE LOGS option fail if all of the logs are not successfully included. When this variable is set to TRUE, these backups will be allowed to succeed with a warning.

  In SAP environments, when DB2_WORKLOAD=SAP is set, the default value of this registry variable is TRUE.

• Operating system: All
• Default: FALSE, Values: FALSE, TRUE
• Specifies whether DMS and AS page validation occurs during a backup. This registry variable is dynamic, that is, it can be set or unset without having to stop and start the instance. (See the Usage Notes of the Backup Command for more details.)

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

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

• Operating system: Windows
• Default: Windows' C:\ProgramData\
• Points to user-defined location that holds the Db2 common application data for the Db2 copy. This registry variable is populated if DB2_COMMON_APP_DATA_TOP_PATH is specified during the response file installation or if Db2 Common Application Data Top Path field is populated during the custom installation step.

  Starting in Version 9.7 Fix Pack 5, this registry variable is visible in db2set command output but is not changeable. Any attempts to change given registry value will result in an error.

• 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.
  Note: Do not change the DB2_COMPATIBILITY_VECTOR registry variable after database creation. For more information, refer to this technote.

• 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 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 z/OS server, set DB2CONNECT_DISCONNECT_ON_INTERRUPT to YES on the gateway.

• 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 Linux UNIX and Windows supportfor details on various scenarios and the proper course of action.

• Operating system: All
• Default: OFF, Values: ON, OFF
• In Db2 pureScale environments only, this variable controls whether applications are allowed write access to the table while an index is being created on that table.
• This variable is available in Version 11.1.3.3 and newer releases.
• This feature can only be enabled on a recoverable database (where logarchmeth1 or logarchmeth1 or logarchmeth2 database configuration parameters is not set to OFF).
• Changes to this variable do not require the database instance to be restarted
  Note: There is a limitation on creating expression-based indexes in pureScale environments, during which applications can only have read access, even if this registry variable is set.).

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

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

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

• Operating system: All
• Default:
• When its value is as default, Federation will pick up db2dj.ini file from below locations:
  • UNIX: db2_instance_directory/cfg/db2dj.ini
  • Windows: db2_install_directory\cfg\db2dj.ini
• Specifies the absolute path name of the federation configuration file, for example: db2set DB2_DJ_INI=$HOME/sqllib/cfg/my_db2dj.ini This file contains the settings for data source environment variables. These environment variables are used by the Informix® wrapper and by the wrappers provided by InfoSphere® Federation Server.
  Here is a sample federation configuration file:

  INFORMIXDIR=/informix/client_sdk 
  INFORMIXSERVER=inf93 
  ORACLE_HOME=/usr/oracle9i 
  SYBASE=/sybase/V12 
  SYBASE_OCS=OCS-12_5

  The following restrictions apply to the db2dj.ini file:

  • Entries must follow the format evname=value where evname is the name of the environment variable and value is its value.
  • The environment variable name has a maximum length of 255 bytes.
  • The environment variable value has a maximum length of 765 bytes.

  This variable is ignored unless the database manager parameter federated is set to YES.

• 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_DMU_DEFAULT is set as IMPLICITLYHIDDENMISSING

    db2 load from delfile1 of del insert into table1

    If table1 has implicitly hidden columns, the load utility assumes that the data for implicitly hidden columns is not in the input file.
  • DB2_DMU_DEFAULT is set as IMPLICITLYHIDDENINCLUDE

    db2 load from delfile1 of del insert into table1
    

    If table1 has implicitly hidden columns, the load utility assumes that the data for implicitly hidden columns is in the input file and attempts to load it.

• Operating system: All
• Default: Not set (but Db2 will still try to access the Knowledge Center from the IBM website), Values: http://hostname where hostname= valid host name or IP address
• Specifies the host name on which the Db2 Knowledge Center is installed. This variable can be automatically set during the installation of the Db2 Knowledge 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.

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

• Operating system: All
• Default: NULL
• This variable specifies multiple path and name pairs for the db2dsdriver.cfg configuration file. If you specify only a path for a path and name pair, the file name defaults to a value of db2dsdriver.cfg. If you specify only a file name for a pair, the path defaults to a location that is based on your operating system and driver type. You can specify any file extension, including .cfg. For details, see the db2dsdriver.cfg configuration file location.

  A single path name pair can have a maximum of 255 characters. The maximum number of characters for the environment variable value depends on the operating system.

• Use the following delimiter characters to distinguish between each path and name pair:
  • On Windows operating systems, use the semicolon (;).
  • On Linux and UNIX operating systems, use either a semicolon (;) or a colon (:). You cannot use the semicolon character and the colon character together as a delimiter.
  Do not use the delimiter character in the directory name.
• The directories are searched sequentially from left to right in the order that you specify for the value of the DB2DSDRIVER_CFG_PATH variable.
• The period (.) specifies the current directory.

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

• Operating system: All
• Default: YES, Values: YES or NO
• This variable controls whether the Configuration Advisor is run automatically at database creation. You do not need to restart the instance after you change 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.

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

• Operating system: All
• Default: ON, Values: ON, OFF
• This registry variable enables reclaimable storage operations, such as ‘ALTER TABLESPACE ... REDUCE MAX’ and ‘ALTER TABLESPACE ... LOWER HIGH WATER MARK’, in Db2 pureScale environments. (These operations are always available in non-pureScale environments).
• Prior to Version 11.5, the default value is OFF. Starting in Version 11.5, the default value is ON.
• Changes to this variable do not require the database instance to be restarted, or the database to be reactivated.
• For additional details, see ../../com.ibm.db2.luw.admin.dbobj.doc/doc/c0055392.html.

• Operating system: All
• Default: 0 (no limit), Values: A value specified in KB/Kb/kb, MB/Mb/mb, or GB/Gb/gb; While there is no fixed upper limit for this variable, it is limited by the amount of available memory from the monitor heap.
• Important: This registry variable is deprecated in version 10.5 and might be removed in a future release. Setting this registry variable has no effect because of the changes that were made to the underlying event monitor infrastructure in Version 9.7.
• This registry variable specifies the maximum number of bytes that can be queued up waiting to be written to a particular event monitor. Once this limit is reached, agents attempting to send event monitor records will wait until the queue size drops below this threshold.
• 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.

• Operating system: All
• Default: Not set; Values:
  • ALL: Indicates that the output for all statement event monitors is to be filtered. This option is exclusive.
  • 'nameA nameB nameC': Where each name in the string represents the name of an event monitor for which records are to be filtered. If more than one name is supplied, each name must be separated by a single blank. All input names will be made uppercase by Db2. The maximum number of event monitors you can specify is 128. Each monitor name can be up to a maximum of 128 characters long.
  • 'nameA:op1,op2 nameB:op1,op2 nameC:op1': Where each name in the string represents the name of an event monitor for which records are to be filtered. Each option (op1, op2, etc) represents an integer value mapping to a specific SQL operation. Specifying integer values allows users to determine which rules to apply to which event monitor.
• DB2_EVMON_STMT_FILTER can be used to reduce the number of records written by a statement event monitor. When set, this registry variable causes only the records for the following SQL operations to be written to the specified event monitor:

  Table 1. Values to use for DB2_EVMON_STMT_FILTER to restrict event monitor output to specific SQL operations

  SQL operation	Integer value mapping
  EXECUTE	2
  EXECUTE_IMMEDIATE	3
  CLOSE	6
  STATIC COMMIT	8
  STATIC ROLLBACK	9

  All other operations will not appear in the output of the statement event monitor. To customize the set of operations for which records are written to the event monitor, use integer values.

  Example 1:

  db2set DB2_EVMON_STMT_FILTER= 'mon1 monitor3'

  In this example, mon1 and monitor3 event monitors will receive a record for a restricted list of application requests. For example, if an application being monitored by the mon1 statement event monitor prepares a dynamic SQL statement, opens a cursor based on that statement, fetches 10,000 rows from that cursor, and then issues a cursor close request, only a record for a close request will appear in the mon1 event monitor output.
  Example 2:

  db2set DB2_EVMON_STMT_FILTER='evmon1:3,8 evmon2:5,9'

  In this example, evmon1 and evmon2 will receive a record for a restricted list of application requests. For example, if an application being monitored by the evmon1 statement event monitor issues a create statement , only the execute immediate and static commit operations will appear in the evmon1 event monitor output. If an application being monitored by the evmon2 statement event monitor performs SQL involving both a fetch and a static rollback only these two operations will appear in the evmon2 event monitor output.

• Operating system: Windows
• Default: YES, Values: YES or NO
• Prevents unauthorized access to Db2 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 server products and NO on clients.

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

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

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

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

• Operating system: All
• Default: OFF, Values: ON |OFF
• Specifies whether to bypass IP check for HADR connections
• This variable is primarily used in Network Address Translation (NAT) environments to bypass IP cross check for HADR connections. Use of this variable is not recommended in other environments because it weakens the sanity check of the HADR configuration. By default, configuration consistency for the local and remote host parameters is verified when an HADR connection is established. Host names are mapped to IP addresses for the cross check. Two checks are performed:
  • HADR_LOCAL_HOST parameter on primary = HADR_REMOTE_HOST parameter on standby
  • HADR_REMOTE_HOST parameter on primary = HADR_LOCAL_HOST parameter on standby
  The connection will be closed if the check fails.

  When this parameter is turned on, no IP check occurs.

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

• Operating system: All
• Default: 1, Values: 0-2
• For databases that are configured with High Availability Disaster Recovery (HADR) and the Reads on Standby (ROS) feature, this variable controls the reporting of the replay-only window to the Db2 diagnostic log (db2diag.log). These diagnostic messages are useful for you to determine which operations on the Primary database are causing replay-only windows to be activated on the Standby database(s). More information about the replay-only window is available in Knowledge Center topic: Replay-only window on the active standby database.
  The following values are possible:
  • 0: Diagnostic messages only report the start and end of the replay-only window. There is no reporting of the operation that causes the activation of the replay-only window.
  • 1: Additional diagnostic messages report the operation that causes the replay-only window. Only the first operation in each replay only window is reported.
  • 2: Additional diagnostic messages report all the operations that can cause the replay-only window.
• When the LOG_DDL_STMTS database configuration parameter is set to YES, the DDL statement text is included in these diagnostic message(s).
• This variable is available in Version 11.1.3.3 and newer releases.
• Changes to this variable do not require the database instance to be restarted.

• 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.
• In versions prior to 11.1.4.4, a Db2 instance restart is required for changes to this variable to take effect. In Version 11.1.4.4 or newer releases, changes to this variable will take effect after a deactivate and activate of the standby database.

• Operating system: All
• Default=OFF Values: ON | OFF
• When the HADR Reads on Standby feature is enabled (ie. DB2_HADR_ROS registry variable is set to ON), this variable controls the behavior of the standby database replay-only window. When set to OFF, the standby database will enter replay-only window when DDL or maintenance log-records are replayed, and all application connections on the standby database will be forced off. When set to ON, the standby database will not enter replay-only window when DDL or maintenance log-records are replayed. Instead, only application connections on the standby database which hold locks on tables, indexes or other objects that conflict with the DDL or maintenance operation to be replayed will be forced off. For more information on replay-only window and avoidance, see Replay-only window (and replay-only window avoidance) on the active standby database.
• This variable is available in Version 11.1.4.4 and newer releases.
• Changes to this variable will take effect after deactivate and activate of the standby database.

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

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

• 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

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

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

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

• 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

• Operating system: All
• Default:Null, Values: base_domain_name[:port_number[:SSL]], for example, base_domain_name:636:SSL when using an SSL enabled LDAP host
• Specifies the host name, optional port number and optional connection type of the location for the LDAP directory where base_domain_name is the TCP/IP host name, [:port_number] is the port number, and [:SSL] indicates usage of an encrypted connection.
• 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.

• Operating system: All
• Default: YES, Values: YES or NO
• Specifies whether Db2 caches its internal LDAP connection handles. When this variable is set to NO, Db2 will not cache its LDAP connection handles to the directory server. This will likely result in a negative performance impact, but it might be desirable to set DB2LDAP_KEEP_CONNECTION to NO if the number of simultaneously active LDAP client connections to the directory server needs to be minimized.

  To maximize performance, this variable is set to YES by default.

  The DB2LDAP_KEEP_CONNECTION registry variable is only implemented as a global level profile registry variable in LDAP, so you must set it by specifying the -gl option with the db2set command as follows:

   db2set -gl DB2LDAP_KEEP_CONNECTION=NO

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

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

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

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

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

• Operating system: All
• Default: NO, Values: YES or NO
• The DB2_MAP_XML_AS_CLOB_FOR_DLC registry variable provides the ability to override the default DESCRIBE and FETCH behavior of XML values for clients (or DRDA Application Requestors) that do not support XML as a data type. The default value is NO, which specifies that for these clients a DESCRIBE of XML values will return BLOB(2GB), and a FETCH of XML values will result in an implicit XML serialization to BLOB that includes an XML declaration indicating an encoding of UTF-8.

  When the value is YES, a DESCRIBE of XML values will return CLOB(2GB), and a FETCH of XML values will result in an implicit XML serialization to CLOB that does not contain an XML declaration.

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

• Operating system: AIX with storage key support
• Default: NO, Values: NO or YES
• This registry variable enables a memory protection feature that uses storage keys to prevent data corruption in the buffer pool caused by invalid memory access. Memory protection works by identifying at which times the Db2 engine threads should have access to the buffer pool memory and at which times they should not have access. When DB2_MEMORY_PROTECT is set to YES, any time a Db2 engine thread tries to illegally access buffer pool memory, that engine thread traps.
  Note: You will not be able to use the memory protection if DB2_LGPAGE_BP is set to YES. Even if DB2_MEMORY_PROTECT is set to YES, Db2 will fail to protect the buffer pool memory and disable the feature.

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

• Operating system: All
• Default: ON, Values: ON or OFF
• When this variable is set to ON (the default), the NCHAR, NVARCHAR and NCLOB spellings for the graphic data types are available for use in Unicode databases. Various national character related functions such as NCHAR() and TO_NCHAR() are also available.

  This variable should only be set to OFF if an existing database has user defined types named NCHAR, NVARCHAR, or NCLOB.

  Important: This registry variable is deprecated in version 10.5 and might be removed in a future release. You no longer need to use user defined types called NCHAR, NVARCHAR or NCLOB because all the character length sensitive functions now return the number of characters. Start using the nchar_mapping database configuration parameter to specify the data type mapping for national character string data types in your Unicode databases before this registry variable is discontinued. For more details, see nchar_mapping - National character mapping configuration parameter.

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

• Operating system: Linux and UNIX
• Default: 3, Values: 1[:FORK][:NORESPAWN] to 100[:FORK][:NORESPAWN]
• 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.

  In Db2 11.1.4.5 and later, the check password daemons will automatically respawn if they crash or are closed abnormally. An optional NORESPAWN parameter was introduced with 11.1.4.5 which can be added to prevent the check password daemons from respawning.

  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.

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

db2set DB2_OPTSTATS_LOG=ON,NUM=6,SIZE=25,NAME=mystatslog,DIR=mystats

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

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

• Operating system: All
• Default: FALSE, Values: TRUE or FALSE
• Specifies whether the dynamic data format feature, also known as progressive streaming should be disabled. When DB2_RESTRICT_DDF is set to TRUE, the server informs the JDBC driver that the dynamic data format feature is to be disabled.

  In SAP environments, when DB2_WORKLOAD=SAP is set, the default value of this registry variable is TRUE.

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

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

db2set DB2_SAS_SETTINGS=ENABLE_SAS_EP:TRUE;
  LIBRARY_PATH:/home/instowner/sqllib/function/sas

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

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

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

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

• 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.
• In versions prior to11.1.4.4, a Db2 instance restart is required for changes to this variable to take effect. In Version 11.1.4.4 or newer releases, changes to this variable will take effect after a deactivate and activate of the standby database.

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

• Operating system: All
• Default: NO (YES when DB2_WORKLOAD=SAP), Values: YES, NO
• This variable specifies whether lock attributes that are used for lock avoidance can be synchronously removed during commit processing before any of the locks are released. This is to ensure a query (with currently committed or skip inserted feature) running during the processing of a commit would wait for these locks (instead of skipping the data) and be able to see all the data that is changed as part of the transaction that is committing.
• This variable affects row organized tables only.
• This variable is available in Version 11.1.3.3 and newer releases.
• Changes to this variable do not require the database instance to be restarted.

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

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

• 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

• 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.
  • NULL or NONE: No URI references are permitted, and the transformation with such a stylesheet fails.
  • ALL: All references to URIs are allowed.
    Note: Uncontrolled reference to an external URI might be a severe security issue.
  • List of URIs: Only references to URIs that are located in subdirectories of the URIs from the list are allowed, as shown in the following example:

    db2set DB2_XSLT_ALLOWED_PATH = http://some.website.com/test/dir   /home/Joe/resource.txt