Appendix E. Configuration Parameters That Can Be Changed Online

Configurable online configuration parameters take immediate effect without the need to stop and start the instance or deactivate and activate the database. You no longer have to disconnect users when you fine-tune your system, giving you more flexibility for deciding when to change the configuration.

Key database and database manager configuration parameters can be set online. For example, memory heaps such as CATALOGCACHE_SZ, PCKCACHE_SZ, STMTHEAP, SORTHEAP, and UTIL_HEAP_SZ are dynamic, allowing you to adjust memory usage as workloads vary over time. Other parameters, such as LOCKLIST, MAXLOCKS, and DLCHKTIME, will allow you to adjust the locking characteristics of your database system, which can improve performance.

You can choose to defer a change to a configurable online configuration parameter so that the configuration change will be made at the next instance start or database activation. A SHOW DETAILS option has been added to the GET DATABASE and GET DATABASE MANAGER CONFIGURATION commands that will list both the current value and the value that will be used at the next instance start or database activation.

In a few cases, you can set the parameter you are configuring to automatic, and DB2 will then adjust its value automatically as workload on the system changes. For example, setting MAXAPPLS to automatic says there is no limit to the maximum number of applications, except when memory is exhausted. The GET DATABASE and GET DATABASE MANAGER CONFIGURATION commands have been changed to indicate the configuration values set to automatic and their current values.

For some database manager configuration parameters, the database manager must be stopped (db2stop) and restarted (db2start) for the new parameter values to take effect.

For some database parameters, changes will take effect only when the database is reactivated. In these cases, all applications must first disconnect from the database. (If the database was activated, it must be deactivated and reactivated.) Then, at the first new connect to the database, the changes will take effect.

Other parameters can be changed online; these are called configurable online configuration parameters.

If you change the setting of a configurable online database manager configuration parameter while you are attached to an instance, the default behavior of the UPDATE DBM CFG command will be to apply the change immediately. If you do not want the change applied immediately, use the DEFERRED option on the UPDATE DBM CFG command.

For clients, changes to the database manager configuration parameters take effect the next time the client connects to a server.

If you change a configurable online database configuration parameter while connected, the default behavior is to apply the change online, wherever possible. You should note that some parameter changes may take a noticeable amount of time to take effect, due to the overhead associated with allocating space. To change configuration parameters online from the command line processor, a connection to the database is required.

Each configurable online configuration parameter has a propagation class associated with it. The propagation class indicates when you can expect a change to the configuration parameter to take effect. There are three propagation classes:

Immediate: Parameters that change immediately upon command or API invocation. For example, DIAGLEVEL has a propagation class of immediate.
Statement boundary: Parameters that change on statement and statement-like boundaries. For example, if you change the value of SORTHEAP, all new SQL requests will start using the new value.
Transaction boundary: Parameters that change on transaction boundaries. For example, a new value for DL_EXPINT is updated after a COMMIT statement.

Changing some database configuration parameters can influence the access plan chosen by the SQL optimizer. After changing any of these parameters, you should consider rebinding your applications to ensure the best access plan is being used for your SQL statements. Any parameters that were modified online (for example, by using the UPDATE DATABASE CONFIGURATION USING <parm> IMMEDIATE command) will cause the SQL optimizer to choose new access plans for new SQL statements. However, the SQL statement cache will not be purged of existing entries. To clear the contents of the SQL cache, use the FLUSH PACKAGE CACHE statement.

Although new parameter values may not be immediately effective, viewing the parameter settings (using GET DATABASE MANAGER CONFIGURATION or GET DATABASE CONFIGURATION commands) will always show the latest updates. Viewing the parameter settings using the SHOW DETAIL clause on these commands will show both the latest updates and the values in memory.

$ db2 attach to v8inst

$ db2 get dbm cfg show detail

The "Automatic" column in Tables E.1?E.3 indicates whether the parameter supports the AUTOMATIC keyword on the UPDATE DBM CFG or UPDATE DB CFG FOR <dbname> command. If you set a parameter to automatic, DB2 will automatically adjust the parameter to reflect current resource requirements for the instance or the database.

Table E.1. Configurable Database Manager Configuration Parameters
Parameter	Configurable Online	Automatic
catalog_noauth	Yes	No
comm_bandwidth	Yes	No
conn_elapse	Yes	No
cpuspeed	Yes	No
dft_account_str	Yes	No
dft_monswitches - dft_mon_bufpool - dft_mon_lock - dft_mon_sort - dft_mon_stmt - dft_mon_table - dft_mon_timestamp - dft_mon_uow	Yes	No
dftdbpath	Yes	No
diaglevel	Yes	No
diagpath	Yes	No
discover_inst	Yes	No
fcm_num_buffers	Yes	No
fed_noauth	Yes	No
health_mon	Yes	No
indexrec	Yes	No
instance_memory	No	Yes
max_connretries	Yes	No
max_querydegree	Yes	No
notifylevel	Yes	No
start_stop_time	Yes	No
use_sna_auth	Yes	No

Table E.2. Configurable Database Configuration Parameters
Parameter	Configurable Online	Automatic
autorestart	Yes	No
avg_appls	Yes	No
blk_log_dsk_ful	Yes	No
catalogcache_sz	Yes	No
database_memory	No	Yes
dbheap	Yes	No
dft_degree	Yes	No
dft_extent_sz	Yes	No
dft_loadrec_ses	Yes	No
dft_prefetch_sz	Yes	No
dft_queryopt	Yes	No
discover_db	Yes	No
dlchktime	Yes	No
dl_expint	Yes	No
dl_num_copies	Yes	No
dl_time_drop	Yes	No
dl_token	Yes	No
dl_upper	Yes	No
dl_wt_iexpint	Yes	No
indexrec	Yes	No
locklist	Yes	No
logsecond	Yes	No
maxappls	Yes	Yes
maxfilop	Yes	No
maxlocks	Yes	No
mincommit	Yes	No
num_db_backups	Yes	No
num_freqvalues	Yes	No
num_quantiles	Yes	No
pckcachesz	Yes	No
seqdetect	Yes	No
sortheap	Yes	No
stmtheap	Yes	No
tsm_mgmtclass	Yes	No
tsm_nodename	Yes	No
tsm_owner	Yes	No
tsm_password	Yes	No
util_heap_sz	Yes	No