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. 
If the filtering criteria of multiple profiles match exactly, only the newest is accepted when profiles are started, and the others are rejected as duplicates.
Filtering categories for monitoring threads and connections
For profiles with overlapping criteria Db2 applies only one profile from each filtering category.
| 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.
|
| Client product identifier | Specify only the PRDID column. |
| Role or authorization identifier | Specify one or both of the following columns:
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. Default profiles specified by '*' cannot be used for both ROLE and AUTHID. If it is used, '*' must be specified for only one of these attributes. |
| 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:
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. Default profiles specified by '*' cannot be used for both COLLID and PKGNAME. If it is used, '*' must be specified for only one of these attributes. |
| Client application name, user ID, or workstation ID. | Specify only one of the following columns:
|
Rules for applying multiple profiles with overlapping filtering criteria
- Rule: Accept only the newest duplicate profile
- For 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.
- 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 DSN12015 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 12 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:
- Specific value, such as PRDID='DSN12015'
- Partial wildcard value, such as PRDID='DSN*'
- 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.
- IP address or domain name, in the LOCATION column.
- Product identifier, in the PRDID column.
- Role and authorization identifier, in both ROLE and AUTHID columns. Within this category, Db2 uses the following order of precedence:
- ROLE and AUTHID
- ROLE only
- AUTHID only
- Server location name, location alias, or database name, in the LOCATION column.
- The location name of a requester, for monitored threads from a Db2 for z/OS requester, in the LOCATION column.
- Collection identifier and package name, in both COLLID and PKGNAME columns. Within this category, Db2 uses the following order of precedence:
- COLLID and PKGNAME
- COLLID only
- PKGNAME only
- Client application name, user identifier, or workstation name, in the following columns:
- CLIENT_APPLNAME
- CLIENT_USERID
- 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.
