Expansion of Special-register

Values and Value Expressions › Expansion of Special-register

Expansion of Special-register

The expanded parameters of special-register identify system-supplied variables whose value is determined when the SQL statement in which they appear is executed.

Syntax

Expansion of special-register

►►─┬─ USER ──────────────┬────────────────────────────────────────────────────►◄
   ├─ GROUP ─────────────┤
   ├─ CURRENT DATE ──────┤
   ├─ CURRENT TIME ──────┤
   ├─ CURRENT TIMESTAMP ─┤
   ├─ CURRENT TIMEZONE ──┤
   ├─ CURRENT DATABASE ──┤
   ├─ CURRENT SCHEMA ────┤
   └─ CURRENT SQLID ─────┘

Note: SQL Standard Compatibility. All special registers except USER are CA IDMS extensions of the SQL standard.

Parameters

USER

Contains the authorization identifier of the user executing the SQL session.

This value is established when the user signs on to the teleprocessing monitor or when the batch application is started by the operating system.

If no user has been established, the value of USER is blanks.

GROUP

Contains the default group identifier associated with the executing user as defined to the security facility.

If no user has been established or if the user has not been assigned a default group, the value of GROUP is blank.

CURRENT DATE

Contains the current date when the SQL statement is executed.

CURRENT TIME

Contains the current time when the SQL statement is executed.

CURRENT TIMESTAMP

Contains the current date and time when the SQL statement is executed with a precision of millionths of a second.

CURRENT TIMEZONE

Contains the difference between current time and Greenwich Mean Time expressed as a time duration.

This value is calculated from operating system values.

CURRENT DATABASE

Contains the name of the database to which the SQL session is connected.

CURRENT SCHEMA

Contains the current schema identifier associated with the SQL session.

This value is established in the CURRENT SCHEMA parameter of a SET SESSION statement. If the SET SESSION statement has not been issued, it is the value of the SCHEMA profile variable associated with the user session.

If no value has been established, the value of CURRENT SCHEMA is blanks.

CURRENT SQLID

Is a synonym for CURRENT SCHEMA.

Usage

Values in CURRENT DATE, TIME, and TIMESTAMP

All occurrences of CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP appearing within a single SQL statement are effectively evaluated at the same time.

Data Types and Equivalent Scalar Functions of Special Register Variables

This table gives the data types of CA IDMS special registers and the equivalent scalar function invocation:

Special register	Data type	Equivalent Scalar Function
USER	CHARACTER(18)	USER()
GROUP	CHARACTER(18)
CURRENT DATE	DATE	CURDATE()
CURRENT TIME	TIME	CURTIME()
CURRENT TIMESTAMP	TIMESTAMP	NOW()
CURRENT TIMEZONE	DECIMAL (6,0)
CURRENT SCHEMA	CHARACTER (18)
CURRENT SQLID	CHARACTER (18)
CURRENT DATABASE	CHARACTER (8)	DATABASE()