Skip to main content
Skip table of contents

System Functions

You are looking at an older version of the documentation. The latest version is found here.

System functions provide access to information in the Data Virtuality Server system via a query. Here's an overview of them:

To view the full table, click the expand button in its top right corner


Function
Description
Return value type
CURRENT_DATABASE()Retrieves the catalog name of the databaseString
DV_SESSION_SET('name', value)Sets the indicated session variable to the indicated valueObject
DV_SESSION_GET('name')Retrieves the value (null if no value has been set)Object
ENV('key')Retrieves the system (i.e. Data Virtuality Server) property specified by keyString
SESSION_ID()Retrieves current session ID in string formString
REQUEST_ID()

Retrieves the current request ID

Long
USER()Retrieves the name of the user executing the queryString
LASTUPDATED('fqn')

Retrieves the oldest materialization timestamp from all query components. fqn can be the fully qualified name of a table/view or column. Returns NULL  if no component is materialized.

Example:

SELECT LASTUPDATED('schema_name.view_name'), LASTUPDATED('schema_name.view_name.column_name');;

Timestamp

Calling any system function is very simple - here's how to run USER(), for example:

SQL
SELECT USER();;
System functions' names are not case-sensitive: both USER() and user() will work.

Special Note on ENV('key')

One important thing to remember about this function is that it is not enabled by default for security reasons to prevent untrusted access to system properties. To enable it, you'll need to use the following command and restart the Data Virtuality Server:

SQL
CALL SYSADMIN.executeCli('/subsystem=teiid:write-attribute(name=allow-env-function,value=true)');;

Special Note on DV_SESSION_SET('name', value) and DV_SESSION_GET('name')

Unlike other system functions described above, these two go together, and their purpose is to set and read variables at the session level. 

The first function sets the indicated variable to the indicated value and returns the old value, and the second retrieves the current value (if no value has been set previously, null will be returned). Variables are key-value pairs; the name is a string, the value and the return value are objects. 

A special case is the dv.maxRecursion session variable: it is a session variable per se, with a fixed default value which can be modified using DV_SESSION_SET('name', value)

SQL
SELECT DV_SESSION_SET('dv.maxRecursion', 25);;
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.