How Db2 applies multiple matching profiles for threads and connections

Generally, when multiple profiles from different filtering categories specify overlapping criteria, Db2 applies all of them. However, when profiles specify overlapping criteria for the same filtering category, Db2 applies only one profile, usually the profile with more specific filtering criteria. Start of changeIf the filtering criteria of multiple profiles match exactly, only the newest is accepted when profiles are started, and the others are rejected as duplicates.End of change

Begin program-specific programming interface information.

Filtering categories for monitoring threads and connections

The filtering criteria that define for profiles for remote threads and connections are organized into the filtering categories shown in the following table. For profiles with overlapping criteria Db2 applies only one profile from each filtering category.
Table 1. Categories and columns used to specify valid profiles for threads and connections
Filtering category Columns to specify
Client IP address or domain name Specify only the LOCATION column. The value can be an IP address or domain name.

This category is the only accepted filtering criteria for profiles that specify the MONITOR CONNECTIONS.

If one of the following values is specified, this category can also be used for profiles that specify the MONITOR ALL THREADS or MONITOR ALL CONNECTONS keywords: '*', '::0', or '0.0.0.0'.
Client product identifier Specify only the PRDID column.
Role or authorization identifier Start of changeSpecify one or both of the following columns:
  1. ROLE
  2. AUTHID

Profiles that specify both ROLE and AUTHID take precedence over profiles that specify only one value. Profiles that specify only ROLE take precedence over profiles that specify only AUTHID.

Only one default profile is accepted that specifies '*' in the ROLE or AUTHID (or both) columns. If a profile uses '*' for one of these columns, Db2 rejects other profiles that specify '*' in either column as duplicates.

End of change
Location name, or location alias Specify only the location name or location alias in LOCATION column.

This category applies only to profiles that specify MONITOR THREADS and MONITOR IDLE THREADS.
Collection identifier or package name Specify one or both of the following columns:
  1. COLLID
  2. PKGNAME

Profiles that specify both COLLID and PKGNAME take precedence over profiles that specify only one value. Profiles that specify only COLLID take precedence over profiles that specify only PKGNAME.

Only one default profile is accepted that specifies '*' in the COLLID or PKGNAME (or both) columns. If a profile uses '*' for one of these columns, Db2 rejects other profiles that specify '*' for either column as duplicates.
Client application name, user ID, or workstation ID. Specify only one of the following columns:
  • CLIENT_APPLNAME
  • CLIENT_USERID
  • CLIENT_WRKSTNNAME

Rules for applying multiple profiles with overlapping filtering criteria

When a thread or connection matches the criteria of more than one profile, Db2 uses the following rules to determine which profiles to apply.
Tip: Start of change Profiles that use the MONITOR product-type CONNECTIONS FOR SECURITY do not interact with other profiles that monitor threads and connections, and many of the following rules do not apply to them. For more information , see Discovering and controlling secure TCP/IP connectivity with profile tablesEnd of change
Start of changeRule: Accept only the newest duplicate profileEnd of change
Start of changeFor profiles that specify identical filtering criteria in DSN_PROFILE_TABLE, Db2 accepts only the profile with the newest timestamp in the PROFILE_TIMESTAMP column. The other duplicate rows are rejected when you start the profiles.End of change
Rule: Apply all profiles from different filtering categories

For profiles that specify overlapping filtering criteria from different filtering categories, Db2 applies all profiles with matching filtering criteria.

For example, assume that DSN_PROFILE_TABLE contains rows with the following values:

PROFILEID LOCATION AUTHID PRDID
12 null null DSN13011
13 null JIM null
14 * null null

The actions specified in the DSN_PROFILE_ATTRIBUTES table for all three of these profiles apply to a thread from the JIM authorized ID, from a Db2 13 for z/OS requester, at any IP address.

Rule: Exact values take priority over wildcards, and wildcards take priority over defaults
For filtering criteria that accept wildcards or defaults, Db2 gives precedence to the profile with the more exact value for the same filtering criteria. That is, a profile that specifies an exact value in a particular column takes precedence over a profile that uses characters followed by an asterisk (*) to specify a wildcard, or a profile that uses single-byte asterisk ('*') to specify a default.

For example, Db2 uses the following order of precedence to determine which profile to apply for profiles that filter based on product identifiers in the PRDID column:

  1. Specific value, such as PRDID='DSN13011'
  2. Partial wildcard value, such as PRDID='DSN*'
  3. Default value, such as PRDID='*'

The result is that you can use wildcard or default profiles to specify strict thresholds that apply to most threads and connections, but you can also allow higher thresholds for threads and connections from specific environments or users.

For another example, assume that at DSN_PROFILE table contains rows with the following values:

PROFILEID AUTHID
21 AUTHID="USER1'
22 AUTHID='USER*'
23 AUTHID='*'

Also assume that the DSN_PROFILE_ATTRIBUTES table contains rows with the following values:

PROFILEID KEYWORDS ATTRIBUTE1 ATTRIBUTE2
21 MONITOR THREADS EXCEPTION 100
22 MONITOR THREADS EXCEPTION 50
23 MONITOR THREADS EXCEPTION 20

In this case, the profiles with PROFILEID=22 or PROFILEID=23 do not apply to threads from the USER1 authorization ID. USER1 can have as many as 100 threads before a request is rejected, whereas other authorization IDs that begin with USER can have only 50 threads, and authorization IDs that do not begin with USER can only have 20 threads.

However, threads from USER1 do count against the thresholds for evaluating all three profiles, so if USER1 has 50 or more active threads, threads from all other authorization IDs are rejected.

Rule: Apply only one profile for the same filtering category
For profiles that specify overlapping filtering criteria in the same filtering category, Db2 applies only one profile.

For example, assume that DSN_PROFILE_TABLE contains rows with the following values:

PROFILEID AUTHID
17 *
18 JIM

Also assume that the DSN_PROFILE_ATTRIBUTES table contains rows with the following values:

PROFILEID KEYWORDS
17 SPECIAL_REGISTER
18 MONITOR IDLE THREADS

Only the PROFILEID=18 applies to any thread from the JIM authorization ID. In this example, the special register setting specified by the profile with PROFILEID=17 does not apply to any thread from the JIM authorization ID.

Rule: Different filtering categories take precedence
Db2 evaluates the filtering criteria for profiles in the following order of precedence. This list also illustrates the next two rules.
  1. IP address or domain name, in the LOCATION column.
  2. Product identifier, in the PRDID column.
  3. Role and authorization identifier, in both ROLE and AUTHID columns. Within this category, Db2 uses the following order of precedence:
    1. ROLE and AUTHID
    2. ROLE only
    3. AUTHID only
  4. Server location name, location alias, or database name, in the LOCATION column.
  5. The location name of a requester, for monitored threads from a Db2 for z/OS requester, in the LOCATION column.
  6. Collection identifier and package name, in both COLLID and PKGNAME columns. Within this category, Db2 uses the following order of precedence:
    1. COLLID and PKGNAME
    2. COLLID only
    3. PKGNAME only
  7. Client application name, user identifier, or workstation name, in the following columns:
    1. CLIENT_APPLNAME
    2. CLIENT_USERID
    3. CLIENT_WRKSTNNAME
Rule: Apply the profile that specifies more criteria in the same filtering category
Db2 applies the profile that specifies more criteria in a category. That is Db2 applies profiles in the following order of precedence.

For example assume that DSN_PROFILE_TABLE contains rows with the following values:

PROFILEID ROLE AUTHID
17 ROLE_DBA *
18 null USER1

Db2 applies the profile with PROFILEID=17 because it specifies more criteria.

Rule: Certain criteria take priority within filtering categories
Within certain categories, Db2 gives priority to certain criteria.
  • ROLE takes precedence over AUTHID.
  • COLLID takes precedence over PKGNAME.
For example, assume that at DSN_PROFILE table contains rows with the following values:
PROFILEID COLLID PKGNAME
19 null PKG1
20 COLL1 null

Db2 applies the profile with PROFILEID=20 because COLLID always takes precedence over PKGNAME.

End program-specific programming interface information.