Appendix E. Configuration Parameters That Can Be Changed Online

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

Table E.3. DAS Configuration Parameters

Parameter

Configurable Online

Automatic

contact_host

Yes

No

das_codepage

Yes

No

das_territory

Yes

No

db2system

Yes

No

discover

Yes

No

jdk_path

Yes

No

smtp_server

Yes

No