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.
To modify special register values for the behavior of specific dynamic SQL statements, complete the following steps:
- Create the profile and specify its filtering criteria by inserting values in a SYSIBM.DSN_PROFILE_TABLE row:
- 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.
- 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.
- 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.
- Specify the special register that you want to set by inserting a row into SYSIBM.DSN_PROFILE_ATTRIBUTES with the following column values:
- Insert the PROFILEID value from the related DSN_PROFILE_TABLE row that specifies the filtering criteria for this profile.
- Insert 'SPECIAL_REGISTER' in the KEYWORDS column.
- Insert values in the ATTRIBUTEn columns to specify the specific actions for the profile:
- 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
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:
- TIME ZONE
- SESSION TIMEZONE
- Load or reload the profile tables into memory by issuing a START PROFILE command, as described in Starting and stopping profiles.
|17||SPECIAL_REGISTER||SET CURRENT APPLICATION COMPATIBILITY = 'V10R1'||2013-10-23...|