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, see "Database Physical Directories".) 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.
In a partitioned database environment this file exists for each database partition. If you want to have all the database partitions (or a subset of them) share the same database configuration values, use db2_all. An alternative is to write a script or application to update the multiple database configuration files. Each of the configuration files would then have the same values.
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:
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 found 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 (including logretain and 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". |
The following table lists the parameters in the database configuration file. When changing the database configuration parameters, consider the detailed information for the parameter.
The column "Performance Impact" in the following table provides an indication of the relative importance of each parameter as it relates to system performance. It is impossible for this column to apply accurately to all environments; you should view this information as a generalization.
Table 48. Configurable Database Configuration Parameters
Table 49. Informational Database Configuration Parameters
Parameter | Additional Information |
---|---|
backup_pending | "Backup Pending Indicator (backup_pending)" |
codepage | "Code Page for the Database (codepage)" |
codeset | "Codeset for the Database (codeset)" |
collate_info | "Collating Information (collate_info)" |
country | "Country code for the Database (country)" |
database_consistent | "Database is Consistent (database_consistent)" |
database_level | "Database Release Level (database_level)" |
log_retain_status | "Log Retain Status Indicator (log_retain_status)" |
loghead | "Log Head Identification (loghead)" |
logpath | "Location of Log Files (logpath)" |
multipage_alloc | "MultiPage File Allocation Enabled (multipage_alloc)" |
nextactive | "Next Active Log (nextactive)" |
numsegs | "Default Number of SMS Containers (numsegs)" |
release | "Configuration File Release Level (release)" |
restore_pending | "Restore Pending (restore_pending)" |
rollfwd_pending | "Roll Forward Pending Indicator (rollfwd_pending)" |
territory | "Territory for the Database (territory)" |
user_exit_status | "User Exit Status Indicator (user_exit_status)" |