Example profiles that monitor 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
17¹	null	null	null	SQL09073	COLL1	null
Notes: 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.

The following examples assume that the DSN_PROFILE_ATTRIBUTES table contains rows that have matching values in the PROFILEID column, and that these rows also contain the same values in the KEYWORD column. Two profiles from the same category that specify different monitoring functions are both applied.

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.

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

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

Start of change When you consider these values in combination with the values in combination with the values in Table 1, you see that the following thresholds are created: End of change

"Profile 11" indicates that database administrators are accepted as many as 100 threads.
"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.

Start of change All of the example profiles specify the MONITOR THREADS function, and they all specify filtering criteria from the same category. So, only one of 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. End of change

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. So, DB2 uses all three profiles in combination to evaluate whether to apply thresholds to the thread. All of these profiles are applied, regardless of the functions or thresholds that are specified in the associated rows (which are not shown here) in the DSN_PROFILE_ATTRIBUTES table.

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.