System Functions

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
`CALLER()`	Retrieves the name of the user executing the query. For jobs: when `runAs` = `OWNER`, `CALLER()` returns the owner of the job For procedures: when `EXECUTE AS` = `OWNER`, `CALLER()` returns the name of the user who calls the procedure	String
`CURRENT_DATABASE()`	Retrieves the catalog name of the database	String
`DV_SESSION_SET('name', value)`	Sets the indicated session variable to the indicated value	Object
`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 `key`	String
`SESSION_ID()`	Retrieves current session ID in string form	String
`REQUEST_ID()`	Retrieves the current request ID	Long
`USER()`	Retrieves the name of the user owning the query	String
`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

$body

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);;