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

Table 1. Information returned by the ENV_GET_REG_VARIABLES table function
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: I = instance G = global N = database partition E = environment
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:
```
db2set db2dbdft=CORP_1
db2start
```
Then you can issue a query to show that registry variable setting:
```
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'
```
This query returns the following output:
```
VALUE                ON_DISK_VALUE
-------------------- --------------------
CORP_1               CORP_1

   1 record(s) selected.
```
To demonstrate the difference between in memory and on disk values for the registry settings, the DB2DBDFT registry variable is then altered:
```
db2set db2dbdft=DEPT_MAJOR
```
Running the same query as the previous one, shows that this new value is immediately picked up by the ENV_GET_REG_VARIABLES function:
```
VALUE                ON_DISK_VALUE
-------------------- --------------------
CORP_1               DEPT_MAJOR

   1 record(s) selected.
```
However, note that the in memory value will not change to the new value until the database instance is restarted.