DB2 Version 9.7 for Linux, UNIX, and Windows

System monitoring for the IBM Data Server Driver for JDBC and SQLJ

To assist you in monitoring the performance of your applications with the IBM® Data Server Driver for JDBC and SQLJ, the driver provides two methods to collect information for a connection.

That information is:

Core driver time: The sum of elapsed monitored API times that were collected while system monitoring was enabled, in microseconds. In general, only APIs that might result in network I/O or database server interaction are monitored.
Network I/O time: The sum of elapsed network I/O times that were collected while system monitoring was enabled, in microseconds.
Server time: The sum of all reported database server elapsed times that were collected while system monitoring was enabled, in microseconds.
Application time: The sum of the application, JDBC driver, network I/O, and database server elapsed times, in milliseconds.

The two methods are:

The DB2SystemMonitor interface
The TRACE_SYSTEM_MONITOR trace level

To collect system monitoring data using the DB2SystemMonitor interface: Perform these basic steps:

Invoke the DB2Connection.getDB2SystemMonitor method to create a DB2SystemMonitor object.
Invoke the DB2SystemMonitor.enable method to enable the DB2SystemMonitor object for the connection.
Invoke the DB2SystemMonitor.start method to start system monitoring.
When the activity that is to be monitored is complete, invoke DB2SystemMonitor.stop to stop system monitoring.
Invoke the DB2SystemMonitor.getCoreDriverTimeMicros, DB2SystemMonitor.getNetworkIOTimeMicros, DB2SystemMonitor.getServerTimeMicros, or DB2SystemMonitor.getApplicationTimeMillis methods to retrieve the elapsed time data.
The server time that is returned by DB2SystemMonitor.getServerTimeMicros does not include commit or rollback time.

For example, the following code demonstrates how to collect each type of elapsed time data. The numbers to the right of selected statements correspond to the previously described steps.

Figure 1. Example of using DB2SystemMonitor methods to collect system monitoring data

import java.sql.*;
import com.ibm.db2.jcc.*;
public class TestSystemMonitor 
{
  public static void main(String[] args) 
  {
    String url = "jdbc:db2://sysmvs1.svl.ibm.com:5021/san_jose";
    String user="db2adm";         
    String password="db2adm";
    try 
    {                                                                        
      // Load the IBM Data Server Driver for JDBC and SQLJ
      Class.forName("com.ibm.db2.jcc.DB2Driver");                            
      System.out.println("**** Loaded the JDBC driver");

      // Create the connection using the IBM Data Server Driver for JDBC and SQLJ
      Connection conn = DriverManager.getConnection (url,user,password);                               
      // Commit changes manually
      conn.setAutoCommit(false);
      System.out.println("**** Created a JDBC connection to the data source");
      DB2SystemMonitor systemMonitor =                                1 
        ((DB2Connection)conn).getDB2SystemMonitor();
      systemMonitor.enable(true);                                     2 
      systemMonitor.start(DB2SystemMonitor.RESET_TIMES);              3 
      Statement stmt = conn.createStatement();
      int numUpd = stmt.executeUpdate(                               
        "UPDATE EMPLOYEE SET PHONENO='4657' WHERE EMPNO='000010'");
      systemMonitor.stop();                                           4 
      System.out.println("Server elapsed time (microseconds)=" 
        + systemMonitor.getServerTimeMicros());                       5 
      System.out.println("Network I/O elapsed time (microseconds)=" 
        + systemMonitor.getNetworkIOTimeMicros());
      System.out.println("Core driver elapsed time (microseconds)=" 
        + systemMonitor.getCoreDriverTimeMicros());
      System.out.println("Application elapsed time (milliseconds)=" 
        + systemMonitor.getApplicationTimeMillis());
      conn.rollback();
      stmt.close();
      conn.close();
    }
    // Handle errors  
    catch(ClassNotFoundException e)
    {
      System.err.println("Unable to load the driver, " + e);
    }
    catch(SQLException e)
    {
      System.out.println("SQLException: " + e);
      e.printStackTrace();
    }
  }    
}

To collect system monitoring information using the trace method: Start a JDBC trace, using configuration properties or Connection or DataSource properties. Include TRACE_SYSTEM_MONITOR when you set the traceLevel property. For example:

String url = "jdbc:db2://sysmvs1.stl.ibm.com:5021/san_jose" +
 ":traceFile=/u/db2p/jcctrace;" +
 "traceLevel=" + com.ibm.db2.jcc.DB2BaseDataSource.TRACE_SYSTEM_MONITOR + ";";

The trace records with system monitor information look similar to this:

[jcc][SystemMonitor:start]  
…
[jcc][SystemMonitor:stop] core: 565.67ms | network: 211.695ms | server: 207.771ms