SQL Special Registers || DB2 for i SQL

Some Special Registers in SQL DB2 for i SQL

We can use certain Special registers in our SQL queries. For example, CURRENT DATE, CURRENT TIME etc. that contains information that can be referenced in the SQL queries.

Special Register

Some of the special register we use are listed here:

CURRENT DATE or CURRENT_DATE: gives the current date of the local system.

SELECT current date FROM sysibm.sysdummy1

Or

SELECT current_date FROM sysibm.sysdummy1

Output:

CURRENT DATE
  02/14/24  

CURRENT TIME or CURRENT_TIME: gives the current time of the local system.

SELECT current time FROM sysibm.sysdummy1

Or

SELECT current_time FROM sysibm.sysdummy1

Output:

CURRENT TIME
  21:49:31  

CURRENT TIMESTAMP or CURRENT_TIMESTAMP: gives the current date and time in the timestamp format.

SELECT current timestamp FROM sysibm.sysdummy1

Or

SELECT current_timestamp FROM sysibm.sysdummy1

Output:

CURRENT TIMESTAMP         
2024-02-14-21.50.51.975954

CURRENT USER or CURRENT_USER: gives the primary job user.

SELECT current user FROM sysibm.sysdummy1

Or

SELECT current_user FROM sysibm.sysdummy1

Output:

CURRENT USER
EASYCLASS   

USER or SESSION_USER: gives the runtime user profile of the job.

SELECT user FROM sysibm.sysdummy1

Or

SELECT session_user FROM sysibm.sysdummy1

Output:

SESSION_USER
EASYCLASS   

SYSTEM_USER: gives the user profile of the job connected to the database.

SELECT SYSTEM_USER FROM sysibm.sysdummy1

Output:

SYSTEM_USER
EASYCLASS