DB2 Version 9.7 for Linux, UNIX, and Windows

Configuring the DB2 database manager with configuration parameters

The disk space and memory allocated by the database manager on the basis of default values of the parameters might be sufficient to meet your needs. In some situations, however, you might not be able to achieve maximum performance using these default values.

About this task

Since the default values are oriented towards machines that have relatively small memory resources and are dedicated as database servers, you might need to modify these values if your environment has:

Large databases
Large numbers of connections
High performance requirements for a specific application
Unique query or transaction loads or types

Each transaction processing environment is unique in one or more aspects. These differences can have a profound impact on the performance of the database manager when using the default configuration. For this reason, you are strongly advised to tune your configuration for your environment.

A good starting point for tuning your configuration is to use the Configuration Advisor or the AUTOCONFIGURE command which will generate values for parameters based on your responses to questions about workload characteristics.

Some configuration parameters can be set to AUTOMATIC, allowing the database manager to automatically manage these parameters to reflect the current resource requirements. To turn off the AUTOMATIC setting of a configuration parameter while maintaining the current internal setting, use the MANUAL keyword with the UPDATE DATABASE CONFIGURATION command. If the database manager updates the value of these parameters, the GET DB CFG SHOW DETAIL and GET DBM CFG SHOW DETAIL commands will show the new value.

Parameters for an individual database are stored in a configuration file named SQLDBCONF. This file is stored along with other control files for the database in the SQLnnnnn directory, where nnnnn is a number assigned when the database was created. Each database has its own configuration file, and most of the parameters in the file specify the amount of resources allocated to that database. The file also contains descriptive information, as well as flags that indicate the status of the database.

Attention: If you edit db2systm, SQLDBCON, or SQLDBCONF using a method other than those provided by the database manager, you might make the database unusable. Do not change these files using methods other than those documented and supported by the database manager.

In a partitioned database environment, a separate SQLDBCONF file exists for each database partition. The values in the SQLDBCONF file might be the same or different at each database partition, but the recommendation is that in a homogeneous environment, the configuration parameter values should be the same on all database partitions. Typically, there could be a catalog node needing different database configuration parameters setting, while the other data partitions have different values again, depending on their machine types, and other information.

Note: You can update configuration parameters or see their values using IBM® Data Studio. For more information, follow the IBM Data Studio related link.

Procedure

Update configuration parameters.
- Using the command line processor:
  Commands to change the settings can be entered as follows:
  For database manager configuration parameters:
  - GET DATABASE MANAGER CONFIGURATION (or GET DBM CFG)
  - UPDATE DATABASE MANAGER CONFIGURATION (or UPDATE DBM CFG)
  - RESET DATABASE MANAGER CONFIGURATION (or RESET DBM CFG) to reset all database manager parameters to their default values
  - AUTOCONFIGURE
  For database configuration parameters:
  - GET DATABASE CONFIGURATION (or GET DB CFG)
  - UPDATE DATABASE CONFIGURATION (or UPDATE DB CFG)
  - RESET DATABASE CONFIGURATION (or RESET DB CFG) to reset all database parameters to their default values
  - AUTOCONFIGURE
- Using application programming interfaces (APIs):
  The APIs can be called from an application or a host-language program. Call the following DB2® APIs to view or update configuration parameters:
  - db2AutoConfig - Access the Configuration Advisor
  - db2CfgGet - Get the database manager or database configuration parameters
  - db2CfgSet - Set the database manager or database configuration parameters
- Using common SQL application programming interface (API) procedures:
  You can call the common SQL API procedures from an SQL-based application, a DB2 command line, or a command script. Call the following procedures to view or update configuration parameters:
  - GET_CONFIG - Get the database manager or database configuration parameters
  - SET_CONFIG - Set the database manager or database configuration parameters
- Using IBM Data Studio, right-click the instance to open the task assistant to update the database manager configuration parameters.
- Using the Configuration Assistant
  The Configuration Assistant can also be used to set the database manager configuration parameters on a client. Other parameters can be changed online; these are called configurable online configuration parameters.
View updated configuration values.
For some database manager configuration parameters, the database manager must be stopped (db2stop) and then restarted (db2start) for the new parameter values to take effect.

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

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.
To change a database manager configuration parameter online:
```
db2 attach to instance-name    
db2 update dbm cfg using parameter-name value    
db2 detach
```
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 might 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. To change a database configuration parameter online:
```
db2 connect to dbname    
db2 update db cfg using parameter-name parameter-value    
db2 connect reset
```
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 four 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 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.
- Connection: Parameters that change on new connection to the database. For example, a new value for dft_degree takes effect for new applications connecting to the database.
While new parameter values might not be immediately effective, viewing the parameter settings (using the GET DATABASE MANAGER CONFIGURATION or GET DATABASE CONFIGURATION command) 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.
Rebind applications after updating database configuration parameters.
Changing some database configuration parameters can influence the access plan chosen by the SQL and XQuery 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 and XQuery statements. Any parameters that were modified online (for example, by using the UPDATE DATABASE CONFIGURATION IMMEDIATE command) will cause the SQL and XQuery optimizer to choose new access plans for new query statements. However, the query statement cache will not be purged of existing entries. To clear the contents of the query cache, use the FLUSH PACKAGE CACHE statement.

Note: A number of configuration parameters (for example, userexit) are described as having acceptable values of either Yes or No, or On or Off in the help and other DB2 documentation. To clarify, Yes should be considered equivalent to On and No should be considered equivalent to Off.