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 DB2-provided security plugins always allow a local implicit connect because they assume that the user has been validated by the OS.
db2set DB2AUTH=DISABLE_CHGPASS,OSAUTHDB
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.
In Version 9.7 Fix Pack 5 and later fix packs, this registry variable is visible in the db2set command output but is not changeable. Any attempts to change the given registry value will result in a DBI1301E Invalid value error.
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.
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 portions of the Log Sequence Number (LSN) space. Each time an active log file is truncated, the LSN is incremented by an amount proportional to the space truncated. If you perform a large number of online backups each day, you might disable closing the last active log file.
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, because the active log file must be truncated and closed in order for the backup to include the logs.
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.
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: This variable is ignored unless the database manager parameter federated is set to YES.
The equivalent keyword that can be specified in the db2dsdriver.cfg file is EnableLDAP.
SQL operation | Integer value mapping |
---|---|
EXECUTE | 2 |
EXECUTE_IMMEDIATE | 3 |
OPEN | 4 |
FETCH | 5 |
CLOSE | 6 |
STATIC COMMIT | 8 |
STATIC ROLLBACK | 9 |
PRE_EXEC | 17 |
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.
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.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.If you are running a large number of fenced routines on your system, you may need to increase the value of this variable. If you are running a very small number of fenced routines, you can reduce it.
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) will also be allocated from the FMP heap. Similar guidelines for fenced routines apply when adjusting the heap 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 have specified a COMM_BUFFER_SZ value by setting the DB2_SAS_SETTINGS registry variable, the FMP heap memory requirements will increase by twice the value of COMM_BUFFER_SZ multiplied by the number of concurrent SAS queries that you want to support.
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.
When this parameter is turned on, no IP check occurs.
This parameter is static, so the instance needs to be restarted to make this parameter effective.
db2set DB2_HISTORY_FILTER=T,L
Possible
values for DB2_HISTORY_FILTER are: 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.
The equivalent keyword that can be specified in the db2dsdriver.cfg file is BaseDN.
To ensure that you have the latest entries in the cache, issue the following command::
REFRESH LDAP IMMEDIATE ALL
This command updates and removes incorrect entries from the database directory and the node directory.
db2set DB2LDAP_CLIENT_PROVIDER
The equivalent keyword that can be specified in the db2dsdriver.cfg file is ClientProvider.
The equivalent keyword that can be specified in the db2dsdriver.cfg file is LDAPServerHost.
To maximize performance, this variable is set to YES by default.
db2set -gl DB2LDAP_KEEP_CONNECTION=NO
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.
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.
This variable should only be set to OFF if an existing database has user defined types named NCHAR, NVARCHAR, or NCLOB.
When DB2NOEXITLIST is turned off and DB2_COMMIT_ON_EXIT is turned on, any in-flight transactions for embedded SQL applications are automatically committed. It is recommended to explicitly add COMMIT or ROLLBACK statements when an application exits.
Applications that dynamically load and unload the DB2 library before the application terminates might crash when calling the DB2 exit handler. This crash might happen because the application attempts to call a function that does not exist in memory. To avoid this situation, set the DB2NOEXITLIST registry variable.
DB2_NUM_CKPW_DAEMONS can be set to a value between 1 and 100. The database manager will create the number of daemons specified by DB2_NUM_CKPW_DAEMONS. Each daemon can handle check password requests directly.
An optional FORK parameter can be added to enable the check password daemons to explicitly spawn an external check password program (db2ckpw) to handle check password requests. This is similar to setting DB2_NUM_CKPW_DAEMONS to zero in previous releases. In FORK mode, each check password daemon will spawn the check password program for each request to check a password. The daemons in FORK mode are started as the instance owner.
If DB2_NUM_CKPW_DAEMONS is set to zero, the effective value is set to 3:FORK, where 3 check password daemons are started in FORK mode.
db2set DB2_OPTSTATS_LOG=ON,NUM=6,SIZE=25,NAME=mystatslog,DIR=mystats
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.
In SAP environments, when DB2_WORKLOAD=SAP is set, the default value of this registry variable is TRUE.
db2set DB2_SAS_SETTINGS="ENABLE_SAS_EP:TRUE;
LIBRARY_PATH:/home/instowner/sqllib/function/sas"
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.
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.
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.
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.
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.
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