Start of change

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.

Before you begin

If the Db2 profile tables and related objects does not exist on the Db2 subsystem, you must create them. For a list of the objects and how to create them, see Profile tables.

Start of changeIf you plan to use TCP/IP domain names for profile filtering, you must enable the database services address space (ssnmDBM1) to access TCP/IP services. See Enabling Db2 to access TCP/IP services in z/OS UNIX System Services.End of change

The distributed data facility (DDF) must be loaded, with the DDF subsystem parameter set to AUTO or COMMAND. See DDF STARTUP OPTION field (DDF subsystem parameter).

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 by using profile tables.

About this task

You can use profile tables to specify the names of procedures that can share locks with any transactions that the procedure invokes in RRS contexts, such as CICS. For more information, see the ATTRIBUTE1 description.

One or more procedure names are identified in the applicable profile when connection is first established by an application. The profile must be defined where the stored procedure runs.

Procedure

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. Start of changeIf 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.End of change 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: Start of changeUse 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.End of change
    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.

Example

Suppose that you insert the following row in SYSIBM.DSN_PROFILE_ATTRIBUTES:

PROFILEID KEYWORDS ATTRIBUTE1 ATTRIBUTE TIMESTAMP
1 SHARE_LOCKS PROCEDURE_LIST=ACCTG.UPDATE_ADDRESS 2021-10-23...

This profile row specifies that the ACCTG.UPDATE_ADDRESS stored procedure can share locks with distributed threads.

End of change