DB2 Version 9.7 for Linux, UNIX, and Windows

UPDATE DATABASE CONFIGURATION command

Modifies individual entries in a specific database configuration file. A database configuration file resides on every database partition on which the database has been created.

Scope

This command updates all database partitions by default, except when DBPARTITIONNUM is specified to update only one database partition.

Authorization

One of the following:

SYSADM
SYSCTRL
SYSMAINT

Required connection

Instance. An explicit attachment is not required, but a database connection is recommended when the database is active. If the database is listed as remote, an instance attachment to the remote node is established for the duration of the command. To change a parameter online, you must be connected to the database.

Command syntax


>>-UPDATE--+-DATABASE-+--+-CONFIGURATION-+---------------------->
           '-DB-------'  +-CONFIG--------+   
                         '-CFG-----------'   

>--+---------------------+-------------------------------------->
   '-FOR--database-alias-'   

>--+----------------------------------+------------------------->
   '-DBPARTITIONNUM--db-partition-num-'   

          .--------------------------------------.   
          V                                      |   
>--USING----config-keyword--+-value------------+-+-------------->
                            +-value--AUTOMATIC-+     
                            +-AUTOMATIC--------+     
                            '-MANUAL-----------'     

   .-IMMEDIATE-.   
>--+-----------+-----------------------------------------------><
   '-DEFERRED--'

Command parameters

AUTOMATIC

Some configuration parameters can be set to AUTOMATIC, allowing DB2® to automatically adjust these parameters to reflect the current resource requirements. For a list of configuration parameters that support the AUTOMATIC keyword, refer to the configuration parameters summary. If a value is specified along with the AUTOMATIC keyword, it might influence the automatic calculations. For specific details about this behavior, refer to the documentation for the configuration parameter.

DEFERRED

Make the changes only in the configuration file, so that the changes take effect the next time you reactivate the database.

FOR database-alias

Specifies the alias of the database whose configuration is to be updated. Specifying the database alias is not required when a database connection has already been established. You can update the configuration file for another database residing under the same database instance. For example, if you are connected only to database db11, and issue update db config for alias db22 using .... immediate:

If there is no active connection on db22, the update will be successful because only the configuration file needs to be updated. A new connection (which will activate the database) will see the new change in memory.
If there are active connections on db22 from other applications, the update will work on disk but not in memory. You will receive a warning saying that the database needs to be restarted.

DBPARTITIONNUM db-partition-num

If a database configuration update is to be applied to a specific database partition, this parameter may be used. If this parameter is not provided, the update will take effect on all database partitions.

IMMEDIATE

Make the changes immediately, while the database is running. IMMEDIATE is the default action, but it requires a database connection to be effective.

This is a default clause when operating in the CLPPlus interface as well. IMMEDIATE need not be called when using CLPPlus processor.

MANUAL

Disables automatic tuning for the configuration parameter. The parameter is set to its current internal value and is no longer updated automatically.

USING config-keyword value

config-keyword specifies the database configuration parameter to be updated. value specifies the value to be assigned to the parameter.

Examples

Update database configuration on partitioned database environment (multi-partition) instance

This example demonstrates how to update database configuration parameter MAXAPPLS from 10 to 50 for a database named SAMPLE.

In a partitioned database environment, a user has an instance that has 4 partitions as defined in the db2nodes.cfg:

10 gilera 0 
20 gilera 1 
30 motobi 0 
40 motobi 1

The user has created the SAMPLE database on the instance. The catalog partition for SAMPLE is on dbpartitionnum 10. Let's assume the user is logged on to system motobi.

Since the default behavior for an instance in a partitioned database environment is to update the database configurations on all database partitions, the following command issued by users will result in the same value for MAXAPPLS across all database partitions:

db2 update db cfg for sample using maxappls 50

To update MAXAPPLS only on dbpartitionnum 30, the following command may be issued:

db2 update db cfg for sample dbpartitionnum 30 using maxappls 50

Usage notes

To view or print a list of the database configuration parameters, use the GET DATABASE CONFIGURATION command.

To reset all the database configuration parameters to the recommended defaults, use the RESET DATABASE CONFIGURATION command.

To change a database configuration parameter, use the UPDATE DATABASE CONFIGURATION command. For example, to change the logging mode to "archival logging" on a single-partition database environment containing a database called ZELLMART, use:

db2 update db cfg for zellmart using logretain recovery

To check that the logretain configuration parameter has changed, use:

db2 get db cfg for zellmart

For example, to change the logging mode to "archival logging" on all partitions (provided the registry variable DB2_UPDDBCFG_SINGLE_DBPARTITION is set, by default, to NULL or FALSE) in a multiple-partitioned database environment containing a database called "zellmart", use:

db2 update db cfg for zellmart using logretain recovery

To check that the logretain configuration parameter has changed on all database partitions, use:

db2_all ";db2 get db cfg for zellmart"

Using the same example as above, but to update the logging mode to only one specific partition (30), use:

db2 update db cfg for zellmart dbpartitionnum 30 using logretain recovery

Optionally, you can leverage the SYSIBMADM.DBCFG view to get data from all partitions without having to use db2_all.

If you are working on a UNIX operating system, and you have the grep command, you can use the following command to view only the logretain values:

db2_all ";db2 get db cfg for zellmart | grep -i logretain"

For more information about DB2 configuration parameters and the values available for each type of database node, see the individual configuration parameter descriptions. The values of these parameters differ for each type of database node configured (server, client, or server with remote clients).

Not all parameters can be updated.

Some changes to the database configuration file become effective only after they are loaded into memory. All applications must disconnect from the database before this can occur. For more information on which parameters are configurable online and which ones are not, see summary list of configuration parameters.

For example, to change the sortheap database configuration parameter online for the SALES database, enter the following commands:

db2 connect to sales 
db2 update db cfg using sortheap 1000 
db2 connect reset

If an error occurs, the database configuration file does not change. The database configuration file cannot be updated if the checksum is invalid. This might occur if the database configuration file is changed without using the appropriate command. If this happens, the database must be restored to reset the database configuration file.