Setting special registers by using profile tables

You can create profiles to specify that Db2 sets certain special register values for remote applications that use TCP/IP connections and meet the criteria that are defined in the profile. FL 500 Profiles can also specify SET CURRENT LOCK TIMEOUT statements for local applications.

To modify special register values for the behavior of specific dynamic SQL statements, 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:

      Application type	Filtering category
      Remote applications:	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.
      FL 500 Local applications:	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.

      Important: Although PKGNAME can be used as a filtering category for profile table rows that use the 'SPECIAL_REGISTER' value for KEYWORDS, when client drivers are used, you should not use PKGNAME alone or in combination with COLLID. For more information, see Using profile tables to control which Db2 for z/OS application compatibility levels to use for specific data server client applications.

      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 special register that you want to set by inserting a row into SYSIBM.DSN_PROFILE_ATTRIBUTES with the following column values:
   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. Insert 'SPECIAL_REGISTER' in the KEYWORDS column.
   3. In the ATTRIBUTEn columns, specify the attributes of the profile:

      ATTRIBUTE1

      For SPECIAL_REGISTER, specifies a SET statement that assigns a value to a special register. The column value consists of a string that contains the SET keyword, the name of the special register, an equal sign, and the value to be assigned.

      The SET statement must follow the rules for the SET special register statement. Also, the following rules apply to the ATTRIBUTE1 value for SET statements for all supported 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.
      • 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.

      The following table lists the SET statements that can be specified in a profile and considerations for specific special registers.

      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
      FL 500 SET CURRENT LOCK TIMEOUT statement	The following syntax variations are not supported in profiles: WAIT integer-constant MODE TO
      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

      ATTRIBUTE2

      For SPECIAL_REGISTER, specifies whether the profile applies to threads for remote applications, local applications, or both.

      FL 500 The value must be NULL unless the ATTRIBUTE1 value specifies a SET CURRENT LOCK TIMEOUT statement.

      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.

      1

      The row applies to local threads only. Profiles are evaluated and SET statements are processed when each package is loaded.

      2

      The row applies to local and remote threads. For remote threads, the behavior is the same as if the value is NULL. For local threads, the behavior is the same as if the value is 1.

      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.