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 the Db2 profile tables and related objects does 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 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. In the LOCATION column, specify the filtering scope of the profile.

      You can specify an IP address or a domain name value that resolves to an IP address. An example fully qualified domain name is 'stlmvs1.svl.example.com'.

      The LOCATION value is not case sensitive, and profile 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 overlapping filtering criteria, Db2 applies only one profile from each filtering category, based on a specific order of precedence. Start of changeIf 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.End of change 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: Start of changeUse 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.End of change
    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 that are specified in the profile.
      ATTRIBUTE1 value Result for exceeded thresholds Messages issued
      EXCEPTION Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
      EXCEPTION_DIAGLEVEL3 Fail the connection request and issue the console message. DSNT774I for every exception
      WARNING Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
      WARNING_DIAGLEVEL1 Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
      WARNING_ DIAGLEVEL2 Allow the connection to remain open, and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
      WARNING_ DIAGLEVEL3 Allow the connection to remain open and issue the console message for every thread that exceeds a profile threshold. DSNT773I for every warning
      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).

      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
      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 DSN_PROFILE_TABLE row that specifies the filtering criteria for this profile.
      Tip: Start of changeUse 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.End of change
    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 that are specified in the profile.

      ATTRIBUTE1 value Result for exceeded thresholds Messages issued
      EXCEPTION Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
      EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
      EXCEPTION_DIAGLEVEL3 Fail the connection request and issue the console message. DSNT774I for every exception
      WARNING Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
      WARNING_DIAGLEVEL1 Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
      WARNING_ DIAGLEVEL2 Allow the connection to remain open and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
      WARNING_ DIAGLEVEL3 Allow the connection to remain open and issue the console message. DSNT773I for every warning
      ATTRIBUTE2
      For MONITOR ALL CONNECTIONS, an integer that specifies the threshold for total 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 not used 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. (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.
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