Learning Visually with Examples [Electronic resources] نسخه متنی

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\SQLOGDIROverflow 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

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.