Monitoring remote connections by using profile tables

You can monitor connections for remote TCP/IP access to Db2 servers. You can use the resulting information to analyze the use of system resources by particular clients, applications, and users. You can also create exception thresholds to prioritize resources accordingly.

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 total number of remote connections from TCP/IP requesters, including the current active connections and the inactive connections. Active connections are either currently associated with an active database access thread or have been queued and are waiting to be serviced. Inactive connections are currently not waiting and not associated with a database access thread.

When you monitor connections by using profile tables, the system-wide threshold that is defined by the value of the CONDBAT subsystem parameter continues to apply. Because the threshold specified by the subsystem parameter would always apply first, Db2 rejects any profile that specifies a threshold for the MONITOR CONNECTIONS keyword that is higher than the value of the CONDBAT subsystem parameter.

Procedure

To monitor remote connections to Db2 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. In the LOCATION column, specify the filtering scope of the profile.

      You can specify an IP address or domain name value. The LOCATON value is not case not case sensitive, and profiles can matches can occur 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.
    3. In the PROFILE_ENABLED column, specify 'Y' so that the profile is enabled when profiles are started.
      The profile starts when you issue a START PROFILE command or when Db2 starts, if the PROFILE_AUTOSTART subsystem parameter setting is YES.
  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 CONNECTIONS'.
    3. In the ATTRIBUTEn columns, specify the attributes of the profile:
      ATTRIBUTE1
      For MONITOR CONNECTIONS, specifies the action and console messages to issue when the threshold is reached for threads that match the filtering criteria specified in the profile, as shown in the following table.
      ATTRIBUTE1 value Action when exceeding thresholds Messages issued
      EXCEPTION Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. DSNT771I
      EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. DSNT771I
      EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. DSNT772I
      EXCEPTION_DIAGLEVEL3 Fail the connection request and issue the console message. DSNT774I
      WARNING Allow the connection to remain open, and issue the console message one time every 5 minutes at most. DSNT771I
      WARNING_DIAGLEVEL1 Allow the connection to remain open, and issue the console message one time every 5 minutes at most. DSNT771I
      WARNING_ DIAGLEVEL2 Allow the connection to remain open, and issue the console message one time every 5 minutes at most. DSNT772I
      WARNING_ DIAGLEVEL3 Allow the connection to remain open, and issue the console message for every thread that exceeds a profile threshold. DSNT773I
      ATTRIBUTE2
      For MONITOR CONNECTIONS, an integer that specifies the threshold of the total number of remote connections that are allowed from each application server.

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

      When the specified value is a negative number, 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. Start of changeYou can also create a DSN_PROFILE_ATTRIBUTES table row to monitor or limit the number of cumulative connections from all dynamic or unknown applications.
    You can create this row independently or in conjunction with a row for a MONITOR CONNECTONS profile. That is, completing step 2 is not strictly required for completing this step.
    1. Specify the PROFILEID value from the related DSN_PROFILE_TABLE row that specifies the filtering criteria for this profile.
      The DSN_PROFILE_TABLE row with the matching PROFILEID value must contain one of the following values in the LOCATION column: '*', '::0', or '0.0.0.0'.
    2. In the KEYWORDS column, specify 'MONITOR ALL CONNECTIONS'.
    3. In the ATTRIBUTEn columns, specify the attributes of the profile:
      ATTRIBUTE1

      For MONITOR ALL CONNECTIONS, specifies the action and console messages to issue when the threshold is reached for connections that match the filtering criteria specified in the profile, as shown in the following table.

      ATTRIBUTE1 value Action when exceeding thresholds Messages issued
      EXCEPTION Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. DSNT771I
      EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. DSNT771I
      EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. DSNT772I
      EXCEPTION_DIAGLEVEL3 Fail the connection request and issue the console message. DSNT774I
      WARNING Allow the connection to remain open, and issue the console message one time every 5 minutes at most. DSNT771I
      WARNING_DIAGLEVEL1 Allow the connection to remain open, and issue the console message one time every 5 minutes at most. DSNT771I
      WARNING_ DIAGLEVEL2 Allow the connection to remain open, and issue the console message one time every 5 minutes at most. DSNT772I
      WARNING_ DIAGLEVEL3 Allow the connection to remain open, and issue the console message for every thread that exceeds a profile threshold. DSNT773I
      ATTRIBUTE2
      For MONITOR ALL CONNECTIONS, an integer that specifies the threshold fortotal cumulative number of remote connections 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 CONDBAT subsystem parameter. See MAX REMOTE CONNECTED field (CONDBAT subsystem parameter).

      When the specified value is a negative number, 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
    End of change
  4. Load or reload the profile tables into memory by issuing a START PROFILE command, as described in Starting and stopping profiles.
Start of change

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 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 CONNECTIONS EXCEPTION 100 null
    2 MONITOR CONNECTIONS EXCEPTION 50 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 100 connections. Additional connections beyond the threshold value of 100 trigger an exception.
  • PROFILEID=2 limits applications from all other unknown dynamic IP addresses not covered by PROFILEID=1. Each individual IP address is limited to 50 connections, after which additional connections from that IP address trigger an exception.
  • PROFILEID=2 also limits the cumulative number of connections across all unknown dynamic IP addresses to 1000. The count of unknown connections is maintained by a periodic background process, so the threshold value 1000 is approximate and might be exceeded before an exception with message DSNT771I is triggered.

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

End of change