Special registers in SQL statements

 

You can specify certain special registers in SQL statements. A special register, for example, CURRENT DATE, contains information that can be referenced in SQL statements.

For locally run SQL statements, the special registers and their contents are shown in the following table.

Special registers	Contents
CURRENT DATE CURRENT_DATE	The current date.
CURRENT DEGREE	The number of tasks the database manager should run in parallel.
CURRENT PATH CURRENT_PATH CURRENT FUNCTION PATH	The SQL path used to resolve unqualified data type names, procedure names, and function names in dynamically prepared SQL statements.
CURRENT SCHEMA	The schema name used to qualify unqualified database object references where applicable in dynamically prepared SQL statements.
CURRENT SERVER CURRENT_SERVER	The name of the relational database currently being used.
CURRENT TIME CURRENT_TIME	The current time.
CURRENT TIMESTAMP CURRENT_TIMESTAMP	The current date and time in timestamp format.
CURRENT TIMEZONE CURRENT_TIMEZONE	A duration of time that links local time to Universal Time Coordinated (UTC) using the formula: local time - CURRENT TIMEZONE = UTC It is taken from the system value QUTCOFFSET.
SESSION_USER USER	The runtime authorization identifier (user profile) of the job.
SYSTEM_USER	The authorization identifier (user profile) of the user connected to the database.

If a single statement contains more than one reference to any of CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special registers, or the CURDATE, CURTIME, or NOW scalar functions, all values are based on a single clock reading.

For remotely run SQL statements, the special registers and their contents are shown in the following table.

Special registers	Contents
CURRENT DATE CURRENT_DATE CURRENT TIME CURRENT_TIME CURRENT TIMESTAMP CURRENT_TIMESTAMP	The current date and time at the remote system, not the local system.
CURRENT DEGREE	The number of tasks the data base manager should run in parallel on the remote system.
CURRENT TIMEZONE CURRENT_TIMEZONE	A duration of time that links the remote system time to UTC.
CURRENT SERVER CURRENT_SERVER	The name of the relational database currently being used.
CURRENT SCHEMA	The current schema value at the remote system.
CURRENT PATH CURRENT_PATH CURRENT FUNCTION PATH	The current path value at the remote system.
SESSION_USER USER	The runtime authorization identifier (user profile) of the job on the remote system.
SYSTEM_USER	The authorization identifier (user profile) of the user connected to the database on the remote system.

When a query over a distributed table references a special register, the contents of the special register on the system that requests the query are used. For more information about distributed tables, see the DB2^® Multisystem topic collection.

 

Parent topic:

Retrieving data using the SELECT statement