Start of change

Rules for setting built-in global variables by using profile tables

In remote applications, some built-in global variables can be set with a value from the profile attributes table. When you set built-in global variables through the profile attributes table, be aware of the following rules and behavior.

You can set the following built-in global variables by specifying the GLOBAL_VARIABLE value for the KEYWORDS column in the DSN_PROFILE_ATRRIBUTES table. For detailed instructions, see Setting built-in global variables by using profile tables.

Global variable Examples
GET_ARCHIVE 
SET SYSIBMADM.GET_ARCHIVE = 'Y'
Start of changeFL 507 MAX_LOCKS_PER_TABLESPACEEnd of change Start of change 
SET SYSIBMADM.MAX_LOCKS_PER_TABLESPACE = NULL
End of change
Start of changeFL 507 MAX_LOCKS_PER_USEREnd of change Start of change 
SET SYSIBMADM.MAX_LOCKS_PER_USER = DEFAULT
End of change
MOVE_TO_ARCHIVE 
SET SYSIBMADM.MOVE_TO_ARCHIVE = 'N'
TEMPORAL_LOGICAL_TRASACTIONTIME 
SET SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME = '2014-11-14-08.05.01.123456789'
TEMPORAL_LOGICAL_TRANSACTIONS 
SET SYSIBM.TEMPORAL_LOGICAL_TRANSACTIONS = 0

The following rules apply to SET statements for built-in global variables in the DSN_PROFILE_ATTRIBUTES table:

  • The schema qualifier of the variable must be specified.
  • The SET statement must have an equal sign (=) between the global variable name and the value that is assigned.
  • Variable names can be specified in mixed case. Extraneous blanks between keywords are removed.
  • The value that is specified for assignment must be valid for the variable and must not be an expression, or reference other special registers or variables.
  • The value that is specified for assignment is passed through this interface as a string constant. 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.
  • The maximum length of the SET statement is 1024 bytes.
  • The statement must be a Unicode string and encoded with the appropriate CCSID for the application.
  • The variable is assigned the value and it is stored as specified.

Built-in global variable values persist for the lifetime of the connection unless the application explicitly sets the global variable. Built-in global variable values that are set explicitly by the application take precedence over values that are set in the profile tables. System directed connections, such as three-part name references, use the values of the global variables of the requesting Db2 site. For example, if a Java™ application establishes a connection to Db2 site 1, the global variable 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 global variable values that are established on site 1 are referenced when the SQL statement runs on site 2.

Commit and rollback operations have no effect on the values of global variables, regardless of how they were established.

The authorization that is required to set built-in global variables is the same that is required to insert rows into the DSN_PROFILE_TABLE and DSN_PROFILE_ATTRIBUTES tables.

End of change