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".) There is a single configuration file per database. Most of the parameters specify the amount of resources allocated to that database. In addition, there is descriptive information and flags indicating the status of the database.

In a partitioned database environment this file exists for each database on each database partition server, and specifies the database parameters for each node. If you want to have all the database partition servers (or a subset of them) share the same database configuration values, you should write a script or application to update the multiple database configuration files. Each of the configuration files should 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:

• 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 on-line help available with the Control Center for information on using these interfaces.

• Using the command line processor. Commands to change the settings can be quickly and conveniently entered. See 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. (See the API Reference for more information.)

Updates to any 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 deactived and reactived.) 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.

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 are not 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.

• High -- indicates the parameter can have a significant impact on performance. You should consciously decide the values of these parameters; which in some cases, will mean that you accept the default provided.
• Medium -- indicates the parameter can have some impact on performance. Your specific environment and needs will determine how much tuning effort should be focussed on these parameters.
• Low -- indicates that the parameter has a less general or less significant impact on performance.
• None -- indicates that the parameter does not directly impact performance. While you do not have to tune these parameters for performance, they can be very important for other aspects of your system configuration, such as enabling communication support.

Table 38. 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)"
autorestart	Low	"Auto Restart Enable (autorestart)"
avg_appls	High	"Average Number of Active Applications (avg_appls)"
buffpage	High	"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)"
dir_obj_name	None	"Object Name in DCE Namespace (dir_obj_name)"
dft_sqlmathwarn	None	"Continue upon Arithmetic Exceptions (dft_sqlmathwarn)"
discover_db	Medium	"Discover Database (discover_db)"
dlchktime	Medium	"Time Interval for Checking Deadlock (dlchktime)"
estore_seg_sz	Medium	"Extended Storage Memory Segment Size (estore_seg_sz)"
indexrec	Medium	"Index Re-creation Time (indexrec)"
indexsort	Low (see note)	"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 39. 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)"

[ DB2 List of Books | Search the DB2 Books ]