General rules for special registers

Changing register values

A commit operation might cause special registers to be re-initialized. Whether a special register is affected by a commit depends on whether the special register has been explicitly set within the application process. For example, assume that the PATH special register has not been explicitly set with a SET PATH statement in the application process. After a commit, the value of PATH is re-initialized. For information on the initialization of PATH, which can take the current value of CURRENT SQLID into consideration, see CURRENT SQLID.

A rollback operation has no effect on the values of special registers. Nor does any SQL statement, with the following exceptions:

• SQL SET statements can change the values of the following special registers:
  • CURRENT APPLICATION ENCODING SCHEME
  • CURRENT DEBUG MODE
  • CURRENT DECFLOAT ROUNDING MODE
  • CURRENT DEGREE
  • CURRENT EXPLAIN MODE
  • CURRENT GET_ACCEL_ARCHIVE
  • CURRENT LOCALE LC_CTYPE
  • CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
  • CURRENT OPTIMIZATION HINT
  • CURRENT PACKAGE PATH
  • CURRENT PACKAGESET
  • CURRENT PATH
  • CURRENT PRECISION
  • CURRENT QUERY ACCELERATION
  • CURRENT REFRESH AGE
  • CURRENT ROUTINE VERSION
  • CURRENT RULES
  • CURRENT SCHEMA
  • CURRENT SQLID¹
  • ENCRYPTION PASSWORD
  • SESSION TIME ZONE
• SQL CONNECT statements can change the value of CURRENT SERVER.

Determining register values

You can use various statements to determine the value of a special register. For instance, a SELECT statement, a SET statement, the VALUES statement (if the statement is within a trigger action) will provide the value of a special register. The following examples find the value of the CURRENT PRECISION special register:

SELECT CURRENT PRECISION FROM SYSIBM.SYSDUMMY1;
SET :hv = CURRENT PRECISION 
VALUES(CURRENT PRECISION)

CCSIDS for register values

Special registers that contain character strings have an associated CCSID. The particular CCSID depends on the context in which the special register is referenced. For more information, see Determining the encoding scheme and CCSID of a string.

Datetime special registers

The datetime registers are named CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP. Datetime special registers are stored in an internal format. When two or more of these registers are implicitly or explicitly specified in a single SQL statement, they represent the same point in time. A datetime special register is implicitly specified when it is used to provide the default value of a datetime column.

If the SQL statement in which a datetime special register is used is in a user-defined function or stored procedure that is within the scope of a trigger, DB2® uses the timestamp for the triggering SQL statement to determine the special register value.

The values of these special registers are based on:

• The time-of-day clock of the processor for the server executing the SQL statement
• The TIMEZONE parameter for this processor. The TIMEZONE parameter is in SYS1.PARMLIB(CLOCKXX).

To evaluate the references when the statement is being executed, a single reading from the time-of-day clock is incremented by the number of hours, minutes, and seconds specified by the TIMEZONE parameter. The values derived from this are assumed to be the local date, time, or timestamp, where local means local to the DB2 that executes the statement. This assumption is correct if the clock is set to local time and the TIMEZONE parameter is zero or the clock is set to UTC (Coordinated Universal Time) and the TIMEZONE parameter gives the difference from UTC.

Because the datetime special registers and the CURRENT TIMEZONE special register depend on the parameter PARMTZ(SYS1.PARMLIB(CLOCKXX)), their values are affected if the local time at the server is changed by the z/OS® system command SET CLOCK. The values of the CURRENT DATE and CURRENT TIMESTAMP special registers might be affected if the local date at the server is changed by the system command SET DATE².

Where special registers are processed

In distributed applications, CURRENT APPLICATION ENCODING SCHEME, CURRENT SERVER, and CURRENT PACKAGESET are processed locally. All other special registers are processed at the server.