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

The following example retrieves 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 output illustrates the result of 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

The following example retrieves all the database configuration parameters values stored on disk for all active database members.

SELECT NAME, DEFERRED_VALUE, DBPARTITIONNUM FROM SYSIBMADM.DBCFG

The following output illustrates the result of 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

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.

filter

The filter argument accepts two values: 0 and 1. The default value is 0, which means no filtering is applied. When it is set to 1, parameters that are not valid for the current server nodetype displays a value of NOT APPLICABLE. This behavior is applicable only on Db2® pureScale® environment.

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

Example 1

In the following example, the second parameter is set to 1, which indicates that the filtering is enabled.

SYSPROC.DB_GET_CFG(-2, 1)

Example 2

The following example shows how, in a Db2 pureScale environment, you can 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 output illustrates the result of 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. The following are valid values: `NONE` - no additional information `AUTOMATIC` - the configuration parameter is set to automatic `COMPUTED` - the configuration parameter is 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. The following are valid values: `NONE` - no additional information `AUTOMATIC` - the configuration parameter `COMPUTED` - the configuration parameter is 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