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.
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:
- In the SYSIBM.DSN_PROFILE_TABLE table, insert a row to create the profile and specify its filtering criteria:
- 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.
- 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.
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.
- 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.
- Insert one or more SYSIBM.DSN_PROFILE_ATTRIBUTES table rows to specify the monitoring functions of the profile and its thresholds:
- 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.
- In the KEYWORDS column, specify 'MONITOR CONNECTIONS'.
- 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 and DSNT772I for a specific active profile, every 5 minutes at most |
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 and DSNT772I for a specific active profile, every 5 minutes at most |
- 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
- You 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.
- 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.
- In the KEYWORDS column, specify 'MONITOR ALL CONNECTIONS'.
- 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 and DSNT772I for a specific active profile, every 5 minutes at most |
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 and DSNT772I for a specific active profile, every 5 minutes at most |
- 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
- 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.
- 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 all other remote connections.
In this case, you might complete the following steps:
- Insert the following DSN_PROFILE_TABLE rows.
PROFILEID |
LOCATION |
ROLE |
AUTHID |
PRDID |
COLLID |
PKGNAME |
1 |
::FFFF:9.30.137.28 |
null |
null |
null |
null |
null |
2 |
::0 |
null |
null |
null |
null |
null |
- 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 |
- Issue a -START PROFILE command.
Creating these example profiles has the following results:
- PROFILEID=1 limits applications running from IP address ::FFFF: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.