Monitoring threads by using profiles
You can monitor threads for remote TCP/IP access to Db2® servers. You can use the information to analyze the use of system resources by particular clients, applications, and users, and prioritize resources accordingly.
Before you begin
Create a complete set of profile tables on the Db2 subsystem.
- SYSIBM.DSN_PROFILE_TABLE
- SYSIBM.DSN_PROFILE_HISTORY
- SYSIBM.DSN_PROFILE_ATTRIBUTES
- SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY
- SYSIBM.DSN_PROFILE_TABLE_IX_ALL
- SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
- SYSIBM.DSN_PROFILE_ATTRIBUTES_IX_ALL
Procedure
To monitor threads:
- Insert values in the SYSIBM.DSN_PROFILE_TABLE table to
create and specify the scope of the profile:
- Insert a value in the PROFILEID column to identify the profile.
- Insert values to specify the filtering criteria: You can specify filtering criteria
from any one of the following combinations:
- 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.
- Role, in the ROLE column only.
- Authorization identifier, in the AUTHID column 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.
- Collection identifier and package name, in both COLLID and PKGNAME columns.
- Collection identifier, in the COLLID column only.
- Package name, in the PKGNAME column only.
- Client application name, in the CLIENT_APPLNAME column.
- Client user identifier, in the CLIENT_USERID column.
- Client workstation name, in the CLIENT_WRKSTNNAME column.
Other combinations of criteria are not accepted.
You can specify several profiles that have overlapping criteria. Db2 might apply such overlapping profiles in combination, or it might apply one profile and exclude others, depending on the overlapping criteria.
- Specify the monitoring function of the profile by inserting
rows into the SYSIBM.DSN_PROFILE_ATTRIBUTES table.
- Insert a value in the PROFILEID column to identify which profiles use this type monitoring function. Insert a value that matches value of the PROFILEID column in the corresponding SYSIBM.DSN_PROFILE_TABLE rows.
- Insert the 'MONITOR THREADS' value in the KEYWORD column
of DSN_PROFILE_ATTRIBUTES table to specify the monitoring function.
The profile monitors the total number of concurrent active
remote threads that use TCP on the Db2 subsystem.
When the profile criteria are based on only the collection or package, Db2 checks only the first collection or package that is associated the first SQL statement that is executed under the thread.
The system-wide threshold that is defined by the value of the MAXDBAT subsystem parameter continues to apply. Therefore Db2 rejects any profile that specifies a threshold for the MONITOR THREADS keyword that is higher than the value of the MAXDBAT subsystem parameter.
- Insert values in the ATTRIBUTE1 column of DSN_PROFILE_ATTRIBUTES table to specify how Db2 responds when a threshold is met.
- Insert values in the ATTRIBUTE2 column of DSN_PROFILE_ATTRIBUTES table to specify the threshold that the monitor uses.
The possible values in the ATTRIBUTE1 column have two parts. The first part is the type of monitoring:
- WARNING
- A console message is issued at most every 5 minutes depending on the specified diagnosis level. When WARNING is specified, WARNING_DIAGLEVEL1 is used.
- EXCEPTION
- When EXCEPTION is specified, EXCEPTION_DIAGLEVEL1 is used. If the profile threshold is zero and
is exceeded, the thread is canceled. When the profile threshold is greater than zero, the action
taken depends on the filtering scope of the profile as described in the following table:
Table 1. Actions taken when thresholds for EXCEPTION profiles are exceeded Filtering scope Action IP Address or domain name The thread is queued until another server thread becomes available (that is, when another thread deallocates (in ACTIVE mode) or becomes inactive (in INACTIVE mode)) and the number of concurrent active threads falls below the profile exception threshold. The connection that is associated with the thread remains open and is not terminated.
Product identifier, role, authorization identifier, or server location name The thread is queued and suspended until another server thread becomes available and the number of concurrent active threads falls below the profile exception threshold. In ACTIVE mode, another thread becomes available when it deallocates. In INACTIVE mode, a thread becomes available when it becomes inactive. The connection that is associated with the thread remains open and is not terminated.
The Db2 server suspends only as many threads as the profile exception threshold. When the total number of the threads being queued and suspended exceeds the profile exception threshold value for a particular profile ID, the Db2 server fails subsequent connection requests and returns SQLCODE -30041 to the client.
Collection identifier, package name, client user name, client application name, or client workstation name The thread is queued and suspended until another server thread becomes available (that is, when another thread deallocates (in ACTIVE mode) or goes inactive (in INACTIVE mode)) and the number of concurrent active threads goes below the profile exception threshold. The connection that is associated with the thread remains open and is not terminated.
The profile exception
threshold value (ATTRIBUTE2) determines the maximum number of threads that are queued and suspended,
as well as the maximum number of threads that can run concurrently. When the total number of threads
that are being queued and suspended exceeds the ATTRIBUTE2 value for a particular profile ID, Db2 fails the subsequent SQL statement and returns SQLCODE
-30041 to the client. For example, suppose that a profile for a package is started. That profile has an ATTRIBUTE2 value of 2. If five threads are started that request to run the package, two threads run concurrently, and two threads are queued and suspended. Db2 fails the SQL statement for the fifth thread.
The second part of the value in the ATTRIBUTE1 column controls the amount of detail in the message that Db2 issues when the threshold is met:
- type_DIAGLEVEL1
- Where type is WARNING or EXCEPTION, Db2 takes the appropriate action for the specified type and issues a DSNT771I console message, which contains minimal information.
- type_DIAGLEVEL2
- Where type is WARNING or EXCEPTION, Db2 takes the appropriate action for the specified type. Db2 also issues a DSNT772I console message, which contains additional information such as the profile ID and reason code in the message text.
type_DIAGLEVEL3
Where type is WARNING or EXCEPTION, Db2
takes the specified action. If type is WARNING, Db2 also issues a DSNT773I console message, which contains detailed information that you can use to identify the thread that exceeded the warning value.
If type is EXCEPTION, Db2 also issues a DSNT774I console message, which contains detailed information that you can use to identify the thread that exceeded the exception value.

The following table summarizes the meanings of the different columns of the DSN_PROFILE_ATTRIBUTES table for the MONITOR THREADS function.
Table 2. Summary of DSN_PROFILE_ATTRIBUTES values for the MONITOR THREADS function KEYWORD ATTRIBUTE1 column ATTRIBUTE2 column ATTRIBUTE3 column MONITOR THREADS Specify one of the following types of monitoring: - WARNING
- WARNING_DIAGLEVEL1
- WARNING_DIAGLEVEL2
WARNING_DIAGLEVEL3
- EXCEPTION
- EXCEPTION_DIAGLEVEL1
- EXCEPTION DIAGLEVEL2
EXCEPTION_DIAGLEVEL3
Insert a value to indicate the threshold for the maximum allowed number of server threads that meet the profile criteria. The value that you specify must be less than or equal to the value of the MAXDBAT subsystem parameter.
Not used.