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.

Before you begin

If a set of profile tables and related objects do not exist on the Db2 subsystem, you must create them. For a list of the objects and how to create them, see Profile tables.

The distributed data facility (DDF) must be loaded, with the DDF subsystem parameter set to AUTO or COMMAND. See DDF STARTUP OPTION field (DDF subsystem parameter).

Start of changeIf you plan to use TCP/IP domain names for profile filtering, you must enable the database services address space (ssnmDBM1) to access TCP/IP services. See Enabling Db2 to access TCP/IP services in z/OS UNIX System Services.End of change

This task describes one of several uses for profile tables. For an overview of how to use profile tables and a summary of the different uses, see Monitoring and controlling Db2 by using profile tables.

About this task

This type of profile monitors the approximate time in seconds that an active server thread is allowed to remain idle, with warning or error thresholds. 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. An exception type profile issues messages and cancels threads when the specified threshold is reached.

The IDTHTOIN subsystem parameter does not apply to any thread that matches the filtering criteria of a MONITOR IDLE THREADS profile. Therefore, you can also use MONITOR IDLE THREADS to enable longer idle wait times for threads from specific remote applications, without increasing the system-wide limit for idle thread timeouts. However, always create an EXCEPTION profile to prevent such threads from remaining idle indefinitely. A zero value for either type of profile allows qualifying threads to remain idle indefinitely.

Important: The timeout limit specified by the IDTHTOIN subsystem parameter setting does not apply to threads that match the filtering criteria of a MONITOR IDLE THREADS profile. Consequently, 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.

Procedure

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

  1. In SYSIBM.DSN_PROFILE_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 matching filtering criteria, Db2 applies only one profile from each filtering category, based on a specific order of precedence. Also, exact values take precedence over values that use an asterisk (*) wildcard. However, profiles from different filtering categories can all apply. For more about these rules, see How Db2 applies multiple matching profiles for threads and connections.
  2. In a SYSIBM.DSN_PROFILE_ATTRIBUTES table, insert a row to specify the monitoring function of the profile and its thresholds:
    1. Specify the PROFILEID value from the related DSN_PROFILE_TABLE row that specifies the filtering criteria for this profile.
    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 issued for monitored idle threads that meet the conditions specified in the profile, as shown in the following table.
      ATTRIBUTE1 value Action when exceeding thresholds Messages issued
      EXCEPTION Abort the thread, pool the DBAT, terminate the connection, and issue the console message. DSNT771I
      EXCEPTION_ DIAGLEVEL1 Abort the thread, pool the DBAT, terminate the connection, and issue the console message. DSNT771I
      EXCEPTION_DIAGLEVEL2 Abort the thread, pool the DBAT, terminate the connection, and issue the console message. DSNT772I
      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
      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
      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
      WARNING Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 3.c.ii DSNT771I
      WARNING_DIAGLEVEL1 Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 3.c.ii DSNT771I
      WARNING_ DIAGLEVEL2 Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 3.c.ii DSNT773I
      WARNING_MESSAGE_FOR_IDLE_TIMEOUT
      1. Issue a single DSNT773I message for a thread that remains in an idle state. 3.c.ii
      2. 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.
      DSNT773I
      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 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 specified by the IDTHTOIN subsystem parameter setting does not apply to threads that match the filtering criteria of a MONITOR IDLE THREADS profile. Consequently, 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. 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. Consequently 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 is specified, this monitor function is disabled 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, as described in Starting and stopping profiles.

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.
End program-specific programming interface information.