IBM Books

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, 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".

Database Configuration Parameter Summary

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
Parameter Performance Impact Additional Information
adsm_mgmtclass None "ADSTAR Distributed Storage Manager Management Class (adsm_mgmtclass)"
adsm_nodename None "ADSTAR Distributed Storage Manager Node Name (adsm_nodename)"
adsm_owner None "ADSTAR Distributed Storage Manager Owner Name (adsm_owner)"
adsm_password None "ADSTAR Distributed Storage Manager Password (adsm_password)"
app_ctl_heap_sz Medium "Application Control Heap Size (app_ctl_heap_sz)"
applheapsz Medium "Application Heap Size (applheapsz)"
audit_buf_sz Medium "Audit Buffer Size (audit_buf_sz)"
autorestart Low "Auto Restart Enable (autorestart)"
avg_appls High "Average Number of Active Applications (avg_appls)"
buffpage High (when active) "Buffer Pool Size (buffpage)"
catalogcache_sz Medium "Catalog Cache Size (catalogcache_sz)"
chngpgs_thresh High "Changed Pages Threshold (chngpgs_thresh)"
copyprotect None "Copy Protection Enable (copyprotect)"
dbheap Medium "Database Heap (dbheap)"
dft_degree High "Default Degree (dft_degree)"
dft_extent_sz Medium "Default Extent Size of Table Spaces (dft_extent_sz)"
dft_loadrec_ses Medium "Default Number of Load Recovery Sessions (dft_loadrec_ses)"
dft_prefetch_sz Medium "Default Prefetch Size (dft_prefetch_sz)"
dft_queryopt Medium "Default Query Optimization Class (dft_queryopt)"
dft_sqlmathwarn None "Continue upon Arithmetic Exceptions (dft_sqlmathwarn)"
dir_obj_name None "Object Name in DCE Namespace (dir_obj_name)"
discover_db Medium "Discover Database (discover_db)"
dlchktime Medium "Time Interval for Checking Deadlock (dlchktime)"
dl_expint None "DataLink Access Token Expiry Interval (dl_expint)"
dl_num_backup None "DataLink Number of Backups (dl_num_backup)"
dl_num_copies None "DataLink Number of Copies (dl_num_copies)"
dl_time_drop None "DataLink Time After Drop (dl_time_drop)"
estore_seg_sz Medium "Extended Storage Memory Segment Size (estore_seg_sz)"
indexrec Medium "Index Re-creation Time (indexrec)"
indexsort Low (see ***) "Index Sort Flag (indexsort)"
locklist High "Maximum Storage for Lock List (locklist)"
locktimeout Medium "Lock Timeout (locktimeout)"
logbufsz High "Log Buffer Size (logbufsz)"
logfilsiz Medium "Size of Log Files (logfilsiz)"
logprimary Medium "Number of Primary Log Files (logprimary)"
logretain Low "Log Retain Enable (logretain)"
logsecond Medium "Number of Secondary Log Files (logsecond)"
maxappls High "Maximum Number of Active Applications (maxappls)"
maxfilop Medium "Maximum Database Files Open per Application (maxfilop)"
maxlocks High "Maximum Percent of Lock List Before Escalation (maxlocks)"
mincommit High "Number of Commits to Group (mincommit)"
newlogpath Low "Change the Database Log Path (newlogpath)"
num_estore_segs Medium "Number of Extended Storage Memory Segments (num_estore_segs)"
num_freqvalues Low "Number of Frequent Values Retained (num_freqvalues)"
num_iocleaners High "Number of Asynchronous Page Cleaners (num_iocleaners)"
num_ioservers High "Number of I/O Servers (num_ioservers)"
num_quantiles Low "Number of Quantiles for Columns (num_quantiles)"
pckcachesz High "Package Cache Size (pckcachesz)"
rec_his_retentn None "Recovery History Retention Period (rec_his_retentn)"
seqdetect High "Sequential Detection Flag (seqdetect)"
softmax Medium "Recovery Range and Soft Checkpoint Interval (softmax)"
sortheap High "Sort Heap Size (sortheap)"
stat_heap_sz Low "Statistics Heap Size (stat_heap_sz)"
stmtheap Medium "Statement Heap Size (stmtheap)"
userexit Low "User Exit Enable (userexit)"
util_heap_sz Low "Utility Heap Size (util_heap_sz)"
Note: Changing the indexsort parameter to a value other than the default can have a negative impact on the performance of creating indexes. You should always try to use the default for this parameter.

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)"

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]