Setting special registers with profile tables

You can create profiles to specify that Db2 sets certain special register values for remote applications that meet the criteria that are defined in the profile.

Before you begin

You must create a set of profile tables and related objects on the Db2 subsystem. For a list of objects and how to create them, see Profile tables.

This task describes one of several uses for profile tables. For an overview of how to use profile tables and a summary of the different uses, see Monitoring and controlling Db2 with profile tables.

About this task

You can use profile tables to set the value of certain Db2 special registers. For the list of these special registers, see the description of ATTRIBUTE1.

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

Procedure

To modify special register values for the behavior of specific dynamic SQL statements, complete the following steps:

  1. Create the profile and specify its filtering criteria by inserting values in a SYSIBM.DSN_PROFILE_TABLE row:
    1. Insert a unique value in the PROFILEID column, or accept the generated default value. This value identifies the profile and the relationship between DSN_PROFILE_TABLE and DSN_PROFILE_ATTRIBUTES rows.
    2. Insert values to specify the filtering criteria of the profile.
      The values that you insert must be 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_WORKSTNNAME

      Other filtering columns must contain the null value.

      Tip: If you create multiple profiles with matching filtering criteria, Db2 applies only one profile from each filtering category, based on a specific order of precedence. Also, exact values take precedence over values that use an asterisk (*) wildcard. However, profiles from different filtering categories can all apply. For more about these rules, see How Db2 applies multiple matching profiles for threads and connections.
    3. Insert 'Y' in the PROFILE_ENABLED column to specify that the profile is enabled when profiles are started.
      The profile starts when you issue a START PROFILE command or when Db2 starts, if the PROFILE_AUTOSTART subsystem parameter setting is YES.
  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. Insert the PROFILEID value from the related DSN_PROFILE_TABLE row that specifies the filtering criteria for this profile.
    2. Insert 'SPECIAL_REGISTER' in the KEYWORDS column.
    3. Insert values in the ATTRIBUTEn columns to specify the specific actions for 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 specified for assignment must be valid for the special register and must not be an expression, or reference other special registers or variables
      • The value specified for assignment is passed through this interface as a literal string. 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.
      • Special register names can be specified in mixed case, and extraneous blanks are allowed between keywords.
      • 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  
      SET CURRENT APPLICATION COMPATIBILITY  
      SET CURRENT DEBUG MODE  
      SET CURRENT DECFLOAT ROUNDING MODE  
      SET CURRENT DEGREE  
      SET CURRENT EXPLAIN MODE  
      SET CURRENT GET_ACCEL_ARCHIVE  
      SET CURRENT LOCALE LC_CTYPE The following syntax variations are not supported by profiles:
      • CURRENT LC_CTYPE
      • CURRENT_LC_CTYPE
      SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION The following syntax variations are not supported by profiles:
      • CURRENT MAINTAINED TYPES
      • CURRENT MAINTAINED TYPES FOR OPTIMIZATION
      SET CURRENT OPTIMIZATION HINT  
      SET CURRENT PACKAGE PATH  
      SET CURRENT PRECISION  
      SET CURRENT QUERY ACCELERATION  
      SET CURRENT QUERY ACCELERATION WAITFORDATA  
      SET CURRENT REFRESH AGE The value 99999999999999 is not supported.

      Use the value ANY instead.

      SET CURRENT ROUTINE VERSION  
      SET CURRENT RULES  
      SET CURRENT SQLID 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  
      SET CURRENT TEMPORAL SYSTEM_TIME  
      SET ENCRYPTION PASSWORD  
      SET PATH  
      SET SCHEMA The following syntax variations are not supported in profiles:
      • CURRENT_SCHEMA (with underscore)
      SET SESSION TIME ZONE The following syntax variations are not supported in profiles:
      • TIMEZONE
      • TIME ZONE
      • SESSION TIMEZONE
      ATTRIBUTE2
      NULL
      ATTRIBUTE3
      NULL
  3. Load or reload the profile tables into memory by issuing a START PROFILE command, as described in Starting and stopping profiles.

Example

Suppose that you insert the following row in SYSIBM.DSN_PROFILE_ATTRIBUTES:
PROFILEID KEYWORDS ATTRIBUTE1 ATTRIBUTE TIMESTAMP
17 SPECIAL_REGISTER SET CURRENT APPLICATION COMPATIBILITY = 'V10R1' 2013-10-23...
This row specifies that Db2 is to set the APPLICATION COMPATIBILITY special register to 'V10R1' when referenced by dynamic SQL for applications that match the filtering criteria specified in the row with PROFILEID='17' DSN_PROFILE_TABLE row.