REG_LIST_VARIABLES table function - return registry settings

Note: The REG_LIST_VARIABLES table function has been deprecated and replaced by the ENV_GET_REG_VARIABLES table function - Retrieve database registry settings that are in use.

The REG_LIST_VARIABLES table function returns the Db2® registry settings that are currently being used by the database partition from which the function was invoked.

Syntax

The schema is SYSPROC.

Authorization

EXECUTE privilege on the REG_LIST_VARIABLES table function.

Information returned

Table 1. Information returned by the REG_LIST_VARIABLES table function
Column name	Data type	Description
DBPARTITIONNUM	SMALLINT	The logical partition number of each database partition on which this function runs.
REG_VAR_NAME	VARCHAR(256)	The name of the Db2 registry variable.
REG_VAR_VALUE	VARCHAR(2048)	The current setting of the Db2 registry variable.
IS_AGGREGATE	SMALLINT	Indicates if the Db2 registry variable is an aggregate variable. Possible return values are: 0 - Not aggregate 1 - Aggregate
AGGREGATE_NAME	VARCHAR(256)	The 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 if it is being set by an aggregate that is overridden, the value of AGGREGATE_NAME is NULL.
LEVEL	CHAR(1)	Indicates the level at which the Db2 registry variable acquires its value. Possible return value are: I - Instance G - Global N - Database partition E - Environment

Usage notes

The output from the db2set command and REG_LIST_VARIABLES might not be identical if a Db2 registry variable is configured using the db2set command after the instance is started because REG_LIST_VARIABLES only returns the values that are used when the instance starts.

Example

Request the Db2 registry settings that are currently being used by the current database partition. To format the output into a manageable table, you can use the SUBSTR scalar function to remove trailing blanks from variable length character string column values.

SELECT DBPARTITIONNUM,
       SUBSTR(REG_VAR_NAME, 1, 15) AS REG_VAR_NAME,
       SUBSTR(REG_VAR_VALUE, 1, 13) AS REG_VAR_VALUE,
       IS_AGGREGATE,
       SUBSTR(AGGREGATE_NAME, 1, 14) AS AGGREGATE_NAME,
       LEVEL
  FROM TABLE(SYSPROC.REG_LIST_VARIABLES()) AS REGISTRYINFO

This query returns the following output:

DBPARTITIONNUM REG_VAR_NAME    REG_VAR_VALUE IS_AGGREGATE AGGREGATE_NAME LEVEL
-------------- --------------- ------------- ------------ -------------- -----
             0 DB2ADMINSERVER  DB2DAS00                 0              -     I
             0 DB2INSTPROF     D:\SQLLIB                0              -     I
             0 DB2PATH         D:\SQLLIB                0              -     I
             0 DB2SYSTEM       D570                     0              -     I
             0 DB2TEMPDIR      D:\SQLLIB\               0              -     I
             0 DB2_EXTSECURITY YES                      0              -     I
            
6 record(s) selected.