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
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
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
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:
|
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:
|
DATATYPE | VARCHAR(128) | Configuration parameter data type. |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
MEMBER | SMALLINT | member - Database member monitor element |