DBMCFG administrative view and DBM_GET_CFG table function - Retrieve database manager configuration parameter information

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

Depending on whether you are using the administrative view or the table function, refer to one of the following sections:

DBMCFG administrative view

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
  • ACCESSCTRL authority
  • SECADM 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                     
alt_diagpath         
alt_diagpath_resolved
alternate_auth_enc               AES_ONLY               
aslheapsz                        15                     
audit_buf_sz                     0                      
authentication                   SERVER                 
catalog_noauth                   YES                    
cf_diaglevel                     2
cf_diagpath                      /home/hotellnx93/db2docs2/sqllib/db2dump/
cf_diagpath_resolved             /home/hotellnx93/db2docs2/sqllib/db2dump/
cf_mem_sz                        131072
cf_num_conns                     16
cf_num_workers                   1
clnt_krb_plugin                  
...
comm_bandwidth                   0.000000e+00           
comm_exit_list
conn_elapse                      0                      
cpuspeed                         4.000000e-05           
cur_eff_arch_lvl                 V:10 R:5 M:0 F:1 I:0 SB:0 
cur_eff_code_lvl                 V:10 R:5 M:0 F:1 I:0 SB:0 
dft_account_str                                         
dft_mon_bufpool                  OFF                    
...
dft_mon_timestamp                ON                     
dft_mon_uow                      OFF                    
...
jdk_path                         /home/hotellnx93/db2docs2/sqllib/java/jdk64
...
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        ...
alt_diagpath                                       NONE        ...
alt_diagpath_resolved                              NONE        ...
alternate_auth_enc     NOT_SPECIFIED               NONE        ...
aslheapsz              15                          NONE        ...
audit_buf_sz           0                           NONE        ...
authentication         SERVER                      NONE        ...
catalog_noauth         YES                         NONE        ...
cf_diaglevel           2                           NONE        ...
cf_diagpath            /home/userc/sqllib/db2dump/ NONE        ...
cf_diagpath_resolved   /home/userc/sqllib/db2dump/ NONE        ...
cf_mem_sz              131072                      AUTOMATIC   ...
cf_num_conns           16                          AUTOMATIC   ...
cf_num_workers         1                           AUTOMATIC   ...
clnt_krb_plugin                                    NONE        ...
clnt_pw_plugin                                     NONE        ...
cluster_mgr                                        NONE        ...
comm_bandwidth         0.000000e+00                NONE        ...
comm_exit_list                                     NONE        ...
conn_elapse            0                           NONE        ...
cpuspeed               4.000000e-05                NONE        ...
cur_eff_arch_lvl       V:10 R:5 M:0 F:1 I:0 SB:0   NONE        ...
cur_eff_code_lvl       V:10 R:5 M:0 F:1 I:0 SB:0   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        ...
diagsize               0                           NONE        ...
dir_cache              YES                         NONE        ...
discover               SEARCH                      NONE        ...
discover_inst          ENABLE                      NONE        ...
fcm_num_anchors        0                           AUTOMATIC   ...
fcm_num_buffers        0                           AUTOMATIC   ...
fcm_num_channels       256                         AUTOMATIC   ...
...              
Output for this query (continued).
... DEFERRED_VALUE              DEFERRED_VALUE_FLAGS DATATYPE     
... --------------------------- -------------------- ------------ 
... 0                           NONE                 INTEGER      
... -1                          NONE                 INTEGER      
...                             NONE                 VARCHAR(215) 
...                             NONE                 VARCHAR(215) 
... AES_ONLY                    NONE                 VARCHAR(32)  
... 15                          NONE                 BIGINT       
... 0                           NONE                 BIGINT       
... SERVER                      NONE                 VARCHAR(32)  
... YES                         NONE                 VARCHAR(3)   
... 2                           NONE                 INTEGER      
... /home/userc/sqllib/db2dump/ NONE                 VARCHAR(215) 
... /home/userc/sqllib/db2dump/ NONE                 VARCHAR(215) 
... 131072                      AUTOMATIC            INTEGER      
... 16                          AUTOMATIC            INTEGER      
... 1                           AUTOMATIC            INTEGER      
...                             NONE                 VARCHAR(32)  
...                             NONE                 VARCHAR(32)  
...                             NONE                 VARCHAR(262) 
... 0.000000e+00                NONE                 REAL         
...                             NONE                 VARCHAR(128) 
... 0                           NONE                 INTEGER      
... 4.000000e-05                NONE                 REAL         
... V:10 R:5 M:0 F:1 I:0 SB:0   NONE                 VARCHAR(50)  
... V:10 R:5 M:0 F:1 I:0 SB:0   NONE                 VARCHAR(50)  
...                             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) 
... 0                           NONE                 BIGINT       
... YES                         NONE                 VARCHAR(3)   
... SEARCH                      NONE                 VARCHAR(8)   
... ENABLE                      NONE                 VARCHAR(8)   
... 0                           AUTOMATIC            BIGINT       
... 512                         AUTOMATIC            BIGINT       
... 256                         AUTOMATIC            INTEGER      
...

DBM_GET_CFG table function

Syntax

Read syntax diagramSkip visual syntax diagram DBM_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 DBM_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

Retrieve the workload manager settings from the database manager configuration:
SELECT NAME, VARCHAR(VALUE, 30) AS VALUE
   FROM TABLE(SYSPROC.DBM_GET_CFG())
   WHERE NAME LIKE 'wlm%'
   ORDER BY NAME
The following is an example of output from this query.
NAME                             VALUE               
-------------------------------- --------------------
wlm_disp_concur                 128
wlm_disp_cpu_shares             NO
wlm_disp_min_util               5
wlm_dispatcher                  YES 

  4 record(s) selected. 

Usage Notes

If instance_memory is configured per-member on DPF or Db2® pureScale® instances, then the deferred value for individual members is returned as the member_inst_mem configuration parameter name rather than through instance_memory for individual members. For more details, see instance_memory - Instance memory configuration parameter

Information returned

Table 1. Information returned by the DBMCFG administrative view and DBM_GET_CFG table function
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.
DBPARTITIONNUM SMALLINT dbpartitionnum - Database partition number monitor element
MEMBER SMALLINT member - Database member monitor element