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:
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
Table E.2. Configurable Database Configuration Parameters
Table E.3. DAS Configuration Parameters