DB2 10.5 for Linux, UNIX, and Windows

DBCFG administrative view and DB_GET_CFG table function - Retrieve database configuration parameter information

The DBCFG administrative view and the DB_GET_CFG table function retrieve database configuration parameter information for the currently connected database for all active database members.

To ensure that information is retrieved from all database members in the instance, run the ACTIVATE DATABASE command before using the administrative view or table function. Depending on whether you are using the administrative view or the table function, refer to one of the following sections:

DBCFG administrative view
DB_GET_CFG table function

DBCFG administrative view

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
DBADM authority
SQLADM authority

Default PUBLIC privilege

In a nonrestrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

Examples

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

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

The following is an example of 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_reorg                       OFF           
             0 autorestart                      ON            

  6 record(s) selected.

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

SELECT NAME, DEFERRED_VALUE, DBPARTITIONNUM FROM SYSIBMADM.DBCFG

The following is an example of 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  
...

DB_GET_CFG table function

Syntax


>>-DB_GET_CFG--(--+--------+--)--------------------------------><
                  '-member-'

The schema is SYSPROC.

Table function parameter

member: An optional input argument of type INTEGER that specifies the number of a database member in the same instance as the currently connected database. Specify -1 for the current database member, or -2 for all active database members. If the null value is specified, -1 is set implicitly. On an active database member, the database is available for connection and use by applications.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the DB_GET_CFG table function
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

In a nonrestrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Example

In a DB2® pureScale® environment, retrieve the automatic maintenance settings in the database configuration that are stored in memory for all active members.

SELECT NAME, VARCHAR(VALUE, 20) AS VALUE, MEMBER,
   DBPARTITIONNUM FROM TABLE(SYSPROC.DB_GET_CFG(-2))
   WHERE NAME LIKE 'auto_%' ORDER BY NAME, MEMBER

The following is an example of output from this query.

NAME                             VALUE                MEMBER DBPARTITIONNUM
-------------------------------- -------------------- ------ --------------
auto_db_backup                   OFF                  0                   0
auto_db_backup                   OFF                  1                   0
auto_db_backup                   OFF                  2                   0
auto_del_rec_obj                 OFF                  0                   0
auto_del_rec_obj                 OFF                  1                   0
auto_del_rec_obj                 OFF                  2                   0
auto_maint                       ON                   0                   0
auto_maint                       ON                   1                   0
auto_maint                       ON                   2                   0
auto_reorg                       OFF                  0                   0
auto_reorg                       OFF                  1                   0
auto_reorg                       OFF                  2                   0
auto_reval                       DEFERRED             0                   0
auto_reval                       DEFERRED             1                   0
auto_reval                       DEFERRED             2                   0
auto_runstats                    ON                   0                   0
auto_runstats                    ON                   1                   0
auto_runstats                    ON                   2                   0
auto_stats_views                 OFF                  0                   0
auto_stats_views                 OFF                  1                   0
auto_stats_views                 OFF                  2                   0
auto_stmt_stats                  ON                   0                   0
auto_stmt_stats                  ON                   1                   0
auto_stmt_stats                  ON                   2                   0
auto_tbl_maint                   ON                   0                   0
auto_tbl_maint                   ON                   1                   0
auto_tbl_maint                   ON                   2                   0
autorestart                      ON                   0                   0
autorestart                      ON                   1                   0
autorestart                      ON                   2                   0

  30 record(s) selected.

Information returned

Table 1. Information returned by the DBCFG administrative view and the DB_GET_CFG table function
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 `COMPUTED` - the configuration parameter has been set to a computed value
DATATYPE	VARCHAR(128)	Configuration parameter data type.
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element
MEMBER	SMALLINT	member - Database member monitor element