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