ENV_GET_REG_VARIABLES table function - Retrieve database registry settings that are in use
The ENV_GET_REG_VARIABLES table function returns the database registry settings from one or all database members.
Note: The ENV_GET_REG_VARIABLES table
function replaces the deprecated REG_VARIABLES administrative
view. The ENV_GET_REG_VARIABLESfunction is different
in that it allows for a single parameter value to denote a specific
member to query, and returns an addition result for the registry setting
currently stored on disk.
Authorization
One of the
following authorizations is required:
- EXECUTE privilege on the ENV_GET_REG_VARIABLES table function
- DATAACCESS authority
- DBADM authority
- SQLADM authority
In addition, one of the following privileges or authorities
is also required:
- EXECUTE privilege on the ENV_GET_REG_VARIABLES table function
Default PUBLIC privilege
None
Syntax
The schema is SYSPROC.
Table function parameters
-
member
- An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all database members. If the NULL value is specified, -1 is set implicitly. showall
- An input argument of type SMALLINT that specifies if information on all registry variables is returned. Specify 0 to show only those registry variables currently set, or 1 to show all registry variables. If the null value is specified, 0 is set implicitly. The default value is NULL.
Information returned
Column name | Data type | Description |
---|---|---|
MEMBER | SMALLINT | member - Database member monitor element |
REG_VAR_NAME | VARCHAR(256) | Name of the database registry variable. |
REG_VAR_VALUE | VARCHAR(2048) | Current setting of the database registry variable in memory. |
REG_VAR_ON_DISK_VALUE | VARCHAR(2048) | Current setting of the database registry variable on disk. |
IS_AGGREGATE | SMALLINT | Indicates whether or not the database registry variable is an aggregate variable. The possible return values are 0 if it is not an aggregate variable, and 1 if it is an aggregate variable. |
AGGREGATE_NAME | VARCHAR(256) | Name of the aggregate if the database registry variable is currently obtaining its value from a configured aggregate. If the registry variable is not being set through an aggregate, or is set through an aggregate but has been overridden, the value of AGGREGATE_NAME is NULL. |
LEVEL | CHAR(1) | Indicates the level at which the database registry variable acquires its value. The
possible return values and the corresponding levels that they represent
are:
|
REG_VAR_DEFAULT_VALUE | VARCHAR(2048) | Default value for the registry variable. |
Examples
- Show
all database registry settings (including
registry variables that are not set) for all database members:
select MEMBER, REG_VAR_NAME, REG_VAR_VALUE, REG_VAR_ON_DISK_VALUE from table(ENV_GET_REG_VARIABLES(-2, 1)
- In this example, the registry variable DB2DBDFT,
which specifies the database alias name to use for implicit connections,
is set to CORP_1. This is done before the database instance starts:
Then you can issue a query to show that registry variable setting:db2set db2dbdft=CORP_1 db2start
This query returns the following output:select substr(reg_var_value,1,20) as VALUE, substr(reg_var_on_disk_value,1,20) as ON_DISK_VALUE from table(env_get_reg_variables(-1)) where reg_var_name = 'DB2DBDFT'
To demonstrate the difference between in memory and on disk values for the registry settings, the DB2DBDFT registry variable is then altered:VALUE ON_DISK_VALUE -------------------- -------------------- CORP_1 CORP_1 1 record(s) selected.
Running the same query as the previous one, shows that this new value is immediately picked up by the ENV_GET_REG_VARIABLES function:db2set db2dbdft=DEPT_MAJOR
However, note that the in memory value will not change to the new value until the database instance is restarted.VALUE ON_DISK_VALUE -------------------- -------------------- CORP_1 DEPT_MAJOR 1 record(s) selected.