DB2 Version 10.1 for Linux, UNIX, and Windows

DBMCFG administrative view - Retrieve database manager configuration parameter information

The DBMCFG administrative view returns database manager configuration parameter information including the values in memory and the values stored on disk.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the DBMCFG administrative view
CONTROL privilege on the DBMCFG administrative view
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

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

Examples

Example 1: Retrieve values for all the database manager configuration parameters stored on disk:

SELECT NAME, DEFERRED_VALUE FROM SYSIBMADM.DBMCFG

The following is an example of output for this query.

NAME                             DEFERRED_VALUE         
-------------------------------- -------------------...----
agent_stack_sz                   0                      
agentpri                         -1                     
alternate_auth_enc               AES_ONLY               
aslheapsz                        15                     
audit_buf_sz                     0                      
authentication                   SERVER                 
catalog_noauth                   YES                    
clnt_krb_plugin                                         
...
comm_bandwidth                   0.000000e+00           
conn_elapse                      0                      
cpuspeed                         4.000000e-05           
dft_account_str                                         
dft_mon_bufpool                  OFF                    
...
dft_mon_timestamp                ON                     
dft_mon_uow                      OFF                    
...
jdk_path                         /wsdb/v91/bldsupp/AIX5L
...
ssl_svcename                     22711                  
ssl_svr_keydb                    /GSKit/Keystore/key.kdb
ssl_svr_label
ssl_svr_stash                    /GSKit/Keystore/key.sth

Example 2: Retrieve all the database manager configuration parameters values.

SELECT * FROM SYSIBMADM.DBMCFG

The following is an example of output for this query.

NAME                VALUE                    VALUE_FLAGS ...
-----------------   -----------------------  ----------- ...
agent_stack_sz      0                        NONE        ...
agentpri            -1                       NONE        ...
alternate_auth_enc  NOT_SPECIFIED            NONE        ...
aslheapsz           15                       NONE        ...
audit_buf_sz        0                        NONE        ...
authentication      SERVER                   NONE        ...
catalog_noauth      YES                      NONE        ...
clnt_krb_plugin                              NONE        ...
clnt_pw_plugin                               NONE        ...
comm_bandwidth      0.000000e+00             NONE        ...
conn_elapse         0                        NONE        ...
cpuspeed            4.000000e-05             NONE        ...
dft_account_str                              NONE        ...
dft_mon_bufpool     OFF                      NONE        ...
dft_mon_lock        OFF                      NONE        ...
dft_mon_sort        OFF                      NONE        ...
dft_mon_stmt        OFF                      NONE        ...
dft_mon_table       OFF                      NONE        ...
dft_mon_timestamp   ON                       NONE        ...
dft_mon_uow         OFF                      NONE        ...
dftdbpath           /home/userb              NONE        ...
diaglevel           3                        NONE        ...
diagpath            /home/userc/tmp/ $m      NONE        ...
diagpath_resolved   /home/userc/tmp/DIAG000  NONE        ...
dir_cache           YES                      NONE        ...
discover            SEARCH                   NONE        ...
discover_inst       ENABLE                   NONE        ...
fcm_num_anchors     0                        AUTOMATIC   ...
fcm_num_buffers     0                        AUTOMATIC   ...
fcm_num_connect     0                        AUTOMATIC   ...

Output for this query (continued).

... DEFERRED_VALUE          DEFERRED_VALUE_FLAGS      DATATYPE     
... ----------------------- ------------------------- ----------   
... 0                       NONE                      INTEGER      
... -1                      NONE                      INTEGER      
... AES_ONLY                NONE                      VARCHAR(32)  
... 15                      NONE                      BIGINT       
... 0                       NONE                      BIGINT       
... SERVER                  NONE                      VARCHAR(32)  
... YES                     NONE                      VARCHAR(3)   
...                         NONE                      VARCHAR(32)  
...                         NONE                      VARCHAR(32)  
... 0.000000e+00            NONE                      REAL         
... 0                       NONE                      INTEGER      
... 4.000000e-05            NONE                      REAL         
...                         NONE                      VARCHAR(25)  
... OFF                     NONE                      VARCHAR(3)   
... OFF                     NONE                      VARCHAR(3)   
... OFF                     NONE                      VARCHAR(3)   
... OFF                     NONE                      VARCHAR(3)   
... OFF                     NONE                      VARCHAR(3)   
... ON                      NONE                      VARCHAR(3)   
... OFF                     NONE                      VARCHAR(3)   
... /home/userb             NONE                      VARCHAR(215) 
... 3                       NONE                      INTEGER      
... /home/userc/tmp $m      NONE                      VARCHAR(215) 
... /home/userc/tmp/DIAG000 NONE                      VARCHAR(215) 
... YES                     NONE                      VARCHAR(3)   
... SEARCH                  NONE                      VARCHAR(8)   
... ENABLE                  NONE                      VARCHAR(8)   
... 0                       AUTOMATIC                 BIGINT       
... 512                     AUTOMATIC                 BIGINT       
... 0                       AUTOMATIC                 BIGINT       
...

Information returned

Table 1. Information returned by the DBMCFG administrative view
Column name	Data type	Description
NAME	VARCHAR(32)	Configuration parameter name.
VALUE	VARCHAR(256)	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
DEFERRED_VALUE	VARCHAR(256)	The value of the configuration parameter on disk. For some database manager configuration parameters, the database manager must be stopped (db2stop) and restarted (db2start) for this value to take effect.
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
DATATYPE	VARCHAR(128)	Configuration parameter data type.