DB2 Version 10.1 for Linux, UNIX, and Windows

ENV_GET_REG_VARIABLES table function - Retrieve DB2 registry settings in use

The ENV_GET_REG_VARIABLES table function returns the DB2® 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.

Syntax


>>-ENV_GET_REG_VARIABLES--(--member--)-------------------------><

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.

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

Example

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 DB2 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 DB2 instance is restarted.

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 DB2 registry variable.
REG_VAR_VALUE	VARCHAR(2048)	Current setting of the DB2 registry variable in memory.
REG_VAR_ON_DISK_VALUE	VARCHAR(2048)	Current setting of the DB2 registry variable on disk.
IS_AGGREGATE	SMALLINT	Indicates whether or not the DB2 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 DB2 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 DB2 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