DB2 Version 9.7 for Linux, UNIX, and Windows

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:


>>-+-+-CURRENT CLIENT_ACCTNG-+-----------------------+---------><
   | '-CLIENT ACCTNG---------'                       |   
   +-+-CURRENT CLIENT_APPLNAME-+---------------------+   
   | '-CLIENT APPLNAME---------'                     |   
   +-+-CURRENT CLIENT_USERID-+-----------------------+   
   | '-CLIENT USERID---------'                       |   
   +-+-CURRENT CLIENT_WRKSTNNAME-+-------------------+   
   | '-CLIENT WRKSTNNAME---------'                   |   
   +-+-CURRENT DATE-----+----------------------------+   
   | |              (1) |                            |   
   | '-CURRENT_DATE-----'                            |   
   +-CURRENT DBPARTITIONNUM--------------------------+   
   +-CURRENT DECFLOAT ROUNDING MODE------------------+   
   +-CURRENT DEFAULT TRANSFORM GROUP-----------------+   
   +-CURRENT DEGREE----------------------------------+   
   +-CURRENT EXPLAIN MODE----------------------------+   
   +-CURRENT EXPLAIN SNAPSHOT------------------------+   
   +-CURRENT FEDERATED ASYNCHRONY--------------------+   
   +-CURRENT IMPLICIT XMLPARSE OPTION----------------+   
   +-CURRENT ISOLATION-------------------------------+   
   +-CURRENT LOCALE LC_MESSAGES----------------------+   
   +-CURRENT LOCALE LC_TIME--------------------------+   
   +-CURRENT LOCK TIMEOUT----------------------------+   
   +-CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION-+   
   +-CURRENT MDC ROLLOUT MODE------------------------+   
   +-CURRENT OPTIMIZATION PROFILE--------------------+   
   +-CURRENT PACKAGE PATH----------------------------+   
   +-+-CURRENT PATH-----+----------------------------+   
   | |              (1) |                            |   
   | '-CURRENT_PATH-----'                            |   
   +-CURRENT QUERY OPTIMIZATION----------------------+   
   +-CURRENT REFRESH AGE-----------------------------+   
   +-+-CURRENT SCHEMA-----+--------------------------+   
   | |                (1) |                          |   
   | '-CURRENT_SCHEMA-----'                          |   
   +-+-CURRENT SERVER-----+--------------------------+   
   | |                (1) |                          |   
   | '-CURRENT_SERVER-----'                          |   
   +-CURRENT SQL_CCFLAGS-----------------------------+   
   +-+-CURRENT TIME-----+----------------------------+   
   | |              (1) |                            |   
   | '-CURRENT_TIME-----'                            |   
   +-+-CURRENT TIMESTAMP-----+--+---------------+----+   
   | |                   (1) |  '-(--integer--)-'    |   
   | '-CURRENT_TIMESTAMP-----'                       |   
   +-+-CURRENT TIMEZONE-----+------------------------+   
   | |                  (1) |                        |   
   | '-CURRENT_TIMEZONE-----'                        |   
   +-+-CURRENT USER-----+----------------------------+   
   | |              (1) |                            |   
   | '-CURRENT_USER-----'                            |   
   +-+-SESSION_USER-+--------------------------------+   
   | '-USER---------'                                |   
   '-SYSTEM_USER-------------------------------------'

Notes:

The SQL2003 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 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 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.