Monitoring idle threads by using profile tables

You can use profile tables to create thresholds that monitor and control how long active server threads from specific remote applications are allowed to remain idle in the Db2 subsystem.

To monitor idle threads by using profile tables, complete the following steps:

1. In the SYSIBM.DSN_PROFILE_TABLE table, insert a row to create the profile and specify its filtering criteria:
   1. In the PROFILEID column, specify a unique value or accept the generated default value. This value identifies the profile and the relationship between DSN_PROFILE_TABLE and DSN_PROFILE_ATTRIBUTES rows.
   2. Specify the filtering criteria of the profile.
      You can specify values from one of the following filtering categories:

      • LOCATION only
      • PRDID only
      • AUTHID, ROLE, or both
      • COLLID, PKGNAME, or both
      • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME

      The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.

      Other filtering columns must contain the null value.

      Tip: If you create multiple profiles with overlapping filtering criteria, Db2 applies only one profile from each filtering category, based on a specific order of precedence. If multiple DSN_PROFILE_TABLE rows specify the same filtering criteria, only the newest is row is accepted when you start the profiles, and the other duplicates are rejected. Also, exact values take precedence over values that use an asterisk (*) wildcard. However, profiles from different filtering categories can all apply. For more information about these rules, see How Db2 applies multiple matching profiles for threads and connections.
2. Insert one or more SYSIBM.DSN_PROFILE_ATTRIBUTES table rows to specify the monitoring functions of the profile and its thresholds:
   1. Specify the PROFILEID value from the DSN_PROFILE_TABLE row that specifies the filtering criteria for this profile.
      Tip: Use the same PROFILEID value for any DSN_PROFILE_ATTRIBUTES rows that require the same filtering criteria. If multiple DSN_PROFILE_TABLE rows contain exactly matching filtering criteria, only the newest duplicate row is accepted when you start the profiles, and the others are rejected and disabled.
   2. In the KEYWORDS column, specify 'MONITOR IDLE THREADS'.
   3. In the ATTRIBUTEn columns, specify the attributes of the profile:

      ATTRIBUTE1

      For MONITOR IDLE THREADS, specifies the type and level of detail for messages that are issued for monitored idle threads that meet the conditions that are specified in the profile.

      ATTRIBUTE1 value	Result for exceeded thresholds	Messages issued
      EXCEPTION	Abort the thread, pool the DBAT, terminate the connection, and issue the console message.	DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_ DIAGLEVEL1	Abort the thread, pool the DBAT, terminate the connection, and issue the console message.	DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_DIAGLEVEL2	Abort the thread, pool the DBAT, terminate the connection, and issue the console message.	DSNT772I for a specific active profile, every 5 minutes at most
      EXCEPTION_ROLLBACK	Abort any active threads that remain idle longer than the specified threshold maintain the connection with the remote application, and issue the console message. If the aborted transaction updated the database, the application environment is placed in a must-abort state so that the application environment is notified that resources were destroyed as a result of the aborted threads. 3.c.i	DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_ROLLBACK_DIAGLEVEL1	Abort any active threads that remain idle longer than the specified threshold and maintain the connection with the remote application, and issue the console message. If the aborted transaction updated the database, the application environment is placed in a must-abort state so that the application environment is notified that resources were destroyed as a result of the aborted threads. 3.c.i	DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_ROLLBACK_DIAGLEVEL2	Abort any active threads that remain idle longer than the specified threshold and maintain the connection with the application environment. If the aborted transaction updated the database, the application environment is placed in a must-abort state so that the application environment is notified that resources were destroyed as a result of the aborted threads. 3.c.i	DSNT772I for a specific active profile, every 5 minutes at most
      WARNING	Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 3.c.ii	DSNT771I for all active profiles, every 5 minutes at most
      WARNING_DIAGLEVEL1	Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 3.c.ii	DSNT771I for all active profiles, every 5 minutes at most
      WARNING_ DIAGLEVEL2	Issue the console message, and allow the thread to remain idle. 3.c.ii	DSNT773I for every warning
      WARNING_MESSAGE_FOR_IDLE_TIMEOUT	Issue a single DSNT773I message for a thread that remains in an idle state. 3.c.ii When a client request message is received, and a COMMIT or ROLLBACK is completed with no resources active past the end of the unit-of-work, remove the warning against the thread.	DSNT771I DSNT773I for every warning

      Notes:

      1. 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 that is 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.

      2. The timeout limit that is specified by the IDTHTOIN subsystem parameter setting does not apply to threads that match the filtering criteria of a MONITOR IDLE THREADS profile. So, threads that qualify for a MONITOR IDLE THREADS profile with a WARNING value in the ATTIRBUTE1 column can remain idle indefinitely. To prevent that situation, you can add another DSN_PROFILE_ATTRIBUTES row with EXCEPTION value in the ATTRIBUTES1 column and same the same PROFILEID value.

      ATTRIBUTE2

      For MONITOR IDLE THREADS, an integer that specifies the threshold (in seconds) that a thread is allowed to remain idle.

      Threads are checked every two minutes to see if they have exceeded the timeout value. If the timeout value is less than two minutes, the thread might not be canceled if it has been inactive for more than the timeout value but less than two minutes.

      It can be any value that is valid for the IDTHTOIN subsystem parameter. See IDLE THREAD TIMEOUT field (IDTHTOIN subsystem parameter).

      Threads that meet the criteria of this type of profile are not limited by the value that is specified by the IDTHTOIN subsystem parameter. So you can 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 means that matching threads are allowed to remain idle indefinitely. When a negative number deactivates this monitor function, and a message is recorded in the profile attributes history table to indicate that this row is rejected.

      ATTRIBUTE3

      NULL

3. Load or reload the profile tables into memory by issuing a START PROFILE command. (For best results, do not issue a STOP PROFILE command when you add or modify existing profiles. Use the STOP PROFILE command only if you intend to disable all existing profiles.) For more information, see Starting and stopping profiles.
4. Check the status of all newly added profiles in the STATUS columns of the DSN_PROFILE_HISTORY and DSN_PROFILE ATTRIBUTES_HISTORY tables.
   Successful completion of the START PROFILE command does not imply that all profiles started successfully. If the STATUS column of either history table contains a value that does not start with 'ACCEPTED', further action is required to enable the profile or the keyword action.

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.