DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
Returns the values of individual entries in a specific database configuration file.
This command returns information only for the database partition on which it is executed.
None
Instance. An explicit attachment is not required, but a connection to the database is required when using the SHOW DETAIL clause. If the database is listed as remote, an instance attachment to the remote node is established for the duration of the command.
>>-GET--+-DATABASE-+--+-CONFIGURATION-+-------------------------> '-DB-------' +-CONFIG--------+ '-CFG-----------' >--+-------------------------+--+-------------+---------------->< '-FOR----database-alias---' '-SHOW DETAIL-'
This is a default clause when operating in the CLPPlus interface. SHOW DETAIL need not be called when using CLPPlus processor.
The following is sample output from GET DATABASE CONFIGURATION (issued on Windows):
Database Configuration for Database
Database configuration release level = 0x0d00
Database release level = 0x0d00
Database territory = US
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = IDENTITY
Alternate collating sequence (ALT_COLLATE) =
Number compatibility = OFF
Varchar2 compatibility = OFF
Date compatibility = OFF
Database page size = 4096
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
Statement concentrator (STMT_CONC) = OFF
Discovery support for this database (DISCOVER_DB) = ENABLE
Restrict access = NO
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
Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN
Backup pending = NO
All committed transactions have been written to disk = 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
Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(60464)
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(6200)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(60)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(1533)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(6728)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(336)
Database heap (4KB) (DBHEAP) = AUTOMATIC(2283)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
Log buffer size (4KB) (LOGBUFSZ) = 256
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5115
Buffer pool size (pages) (BUFFPAGE) = 1000
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(4096)
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Lock timeout (sec) (LOCKTIMEOUT) = -1
Changed pages threshold (CHNGPGS_THRESH) = 80
Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(3)
Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(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(40)
Average number of active applications (AVG_APPLS) = AUTOMATIC(1)
Max DB files open per application (MAXFILOP) = 61440
Log file size (4KB) (LOGFILSIZ) = 1024
Number of primary log files (LOGPRIMARY) = 13
Number of secondary log files (LOGSECOND) = 4
Changed path to log files (NEWLOGPATH) =
Path to log files = D:\DB2\NODE0000\SQL00003\SQLOGDIR\
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Block non logged operations (BLOCKNONLOGGED) = NO
Percent max primary 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) = 520
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
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
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 (RESTART)
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
Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
Automatic maintenance (AUTO_MAINT) = ON
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = ON
Automatic runstats (AUTO_RUNSTATS) = ON
Automatic statement statistics (AUTO_STMT_STATS) = ON
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF
Auto-Revalidation (AUTO_REVAL) = DEFERRED
Currently Committed (CUR_COMMIT) = ON
CHAR output with DECIMAL input (DEC_TO_CHAR_FMT) = NEW
Enable XML Character operations (ENABLE_XMLCHAR) = YES
WLM Collection Interval (WLM_COLLECT_INT) = 0
Monitor Collect Settings
Request metrics (MON_REQ_MATRICS) = BASE
Activity metrics (MON_ACT_MATRICS) = BASE
Object metrics (MON_OBJ_MATRICS) = BASE
Unit of work events (MON_UOW_DATA) = NONE
Lock timeout events (MON_LOCKTIMEOUT) = NONE
Deadlock events MON_DEADLOCK) = WITHOUT_HIST
Lock wait events (MON_LOCKWAIT) = NONE
Lock wait event threshold (MON_LW_THRESH) = 5000000
SMPT Server (SMTP_SERVER) =
Database Configuration for Database
Description Parameter Current Value Delayed Value
-----------------------------------------------------------------------------------------------
Database configuration release level = 0x0d00
Database release level = 0x0d00
Database territory = US
Database code page = 1208
Database code set = utf-8
Database country/region code = 1
Database collating sequence = IDENTITY IDENTITY
Alternate collating sequence (ALT_COLLATE) =
Number compatibility = OFF
Varchar2 compatibility = OFF
Date compatibility = OFF
Database page size = 4096 4096
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE DISABLE
Statement concentrator (STMT_CONC) = OFF OFF
Discovery support for this database (DISCOVER_DB) = ENABLE ENABLE
Restrict access = NO
Default query optimization class (DFT_QUERYOPT) = 5 5
Degree of parallelism (DFT_DEGREE) = 1 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO NO
Default refresh age (DFT_REFRESH_AGE) = 0 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10 10
Number of quantiles retained (NUM_QUANTILES) = 20 20
Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN ROUND_HALF_EVEN
Backup pending = NO
All committed transactions have been written to disk = No
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = YES
Log retain for recovery status = NO
User exit for logging status = NO
Self tuning memory (SELF_TUNING_MEM) = OFF OFF
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(282400) AUTOMATIC(282400)
Database memory threshold (DB_MEM_THRESH) = 10 10
Max storage for lock list (4KB) (LOCKLIST) = 4096 4096
Percent. of lock lists per application (MAXLOCKS) = 10 10
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8) (MAXAPPLS*8)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 5000 5000
Sort list heap (4KB) (SORTHEAP) = 256 256
Database heap (4KB) (DBHEAP) = AUTOMATIC(1200) AUTOMATIC(1200)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*5) (MAXAPPLS*5)
Log buffer size (4KB) (LOGBUFSZ) = 256 256
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000 5000
Buffer pool size (pages) (BUFFPAGE) = 200 200
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(6402) AUTOMATIC(4096)
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256) AUTOMATIC(256)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40016) AUTOMATIC(40000)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384) AUTOMATIC(4384)
Interval for checking deadlock (ms) (DLCHKTIME) = 10000 10000
Lock timeout (sec) (LOCKTIMEOUT) = -1 -1
Changed pages threshold (CHNGPGS_THRESH) = 60 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(3) AUTOMATIC(3)
Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(3) AUTOMATIC(3)
Index sort flag (INDEXSORT) = YES YES
Sequential detect flag (SEQDETECT) = YES YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC AUTOMATIC
Track modified pages (TRACKMOD) = NO NO
Default number of containers = 1 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32 32
Max number of active applications (MAXAPPLS) = AUTOMATIC(40) AUTOMATIC(40)
Average number of active applications (AVG_APPLS) = AUTOMATIC(1) AUTOMATIC(1)
Max DB files open per application (MAXFILOP) = 61440 61440
Log file size (4KB) (LOGFILSIZ) = 1000 1000
Number of primary log files (LOGPRIMARY) = 3 3
Number of secondary log files (LOGSECOND) = 2 2
Changed path to log files (NEWLOGPATH) =
Path to log files = D:\DB2\NODE0000 D:\DB2\NODE0000
\SQL00001\SQLOGDIR\ \SQL00001\SQLOGDIR\
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO NO
Percent max primary log space by transaction (MAX_LOG) = 0 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0 0
Group commit count (MINCOMMIT) = 1 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100 100
Log retain for recovery enabled (LOGRETAIN) = OFF OFF
User exit for logging enabled (USEREXIT) = OFF OFF
HADR database role = STANDARD 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 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC NEARSYNC
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0 0
First log archive method (LOGARCHMETH1) = OFF OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 20
Vendor options (VENDOROPT) =
Auto restart enabled (AUTORESTART) = ON ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM SYSTEM (RESTART)
Log pages during index build (LOGINDEXBUILD) = OFF OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366 366
Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF OFF
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
Automatic maintenance (AUTO_MAINT) = ON ON
Automatic database backup (AUTO_DB_BACKUP) = OFF OFF
Automatic table maintenance (AUTO_TBL_MAINT) = ON ON
Automatic runstats (AUTO_RUNSTATS) = ON ON
Automatic statement statistics (AUTO_STMT_STATS) = ON ON
Automatic statistics profiling (AUTO_STATS_PROF) = OFF OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF OFF
Automatic reorganization (AUTO_REORG) = OFF OFF
Auto-Revalidation (AUTO_REVAL) = DEFERRED DEFERRED
Currently Committed (CUR_COMMIT) = ON ON
CHAR output with DECIMAL input (DEC_TO_CHAR_FMT) = NEW NEW
Enable XML Character operations (ENABLE_XMLCHAR) = YES YES
WLM Collection Interval (WLM_COLLECT_INT) = 0 0
Monitor Collect Settings
Request metrics (MON_REQ_MATRICS) = BASE BASE
Activity metrics (MON_ACT_MATRICS) = BASE BASE
Object metrics (MON_OBJ_MATRICS) = BASE BASE
Unit of work events (MON_UOW_DATA) = NONE NONE
Lock timeout events (MON_LOCKTIMEOUT) = NONE NONE
Deadlock events MON_DEADLOCK) = WITHOUT_HIST WITHOUT_HIST
Lock wait events (MON_LOCKWAIT) = NONE NONE
Lock wait event threshold (MON_LW_THRESH) = 0 5000000
SMPT Server SMTP_SERVER) =If the database configuration file is invalid, the database must be restored from a backup version.
To set the database configuration parameters to the database manager defaults, use the RESET DATABASE CONFIGURATION command.
To retrieve information from all database partitions, use the SYSIBMADM.DBCFG administrative view.
The configuration parameter value returned by issuing the GET DATABASE CONFIGURATION command may vary slightly from the configuration parameter value allocated in DISK.