DB2 Version 9.7 for Linux, UNIX, and Windows

DBCFG administrative view - Retrieve database configuration parameter information

The DBCFG administrative view retrieves database configuration parameter information for the currently connected database for all database partitions.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the DBCFG administrative view
CONTROL privilege on the DBCFG administrative view
DATAACCESS authority

Examples

Example 1: Retrieve the automatic maintenance settings in the database configuration that are stored in memory for all database partitions.

SELECT DBPARTITIONNUM, NAME, VALUE FROM SYSIBMADM.DBCFG WHERE NAME LIKE 'auto_%'

The following example is a sample output for this query.

DBPARTITIONNUM NAME                             VALUE         
-------------- -------------------------------- --------------
             0 auto_maint                       OFF           
             0 auto_db_backup                   OFF           
             0 auto_tbl_maint                   OFF           
             0 auto_runstats                    OFF           
             0 auto_stats_prof                  OFF           
             0 auto_prof_upd                    OFF           
             0 auto_reorg                       OFF           
             0 autorestart                      ON            

  8 record(s) selected.

Example 2: Retrieve all the database configuration parameters values stored on disk for all database partitions.

SELECT NAME, DEFERRED_VALUE, DBPARTITIONNUM FROM SYSIBMADM.DBCFG

The following example is a sample output for this query.

NAME                 DEFERRED_VALUE      DBPARTITIONNUM  
----------------...- ---------------...- --------------  
app_ctl_heap_sz      128                              0  
appgroup_mem_sz      30000                            0  
applheapsz           256                              0  
archretrydelay       20                               0  
...                                                      
autorestart          ON                               0  
avg_appls            1                                0  
blk_log_dsk_ful      NO                               0  
catalogcache_sz      -1                               0  
...

Information returned

Table 1. Information returned by the DBCFG administrative view
Column name	Data type	Description
NAME	VARCHAR(32)	Configuration parameter name.
VALUE	VARCHAR(1024)	The current value of the configuration parameter stored in memory.
VALUE_FLAGS	VARCHAR(10)	Provides specific information for the configuration parameter current value. Valid values are: NONE - no additional information AUTOMATIC - the configuration parameter has been set to automatic COMPUTED - the configuration parameter has been set to a computed value
DEFERRED_VALUE	VARCHAR(1024)	The value of the configuration parameter on disk. For some database configuration parameters, changes only take effect when the database is reactivated. In these cases, all applications must first disconnect from the database. (If the database was activated, then it must be deactivated and reactivated.) The changes take effect at the next connection to the database.
DEFERRED_VALUE_FLAGS	VARCHAR(10)	Provides specific information for the configuration parameter deferred value. Valid values are: NONE - no additional information AUTOMATIC - the configuration parameter has been set to automatic COMPUTED - the configuration parameter has been set to a computed value
DATATYPE	VARCHAR(128)	Configuration parameter data type.
DBPARTITIONNUM	SMALLINT	Database partition number.