Special Registers

A special register is a storage area that is defined for an application process by the database manager and is used to store information that can be referenced in SQL statements. A reference to a special register is a reference to a value provided by the current server. If the value is a string, its CCSID is a default CCSID of the current server. DB2 UDB for AS/400 includes the following special registers.

CURRENT DATE or CURRENT_DATE

The CURRENT DATE special register

specifies a date that is based on a reading of the time-of-day clock when the SQL statement is executed at the current server. All values are based on a single clock reading in the following situations:

• this special register is used more than once within a single SQL statement
• this special register is used with the CURRENT TIME or CURRENT TIMESTAMP special registers or the CURDATE, CURTIME, or NOW scalar functions within a single statement

Example

Using the PROJECT table, set the project end date (PRENDATE) of the MA2111 project (PROJNO) to the current date.

   UPDATE PROJECT
     SET PRENDATE = CURRENT DATE
     WHERE PROJNO = 'MA2111'

CURRENT PATH, CURRENT_PATH, or CURRENT FUNCTION PATH

The CURRENT PATH special register specifies the SQL path used to resolve unqualified data type names (both built-in and distinct type), procedure names, and function names in dynamically prepared SQL statements. It is also used to resolve unqualified procedure names that are specified as host variables in SQL CALL statements (CALL host-variable). The data type is VARCHAR(558).

The CURRENT PATH special register contains a list of one or more collection names, where each collection name is enclosed in delimiters and separated from the following collection by a comma. The delimiters and commas are included in the 558 character length.

For information on when the SQL path is used to resolve unqualified names in both dynamic and static SQL statements and the effect of its value, see Collections and the SQL Path.

The initial value of the CURRENT PATH special register in an activation group is established by the first SQL statement that is executed.

• If the first SQL statement in an activation group is executed from an SQL program or SQL package and the SQLPATH parameter was specified on the CRTSQLxxx command, the path is the value specified in the SQLPATH parameter. The SQLPATH value can also be specified using the SET OPTION statement.
• Otherwise,
  • For SQL naming, "QSYS", "QSYS2", "the value of the authorization ID of the statement" .
  • For system naming, "*LIBL".

You can change the value of the register by executing the statement SET PATH. For details about this statement, see SET PATH.

Example

Set the special register so that collection SMITH is searched before collections QSYS and QSYS2 (SYSTEM PATH).

   SET CURRENT PATH SMITH, SYSTEM PATH

CURRENT SERVER or CURRENT_SERVER

The CURRENT SERVER special register specifies a VARCHAR(18) value that identifies the current application server.

CURRENT SERVER can be changed by the CONNECT (Type 1), CONNECT (Type 2), or SET CONNECTION statements, but only under certain conditions. See the description in "CONNECT (Type 1)", "CONNECT (Type 2)", and "SET CONNECTION".

CURRENT SERVER cannot be specified unless the local relational database is named by adding the entry to the relational database directory using the ADDRDBDIRE or WRKRDBDIRE command.

Example

Set the host variable APPL_SERVE (VARCHAR(18)) to the name of the current server.

   SELECT CURRENT SERVER
     INTO :APPL_SERVE
     FROM ROW1_TABLE

CURRENT TIME or CURRENT_TIME

The CURRENT TIME special register

specifies a time that is based on a reading of the time-of-day clock when the SQL statement is executed at the current server. All values are based on a single clock reading in the following situations:

• this special register is used more than once within a single SQL statement
• this special register is used with the CURRENT DATE or CURRENT TIMESTAMP special registers or the CURDATE, CURTIME, or NOW scalar functions within a single statement

Example

Using the CL_SCHED table, select all the classes (CLASS_CODE) that start (STARTING) later today. Today's classes have a value of 3 in the DAY column.

   SELECT CLASS_CODE FROM CL_SCHED
     WHERE STARTING > CURRENT TIME  AND DAY = 3

CURRENT TIMESTAMP or CURRENT_TIMESTAMP

The CURRENT TIMESTAMP special register

specifies a timestamp that is based on a reading of the time-of-day clock when the SQL statement is executed at the current server. All values are based on a single clock reading in the following situations:

• this special register is used more than once within a single SQL statement
• this special register is used with the CURRENT DATE or CURRENT TIME special registers or the CURDATE, CURTIME, or NOW scalar functions within a single statement

Example

Insert a row into the IN_TRAY table. The value of the RECEIVED column should be a timestamp that indicates when the row was inserted. The values for the other three columns come from the host variables SRC (CHAR(8)), SUB (CHAR(64)), and TXT (VARCHAR(200)).

   INSERT INTO IN_TRAY
     VALUES (CURRENT TIMESTAMP, :SRC, :SUB, :TXT)

CURRENT TIMEZONE or CURRENT_TIMEZONE

The CURRENT TIMEZONE special register specifies the difference between Universal Time Coordinated (UTC)¹⁸ and local time at the current server. The difference is represented by a time duration (a decimal number in which the first two digits are the number of hours, the next two digits are the number of minutes, and the last two digits are the number of seconds). The number of hours is between -24 and 24 exclusive. Subtracting CURRENT TIMEZONE from a local time converts that local time to UTC.

Example

Using the IN_TRAY table, select all the rows from the table and adjust the value to UTC.

   SELECT RECEIVED - CURRENT TIMEZONE, SOURCE,
         SUBJECT, NOTE_TEXT FROM IN_TRAY

USER

The USER special register specifies the run-time authorization ID at the current server. The data type of the special register is VARCHAR(18).

Example

Select all notes from the IN_TRAY table that the user placed there himself.

   SELECT * FROM IN_TRAY
     WHERE SOURCE = USER

¹⁸

Formerly known as Greenwich Mean Time (GMT).