You can monitor idle threads from remote connections.

About this task
You
can create warning and exception type MONITOR IDLE THREADS profiles. A warning type
profile issues messages for threads that remain idle beyond the specified threshold, but it never
cancels any threads. You can use the messages from warning type profiles to analyze the use of
system resources by particular clients, applications, and users. You can also create exception
type profiles that prioritize resources accordingly. An exception type profile
issues messages and cancels threads when the specified threshold is reached.The IDTHTOIN
subsystem parameter never applies to any threads that meet the criteria for any MONITOR IDLE THREADS
profile. Therefore, you can also use MONITOR IDLE THREADS to enable longer idle wait times for
certain threads, without increasing the system-wide limit for idle thread timeouts. A zero value for
either type of profile means that matching threads are allowed to remain idle indefinitely.
Important: Creating an exception type profile in conjunction with each
warning type profile is recommended. Otherwise, any monitored threads can remain idle
indefinitely.

Procedure
To monitor idle threads:
- Insert values in the SYSIBM.DSN_PROFILE_TABLE table to
create the profile 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.
- Insert values in the SYSIBM.DSN_PROFILE_ATTRIBUTES table
to specify the monitoring function of the profile:
- 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.
- Specify the monitoring function by inserting the MONITOR
IDLE THREADS value into the KEYWORD column in the DSN_PROFILE_ATTRIBUTES
table. The
profile monitors the approximate time (in seconds) that an active
server thread is allowed to remain idle.
- Insert values in the ATTRIBUTE1 column of DSN_PROFILE_ATTRIBUTES
table to specify how Db2 responds
when a threshold is met. 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.
Monitored
threads are never canceled, regardless of the IDTHTOIN subsystem parameter, unless a corresponding
EXCEPTION profile is also created.
- EXCEPTION
- Db2 issues the messages
that the diagnosis level specifies, and it cancels any active threads
that remain idle as long as the specified threshold.
EXCEPTION_ROLLBACK
Db2 issues the messages that the
diagnosis level specifies, and aborts any active threads that remain idle longer than the specified
threshold. The connection with the application environment is maintained.If the aborted
transaction performed database updates, the application environment is placed in a must-abort state
so that the application environment is made aware of the resources that have been destroyed as a
result of the aborted threads.
Important: If either of the following situations are
true,
Db2 hides the EXCEPTION_ROLLBACK
event from the remote application environment:
- The aborted transaction performed only read-only operations.
- The transaction committed or aborted, but the associated database access thread remained active
before it became idle.
Because the EXCEPTION_ROLLBACK event is not visible to the remote application environment in
those cases, it might appear to the remote application environment that the database resources are
unexpectedly disappearing. For example, held cursors, kept dynamic statements, and declared
temporary tables that were created in the transaction are destroyed. Use EXCEPTION_ROLLBACK only if
the remote application environment can account for this apparent loss of database
resources.

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_MESSAGE_FOR_IDLE_TIMEOUT
- type is WARNING. Db2 issues message
DSNT773I to display information about the thread that exceeded the warning
threshold. Db2 issues the DSNT773I message
once for the thread while the thread remains in an idle state. After a COMMIT or ROLLBACK is
performed, and no resources are active past the end of the unit of work, Db2 removes the warning against the thread. Db2 also issues console message DSNT771I to indicate how many times the
timeout occurred.
- Insert values in the ATTRIBUTE2 column of DSN_PROFILE_ATTRIBUTES
table to specify the threshold that applies to active idle threads. You can
specify any value that is valid for the IDTHTOIN subsystem
parameter.
The following table summarizes the meanings of the different
columns of the DSN_PROFILE_ATTRIBUTES table for the MONITOR IDLE THREADS
function.
Table 1. Summary of DSN_PROFILE_ATTRIBUTES values for monitoring
idle threads| KEYWORD |
ATTRIBUTE1 column |
ATTRIBUTE2 column |
ATTRIBUTE3 column |
| MONITOR IDLE THREADS |
Specify one of the following types of monitoring:- WARNING
- WARNING_DIAGLEVEL1
- WARNING_DIAGLEVEL2
- EXCEPTION
- EXCEPTION_DIAGLEVEL1
- EXCEPTION_DIAGLEVEL2
EXCEPTION_ROLLBACK
EXCEPTION_ROLLBACK_DIAGLEVEL1
EXCEPTION_ROLLBACK_DIAGLEVEL2
|
Insert a value to indicate the maximum number
of seconds that active server threads are allowed to remain idle when
they meet the profile criteria. |
Not used. |
- Issue the START PROFILE command to start the monitoring
functions that are specified in the profile tables.
What to do next
Examine the
accounting trace data that you obtained to determine which connection or thread exceeded a warning
or exception level that was set by a monitor profile. The following trace fields provide that
information:
- QWAC_PROFMON_TYPE
- Contains 'E' for an exception or a 'W' for a warning condition. Any other value indicates that a
warning or exception did not occur.
- QWAC_PROFMON_PID
- Contains the profile ID of the monitor profile for the connection or thread that experienced the
condition. The profile ID is the PROFILEID value in the SYSADM.DSN_PROFILE_TABLE. This value is a
four-byte, binary integer.