DB2 Version 9.7 for Linux, UNIX, and Windows

DB2 database configuration parameters

Database configuration parameters specify, among other things, the amount of resources to allocate to a database. Values for many of the parameters can be changed to improve performance or increase capacity. Some parameters are for informational purposes only. This type of parameter affects DB2® at the database level.

alt_collate - Alternate collating sequence configuration parameter
This parameter specifies the collating sequence that is to be used for Unicode tables in a non-Unicode database.
app_ctl_heap_sz - Application control heap size configuration parameter
This parameter has been deprecated since Version 9.5, but is still being used by pre-Version 9.5 data servers and clients. Any value specified for this configuration parameter is ignored by the database manager in DB2 Version 9.5 or later releases.
appgroup_mem_sz - Maximum size of application group memory set configuration parameter
In Version 9.5, appgroup_mem_sz has been replaced by the appl_memory configuration parameter.
appl_memory - Application Memory configuration parameter
This parameter allows DBAs and ISVs to control the maximum amount of application memory that is allocated by DB2 database agents to service application requests.
applheapsz - Application heap size configuration parameter
In previous releases, the applheapsz database configuration parameter referred to the amount of application memory each individual database agent working for that application could consume. Starting in Version 9.5, applheapsz refers to the total amount of application memory that can be consumed by the entire application.
archretrydelay - Archive retry delay on error configuration parameter
This parameter specifies the number of seconds to wait after a failed archive attempt before trying to archive the log file again.
auto_del_rec_obj - Automated deletion of recovery objects configuration parameter
This parameter specifies whether database log files, backup images, and load copy images should be deleted when their associated recovery history file entry is pruned.
auto_maint - Automatic maintenance configuration parameter
This parameter is the parent of all the other automatic maintenance database configuration parameters (auto_db_backup, auto_tbl_maint, auto_runstats, auto_stats_prof, auto_stmt_stats, auto_prof_upd, and auto_reorg).
auto_reval - Automatic revalidation and invalidation configuration parameter
This configuration parameter controls the revalidation and invalidation semantics.
autorestart - Auto restart enable configuration parameter
This parameter determines whether the database manager can, in the event of an abnormal termination of the database, automatically call the restart database utility when an application connects to a database.
avg_appls - Average number of active applications configuration parameter
This parameter is used by the query optimizer to help estimate how much buffer pool space will be available at run time for the access plan chosen.
backup_pending - Backup pending indicator configuration parameter
This parameter indicates whether you need to do a full backup of the database before accessing it.
blk_log_dsk_ful - Block on log disk full configuration parameter
This parameter can be set to prevent disk full errors from being generated when DB2 cannot create a new log file in the active log path.
blocknonlogged - Block creation of tables that allow non-logged activity configuration parameter
This parameter specifies whether the database manager will allow tables to have the NOT LOGGED or NOT LOGGED INITIALLY attributes activated.
catalogcache_sz - Catalog cache size configuration parameter
This parameter specifies the maximum space in pages that the catalog cache can use from the database heap.
chngpgs_thresh - Changed pages threshold configuration parameter
This parameter specifies the level (percentage) of changed pages at which the asynchronous page cleaners will be started, if they are not currently active.
codepage - Code page for the database configuration parameter
This parameter shows the code page that was used to create the database. The codepage parameter is derived based on the codeset parameter.
codeset - Codeset for the database configuration parameter
This parameter shows the codeset that was used to create the database. Codeset is used by the database manager to determine codepage parameter values.
collate_info - Collating information configuration parameter
This parameter determines the database's collating sequence. For a language-aware collation, the first 256 bytes contain the string representation of the collation name (for example, "SYSTEM_819_US").
connect_proc - Connect procedure name database configuration parameter
This database configuration parameter allows you to input or update a two-part connect procedure name that will be executed every time an application connects to the database.
country/region - Database territory code configuration parameter
This parameter shows the territory code used to create the database.
cur_commit - Currently committed configuration parameter
This parameter controls the behavior of cursor stability (CS) scans.
database_consistent - Database is consistent configuration parameter
This parameter indicates whether the database is in a consistent state.
database_level - Database release level configuration parameter
This parameter indicates the release level of the database manager which can use the database.
database_memory - Database shared memory size configuration parameter
The database_memory configuration parameter specifies the size of the database memory set. The database memory size counts towards any instance memory limit in effect.
dbheap - Database heap configuration parameter
You can use this parameter to limit the maximum amount of memory allocated for the database heap. Additional memory is automatically added for critical memory requirements.
db_mem_thresh - Database memory threshold configuration parameter
This parameter represents the maximum percentage of committed, but currently unused, database shared memory that the database manager will allow before starting to release committed pages of memory back to the operating system.
date_compat - Date compatibility database configuration parameter
This parameter indicates whether the DATE compatibility semantics associated with the TIMESTAMP(0) data type are applied to the connected database.
dec_to_char_fmt - Decimal to character function configuration parameter
This parameter is used to control the result of the CHAR scalar function and the CAST specification for converting decimal to character values.
decflt_rounding - Decimal floating point rounding configuration parameter
This parameter allows you to specify the rounding mode for decimal floating point (DECFLOAT). The rounding mode affects decimal floating-point operations in the server, and in LOAD.
dft_degree - Default degree configuration parameter
This parameter specifies the default value for the CURRENT DEGREE special register and the DEGREE bind option.
dft_extent_sz - Default extent size of table spaces configuration parameter
This parameter sets the default extent size of table spaces.
dft_loadrec_ses - Default number of load recovery sessions configuration parameter
This parameter specifies the default number of sessions that will be used during the recovery of a table load.
dft_mttb_types - Default maintained table types for optimization configuration parameter
This parameter specifies the default value for the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register. The value of this register determines what types of refresh deferred materialized query tables will be used during query optimization.
dft_prefetch_sz - Default prefetch size configuration parameter
This parameter sets the default prefetch size of table spaces.
dft_queryopt - Default query optimization class configuration parameter
This parameter provides additional flexibility by setting the default query optimization class used when neither the SET CURRENT QUERY OPTIMIZATION statement nor the QUERYOPT option on the bind command are used.
dft_refresh_age - Default refresh age configuration parameter
This parameter represents the maximum time duration since a REFRESH TABLE statement has been processed on a specific REFRESH DEFERRED materialized query table. After this time limit is exceeded, the materialized query table is not used to satisfy queries until the materialized query table is refreshed.
dft_sqlmathwarn - Continue upon arithmetic exceptions configuration parameter
This parameter sets the default value that determines the handling of arithmetic errors and retrieval conversion errors as errors or warnings during SQL statement compilation.
discover_db - Discover database configuration parameter
You can use this parameter to prevent information about a database from being returned to a client when a discovery request is received at the server.
dlchktime - Time interval for checking deadlock configuration parameter
This parameter defines the frequency at which the database manager checks for deadlocks among all the applications connected to a database.
dyn_query_mgmt - Dynamic SQL and XQuery query management configuration parameter
This parameter determines whether Query Patroller will capture information about submitted queries.
enable_xmlchar - Enable conversion to XML configuration parameter
This parameter determines whether XMLPARSE operations can be performed on non-BIT DATA CHAR (or CHAR-type) expressions in an SQL statement.
failarchpath - Failover log archive path configuration parameter
This parameter specifies a path to which DB2 will try to archive log files if the log files cannot be archived to either the primary or the secondary (if set) archive destinations because of a media problem affecting those destinations. This specified path must reference a disk.
groupheap_ratio - Percent of memory for application group heap configuration parameter
This parameter has been deprecated since Version 9.5, but is still being used by pre-Version 9.5 data servers and clients. Any value specified for this configuration parameter will be ignored by the database manager in DB2 Version 9.5 or later releases.
hadr_db_role - HADR database role configuration parameter
This parameter indicates the current role of a database, whether the database is online or offline.
hadr_local_host - HADR local host name configuration parameter
This parameter specifies the local host for high availability disaster recovery (HADR) TCP communication.
hadr_local_svc - HADR local service name configuration parameter
This parameter specifies the TCP service name or port number for which the local high availability disaster recovery (HADR) process accepts connections.
hadr_peer_window - HADR peer window configuration parameter
When you set hadr_peer_window to a non-zero time value, then a HADR primary-standby database pair continues to behave as though still in peer state, for the configured amount of time, if the primary database loses connection with the standby database. This helps ensure data consistency.
hadr_remote_host - HADR remote host name configuration parameter
This parameter specifies the TCP/IP host name or IP address of the remote high availability disaster recovery (HADR) database server.
hadr_remote_inst - HADR instance name of the remote server configuration parameter
This parameter specifies the instance name of the remote server. Administration tools, such as the DB2 Control Center, use this parameter to contact the remote server. High availability disaster recovery (HADR) also checks whether a remote database requesting a connection belongs to the declared remote instance.
hadr_remote_svc - HADR remote service name configuration parameter
This parameter specifies the TCP service name or port number that will be used by the remote high availability disaster recovery (HADR) database server.
hadr_syncmode - HADR synchronization mode for log write in peer state configuration parameter
This parameter specifies the synchronization mode, which determines how primary log writes are synchronized with the standby when the systems are in peer state.
hadr_timeout - HADR timeout value configuration parameter
This parameter specifies the time (in seconds) that the high availability disaster recovery (HADR) process waits before considering a communication attempt to have failed.
indexrec - Index re-creation time configuration parameter
This parameter indicates when the database manager attempts to rebuild invalid indexes, and whether or not any index build is redone during rollforward or high availability disaster recovery (HADR) log replay on the standby database.
jdk_64_path - 64-Bit Software Developer's Kit for Java installation path DAS configuration parameter
This parameter specifies the directory under which the 64-Bit Software Developer's Kit (SDK) for Java™, to be used for running DB2 administration server functions, is installed.
locklist - Maximum storage for lock list configuration parameter
This parameter indicates the amount of storage that is allocated to the lock list. There is one lock list per database and it contains the locks held by all applications concurrently connected to the database.
locktimeout - Lock timeout configuration parameter
This parameter specifies the number of seconds that an application will wait to obtain a lock, helping avoid global deadlocks for applications.
log_retain_status - Log retain status indicator configuration parameter
If set (when the logretain parameter setting is equal to Recovery), this parameter indicates that log files are being retained for use in rollforward recovery.
logarchmeth1 - Primary log archive method configuration parameter
You can use this parameter to specify the media type of the primary destination for archived logs.
logarchmeth2 - Secondary log archive method configuration parameter
This parameter specifies the media type of the secondary destination for archived logs.
logarchopt1 - Primary log archive options configuration parameter
This parameter specifies the options field for the primary destination for archived logs (if required).
logarchopt2 - Secondary log archive options configuration parameter
This parameter specifies the options field for the secondary destination for archived logs (if required).
logbufsz - Log buffer size configuration parameter
This parameter allows you to specify the amount of the database heap (defined by the dbheap parameter) to use as a buffer for log records before writing these records to disk.
logfilsiz - Size of log files configuration parameter
This parameter defines the size of each primary and secondary log file. The size of these log files limits the number of log records that can be written to them before they become full and a new log file is required.
loghead - First active log file configuration parameter
This parameter contains the name of the log file that is currently active.
logindexbuild - Log index pages created configuration parameter
This parameter specifies whether index creation, recreation, or reorganization operations are logged so that indexes can be reconstructed during DB2 rollforward operations or high availability disaster recovery (HADR) log replay procedures.
logpath - Location of log files configuration parameter
This parameter contains the current path being used for logging purposes.
logprimary - Number of primary log files configuration parameter
This parameter allows you to specify the number of primary log files to be preallocated. The primary log files establish a fixed amount of storage allocated to the recovery log files.
logretain - Log retain enable configuration parameter
Starting in Version 9.5, the logarchmeth1 configuration parameter is used to set the LOGRETAIN value.
logsecond - Number of secondary log files configuration parameter
This parameter specifies the number of secondary log files that are created and used for recovery log files (only as needed).
max_log - Maximum log per transaction configuration parameter
This parameter specifies if there is a limit to the percentage of log space that a transaction can consume, and what that limit is.
maxappls - Maximum number of active applications configuration parameter
This parameter specifies the maximum number of concurrent applications that can be connected (both local and remote) to a database. Since each application that attaches to a database causes some private memory to be allocated, allowing a larger number of concurrent applications will potentially use more memory.
maxfilop - Maximum database files open per database
This parameter specifies the maximum number of file handles that can be open per database. Each active application is counted towards the value specified by maxfilop.
maxlocks - Maximum percent of lock list before escalation configuration parameter
This parameter defines a percentage of the lock list held by an application that must be filled before the database manager performs lock escalation.
min_dec_div_3 - Decimal division scale to 3 configuration parameter
This parameter is provided as a quick way to enable a change to computation of the scale for decimal division in SQL.
mincommit - Number of commits to group configuration parameter
This parameter allows you to delay the writing of log records to disk until a minimum number of commits have been performed, helping reduce the database manager overhead associated with writing log records.
mirrorlogpath - Mirror log path configuration parameter
This parameter allows you to specify a string of up to 242 bytes for the mirror log path. The string must point to a path name, and it must be a fully qualified path name, not a relative path name.
mon_act_metrics - Monitoring activity metrics configuration parameter
This parameter controls the collection of activity metrics on the entire database and affects activities submitted by connections associated with any DB2 workload definitions.
mon_deadlock - Monitoring deadlock configuration parameter
This parameter controls the generation of deadlock events at the database level for the lock event monitor.
mon_locktimeout - Monitoring lock timeout configuration parameter
This parameter controls the generation of lock timeout events at the database level for the lock event monitor and affects all DB2 workload definitions.
mon_lockwait - Monitoring lock wait configuration parameter
This parameter controls the generation of lock wait events at the database level for the lock event monitor.
mon_lw_thresh - Monitoring lock wait threshold configuration parameter
This parameter controls the amount of time spent in lock wait before an event for mon_lockwait is generated.
mon_lck_msg_lvl - Monitoring lock event notification messages configuration parameter
This parameter controls the logging of messages to the administration notification log when lock timeout, deadlock, and lock escalation events occur.
mon_obj_metrics - Monitoring object metrics configuration parameter
This parameter controls the collection of data object metrics on the entire database.
mon_pkglist_sz - Monitoring package list size configuration parameter
This parameter controls the maximum number of entries that can appear in the package listing per unit of work as captured by the unit of work event monitor.
mon_req_metrics - Monitoring request metrics configuration parameter
This parameter controls the collection of request metrics on the entire database and affects requests executing in any DB2 service classes.
mon_uow_data - Monitoring unit of work events configuration parameter
This parameter controls the generation of unit of work events at the database level for the unit of work event monitor and affects units of work on the data server.
multipage_alloc - Multipage file allocation enabled configuration parameter
Multipage file allocation is used to improve insert performance. It applies to SMS table spaces only. If enabled, all SMS table spaces are affected: there is no selection possible for individual SMS table spaces.
newlogpath - Change the database log path configuration parameter
This parameter allows you to specify a string of up to 242 bytes to change the location where the log files are stored.
num_db_backups - Number of database backups configuration parameter
This parameter specifies the number of database backups to retain for a database.
num_freqvalues - Number of frequent values retained configuration parameter
This parameter allows you to specify the number of "most frequent values" that will be collected when the WITH DISTRIBUTION option is specified on the RUNSTATS command.
num_iocleaners - Number of asynchronous page cleaners configuration parameter
This parameter allows you to specify the number of asynchronous page cleaners for a database.
num_ioservers - Number of I/O servers configuration parameter
This parameter specifies the number of I/O servers for a database. No more than this number of I/Os for prefetching and utilities can be in progress for a database at any time.
num_log_span - Number log span configuration parameter
This parameter specifies whether there is a limit to how many log files one transaction can span, and what that limit is.
num_quantiles - Number of quantiles for columns configuration parameter
This parameter controls the number of quantiles that will be collected when the WITH DISTRIBUTION option is specified on the RUNSTATS command.
numarchretry - Number of retries on error configuration parameter
This parameter specifies the number of attempts that DB2 must make to archive a log file to the primary or the secondary archive directory before trying to archive log files to the failover directory.
numsegs - Default number of SMS containers configuration parameter
This parameter has been deprecated since Version 9.5, but is still being used by pre-Version 9.5 data servers and clients. Any value specified for this configuration parameter will be ignored by the database manager in DB2 Version 9.5 or later releases.
number_compat - Number compatibility database configuration parameter
This parameter indicates whether the compatibility semantics associated with the NUMBER data type are applied to the connected database.
overflowlogpath - Overflow log path configuration parameter
This parameter specifies a location for DB2 databases to find log files needed for a rollforward operation, as well as where to store active log files retrieved from the archive. It also gives a location for finding and storing log files needed for using db2ReadLog API.
pagesize - Database default page size
This parameter contains the value that was used as the default page size when the database was created. Possible values are: 4 096, 8 192, 16 384 and 32 768. When a buffer pool or table space is created in that database, the same default page size applies.
pckcachesz - Package cache size configuration parameter
This parameter is allocated out of the database shared memory, and is used for caching of sections for static and dynamic SQL and XQuery statements on a database.
priv_mem_thresh - Private memory threshold configuration parameter
This parameter has been deprecated since Version 9.5, but is still being used by pre-Version 9.5 data servers and clients. Any value specified for this configuration parameter will be ignored by the database manager in DB2 Version 9.5 or later releases.
rec_his_retentn - Recovery history retention period configuration parameter
This parameter specifies the number of days that historical information on backups are retained.
restore_pending - Restore pending configuration parameter
This parameter states whether a RESTORE PENDING status exists in the database.
restrict_access - Database has restricted access configuration parameter
This parameter indicates whether the database was created using the restrictive set of default actions. In other words, if it was created with the RESTRICTIVE clause in the CREATE DATABASE command.
rollfwd_pending - Roll forward pending indicator configuration parameter
This parameter informs you whether or not a rollforward recovery is required, and where it is required.
section_actuals - Section actuals configuration parameter
This parameter enables measurement of section actuals (runtime statistics measured during section execution).
self_tuning_mem- Self-tuning memory configuration parameter
This parameter determines whether the memory tuner will dynamically distribute available memory resources as required between memory consumers that are enabled for self-tuning.
seqdetect - Sequential detection flag configuration parameter
This parameter controls whether the database manager is allowed to detect sequential page reading during I/O activity.
sheapthres_shr - Sort heap threshold for shared sorts configuration parameter
This parameter represents a soft limit on the total amount of database shared memory that can be used by sort memory consumers at any one time.
smtp_server - SMTP server database configuration parameter
This parameter identifies a simple mail transfer protocol (SMTP) server. This SMTP server transmits email sent by the UTL_MAIL built-in module.
softmax - Recovery range and soft checkpoint interval configuration parameter
This parameter determines the frequency of soft checkpoints and the recovery range, which help out in the crash recovery process.
sortheap - Sort heap size configuration parameter
This parameter defines the maximum number of private memory pages to be used for private sorts, or the maximum number of shared memory pages to be used for shared sorts.
sql_ccflags - Conditional compilation flags configuration parameter
This parameter contains a list of conditional compilation values for use in conditional compilation of selected SQL statements.
stat_heap_sz - Statistics heap size configuration parameter
This parameter indicates the maximum size of the heap used in collecting statistics using the RUNSTATS command.
stmt_conc - Statement concentrator configuration parameter
This configuration parameter sets the default statement concentrator behavior.
stmtheap - Statement heap size configuration parameter
This parameter specifies the limit of the statement heap, which is used as a work space for the SQL or XQuery compiler during compilation of an SQL or XQuery statement.
territory - Database territory configuration parameter
This parameter shows the territory used to create the database. territory is used by the database manager when processing data that is territory sensitive.
trackmod - Track modified pages enable configuration parameter
This parameter specifies whether the database manager will track database modifications so that the backup utility can detect which subsets of the database pages must be examined by an incremental backup and potentially included in the backup image.
tsm_mgmtclass - Tivoli Storage Manager management class configuration parameter
The Tivoli® Storage Manager management class determines how the TSM server manages the backup versions of the objects being backed up.
tsm_nodename - Tivoli Storage Manager node name configuration parameter
This parameter is used to override the default setting for the node name associated with the Tivoli Storage Manager (TSM) product.
tsm_owner - Tivoli Storage Manager owner name configuration parameter
This parameter is used to override the default setting for the owner associated with the Tivoli Storage Manager (TSM) product.
tsm_password - Tivoli Storage Manager password configuration parameter
This parameter is used to override the default setting for the password associated with the Tivoli Storage Manager (TSM) product.
user_exit_status - User exit status indicator configuration parameter
If set to On, this parameter indicates that the database manager is enabled for rollforward recovery and that the user exit program will be used to archive and retrieve log files when called by the database manager.
userexit - User exit enable configuration parameter
Starting in Version 9.5, the logarchmeth1 configuration parameter is used to set the USEREXIT value.
util_heap_sz - Utility heap size configuration parameter
This parameter indicates the maximum amount of memory that can be used simultaneously by the BACKUP, RESTORE, and LOAD (including load recovery) utilities.
varchar2_compat - varchar2 compatibility database configuration parameter
This parameter indicates whether the compatibility semantics associated with the VARCHAR2 and NVARCHAR2 data types are applied to the connected database.
vendoropt - Vendor options configuration parameter
This parameter specifies additional parameters that DB2 might need to use to communicate with storage systems during backup, restore, or load copy operations.
wlm_collect_int - Workload management collection interval configuration parameter
This parameter specifies a collect and reset interval, in minutes, for workload management (WLM) statistics.

Parent topic: Configuration parameters