Monitoring idle threads by using profiles

You can monitor idle threads from remote connections.

Before you begin

Create a complete set of profile tables on the Db2® subsystem.

The profile tables and related indexes are created when you run job DSNTIJSG during Db2 installation or migration.

A complete set of profile tables and related indexes includes the following objects:
  • 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

Start an accounting trace that generates IFCID 0003 trace records, such as accounting class 1.

Start of change

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.
End of change

Procedure

To monitor idle threads:

  1. Insert values in the SYSIBM.DSN_PROFILE_TABLE table to create the profile and specify the scope of the profile:
    1. Insert a value in the PROFILEID column to identify the profile.
    2. Insert values to specify the filtering criteria: You can specify filtering criteria from any one of the following combinations:
      1. IP address or domain name, in the LOCATION column.
      2. Product identifier, in the PRDID column.
      3. Role and authorization identifier, in both ROLE and AUTHID columns.
      4. Role, in the ROLE column only.
      5. Authorization identifier, in the AUTHID column only.
      6. Server location name, location alias, or database name, in the LOCATION column.
      7. Start of changeThe location name of a requester, for monitored threads from a Db2 for z/OS® requester.End of change
      8. Collection identifier and package name, in both COLLID and PKGNAME columns.
      9. Collection identifier, in the COLLID column only.
      10. Package name, in the PKGNAME column only.
      11. Client application name, in the CLIENT_APPLNAME column.
      12. Client user identifier, in the CLIENT_USERID column.
      13. 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.
  2. Insert values in the SYSIBM.DSN_PROFILE_ATTRIBUTES table to specify the monitoring function of the profile:
    1. 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.
    2. 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.
    3. 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. Start of changeMonitored threads are never canceled, regardless of the IDTHTOIN subsystem parameter, unless a corresponding EXCEPTION profile is also created.End of change
      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.
      Start of changeEXCEPTION_ROLLBACKEnd of change
      Start of changeDb2 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.

      End of change

      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.
    4. 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
    • Start of changeEXCEPTION_ROLLBACKEnd of change
    • Start of changeEXCEPTION_ROLLBACK_DIAGLEVEL1End of change
    • Start of changeEXCEPTION_ROLLBACK_DIAGLEVEL2End of change
    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.
  3. Issue the START PROFILE command to start the monitoring functions that are specified in the profile tables.

What to do next

Begin program-specific programming interface information.

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.