Monitoring threads by using profile tables

You can monitor threads for remote TCP/IP access to Db2 servers, and you can use the information to analyze the use of system resources by particular clients, applications, and users, and prioritize resources accordingly.

To monitor 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.
      The values that you insert must be 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.
   3. In the PROFILE_ENABLED column, specify 'Y' so that the profile is enabled when profiles are started.
      If the PROFILE_AUTOSTART subsystem parameter setting is YES, the profile starts when you issue a START PROFILE command or when Db2 starts.
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 THREADS'.
      A profile that specifies this action monitors the number of threads from each application that meets the filtering criteria of the profile separately.
   3. In the ATTRIBUTEn columns, specify the attributes of the profile:

      ATTRIBUTE1

      For MONITOR THREADS, specifies the action and messages that are issued when the threads that match the filtering criteria of the profile reach the specified thresholds.

      ATTRIBUTE1 value	Result for exceeded thresholds	Messages issued
      EXCEPTION	When the number of active threads exceeds the threshold, queue and suspend subsequent threads. When the number of queued threads exceeds the threshold, the action depends on the filtering scope, as shown in Table 1. The connection that is associated that is with the threads is not terminated and remains open.	DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_ DIAGLEVEL1	When the number of active threads exceeds the threshold, queue and suspend subsequent threads. When the number of queued threads exceeds the threshold, the action depends on the filtering scope, as shown in Table 1. Connections that are associated with the queued or suspended threads are not terminated and remain open.	DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_DIAGLEVEL2	When the number of active threads exceeds the threshold, queue and suspend subsequent threads. When the number of queued threads exceeds the threshold, the action depends on the filtering scope, as shown in Table 1. Connections that are associated with the queued or suspended threads are not terminated and remain open.	DSNT772I for a specific active profile, every 5 minutes at most
      EXCEPTION_DIAGLEVEL3	When the number of active threads exceeds the threshold, queue and suspend subsequent threads. When the number of queued threads exceeds the threshold, the action depends on the filtering scope, as shown in Table 1. Connections that are associated with the queued or suspended threads are not terminated and remain open.	DSNT774I for every exception
      WARNING	Issue the console message.	DSNT771I for all active profiles, every 5 minutes at most
      WARNING_DIAGLEVEL1	Issue the console message.	DSNT771I for all active profiles, every 5 minutes at most
      WARNING_ DIAGLEVEL2	Issue the console message.	DSNT772I for a specific active profile, every 5 minutes at most
      WARNING_ DIAGLEVEL3	Issue the console message.	DSNT773I for every warning

      The following table summarizes the filtering actions that are taken for different filtering categories.

      Table 1. Actions for EXCEPTION profiles for remote threads

      Filtering category	Action
      IP address or fully qualified domain name	The thread remains queued until another server thread becomes available. Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.
      Product identifier, role, authorization identifier, or server location name	When the total number of queued and suspended threads exceeds the threshold, 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	When the total number of queued and suspended threads exceeds the threshold, Db2 fails subsequent SQL statements and returns SQLCODE -30041 to the client. For example, suppose that a profile for a package is started. That profile uses ATTRIBUTE2=2. If five threads request to run the package, two threads run concurrently, two threads are queued and suspended, and Db2 fails the SQL statements for the fifth thread.

      ATTRIBUTE2

      For MONITOR THREADS, an integer that specifies the threshold of the total number of active server threads that are allowed from each remote application.

      The maximum allowed value is equal to the value of the MAXDBAT subsystem parameter. See MAX REMOTE ACTIVE field (MAXDBAT subsystem parameter).

      A negative number deactivates this monitor function, and a message is recorded in the profile attributes history table to indicate that the row is rejected.

      ATTRIBUTE3

      For MONITOR THREADS, specifies the threshold for the maximum number of server threads that are allowed to be suspended the profile criteria. The value must be a whole number, less than or equal to the ATTRIBUTE2 column.

3. You can also create a DSN_PROFILE_ATTRIBUTES table row to monitor or limit the number of cumulative threads from all dynamic or unknown applications.
   You can create this row independently or in conjunction with a row for a MONITOR THREADS profile. That is, completing step 2 is not strictly required for completing this step.
   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 ALL THREADS'.
   3. In the ATTRIBUTEn columns, specify the attributes of the profile:

      ATTRIBUTE1

      For MONITOR ALL THREADS, specifies the action and messages that are issued when the number of active server threads that match the filtering criteria of the profile reach the specified thresholds.

      ATTRIBUTE1 value	Result for exceeded thresholds	Messages issued
      EXCEPTION	The thread remains queued until another server thread becomes available and issue the console message. Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.	DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_ DIAGLEVEL1	The thread remains queued until another server thread becomes available and issue the console message. Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.	DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_DIAGLEVEL2	The thread remains queued until another server thread becomes available and issue the console message. Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.	DSNT772I for a specific active profile, every 5 minutes at most
      EXCEPTION_DIAGLEVEL3	The thread remains queued until another server thread becomes available and issue the console message. Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.	DSNT774I for every exception
      WARNING	Issue the console message.	DSNT771I for all active profiles, every 5 minutes at most
      WARNING_DIAGLEVEL1	Issue the console message.	DSNT771I for all active profiles, every 5 minutes at most
      WARNING_ DIAGLEVEL2	Issue the console message.	DSNT772I for a specific active profile, every 5 minutes at most
      WARNING_ DIAGLEVEL3	Issue the console message for every thread that exceeds a profile threshold.	DSNT773I for every warning

      ATTRIBUTE2

      For MONITOR ALL THREADS, an integer that specifies the threshold for the total cumulative number of active server threads that are allowed from all application servers. This threshold value is compared to an approximate count, which is maintained periodically by a background process, of server threads that are under the control of the default location profile.

      The maximum allowed value is equal to the value of the MAXDBAT subsystem parameter. For more information, see MAX REMOTE ACTIVE field (MAXDBAT subsystem parameter).

      When the specified value is a negative number, this monitor function is not used and a message is recorded in the profile attributes history table to indicate that this row is rejected.

      ATTRIBUTE3

      NULL

   
4. 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.
5. 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.

Example

Assume that you know of one specific remote IP address that accesses Db2, and you want to create a default location profile to monitor threads for all other remote connections.

In this case, you might complete the following steps:

1. Insert the following DSN_PROFILE_TABLE rows.

   PROFILEID	LOCATION	ROLE	AUTHID	PRDID	COLLID	PKGNAME
   1	::FFFFF:9.30.137.28	null	null	null	null	null
   2	::0	null	null	null	null	null

2. Insert the following DSN_PROFILE_ATTRIBUTES rows.

   PROFILEID	KEYWORDS	ATTRIBUTE1	ATTRIBUTE2	ATTRIBUTE3
   1	MONITOR THREADS	EXCEPTION	50	null
   2	MONITOR ALL THREADS	EXCEPTION	200	null
   2	MONITOR ALL CONNECTIONS	EXCEPTION	1000	null

3. Issue a -START PROFILE command.

Creating these example profiles has the following results:

• PROFILEID=1 limits applications running from IP address ::FFFFF:9.30.137.28 to a total of 50 threads. Since ATTRIBUTE3 for this profile is the value NULL, additional connections beyond the exception threshold of 50 are suspended. When more than 50 connections become suspended, an exception is triggered.
• PROFILEID=2 limits applications from all other unknown dynamic IP addresses not covered by PROFILEID=1. , after which the requests are queued and the connections remain open waiting for thread. The count of unknown threads is maintained by a periodic background process, so the threshold value 200 is approximate and might be exceeded before an exception with message DSNT771I is triggered.
• • PROFILEID=2 also limits the cumulative number of connections across all unknown dynamic IP addresses to 1000. In this case, the PROFILED=2 row for MONITOR ALL CONNECTIONS limits the number of open connections, including those suspended and waiting for threads because of the MONITOR ALL THREADS row for PROFILEID=2. Specifying this optional row effectively limits the number of connections that can remain open waiting for threads. In either case, the limits specified by subsystem parameters such as CONDBAT, MAXCONQN, or MAXCONQW still apply to all connections as usual.

  For more information about MONITOR ALL CONNECTIONS profiles, see Monitoring remote connections by using profile tables.

Note that although this example uses MONITOR ALL THREADS and MONITOR ALL CONNECTIONS rows together for PROFILEID=2, it is not required. These profiles can each be created independently.