System Monitor Guide and Reference

Federated Database Systems

A federated system is a multidatabase server that provides remote data access. It provides client access to diverse data sources that can reside on different platforms, both IBM and other vendors, relational and nonrelational. It integrates access to distributed data and presents a single database image of a heterogeneous environment to its users.

The following elements list information about the total access to a data source by applications running in a DB2 federated system and information about access to a data source by a given application running in a federated server instance. They include:

• Data Source Name
• Application Identification
• Disconnects
• Inserts
• Updates
• Deletes
• Create Nicknames
• Pass-Through
• Stored Procedures
• Remote Locks
• Rows Returned by Stored Procedures
• Query Response Time
• Insert Response Time
• Update Response Time
• Delete Response Time
• Create Nickname Response Time
• Pass-Through Time
• Stored Procedure Time
• Remote Lock Time

Data Source Name

Description:  This element contains the name of the data source whose remote access information is being displayed by the federated server. This element corresponds to the 'SERVER' column in SYSCAT.SERVERS.

Usage:  Use this element to identify the data source whose access information has been collected and is being returned.

Application Identification

Description:  This element contains the name of the application currently being monitored.

Usage:  Use this element to identify the application whose access information has been collected and is being returned.

Disconnects

Description:  This element contains a count of the total number of times the federated server has disconnected from this data source on behalf of any application since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

Usage:  Use this element to determine the total number of times the federated server has disconnected from this data source on behalf of any application. Together with the CONNECT count, this element provides a mechanism by which you can determine the number of applications this instance of the federated server believes is currently connected to a data source.

Inserts

Description:  This element contains a count of the total number of times the federated server has issued an INSERT statement to this data source on behalf of any application since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

Usage:  Use this element to determine the level of database activity directed against this data source by the federated server or an application.

You can also use this element to determine the percentage of write activity against this data source by the federated server or an application, with the following formula:

   write_activity =
      (INSERT statements + UPDATE statements + DELETE statements ) /
      (SELECT statements + INSERT statements + UPDATE statements +
      DELETE statements)

Updates

Description:  This element contains a count of the total number of times the federated server has issued an UPDATE statement to this data source on behalf of any application since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

Usage:  Use this element to determine the level of database activity directed against this data source by the federated server or an application.

You can also use this element to determine the percentage of write activity against this data source by the federated server or an application, with the following formula:

   write_activity =
     (INSERT statements + UPDATE statements + DELETE statements ) /
     (SELECT statements + INSERT statements + UPDATE statements +
     DELETE statements)

Deletes

Description:  This element contains a count of the total number of times the federated server has issued a DELETE statement to this data source on behalf of any application since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

Usage:  Use this element to determine the level of database activity directed against this data source by the federated server or an application.

You can also use this element to determine the percentage of write activity against this data source by the federated server or an application, with the following formula:

   write_activity =
     (INSERT statements + UPDATE statements + DELETE statements ) /
     (SELECT statements + INSERT statements + UPDATE statements +
     DELETE statements)

Create Nicknames

Description:  This element contains a count of the total number of times the federated server has created a nickname over an object residing on this data source on behalf of any application since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

Usage:  Use this element to determine the amount of CREATE NICKNAME activity against this data source by this federated server instance or an application. CREATE NICKNAME processing results in multiple queries running against the data source catalogs; therefore, if the value of this element is high, you should determine the cause and perhaps restrict this activity.

Pass-Through

Description:  This element contains a count of the total number of SQL statements that the federated server has passed through directly to this data source on behalf of any application since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

Usage:  Use this element to determine what percentage of your SQL statements can be handled natively by the federated server, and what percentage requires pass-through mode. If this value is high, you should determine the cause and investigate ways to better utilize native support.

Stored Procedures

Description:  This element contains a count of the total number of stored procedures that the federated server has called at this data source on behalf of any application since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

Usage:  Use this element to determine how many stored procedure calls were made locally at the federated database or by an application against the federated database.

Remote Locks

Description:  This element contains a count of the total number of remote locks that the federated server has called at this data source on behalf of any application since the later of:

• The start of the federated server instance
• The last reset of the database monitor counters.

Usage:  Use this element to determine how many remote locks were made remotely at the data source.

Rows Returned by Stored Procedures

Description:  This element contains the number of rows sent from the data source to the federated server as a result of stored procedure operations for this application since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

Usage:  This element has several uses. You can use it to compute the average number of rows sent to the federated server from the data source, per stored procedure, with the following formula:

   rows per stored procedure = rows returned / number of stored procedures invoked

You can also compute the average time to return a row to the federated server from the data source for this application:

   average time = rows returned / aggregate stored procedure response time

Query Response Time

Description:  This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to queries from all applications or a single application running on this federated server instance since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

The response time is measured as the difference in time between the time the federated server requests a row from the data source, and the time the row is available for the federated server to use.

Note:

Due to query blocking, not all attempts by the federated server to retrieve a row result in communication processing; the request to get the next row can potentially be satisfied from a block of returned rows. As a result, the aggregate query response time does not always indicate processing at the data source, but it usually indicates processing at either the data source or client.

Usage:  Use this element to determine how much actual time is spent waiting for data from this data source. This can be useful in capacity planning and tuning the CPU speed and communication rates in SYSCAT.SERVERS. Modifying these parameters can impact whether the optimizer does or does not send requests to the data source.

Insert Response Time

Description:  This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to INSERTs from all applications or a single application running on this federated server instance since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

The response time is measured as the difference in time between the time the federated server submits an INSERT statement to the data source, and the time the data source responds to the federated server, indicating that the INSERT has been processed.

Usage:  Use this element to determine the actual amount of time that transpires waiting for INSERTs to this data source to be processed. This information can be useful for capacity planning and tuning.

Update Response Time

Description:  This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to UPDATEs from all applications or a single application running on this federated server instance since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

The response time is measured as the difference in time between the time the federated server submits an UPDATE statement to the data source, and the time the data source responds to the federated server, indicating the UPDATE has been processed.

Usage:  Use this element to determine how much actual time transpires while waiting for UPDATEs to this data source to be processed. This information can be useful for capacity planning and tuning.

Delete Response Time

Description:  This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to DELETEs from all applications or a single application running on this federated server instance since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

The response time is measured as the difference in time between the time the federated server submits a DELETE statement to the data source, and the time the data source responds to the federated server, indicating the DELETE has been processed.

Usage:  Use this element to determine how much actual time transpires while waiting for DELETEs to this data source to be processed. This information can be useful for capacity planning and tuning.

Create Nickname Response Time

Description:  This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to process CREATE NICKNAME statements from all applications or a single application running on this federated server instance since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

The response time is measured as the difference between the time the federated server started retrieving information from the data source to process the CREATE NICKNAME statement, and the time it took to retrieve all the required data from the data source.

Usage:  Use this element to determine how much actual time was used to create nicknames for this data source.

Pass-Through Time

Description:  This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to PASSTHRU statements from all applications or a single application running on this federated server instance since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

The response time is measured as the difference between the time the federated server submits a PASSTHRU statement to the data source, and the time it takes the data source to respond, indicating that the statement has been processed.

Usage:  Use this element to determine how much actual time is spent at this data source processing statements in pass-through mode.

Stored Procedure Time

Description:  This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to stored procedure statements from all applications or a single application running on this federated server instance since the later of:

• The start of the federated server instance
• The last reset of the database monitor counters.

The response time is measured as the difference between the time the federated server submits a stored procedure to the data source, and the time it takes the data source to respond, indicating that the stored procedure has been processed.

Usage:  Use this element to determine how much actual time is spent at this data source processing stored procedures.

Remote Lock Time

Description:  This element contains the aggregate amount of time, in milliseconds, that this data source spends in a remote lock from all applications or a single application running on this federated server instance since the later of:

• The start of the federated server instance, or
• The last reset of the database monitor counters.

The response time is measured as the difference between the time the federated server submits a remote lock to the data source, and the time the federated server releases a remote lock at the data source

Usage:  Use this element to determine how much actual time is spent at this data source in a remote lock.