Command Reference

GET DATABASE CONFIGURATION

Returns the values of individual entries in a specific database configuration file.

Scope

This command returns information only for the node on which it is executed.

Authorization

None

Required Connection

Instance. An explicit attachment is not required. If the database is listed as remote, an instance attachment to the remote node is established for the duration of the command.

Command Syntax

>>-GET----+-DATABASE-+---+-CONFIGURATION-+--FOR-----------------> '-DB-------' +-CONFIG--------+ '-CFG-----------' >----database-alias--------------------------------------------><

Command Parameters

FOR database-alias: Specifies the alias of the database whose configuration is to be displayed.

Example

Notes:

Output on different platforms may show small variations reflecting platform-specific parameters.
Parameters with keywords enclosed by parentheses can be changed using UPDATE DATABASE CONFIGURATION.
Fields that do not contain keywords are maintained by the database manager and cannot be updated.

The following is sample output from GET DATABASE CONFIGURATION (issued on AIX):

       Database Configuration for Database sample
 
 Database configuration release level                    = 0x0800
 Database release level                                  = 0x0800
 
 Database territory                                      = C
 Database code page                                      = 819
 Database code set                                       = ISO8859-1
 Database country code                                   = 1
 
 Directory object name                    (DIR_OBJ_NAME) =
 Discovery support for this database       (DISCOVER_DB) = ENABLE
 
 Degree of parallelism                      (DFT_DEGREE) = 1
 Default query optimization class         (DFT_QUERYOPT) = 5
 Continue upon arithmetic exceptions   (DFT_SQLMATHWARN) = NO
 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                      = NO
 
 Log retain for recovery status                          = NO
 User exit for logging status                            = NO
 
 Datalink Access Token Expiry Interval (sec) (DL_EXPINT) = 1
 Datalink Number of Copies               (DL_NUM_COPIES) = 1
 Datalink Number of Backups              (DL_NUM_BACKUP) = 1
 Datalink Time after Drop (days)          (DL_TIME_DROP) = 1
 
 Database heap (4KB)                            (DBHEAP) = 1200
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 64
 Log buffer size (4KB)                        (LOGBUFSZ) = 8
 Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 5000
 Buffer pool size (4KB)                       (BUFFPAGE) = 1000
 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) = 100
 
 Max appl. control heap size (4KB)     (APP_CTL_HEAP_SZ) = 128
 
 Sort list heap (4KB)                         (SORTHEAP) = 256
 SQL statement heap (4KB)                     (STMTHEAP) = 2048
 Default application heap (4KB)             (APPLHEAPSZ) = 128
 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) = 10
 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 (4KB)           (DFT_PREFETCH_SZ) = 32
 
 Default number of containers                            = 1
 Default tablespace extentsize (4KB)     (DFT_EXTENT_SZ) = 32
 
 Max number of active applications            (MAXAPPLS) = 40
 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                                       = /home/user1/user1/NODE0000/SQL00001/SQLOGDIR/
 Next active log file                                    =
 First active log file                                   =
 
 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
 
 Auto restart enabled                      (AUTORESTART) = ON
 Index re-creation time                       (INDEXREC) = SYSTEM (RESTART)
 Default number of loadrec sessions    (DFT_LOADREC_SES) = 1
 Recovery history retention (days)     (REC_HIS_RETENTN) = 366
 
 ADSM management class                  (ADSM_MGMTCLASS) =
 ADSM node name                          (ADSM_NODENAME) =
 ADSM owner                                 (ADSM_OWNER) =
 ADSM password                           (ADSM_PASSWORD) =

These fields are identified below. Parameters whose name appears in lowercase are maintained by the database manager and cannot be updated. For more information about database configuration parameters, see the Administration Guide.

ADSM_MGMTCLASS

The ADSM management class specifies how the server should manage the backup versions or archive copies of the objects being backed up. The management class is assigned from the ADSM administrator. Once assigned, this parameter should be set to the management class name. When performing any ADSM backup, the database manager uses this parameter to pass the management class to ADSM.

ADSM_NODENAME

This parameter is used to override the default setting for the node name associated with the ADSM product. The node name is needed when restoring a database that was backed up to ADSM from another node.

ADSM_OWNER

This parameter is used to override the default setting for the owner associated with the ADSM product. The owner name is needed when restoring a database that was backed up to ADSM from another node.

ADSM_PASSWORD

This parameter is used to override the default setting for the password associated with the ADSM product. The password is needed when restoring a database that was backed up to ADSM from another node.

APP_CTL_HEAP_SZ

This parameter determines the maximum size, in 4KB pages, for the application control heap. The heap is required to share information among agents working on behalf of the same application at a node in an MPP or an SMP system. If complex applications are being run, or the MPP configuration has a large number of nodes, the size of this heap should be increased.

APPLHEAPSZ

Specifies the size, in pages, of the application heap that is available for each individual agent.

AUTORESTART

Indicates whether the database manager can automatically issue RESTART DATABASE on a connect if, for example, the database connection was disrupted, or the database was not terminated normally during the previous session.

OFF specifies that it must be done manually.

ON specifies that the database manager does it automatically.

AVG_APPLS

Average number of active applications. Used by the SQL optimizer to help estimate how much buffer pool will be available for the chosen access plan at run time.

backup_pending (Backup pending)

NO specifies that the database is in a usable state.

YES specifies that an offline backup must be performed before the database can be used.

BUFFPAGE

Specifies the size, in pages, of the buffer pool. The buffer pool is used to store and manipulate data read in from the database. This parameter is only used when a buffer pool's size has been explicitly set to -1 through either CREATE BUFFERPOOL, ALTER BUFFERPOOL, or MIGRATE DATABASE. The size of the buffer pool is normally controlled through SQL statements.

CATALOGCACHE_SZ

Controls the size, in pages, of the internal catalog cache (allocated from the dbheap), used by the SQL compiler to hold the packed descriptors for commonly referenced objects such as tables and constraints.

CHNGPGS_THRESH

Changed pages threshold. Used to specify the level (percentage) of changed pages at which the asynchronous page cleaners will be started, if they are not currently active.

codepage (Database code page)

Specifies the code page of the database.

codeset (Database code set)

Specifies the code set of the database.

COPYPROTECT (OS/2 only)

Enables the copy-protect attribute.

country (Database country code)

Specifies the country code of the database.

database_consistent (Database is consistent)

NO specifies that a transaction is pending, or some other task is pending on the database, and that the data is not consistent at this point.

YES specifies that all transactions have been committed or rolled back, and that the data is consistent.

database_level (Database release level)

Database release level. Specifies the release level of the database manager which can use the database.

DBHEAP

Specifies the size, in pages, of the database heap that is used to hold control information on all open cursors accessing the database. Both logbufsz and catalogcache_sz are allocated from the dbheap.

DFT_DEGREE

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 (in pages).

DFT_LOADREC_SES

Default number of load recovery sessions. Specifies the default number of sessions that will be used during the recovery of a table load. Applicable only if roll-forward recovery is enabled.

DFT_PREFETCH_SZ

Default prefetch size of table spaces (in pages).

DFT_QUERYOPT

The query optimization class is used to direct the optimizer to use different degrees of optimization when compiling SQL queries. This parameter provides additional flexibility by setting the default query optimization class used when neither the SET CURRENT QUERY OPTIMIZATION statement nor the QUERYOPT bind command are used.

DIR_OBJ_NAME

Object name in DCE name space. The object name representing a database manager instance (or a database) in the directory. The concatenation of this value and the dir_path_name value yields a global name that uniquely identifies the database manager instance or database in the name space governed by the directory services specified in the dir_type parameter.

DISCOVER_DB

This parameter can be set to DISABLE to prevent information about a database from being returned to a client when a discovery request is issued by the client against the server.

DL_EXPINT

Applies to DB2 File Manager only. This parameter specifies the interval of time (in seconds) for which the file access token generated is valid. The number of seconds the token is valid begins from the time it is generated. The File Manager Filter checks the validity of the token containing this expiry time.

DL_NUM_BACKUP

Applies to DB2 File Manager only. This parameter specifies the number of the most recent DB2 backups for which a File Manager keeps backup information. The unlinked files are garbage collected based on this value. A value of 1 means that the unlinked files are garbage collected at the completion of the next DB2 backup.

DL_NUM_COPIES

Applies to DB2 File Manager only. This parameter specifies the number of additional copies of a file to be made in the archive server (such as an ADSM server) when a file is linked to the database.

DL_TIME_DROP

Applies to DB2 File Manager only. This parameter specifies the interval of time (in days) files would be retained on an archive server (such as an ADSM server) after a DROP TABLE, DROP DATABASE, or DROP TABLESPACE is issued.

DLCHKTIME

Time interval (in milliseconds) for checking deadlock. Defines the frequency at which the database manager checks for deadlocks among all the applications connected to a database.

ESTORE_SEG_SZ

This parameter specifies the number of pages in each of the extended memory segments in the database. There are platform-dependent considerations when setting this configuration parameter.

INDEXREC

Specifies when invalid indexes will be recreated. The default setting is SYSTEM, which uses the value of the database manager configuration parameter indexrec.

The possible output values are:

SYSTEM(ACCESS)
SYSTEM(RESTART)
ACCESS
RESTART.

INDEXSORT

Index sort flag. Indicates whether sorting of index keys will occur during index creation.

LOCKLIST

Specifies the maximum storage, in pages, allocated to the lock list.

LOCKTIMEOUT

Specifies the number of seconds that an application will wait to obtain a lock.

LOGBUFSZ

Specifies the number of pages used to buffer log records prior to writing them to disk. Allocated from dbheap.

LOGFILSIZ

Specifies the amount of disk storage, in pages, allocated to log files used for data recovery. This parameter defines the size of each primary and secondary log file.

loghead (First active log file)

Log head identification. Specifies the name of the log file containing the head of the active log. The next log record that is written will start at the head of the active log.

logpath (Path to log files)

Location of log files. Contains the current path being used for logging purposes.

LOGPRIMARY

Specifies the number of primary log files that can be used for database recovery.

LOGRETAIN

Indicates whether the active log files are to be retained and become online archive log files for use in roll-forward recovery (log retention logging).

log_retain_status (Log retain for recovery status)

Indicates whether log files are being retained for use in roll-forward recovery.

LOGSECOND

Specifies the number of secondary log files that can be used for database recovery.

MAXAPPLS

Specifies the maximum number of application programs (both local and remote) that can connect to the database at one time.

MAXFILOP

Specifies the maximum number of database files that an application program can have open at one time.

MAXLOCKS

Specifies the maximum percentage of the lock list that any one application program can use.

MINCOMMIT

Specifies the number of SQL commits that can be grouped for a given database. Grouping SQL commits permits better control of the I/O and log activity when a commit is performed.

MULTIPAGE_ALLOC

Multi-page 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

Specifies an alternate path to the recovery log files for a database.

Since the newlogpath directory only accepts fully qualified directories, the absolute path must be specified.

nextactive (Next active log file)

Specifies the name of the next recovery log file to be used for logging.

NUM_ESTORE_SEGS

This parameter specifies the number of extended storage memory segments available for use by the database.

NUM_FREQVALUES

Number of frequent values retained. Used to specify the number of "most frequent values" that will be collected when the WITH DISTRIBUTION option is specified in RUNSTATS.

NUM_IOCLEANERS

Specifies the number of asynchronous page cleaners for a database.

NUM_IOSERVERS

Specifies the number of I/O servers for a database. I/O servers are used on behalf of the database agents to perform prefetch I/O and asynchronous I/O by utilities such as backup and restore.

NUM_QUANTILES

Number of quantiles for columns. Controls the number of quantiles that will be collected when the WITH DISTRIBUTION option is specified in RUNSTATS.

numsegs (Default number of containers)

Determines the number of containers that will be created within the default SMS table spaces.

PCKCACHESZ

Specifies the amount of memory to be used for caching packages and dynamic SQL statements.

The label (calculated) is displayed in the output for GET DATABASE CONFIGURATION if:

The internal value is -1
MAXAPPLS*8 is less than 32. In this case, 32 is displayed with the label (calculated).

REC_HIS_RETENTN

Recovery history retention period. Used to specify the number of days that historical information on backups is to be retained.

RESTORE_PENDING

This parameter indicates whether a RESTORE PENDING status exists in the database.

release (Database configuration release level)

Specifies the release level of the database configuration file.

rollfwd_pending (Rollforward pending)

Indicates whether a roll-forward recovery procedure is required for the database.

The possible values are:

NO: Neither the database nor any table space is in roll-forward pending state.
DATABASE: The database first needs to be rolled forward.
TABLESPACES: One or more table spaces in the database requires roll-forward recovery.

SEQDETECT

Indicates whether sequential detection for a database is to be enabled or disabled.

SOFTMAX

This parameter is used to specify the frequency at which soft checkpoints are taken, and to specify the number of logs that are to be recovered after a crash.

SORTHEAP

Specifies the number of private memory pages available for each sort in the application program.

STAT_HEAP_SZ

Statistics heap size (in pages). Specifies the maximum size of the heap used in creating and collecting all table statistics when distribution statistics are being gathered.

STMTHEAP

Specifies the heap size, in pages, that can be used for compiling SQL statements.

territory (Database territory)

Specifies the territory of the database.

USEREXIT

Indicates whether a user exit function for archiving or retrieving log files can be called the next time the database is opened.

OFF specifies that a user exit function cannot be called.

ON specifies that a user exit function can be called.

user_exit_status (User exit for logging status)

OFF specifies that the user exit function cannot be called to store archive log files.

ON specifies that the user exit function can be called to store archive log files.

UTIL_HEAP_SZ

Utility heap size. Specifies the maximum amount of shared memory that can be used simultaneously by the backup, restore, and load utilities.

Usage Notes

If an error occurs, the information returned is not valid. If the configuration file is invalid, an error message is returned. The database must be restored from a backup version.

To set the database configuration parameters to the database manager defaults, use RESET DATABASE CONFIGURATION.

For more information about DB2's configuration parameters, see the Administration Guide.

See Also

RESET DATABASE CONFIGURATION

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]