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 special register.

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 ACCELERATOR
  • CURRENT APPLICATION COMPATIBILITY
  • 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 LOCK TIMEOUT
  • CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
  • CURRENT OPTIMIZATION HINT
  • CURRENT PACKAGE PATH
  • CURRENT PACKAGESET
  • CURRENT PATH
  • CURRENT PRECISION
  • CURRENT QUERY ACCELERATION
  • CURRENT QUERY ACCELERATION WAITFORDATA
  • CURRENT REFRESH AGE
  • CURRENT ROUTINE VERSION
  • CURRENT RULES
  • CURRENT SCHEMA
  • CURRENT SQLID¹
  • CURRENT TEMPORAL BUSINESS_TIME
  • CURRENT TEMPORAL SYSTEM_TIME
  • ENCRYPTION PASSWORD
  • SESSION TIME ZONE
• SQL CONNECT statements can change the value of CURRENT SERVER.

Changing register values from IBM Data Server clients and drivers

In addition to using SQL SET statements, you can use the following IBM® Data Server client and driver interfaces to change the values of most of the special registers that are listed under Changing register values:

• IBM Data Server Driver for JDBC and SQLJ method DB2DataSource.setSpecialRegisters
• For non-Java™ clients, the <specialregisters> subsection in the in db2dsdriver.cfg file

Use of these interfaces has the following restrictions:

• You cannot change the values of the following special registers:
  • CURRENT APPLICATION ENCODING SCHEME
  • CURRENT PACKAGESET
• The special register names are allowed to be in mixed case. Extraneous blanks between keywords are removed.
• The special register assigned value is stored as specified. The value must be valid for the special register.
• Special register values cannot be expressions, cannot reference other special registers, and cannot reference global variables.
• For CURRENT REFRESH AGE, the value 99999999999999 is not supported. Use the value ANY instead.
• Db2 treats all special register values that are passed through the client and driver interfaces as literal strings. For example, if a specified special register value is the same as the name of a special register, Db2 stores the special register name, and not the special register value.

Determining register values

You can use various statements to determine the value of a special register. For instance, a SELECT statement or a SET statement 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 

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.