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.

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

Procedure

To monitor threads:

  1. Insert values in the SYSIBM.DSN_PROFILE_TABLE table to create 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. Specify the monitoring function of the profile by inserting rows into the SYSIBM.DSN_PROFILE_ATTRIBUTES table.
    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. 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.

    3. Insert values in the ATTRIBUTE1 column of DSN_PROFILE_ATTRIBUTES table to specify how Db2 responds when a threshold is met.
    4. 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.

    Start of changeThe 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.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.
    Start of changetype_DIAGLEVEL3End of change
    Start of changeWhere 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.

    End of change

    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
    • Start of changeWARNING_DIAGLEVEL3End of change
    • EXCEPTION
    • EXCEPTION_DIAGLEVEL1
    • EXCEPTION DIAGLEVEL2
    • Start of changeEXCEPTION_DIAGLEVEL3End of change
    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.