5.5. Configuring a Database
Database configuration and instance configuration are fairly similar. We will use the same format to describe database configuration as we used to discuss instance configuration earlier in this chapter. Database concepts are discussed in more detail in Chapter 7, Working with Database Objects.
A database is set up with a default configuration when you create it. You can view this configuration by running the get db cfg for database_name command. Figure 5.16 shows the output of this command on a Windows machine.
Figure 5.16. The contents of the database configuration file
C:Program FilesSQLLIBBIN>db2 get db cfg for sample
Database Configuration for Database
Database configuration release level = 0x0a00
Database release level = 0x0a00
Database territory = US
Database code page = 1252
Database code set = IBM-1252
Database country/region code = 1
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
Discovery support for this database (DISCOVER_DB) = ENABLE
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = YES
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = YES
Log retain for recovery status = NO
User exit for logging status = NO
Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0
Database heap (4KB) (DBHEAP) = 600
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Log buffer size (4KB) (LOGBUFSZ) = 8
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 250
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 50
Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 256
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 22
Lock timeout (sec) (LOCKTIMEOUT) = -1
Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
Number of I/O servers (NUM_IOSERVERS) = 3
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64
Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files = C:DB2NODE0000SQL00
009SQLOGDIR
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
First log archive method (LOGARCHMETH1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =
Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM (ACCESS)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
Automatic maintenance (AUTO_MAINT) = OFF
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = OFF
Automatic runstats (AUTO_RUNSTATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF
|
NOTEIf you are connected to a database, issuing the command get db cfg displays the contents of database configuration file; you don't need to specify the database name as part of the command. |
In this book you will learn some of the more important database configuration parameters. For a full treatment of all database configuration parameters, refer to the DB2 UDB Administration Guide: Performance.
To update one or more parameters in the database configuration file, issue the command:
update db cfg for database_name using parameter_name value parameter_name value...
For example, to update the CHNGPGS_THRESH database configuration parameter in the sample database to a value of 20, issue the command:
update db cfg for sample using CHNGPGS_THRESH 20
Issuing the get db cfg for database_name command after the update db cfg command shows the newly updated values. However, this does not mean the change will take effect right away. Several parameters in the database configuration file require all connections to be removed before the changes take effect on the first new connection to the database. For other parameters, the update is dynamic, and the new value takes effect immediately after executing the command; these are called configurable online parameters.
NOTEConfigurable online parameters of the database configuration file can be updated dynamically only if you first connect to the database. If a database connection has not been performed, the parameter will not be changed immediately, but after all connections are removed. |
Refer to the file ConfigurationParameters.pdf included on the CD-ROM accompanying this book for a list of database configuration parameters that are configurable online. The Control Center provides this information as well; refer to section 5.5.1, Configuring a Database from the Control Center, for details.
To get the current, effective setting for each configuration parameter along with the value of the parameter on the first new connection to the database after all connections are removed, use the show detail option of the get db cfg command. This option requires a database connection. If you run this command after changing the CHNGPGS_THRESH configuration parameter as above, you will see that the current value is 60, but the next effective or delayed value is 20. The related output from the get db cfg show detail command would look like the following:
C:Program FilesSQLLIBBIN>db2 get db cfg for sample show detail Description Parameter Current Value Delayed Value ------------------------------------------------------------------------------------- ... Changed pages threshold (CHNGPGS_THRESH) = 60 20
The show detail option is also helpful in determining the actual value of parameters listed as AUTOMATIC. For example, when you issue the get db cfg command while connected to a database, you may see output like the following for the MAXAPPLS parameter:
C:Program FilesSQLLIBBIN>db2 get db cfg ... Max number of active applications (MAXAPPLS) = AUTOMATIC
If you use the show detail option, the actual value is displayed:
C:Program FilesSQLLIBBIN>db2 get db cfg show detail Description Parameter Current Value Delayed Value ------------------------------------------------------------------------------------- ... Max number of active applications (MAXAPPLS) = AUTOMATIC(40) AUTOMATIC(40)
To reset all the database configuration parameters to their default values, use the command reset db cfg for database_name.
5.5.1. Configuring a Database from the Control Center
You can also configure a database from the Control Center. Figure 5.17 shows the Control Center with the database SAMPLE selected. When you right-click on the database a menu with several options appears.
Although the Control Center's database menu has Start and Stop options, as shown in Figure 5.17, these are used to start and stop the instance where the selected database resides. There are no explicit commands to stop and start a database. To “stop” a database, simply ensure that all connections to the database are removed. You can do this with the force applications command or by disconnecting each application. The first connection to a database “starts” the database. The commands activate database and deactivate database are also related to these concepts, although they are mainly used for performance reasons.
The activate database command activates a database by allocating all the necessary database memory and services or processes required. The first connection to the database normally performs these operations; therefore, by using the activate database command before connecting, the first connection no longer has to pay the price of this extra overhead. The deactivate database command does the opposite; it stops all services or processes needed by the database and releases the memory. A database can be considered “started” when it is activated and “stopped” when it is deactivated.
NOTEThe Restart command option in Figure 5.17 maps to the restart database command, which you can use for recovery purposes when a database was left in an inconsistent state after a crash recovery. Don't use this command if you only want the new value of a database configuration parameter that is not dynamic to take effect. Instead, use the force applications command or ensure all applications disconnect from the database. |
Figure 5.18 shows the Database Configuration window that appears after selecting Configure Parameters from the menu shown in Figure 5.17. In Figure 5.18 the column Pending Value Effective indicates when the pending value for the parameter will take effect, for example, immediately or after the database is “stopped” and “started”. The column Dynamic indicates whether the parameter is configurable online. The rest of the columns are self-explanatory.
Figure 5.18 also illustrates how you can update a database configuration parameter from the Control Center. After selecting the parameter DLCHKTIME and clicking on the three dots button (...), a pop-up window appears displaying the values that this parameter can accept.
5.5.2. The DB2 Commands at the Database Level
Table 5.4 summarizes the most common commands used to configure a database. For more information about database concepts, refer to Chapter 7, Working with Database Objects.
NOTEIf a DB2 registry variable, Database Manager Configuration parameter, or database configuration parameter accept only Boolean values, the values YES and ON and the values NO and OFF respectively are equivalent. |
| Command | Explanation |
|---|---|
| get db cfg | Displays the database configuration file. |
| update db cfg | Updates the database configuration file. |
| reset db cfg | Resets the database configuration file to its default values. |