helpinghand
search
needassistance
 
How To
Use the Mimer Jtrace Driver
Category: JDBC
Introduction

The Mimer Jtrace Driver is a JDBC driver that sits 'on top' of another JDBC driver. All calls made to the Jtrace Driver are passed on to the other JDBC driver.

The Mimer Jtrace Driver generates both a trace of all JDBC calls that are made, as well as timing information.

Note! The Mimer Jtrace Driver is not limited to Mimer JDBC drivers, you can use it with any JDBC 1.2 driver.
Description

The Mimer Jtrace Driver is an unsupported developer tool. You can download it for free here.
Environment

Setting-up the Mimer Jtrace Driver
To be able to use the Mimer Jtrace Driver, it must be loaded into the Java environment. To achieve this, a program loads a driver explicitly by calling the Class.forName() method with the driver class as the parameter.

The name of the Mimer driver class is com.mimer.jtrace.Driver. The driver is then located by the Java environment which searches along the class path, defined in the CLASSPATH environment variable.

Note that you must also load the traced driver. An application that uses the Jtrace Driver will have to load two JDBC drivers. There are two ways to do this:
  • Let the application load both the Jtrace driver and the traced driver. The application must call the Class.forName() method for each driver.
  • Use the driver property in the connection URL string. The application only loads the Jtrace driver by calling the Class.forName() method. The traced driver will be loaded by the Jtrace driver when it parses the connection URL.

URL Syntax
The syntax of a connection URL is (items contained in square brackets are optional):

  jdbc:jtrace:[?trace-options]url
...where the trace-options syntax is as follows:
  [driver=classname&][window&][logfile[=filename]&][dm&]
...and url is the URL of the traced driver.
The following trace-options are known to the trace driver:
  • driver -- Load the specified driver
  • window -- Display the trace in a Window. This is the default
  • logfile -- Display the trace in the specified file. The default file name is jtrace.log in the current directory. Note: the Jtrace Driver appends all new log records to the end of the file.
  • dm -- Use DriverManager.println() for the trace output.
Tracing to a Window
If the trace output is directed to a window, a new window will be opened and the trace output displayed. All traced connections will be traced to the same window. The window is updated continually as the application executes. To save memory, only the most recent 200 lines of trace output is kept in the window.
The trace window has a menu option you can select to display the statistics window. The statistics window is continually updated.

Exiting a Traced Application
All Java applications will exit automatically when all threads in the application are finished. However, when the application opens a GUI window, an internal GUI management thread is created that never finishes. It is assumed that GUI-based Java applications exit explicitly by calling System.exit().

If you are using the Jtrace Driver in window mode on an application that doesn't use a GUI, it is possible that the application does not contain an explicit call to System.exit(). When the application finishes, the Jtrace window will still be active. To exit the application in this case, use the menu alternative Exit application in the Jtrace window. This will call System.exit().

When tracing a GUI-based application with a Jtrace driver in windows mode, avoid using Exit application from the Jtrace Driver. Select Close window instead.
Example

Using the Jtrace Driver
The following URL:

jdbc:jtrace:?logfile&driver=com.mimer.jdbc.Driver&url=jdbc:mimer://myhost/mydb

...will load the Mimer JDBC driver and trace the URL jdbc:mimer://myhost/mydb.
The output will be written to the end of the file C:\Temp\logf.txt, and may look like this:

10:05:30.240  drv1.connect("jdbc:jtrace:?logfile&driver
       =com.mimer.jdbc.Driver&url
       =jdbc:mimer://myhost/mydb", {user=SYSADM, password=SYSADM})
       0.000  con1
10:05:30.240  con1.createStatement()
       0.110  s1
10:05:30.350  s1.executeQuery("select * from mimer.databanks")
       0.330  rs1
10:05:30.680  rs1.next()
       0.060  true
10:05:30.740  rs1.getString(2)
       0.000  "SYSDB             "
10:05:30.740  rs1.getString(1)
       0.000  "SYSTEM            "
10:05:30.740  rs1.getString(3)
       0.000  "L"
10:05:30.740  rs1.getString(5)
       0.000  "SYSDB82           "
10:05:30.740  rs1.getString(4)
       0.000  "SYSDB             "
10:05:30.740  con1.close()
       0.050  
//Object                Opened      Closed
//Connection:           0           0
//Statement:            1           0
//PreparedStatement:    0           0
//CallableStatement:    0           0
//ResultSet:            1           0
//ResultSetMetaData:    0
//DatabaseMetaData:     0
//JDBC calls:           11
//SQL compilations:     1
//Executed queries:     1
//         updates:     0
//Rows fetched:         1
//Columns fetched:      5
//Column NULL checks:   0
//Calls to commit():    0
//Calls to rollback():  0
//Exceptions from JDBC: 0
//
//Statistics from com.mimer.jdbc.Statistics
//Start date:        Wed Jun 13 10:05:29 CEST 2001
//Total requests: 7
//Bytes sent:     425,     60 per request, 152 peak
//Bytes received: 1478,     211 per request, 1036 peak

Jtrace Driver Statistics
The Jtrace driver collects statistics on all JDBC connections it traces.
If the tracing is done to a log file, the statistics are collected for each connection and is written to the log file when the connection is closed.
If the tracing is done to a window, the statistics window can be opened by selecting the statistics item in the show menu. The statistics window shows the combined statistics for all connections.

CounterExplanation
Connection
Statement
PreparedStatement
CallableStatement
ResultSet
The Jtrace driver counts the number of times that JDBC objects of different classes have been opened and closed. This helps to detect if the application forgets to close objects. In the example above, the application failed to close a Statement and a ResultSet object.

When tracing to a log file, the Connection counters are not used (and will always be zero) since the statistics are collected and displayed for each Connection.

ResultSetMetaData
DatabaseMetaData
The trace driver will also count how many times these objects are returned from the driver. Since these classes do not have a close() method, it is not possible to count the number of closes.

JDBC calls
The total number of JDBC driver calls.

SQL compilations
The number of times an SQL statement was sent to the driver.
You might be able to reduce this number by using PreparedStatements with parameter markers.

Executed queries
Executed updates
Counts the number of SQL executions that returned a
ResultSet (queries) or an update count (updates).
You can compare the number of SQL executions with the number of
SQL compilations. A well-designed application will not recompile the SQL statement for each SQL execution.

Rows fetched
The total number of rows fetched.

Columns fetched
The total number of columns fetched. Together with the
Rows fetched counter, this gives an indication of the amount
of data the application has retrieved.

Column NULL checks
Counts the number of times the application checked if the driver returned a NULL value by calling ResultSet.wasNull().
If this counter is less than the Columns fetched counter,
this indicates that the application fetched some columns without checking for NULL values. This can lead to application errors unless it is known that no NULL values exists in the returned ResultSets
(for example if all corresponding table columns were declared NOT NULL).

Calls to commit()
The number of calls to Connection.commit().
If this counter is zero and the Executed updates counter is greater than one, the application could be doing updates of multiple tables without considering the transactional semantics of the operations.

Calls to rollback()
The number of calls to Connection.rollback().

Exceptions from JDBC
The number of SQLExceptions that the traced JDBC driver has thrown.


If the traced driver is a Mimer JDBC driver, the trace driver will return some driver-specific statistics.

The following information is returned from Mimer JDBC drivers:

//Statistics from com.mimer.jdbc.Statistics
The example above shows the name of the class that supplies the driver-specific statistics. Lines after this line are driver specific.

//Start date: Wed Jun 13 10:05:29 CEST 2001
The example above gives the time when the Mimer JDBC connection was created.

//Total requests: 7
The example above displays the number of server requests the driver has made.
For optimal performance, it is important to keep this counter low. You can achieve this by using batched statements and stored procedures. Using the relational power of SQL to avoid record-oriented database access may also reduce the number of server requests needed.

//Bytes sent: 425, 60 per request, 152 peak
The example above shows the total number of bytes that were sent to the server (425) and the average number of bytes per server request (425/7=60). The size of the largest request to the server is also shown (152).

//Bytes received: 1478, 211 per request, 1036 peak
The example above shows the total number of bytes that were received from the server (1478) and the average number of bytes received per server request (1478/60=211). The size of the largest response from the server is also shown (1036).

The Mimer JDBC driver tries to reduce the number of server requests by prefetching rows when ResultSets are read. In the traced example above, the ResultSet contained five rows, and all rows were returned in one network packet (the large 1036 bytes packet).

However, the application only read one row (there was only one call to rs1.next()), so, in this case, the prefetch was wasted. A properly written application would have indicated to the server that it was only going to read one row by calling s1.setMaxRows(1).
Last updated: 2002-08-27

 

Powered by Mimer SQL

Powered by Mimer SQL