Configuring the Db2 database manager with configuration parameters
The disk space and memory that is allocated by the database manager based on default values of the parameters might be sufficient to meet your needs. However, in some situations you might not be able to achieve maximum performance by using these default values.
About this task
- 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 you are using the default configuration. For this reason, you are 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. These tools 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 you are 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 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 that is 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 that are allocated to that database. The file also contains descriptive information, and flags that indicate the status of the database.
If you edit db2systm, SQLDBCON, or SQLDBCONF by using a method other than the methods provided by the database manager, you might make the database unusable. Do not change these files by using methods other than the methods 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. However, the recommendation is that in a homogeneous environment, the configuration parameter values must be the same on all database partitions. Typically, there might be a catalog node that needs different database configuration parameters setting, while the other data partitions have different values again, depending on their workstations types, and other information.
- Update configuration parameters.
- Using the command line processor
Commands to change the settings can be entered as follows:For database manager configuration parameters:
For database 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
- 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
- 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) proceduresYou 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 command line processor
- 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 take effect only 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 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 is to apply the change immediately. If you do not want the change to be applied immediately, use the DEFERRED option on the UPDATE DBM CFG command.To change a database manager configuration parameter online, use the following commands:
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. Some parameter changes might take a noticeable amount of time to take effect due to the additional processing time associated with allocating space. To change configuration parameters online from the command line processor, a connection to the database is needed. To change a database configuration parameter online, use the following commands:
db2 connect to dbname db2 update db cfg using parameter-name parameter-value db2 connect resetEach configurable online configuration parameter has a propagation class that is associated with it. The propagation class indicates when you can expect a change to the configuration parameter to take effect. The following is a list of four propagation classes:
- Immediate - Parameters that change immediately upon command or API invocation. For instance, diaglevel has a propagation class of immediate.
- Statement boundary - Parameters that change on statement and statement-like boundaries. For instance, if you change the value of sortheap, all new requests use the new value.
- Transaction boundary - Parameters that change on transaction boundaries. For instance, a new value for dl_expint is updated after a COMMIT statement.
- Connection - Parameters that change on new connection to the database. For instance, a new value for dft_degree takes effect for new applications that are connecting to the database.
While new parameter values might not be immediately effective, viewing the parameter settings (by using the GET DATABASE MANAGER CONFIGURATION or GET DATABASE CONFIGURATION command) always shows the latest updates. Viewing the parameter settings by using the SHOW DETAIL clause on these commands shows both the latest updates and the values in memory.
Rebind applications after you update the database configuration parameters.
Changing some database configuration parameters can influence the access plan that is chosen by the SQL and XQuery optimizer. After you change any of these parameters, consider rebinding your applications to ensure that 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) cause the SQL and XQuery optimizer to choose new access plans for new query statements. However, the query statement cache is not 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, health_mon) 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 must be considered equivalent to On and No must be considered equivalent to Off.