Examples for profiles that monitor and control threads and connections

Examples are useful for helping you to understand the interactions between profiles that monitor system resources such as threads and connections.

The following example shows how Db2 determines which profiles to apply when the criteria of more than one profile match the attributes of a thread or connection. For example, assume that DSN_PROFILE_TABLE contains rows that contain the following values (some columns are not shown).

Table 1. Sample profiles in DSN_PROFILE_TABLE
PROFILEID LOCATION ROLE AUTHID PRDID COLLID PKGNAME
11 null ROLE_DBA null null null null
12 null null USER1 null null null
13 null ROLE_DBA USER1 null null null
14 null ROLE_APP null null null null
15
TEST.SVL.IBM.COM
 null null null null null
16 null null null SQL09073 null null
171 null null null SQL09073 COLL1 null
Start of change18End of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change Start of changeDSN*2End of change Start of changenullEnd of change Start of changenullEnd of change
Start of change19End of change Start of changenullEnd of change Start of changenullEnd of change Start of changeUSER*3End of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change
Start of change20End of change Start of change9.30.222.0/24End of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change
Start of change21End of change Start of change2001:DB8:ABCD:0012::/64End of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change
Start of change22End of change Start of change0.0.0.04End of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change Start of changenullEnd of change
Notes:
  1. The profile that is identified by PROFILEID=17 specifies values for columns in different filtering categories. Consequently, Db2 rejects this row when you issue the START PROFILE command.
  2. Start of changeThe value DSN* is a string in which the first three characters match DSN followed by the version identifier. For example, PRDID='DSN12015' or any PRDID where the first three characters match 'DSN'.End of change
  3. Start of changeThe value of USER* is a string in which the first four characters match USER followed by the unique user ID. For example, AUTHID='USER8' or any AUTHID where the first four characters match 'USER'.End of change
  4. Start of changeSetting the LOCATION column to '0.0.0.0' has the same effect as setting it to '::0'.End of change

The following examples assume that DSN_PROFILE_ATTRIBUTES also contains rows with the matching PROFILEID value and a KEYWORDS value that accepts the filtering criteria in each example.

Consider example threads that have the following attributes:
ROLE='ROLE_APP' and AUTHID='USER1':
The criteria of profile 12 and profile 14 match the thread, but Db2 uses only profile 14 to evaluate whether to apply a threshold to the thread because ROLE takes precedence over AUTHID.
ROLE='ROLE_DBA' and AUTHID='USER2':
Db2 applies only the profile that is identified by PROFILEID=11.
ROLE='ROLE_DBA' and AUTHID='USER1':
The criteria of the following profiles match the thread: PROFILEID=11, PROFILEID=12, and PROFILEID=13. However Db2 applies only PROFILEID=13 to evaluate whether to apply a threshold against the thread. The profile that defines both ROLE and AUTHID takes precedence over a profile that defines only one of those values.

In practice this result means that a profile that sets a lower threshold might be overruled by a profile that specifies a greater threshold. For example, assume that the DSN_PROFILE_ATTRIBUTES table contains the rows shown in the following table.

Table 2. Sample rows in SYSIBM.DSN_PROFILE_ATTRIBUTES
PROFILEID KEYWORDS ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3
11 MONITOR THREADS EXCEPTION 100 null
12 MONITOR THREADS EXCEPTION 20 null
13 MONITOR THREADS EXCEPTION 50 null

When you consider these values and the values in Table 1, you see that the following thresholds are created:

  • Profile 11 indicates that as many as 100 threads are accepted from the ROLE_DBA role.
  • Profile 12 indicates that as many as 20 threads are accepted from the USER1 authorization ID.
  • Profile 13 indicates that as many as 50 threads are accepted for threads from the USER1 authorization ID under the ROLE_DBA role.

All of the example profiles specify filtering criteria from the same category. So, only one of the profiles applies to any particular thread. In this example, profile 13 applies to any thread that matches the AUTHID='USER1' and ROLE='ROLE_DBA' values. Therefore, because profile 13 takes precedence, profile 12 is never applied to any thread that meets both of these criteria. So, as many as 50 threads might be accepted from the 'USER1' authorization ID, before any action is taken.

However, profile 12 applies to any thread from 'USER1' under a different ROLE value, and every thread that has been accepted from 'USER1' (including any that also specified ROLE='ROLE_DBA') is now counted toward the evaluation of profile 12 in that case.

LOCATION='TEST.SVL.IBM.COM', ROLE='ROLE_APP', and PRDID='SQL09073':
The criteria of the following profiles match the thread: PROFILEID=14, PROFILEID=15, PROFILEID=16.

Because the criteria of these profiles are from separate filtering categories. Db2 applies the thresholds for all three profiles to the thread.

Start of changePRDID='DSN10015':End of change
Start of changeThe criteria of profile 18 matches the thread. Db2 applies PROFILEID=18 to evaluate whether to apply a threshold against this thread.End of change
Start of changeAUTHID='USER8':End of change
Start of changeThe criteria of profile 19 matches the thread and Db2 will apply this profile to evaluate whether to apply a threshold against this thread.End of change
Start of changeLOCATION='9.30.222.21':End of change
Start of changeThe criteria of profile 20 matches the thread since the IP address is part of the subnet range 9.30.222.1 to 9.30.222.254. Db2 applies this PROFILEID=20 to evaluate whether to apply a threshold against this thread.End of change
Start of changeLOCATION='2001:DB8:ABCD:0012:0000:0000:0000:0003':End of change
Start of changeThe criteria of profile 21 matches the thread since the device address (the last four groupings of numbers, 0000:0000:0000:0003) is part of the subnet range 2001:DB8:ABCD:0012:0000:0000:0000:0000 to 2001:DB8:ABCD:0012:FFFF:FFFF:FFFF:FFFF. Db2 applies this PROFILEID=21 to evaluate whether to apply a threshold against this thread.End of change
Start of changeLOCATION='192.168.0.103':End of change
Start of changeThe criteria of profile 22 matches the thread since the special IPv4 address of 0.0.0.0 will match any remote client IPv4 address to this profile. Db2 applies this PROFILEID=22 to evaluate whether to apply a threshold against this thread.End of change

The following table shows partial sample data for certain columns in the SYSIBM.DSN_PROFILE_ATTRIBUTES table that specify how Db2 monitors threads and remote connections.

Table 3. Sample rows in SYSIBM.DSN_PROFILE_ATTRIBUTES
PROFILEID KEYWORDS ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE_ TIMESTAMP
21 MONITOR THREADS EXCEPTION 100 null 2008-12-19...
22 MONITOR IDLE THREADS EXCEPTION 30 null 2008-12-17...
23 MONITOR CONNECTIONS WARNING 50 null 2009-01-21...
  • Profile 21 indicates that Db2 monitors active threads that meet the criteria defined by the DSN_PROFILE_TABLE row that contains 21 in the PROFILEID column. When the number of active threads exceeds 100, Db2 issues a message and suspends any new thread requests. When the number of the suspended threads exceeds 100, Db2 starts to reject any new thread request and issues SQLCODE -30041.
  • Profile 22 indicates that Db2 monitors idle threads that meet the criteria defined by the DSN_PROFILE_TABLE that contains 22 in the PROFILEID column. When a thread remains idle for more than 30 seconds, Db2 issues a message and terminates the idle thread.
  • Profile 23 indicates that Db2 monitors remote connections that meet the criteria defined the DSN_PROFILE_TABLE row that contains 23 in the PROFILEID column. When the number of remote connections reaches 50, Db2 issues a message and continues to service new connection requests.