Special registers

A special register is a storage area that is defined for an application process by the database manager. It 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.

The special registers can be referenced as follows:

Read syntax diagramSkip visual syntax diagramCURRENT CLIENT_ACCTNGCLIENT ACCTNGCURRENT CLIENT_APPLNAMECLIENT APPLNAMECURRENT CLIENT_USERIDCLIENT USERIDCURRENT CLIENT_WRKSTNNAMECLIENT WRKSTNNAMECURRENT DATECURRENT_DATE1CURRENT DBPARTITIONNUMCURRENT DECFLOAT ROUNDING MODECURRENT DEFAULT TRANSFORM GROUPCURRENT DEGREECURRENT EXPLAIN MODECURRENT EXPLAIN SNAPSHOTCURRENT FEDERATED ASYNCHRONYCURRENT IMPLICIT XMLPARSE OPTIONCURRENT ISOLATIONCURRENT LOCALE LC_MESSAGESCURRENT LOCALE LC_TIMECURRENT LOCK TIMEOUTCURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATIONCURRENT MDC ROLLOUT MODECURRENT MEMBERCURRENT OPTIMIZATION PROFILECURRENT PACKAGE PATHCURRENT PATHCURRENT_PATH1CURRENT QUERY OPTIMIZATIONCURRENT REFRESH AGECURRENT SCHEMACURRENT_SCHEMA1CURRENT SERVERCURRENT_SERVER1CURRENT SQL_CCFLAGSCURRENT TEMPORAL BUSINESS_TIMECURRENT TEMPORAL SYSTEM_TIMECURRENT TIMECURRENT_TIME1CURRENT TIMESTAMPCURRENT_TIMESTAMP1(integer)CURRENT TIMEZONECURRENT_TIMEZONE1CURRENT USERCURRENT_USER1SESSION_USERUSERSYSTEM_USER
Notes:
  • 1 The SQL2008 Core standard uses the form with the underscore.

Some special registers can be updated using the SET statement. The following table shows which of the special registers can be updated as well as indicating which special register can be the null value.

Table 1. Updatable and nullable special registers
Special Register Updatable Nullable
CURRENT CLIENT_ACCTNG No No
CURRENT CLIENT_APPLNAME No No
CURRENT CLIENT_USERID No No
CURRENT CLIENT_WRKSTNNAME No No
CURRENT DATE No No
CURRENT DBPARTITIONNUM No No
CURRENT DECFLOAT ROUNDING MODE No No
CURRENT DEFAULT TRANSFORM GROUP Yes No
CURRENT DEGREE Yes No
CURRENT EXPLAIN MODE Yes No
CURRENT EXPLAIN SNAPSHOT Yes No
CURRENT FEDERATED ASYNCHRONY Yes No
CURRENT IMPLICIT XMLPARSE OPTION Yes No
CURRENT ISOLATION Yes No
CURRENT LOCALE LC_MESSAGES special register Yes No
CURRENT LOCALE LC_TIME Yes No
CURRENT LOCK TIMEOUT Yes Yes
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION Yes No
CURRENT MDC ROLLOUT MODE Yes No
CURRENT MEMBER No No
CURRENT OPTIMIZATION PROFILE Yes Yes
CURRENT PACKAGE PATH Yes No
CURRENT PATH Yes No
CURRENT QUERY OPTIMIZATION Yes No
CURRENT REFRESH AGE Yes No
CURRENT SCHEMA Yes No
CURRENT SERVER No No
CURRENT SQL_CCFLAGS Yes No
CURRENT TEMPORAL BUSINESS_TIME Yes Yes
CURRENT TEMPORAL SYSTEM_TIME Yes Yes
CURRENT TIME No No
CURRENT TIMESTAMP No No
CURRENT TIMEZONE No No
CURRENT USER No No
SESSION_USER Yes No
SYSTEM_USER No No
USER Yes No

When a special register is referenced in a routine, the value of the special register in the routine depends on whether the special register is updatable or not. For non-updatable special registers, the value is set to the default value for the special register. For updatable special registers, the initial value is inherited from the invoker of the routine and can be changed with a subsequent SET statement inside the routine.