Special registers

A special register is a storage area that is defined for an application process by DB2® 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.

The special registers can be referenced as follows:

>>-+-CURRENT APPLICATION ENCODING SCHEME---------------------------------+-><
   +-CURRENT CLIENT_ACCTNG-----------------------------------------------+   
   +-CURRENT CLIENT_APPLNAME---------------------------------------------+   
   +-CURRENT CLIENT_USERID-----------------------------------------------+   
   +-CURRENT CLIENT_WRKSTNNAME-------------------------------------------+   
   +-+-CURRENT DATE-----+------------------------------------------------+   
   | |              (1) |                                                |   
   | '-CURRENT_DATE-----'                                                |   
   +-CURRENT DEBUG MODE--------------------------------------------------+   
   +-CURRENT DECFLOAT ROUNDING MODE--------------------------------------+   
   +-CURRENT DEGREE------------------------------------------------------+   
   +-CURRENT EXPLAIN MODE------------------------------------------------+   
   +-CURRENT GET_ACCEL_ARCHIVE-------------------------------------------+   
   |            .-LOCALE-.                                               |   
   +-+-CURRENT--+--------+--LC_CTYPE-+-----------------------------------+   
   | '-CURRENT_LC_CTYPE--------------'                                   |   
   |                     .-TABLE-.         .-FOR OPTIMIZATION-.          |   
   +-CURRENT MAINTAINED--+-------+--TYPES--+------------------+----------+   
   +-CURRENT MEMBER------------------------------------------------------+   
   +-CURRENT OPTIMIZATION HINT-------------------------------------------+   
   +-CURRENT PACKAGE PATH------------------------------------------------+   
   +-CURRENT PACKAGESET--------------------------------------------------+   
   +-+-CURRENT PATH-+----------------------------------------------------+   
   | '-CURRENT_PATH-'                                                    |   
   +-CURRENT PRECISION---------------------------------------------------+   
   +-CURRENT QUERY ACCELERATION------------------------------------------+   
   +-CURRENT REFRESH AGE-------------------------------------------------+   
   +-CURRENT ROUTINE VERSION---------------------------------------------+   
   +-CURRENT RULES-------------------------------------------------------+   
   +-+-CURRENT SCHEMA-----+----------------------------------------------+   
   | |                (1) |                                              |   
   | '-CURRENT_SCHEMA-----'                                              |   
   +-CURRENT SERVER------------------------------------------------------+   
   +-CURRENT SQLID-------------------------------------------------------+   
   +-+-CURRENT TIME-----+------------------------------------------------+   
   | |              (1) |                                                |   
   | '-CURRENT_TIME-----'                                                |   
   |                            .-(--6--)-------.  .-WITHOUT TIME ZONE-. |   
   +-+-CURRENT TIMESTAMP-----+--+---------------+--+-------------------+-+   
   | |                   (1) |  '-(--integer--)-'  '-WITH TIME ZONE----' |   
   | '-CURRENT_TIMESTAMP-----'                                           |   
   +-CURRENT TIME ZONE---------------------------------------------------+   
   +-SESSION TIME ZONE---------------------------------------------------+   
   |                     (2)                                             |   
   +-ENCRYPTION PASSWORD-------------------------------------------------+   
   '-+-SESSION_USER-+----------------------------------------------------'   
     '-USER---------'

Notes:

The SQL standard uses the form with the underline.
The ENCRYPTION PASSWORD special register can only be explicitly referenced in the SET ENCRYPTION PASSWORD statement. It is used implicitly used by the encryption and decryption functions.

General rules for special registers
CURRENT APPLICATION ENCODING SCHEME
CURRENT APPLICATION ENCODING SCHEME specifies which encoding scheme is to be used for dynamic statements. It allows an application to indicate the encoding scheme that is used to process data. This register is not supported in REXX applications or in stored procedures written in REXX.
CURRENT CLIENT_ACCTNG
CURRENT CLIENT_ACCTNG contains the value of the accounting string from the client information that is specified for the connection.
CURRENT CLIENT_APPLNAME
CURRENT CLIENT_APPLNAME contains the value of the application name from the client information that is specified for the connection.
CURRENT CLIENT_USERID
CURRENT CLIENT_USERID contains the value of the client user ID from the client information that is specified for the connection.
CURRENT CLIENT_WRKSTNNAME
CURRENT CLIENT_WRKSTNNAME contains the value of the workstation name from the client information that is specified for the connection.
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.
CURRENT DEBUG MODE
CURRENT DEBUG MODE specifies the default value for the DEBUG MODE option when certain routines are created. The DEBUG MODE option specifies whether the routine should be built with the ability to run in debugging mode.
CURRENT DECFLOAT ROUNDING MODE
CURRENT DECFLOAT ROUNDING MODE specifies the default rounding mode that is used for DECFLOAT values.
CURRENT DEGREE
CURRENT DEGREE specifies the degree of parallelism for the execution of queries that are dynamically prepared by the application process.
CURRENT EXPLAIN MODE
The CURRENT EXPLAIN MODE special register contains the values that control the EXPLAIN behavior in regards to eligible dynamic SQL statements.
CURRENT GET_ACCEL_ARCHIVE
The CURRENT GET_ACCEL_ARCHIVE special register specifies whether a dynamic SQL query that references a table that is archived on an accelerator server uses the archived data. The special register does not apply to static SQL queries.
CURRENT LOCALE LC_CTYPE
CURRENT LOCALE LC_CTYPE specifies the LC_CTYPE locale that will be used to execute SQL statements that use a built-in function that references a locale. Functions LCASE, UCASE, and TRANSLATE (with a single argument) refer to the locale when they are executed.
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION specifies a value that identifies the types of objects that can be considered to optimize the processing of dynamic SQL queries. This register contains a keyword representing table types.
CURRENT MEMBER
CURRENT MEMBER specifies the member name of a current DB2 data sharing member on which a statement is executing. The value of CURRENT MEMBER is a character string.
CURRENT OPTIMIZATION HINT
CURRENT OPTIMIZATION HINT specifies the user-defined optimization hint that DB2 should use to generate the access path for dynamic statements.
CURRENT PACKAGE PATH
CURRENT PACKAGE PATH specifies a value that identifies the path used to resolve references to packages that are used to execute SQL statements. This special register applies to both static and dynamic statements.
CURRENT PACKAGESET
CURRENT PACKAGESET specifies an empty string, a string of blanks, or the collection ID of the package that will be used to execute SQL statements.
CURRENT PATH
CURRENT PATH specifies the SQL path used to resolve unqualified data type 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).
CURRENT PRECISION
CURRENT PRECISION specifies the rules to be used when both operands in a decimal operation have precisions of 15 or less.
CURRENT QUERY ACCELERATION
The CURRENT QUERY ACCELERATION special register specifies a value that identifies when DB2 sends dynamic SQL queries to an accelerator server and what DB2 does if the accelerator server fails. The special register does not apply to static SQL queries.
CURRENT REFRESH AGE
CURRENT REFRESH AGE specifies a timestamp duration value. This duration is the maximum duration since a REFRESH TABLE statement has been processed on a system-maintained REFRESH DEFERRED materialized query table such that the materialized query table can be used to optimize the processing of a query. This special register affects dynamic statement cache matching.
CURRENT ROUTINE VERSION
CURRENT ROUTINE VERSION specifies the version identifier that is to be used when invoking a native SQL procedure. CURRENT ROUTINE VERSION is used for CALL statements that use a host variable to specify the procedure name.
CURRENT RULES
CURRENT RULES specifies whether certain SQL statements are executed in accordance with DB2 rules or the rules of the SQL standard.
CURRENT SCHEMA
The CURRENT SCHEMA special register specifies the schema name used to qualify unqualified database object references in dynamically prepared SQL statements.
CURRENT SERVER
CURRENT SERVER specifies the location name of the current server.
CURRENT SQLID
CURRENT SQLID specifies the SQL authorization ID of the process.
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.
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.
CURRENT TIME ZONE
The CURRENT TIME ZONE special register specifies a value that contains the difference between UTC and local time as defined by the current server, if the SESSION TIME ZONE special register has not been set.
ENCRYPTION PASSWORD
The ENCRYPTION PASSWORD special register specifies the encryption password and the password hint (if one exists) that are used by the encryption and decryption built-in functions.
SESSION_USER
SESSION_USER specifies the primary authorization ID of the process.
SESSION TIME ZONE
The SESSION TIME ZONE special register specifies a value that identifies the time zone of the application process.
USER
USER specifies the primary authorization ID of the process. The data type is VARCHAR(128). SESSION_USER is the preferred spelling.
Special registers in a user-defined function or a stored procedure
You can use all special registers in a user-defined function or a stored procedure. However, you can modify only some of those special registers.

Parent topic: Language elements