Administration Guide

Database Parameters

Parameters for an individual database are stored in a configuration file named SQLDBCON. 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. (For more information about the location of this directory, refer to "Database Physical Directories" in the Administration Guide, Design and Implementation.) 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.

The SQLDBCON file cannot be directly edited, and can only be changed or viewed via a supplied API or by a tool which calls that API.

Attention: If you edit the file using a method other than those provided by DB2, you may make the database unusable. We strongly recommend that you do not change this file using methods other than those documented and supported by DB2.

You may use one of the following three methods to reset, update, and view the database configuration parameters:

• Using the Control Center. The DB2 Control Center provides both the Configure Database notebook and the Performance Configuration SmartGuide to alter the value of configuration parameters. This SmartGuide generates values to parameters based on the responses you provide to a set of questions, such as the workload and the type of transactions that run against the database. See the online help available with the Control Centerfor information on using these interfaces.

  In a partitioned database environment, the SQLDBCON file exists for each database partition. In this environment, the Configure Database notebook updates the configuration for individual database partitions. If you want to have all the database partitions (or a subset of them) share the same database configuration values, you can:

  • Use the Configure Database notebook to update each database partition configuration file separately.

  • Use the db2_all command with a script to update the database configuration files. For information about the db2_all command, refer to the description of how to issue a command to multiple database partition servers in the Administration Guide, Design and Implementation.

  • Use an application to update multiple database configuration files.

  Note:

  The Performance Configuration SmartGuide is not available in the partitioned database environment.

• Using the command line processor. Commands to change the settings can be quickly and conveniently entered. Refer to the Command Reference for more information about the following commands:

  • GET DATABASE CONFIGURATION (or GET DB CFG)

  • UPDATE DATABASE CONFIGURATION (or UPDATE DB CFG)

  • RESET DATABASE CONFIGURATION (or RESET DB CFG)

• Using the application programming interfaces (APIs). The APIs can easily be called from a host-language program. Refer to the Administrative API Reference for more information.

Updates to most changeable parameters will not take effect while applications are connected to the database. All applications must first disconnect from the database. (If the database was activated, then it must be deactivated and reactivated.) Then, at the first new connect to the database, the changes will take effect. You should note that some parameter changes, such as newlogpath, logfilsiz and logprimary, may take a noticeable amount of time to take effect due to the overhead associated with allocating space. You may wish to make a test connection to the database so the change will be made at the time of the test connection and any overhead will not affect other users. If you are concerned about the overhead as discussed here, consider using the ACTIVATE DATABASE command as described in the Command Reference.

Note:

You do not need to disconnect from the database if you update the value of the mincommit parameter; this parameter is updated automatically when you change its value.

Changing some database configuration parameters can influence the access plan chosen by the SQL optimizer. These database parameters are discussed in Configuration Parameters Affecting Query Optimization. After changing any of the parameters discussed there, you should consider rebinding your applications to ensure the best access plan is being used for your SQL statements.

While new parameter values may not be immediately effective, viewing the parameter settings will always show the latest updates.

Note:

A number of database 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 books. To clarify what may be confusing, "Yes" should be considered equivalent to "On" and "No" should be considered equivalent to "Off".

[ DB2 List of Books | Search the DB2 Books ]