Rules for setting special registers by using profile tables

In remote applications, a special register can be referenced with a value from the profile attributes table. In addition to general rules, there are rules specific to using special registers set in profiles.

Changing register values

A rollback operation has no effect on the values of special registers.

SET statements for the following special registers can be specified in DSN_PROFILE_ATTRIBUTES table with the SPECIAL_REGISTER keyword. For detailed instructions, see Setting special registers by using profile tables.

SET special register statement Profile-specific considerations
SET CURRENT ACCELERATOR statement  
SET CURRENT APPLICATION COMPATIBILITY statement  
SET CURRENT DEBUG MODE statement  
SET CURRENT DECFLOAT ROUNDING MODE statement  
SET CURRENT DEGREE statement  
SET CURRENT EXPLAIN MODE statement  
SET CURRENT GET_ACCEL_ARCHIVE statement  
SET CURRENT LOCALE LC_CTYPE statement The following syntax variations are not supported in profiles:
  • CURRENT LC_CTYPE
  • CURRENT_LC_CTYPE
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement The following syntax variations are not supported in profiles:
  • CURRENT MAINTAINED TYPES
  • CURRENT MAINTAINED TYPES FOR OPTIMIZATION
SET CURRENT OPTIMIZATION HINT statement  
SET CURRENT PACKAGE PATH statement  
SET CURRENT PRECISION statement  
SET CURRENT QUERY ACCELERATION statement  
SET CURRENT QUERY ACCELERATION WAITFORDATA statement  
SET CURRENT REFRESH AGE statement The value 99999999999999 is not supported.

Use the value ANY instead.
SET CURRENT ROUTINE VERSION statement  
SET CURRENT RULES statement  
SET CURRENT SQLID statement If the SET CURRENT SQLID statement is run in a stored procedure or user-defined function package that has a dynamic SQL behavior other than run behavior, the SET CURRENT SQLID statement does not affect the authorization ID that is used for dynamic SQL statements in the package. The dynamic SQL behavior determines the authorization ID.

For more information, see DYNAMICRULES bind option.
SET CURRENT TEMPORAL BUSINESS_TIME statement  
SET CURRENT TEMPORAL SYSTEM_TIME statement  
SET ENCRYPTION PASSWORD statement  
SET PATH statement  
SET SCHEMA statement The following syntax variations are not supported in profiles:
  • CURRENT_SCHEMA (with underscore)
SET SESSION TIME ZONE statement The following syntax variations are not supported in profiles:
  • TIMEZONE
  • TIME ZONE
  • SESSION TIMEZONE

The following rules apply for the SET statements for special registers:

  • An equal sign (=) must be specified between the special register name and the value that is assigned.
  • The value that is specified for assignment must be valid for the special register.
  • The value must not be an expression, or reference other special registers or variables, unless the statement is SET CURRENT PACKAGE PATH.
  • The value that is specified for assignment is passed through this interface as a literal string, unless the statement is SET CURRENT PACKAGE PATH. For example, if a value to be assigned is the same as the name of a special register, Db2 stores the special register name, and not the special register value, and the statement returns SQL code -725.
  • The maximum length of a SET statement is 1024 bytes.
  • The statement must be a Unicode string and encoded with the appropriate CCSID for the application.

Special register values that are set through the profile table take precedence over values that are set by the application before the first non-SET SQL statement. For example, values set by a profile override values that are set through a client connection or data source properties when the connection is first established. The special register values persist for the lifetime of the connection unless the application explicitly sets the special register. Special register values that are set explicitly by the application take precedence over values that are set by the profile table facility and values that are set by the client connection and data source properties. System directed connections, such as three-part name references, use the values of the special registers of the requesting Db2 site. For example, if a Java™ application establishes a connection to Db2 site 1, the special register values are established with the profile tables from site 1. Later, if the application runs an SQL statement with an implicit three-part name connection to Db2 site 2, then the special register values that are established on site 1 are referenced when the SQL statement runs on site 2.

Special register values that are set through the profile table facility observe the existing rules for inheritance within a routine as defined by INHERIT SPECIAL REGISTERS or DEFAULT SPECIAL REGISTERS routine options. After a user-defined function or a stored procedure completes, Db2 restores all special registers to the values they had before invocation.

As with other special register behavior, commits and rollbacks do not change special register values regardless of how they were established.