Hi,
We have been trying to measure the performance of SQL queries in Java
applications using JDBC and SQLJ to figure out the best method for our
environment. We intented to use DB2SystemMonitor interface as
instructed in IBM documentations. Everything sounds great and the API
is very simple to use, but we see unexpected results when it comes to
reality, and we couldn't find anything on IBM's website or on the
internet about this kind of issue. The resources are incredibly
limited. We are hoping someone here would help us with this.
The issue we are having is the returning values don't make sense all
the time. We can measure 4 things with this interface (from IBM's
website):
Server time: The sum of all reported DB2 server elapsed times that were
collected while system monitoring was enabled, in microseconds.
Network I/O time: The sum of elapsed network I/O times that were
collected while system monitoring was enabled, in microseconds.
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 DB2 server
interaction are monitored.
Application time: The sum of the application, JDBC driver, network I/O,
and DB2 server #elapsed times, in milliseconds.
Also, we found on other pages on IBM's website stating that Network
Time includes Server Time, and Core Driver Time includes Network Time +
Server Time. According to this, it should be like this:
Application Time Core Driver Time Network I/O Time Server Time
But our results are not like this always. For example (real test
values):
ServerTime: 5978 NetworkIOTime: 7565 DriverTime: 40345 AppTime: 30
Here AppTime is milliseconds, and if we convert it to microseconds,
it's 30000. As it seems, DriverTime is bigger than ApplicationTime,
which is not possible by definition. This is about the 30% of the
cases.
Also, in some tests, which is about 10% of the cases, ServerTime
returns bigger than NetworkTime, which is not possible either by
definition.
We are really stuck and can't find any information anywhere. We'd
really appreciate any help on this issue.
Thanks.
PS: DB2 runs on OS/390 7.1.2. I am running tests on Windows XP, using
DB2 Application Development Client FixPak 13.