Understanding DB2: Learning Visually with Examples (2nd Edition)

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 Files\SQLLIB\BIN>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:\DB2\NODE0000\SQL00 009\SQLOGDIR\ 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

NOTE

If 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.

NOTE

Configurable 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 Files\SQLLIB\BIN>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 Files\SQLLIB\BIN>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 Files\SQLLIB\BIN>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.

NOTE

The 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.

NOTE

If 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.