Sharing locks for stored procedures that invoke transactions in RRS contexts by using profile tables

You can create profiles to allow external stored procedures to share locks.

To specify stored procedures that can share locks in RSS contexts, 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 names of stored procedures that can share locks by inserting one or more rows in the SYSIBM.DSN_PROFILE_ATTRIBUTES table, 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 SHARE_LOCKS in the KEYWORDS column.
   3. In the ATTRIBUTEn columns, specify the attributes of the profile:

      ATTRIBUTE1

      Assigns one or more stored procedure names to allow the sharing of locks with any transactions that it invokes in an RRS context, such as a CICS transaction through the External CICS interface (EXCI). The value must be in the following format:

      PROCEDURE_LIST=procedure-name, procedure-name, ...

      Each procedure-name must identify an external procedure (not an external SQL procedure), be qualified with the procedure schema, and not specify a three-part name. The length attribute of the ATTRIBUTE1 value must not exceed 1024 bytes.

      ATTRIBUTE2

      NULL

      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.