Setting built-in global variables by using profile tables

You can create profiles to specify that Db2 sets global variable values for applications that meet the criteria that are defined in the profile.

To set global variables for specific remote applications, complete the following steps:

1. In the SYSIBM.DSN_PROFILE_TABLE table, insert a row to create the profile and specify its filtering criteria:
   1. In the PROFILEID column, specify a unique value or accept the generated default value. This value identifies the profile and the relationship between DSN_PROFILE_TABLE and DSN_PROFILE_ATTRIBUTES rows.
   2. Specify the filtering criteria of the profile.
      You can specify values in the columns from one of the following filtering categories:

      • LOCATION only
      • PRDID only
      • AUTHID, ROLE, or both
      • COLLID, PKGNAME, or both
      • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME

      The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.

      Other filtering columns must contain the null value.

      Tip: If you create multiple profiles with overlapping filtering criteria, Db2 applies only one profile from each filtering category, based on a specific order of precedence. If multiple DSN_PROFILE_TABLE rows specify the same filtering criteria, only the newest is row is accepted when you start the profiles, and the other duplicates are rejected. Also, exact values take precedence over values that use an asterisk (*) wildcard. However, profiles from different filtering categories can all apply. For more information about these rules, see How Db2 applies multiple matching profiles for threads and connections.
   3. In the PROFILE_ENABLED column, specify 'Y' so that the profile is enabled when profiles are started.
      If the PROFILE_AUTOSTART subsystem parameter setting is YES, the profile starts when you issue a START PROFILE command or when Db2 starts.
2. Specify the global variables that you want to set by inserting SYSIBM.DSN_PROFILE_ATTRIBUTES rows:
   1. Specify the PROFILEID value from the DSN_PROFILE_TABLE row that specifies the filtering criteria for this profile.
      Tip: Use the same PROFILEID value for any DSN_PROFILE_ATTRIBUTES rows that require the same filtering criteria. If multiple DSN_PROFILE_TABLE rows contain exactly matching filtering criteria, only the newest duplicate row is accepted when you start the profiles, and the others are rejected and disabled.
   2. In the KEYWORDS column, specify 'GLOBAL_VARIABLE'.
   3. In the ATTRIBUTEn columns, specify the attributes of the profile:

   ATTRIBUTE1

   For GLOBAL_VARIABLE, specifies a SET statement to provide a value for a built-in global variable.

   The SET statement must adhere to the rules described in SET assignment-statement. Also, the following rules apply to the ATTRIBUTE1 value for SET statements for all supported built-in global variables:

   • 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.

   You can set the following global variable values by using profile tables:

   Global variable	Examples
   GET_ARCHIVE	SET SYSIBMADM.GET_ARCHIVE = 'Y'
   FL 507 MAX_LOCKS_PER_TABLESPACE	SET SYSIBMADM.MAX_LOCKS_PER_TABLESPACE = NULL
   FL 507 MAX_LOCKS_PER_USER	SET SYSIBMADM.MAX_LOCKS_PER_USER = DEFAULT
   MOVE_TO_ARCHIVE	SET SYSIBMADM.MOVE_TO_ARCHIVE = 'N'
   TEMPORAL_LOGICAL_TRANSACTION_TIME	SET SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME = '2014-11-14-08.05.01.123456789'
   TEMPORAL_LOGICAL_TRANSACTIONS	SET SYSIBM.TEMPORAL_LOGICAL_TRANSACTIONS = 0

   ATTRIBUTE2

   NULL

   The profile applies to remote threads only. The profile is evaluated and SET statements are processed only when the first package is loaded, and when the first SQL statement (other than a SET statement) in the package executes.

   ATTRIBUTE3

   NULL

3. Load or reload the profile tables into memory by issuing a START PROFILE command. (For best results, do not issue a STOP PROFILE command when you add or modify existing profiles. Use the STOP PROFILE command only if you intend to disable all existing profiles.) For more information, see Starting and stopping profiles.
4. Check the status of all newly added profiles in the STATUS columns of the DSN_PROFILE_HISTORY and DSN_PROFILE ATTRIBUTES_HISTORY tables.
   Successful completion of the START PROFILE command does not imply that all profiles started successfully. If the STATUS column of either history table contains a value that does not start with 'ACCEPTED', further action is required to enable the profile or the keyword action.