Running DOTS (Database Open Source Test Suite) with Derby

Sunitha Kambhampati ( skambha@Yngvi.Org)

Preface:

I ran DOTS (Database Open Source Test Suite) with Derby as the underlying database. The setup was simple and it seems like this test suite can be used for testing on Linux. This document talks about how I went about trying to setup DOTS with Derby. I hope this will be of some help to the community. Please feel free to add your suggestions/ modifications to this document. Thanks.

1. Introduction. 1

2. Environment 2

2.1 Software Requirements. 2

2.2 The test environment that was used in this study: 2

3. Overview of DOTS and test configurations with Derby. 3

4. Steps to setup and run Dots: 3

4.1. Installation of DOTS. 3

4.2. Database (Derby) related setup. 4

4.3. Start the database server 5

4.4. Configuring test run parameters for DOTS client 6

4.5. Running the benchmark. 7

4.6. Checking the output and investigating possible problems. 8

4.7. Cleaning up. 8

5. Test Results with Derby. 9

6. Summary. 9

7. External Links. 10

APPENDIX A: derbydotsscripts.tar.gz contents. 10

APPENDIX B: Sample result file with Derby. 11

APPENDIX C: derby.ini 16

APPENDIX D: derbyNS.ini 18

1. Introduction

Database Opensource Test Suite (DOTS) is an open source test suite that is used to stress test database systems and to measure database reliability (http://ltp.sourceforge.net/dotshowto.php#SEC1 . DOTS is licensed under GNU General Public License (GPL). The test cases are in Java and make JDBC calls to the database. They are aimed to test 100% JDBC API coverage. There are two kinds of test cases, basic and advanced. There are 8 basic tests prefixed with BTCJ and 2 advanced tests prefixed by ATCJ. The advanced test cases simulate real world workloads including user authentication/registration scenario and an online auction scenario.

Derby is an Apache DB subproject. Derby is a standards based java relational database system.(http://db.apache.org/derby/). Some main features of derby are it is embedded, has small footprint, is pure java, easy to use and secure.

The aim of this document is to help setup DOTS to run with Derby. The following link http://ltp.sourceforge.net/dotshowto.php#SEC1 at the DOTS site provides a good overview of the architectural components of the test suite as well as the schema details.

This document basically does a walkthrough of a setup/ run of DOTS with Derby as the database. There are some scripts that are given with the hope that it will aid in reducing your setup time.

2. Environment

This section talks about the hardware and software needed to run DOTS. In sub-section 2.1, the requirement as stated by DOTS is given. Sub-section 2.2 lists the actual test environment used.

2.1 Software Requirements:

Database: Derby (derby jars: derby.jar, derbynet.jar and derbytools.jar and if using network server, also need the drda client – db2jcc.jar and db2jcc_license_c.jar)

OS: Linux 2.4.4

JVM: J2SE1.3.1 or higher

Hardware Requirements:

CPU: 700Mhz or higher

Memory: 512Mb or higher

Disk Capacity: 2 GB or higher

2.2 The test environment that was used in this study:

Test machine:

OS Name	Suse Linux
Version	9.0
System Manufacturer	IBM
System Model	86473FX
System Type	X86-based PC
Processor	x86 Family 15 Model 2 Stepping 7 GenuineIntel ~2400 Mhz
Processor	x86 Family 15 Model 2 Stepping 7 GenuineIntel ~2400 Mhz
Memory	4Gb

Software Used:

Derby jars –

Instructions to build derby jars and downloads are at

        http://db.apache.org/derby/derby_downloads.html

Java -

Java 1.4.2

3. Overview of DOTS and test configurations with Derby

A high level overview of the main components of DOTS is as follows

There is the database system that DOTS is run against.

DOTS client that drives the test run

There is a performance monitor that runs on the same machine as the database and communicates with the DOTS client via sockets passing information about the CPU usage and other performance related attributes.

DOTS can be run in two modes:

DOTS client on one machine, and the database server on another machine
DOTS client and database server on the same machine.

Derby with DOTS can run in two modes

Derby in embedded mode runs as part of the DOTS client (ie in the same JVM as the DOTS client). Hence the only possible configuration would be DOTS client and embedded database as well as the performance monitor program on one machine.
Derby in client –server mode (Network Server). If you use Derby Network Server, then the possible configurations are

Database server and performance monitor on one machine and the DOTS client on another machine
Database server, performance monitor and DOTS client on one machine.

4. Steps to setup and run Dots:

In the example described here: the database, DOTS client and the performance monitor are all on one machine. If you decide to have the DOTS client and database server on different machines, the steps will be some what similar except that you will have to ensure that the necessary jars are present on the client and the database server machines.

The sections 3.1 and 3.2 of the Database Opensource Test Suite User’s Guide provide excellent overview. Section 3.1 (http://ltp.sourceforge.net/dotshowto.php#SEC11) and

Section 3.2 (http://ltp.sourceforge.net/dotshowto.php#SEC12)

4.1. Installation of DOTS

1)            Download the DOTS 1.1.1 (Dots-1.1.1.gz) from here http://sourceforge.net/project/showfiles.php?group_id=3382

Unzip the gz file

$ tar zxvf Dots-1.1.1.gz

After this, there will be a Dots directory with different sub directories with source of the tests, scripts, make file etc.

In this example, Dots has been installed at /home/perftest/Dots. Let $DOTS_ROOT refer to your dots installation. Here it will be /home/perftest/Dots.

2)      Make sure that java is installed, else install it and set the path variable to include jvm.

3) Compiling the DOTS source

Change directory to your Dots installation

cd /home/perftest/Dots/

Before running the make command, make sure that you have javac in your path.

make all

This will compile all the source java files in the src subdirectory of your DOTS installation. If this is successful, you will see no errors during the make, and there will be two jars under $DOTS_ROOT: PerfMon.jar and the Dots.jar

PerfMon.jar – This jar file contains the classes necessary to start the performance monitor client. This jar is required to run performance monitor.

Dots.jar – This jar file has all the test cases that are needed to run tests. Also contains the DOTS client component needed to drive the test runs.

4.2. Database (Derby) related setup

The database related setup involves setting up the necessary database jar files, creating the database and loading the schema. For both Derby (embedded or client-server mode), the ij tool that is in derbytools.jar is used for creation of database and loading of schema.

There is a derbydotsscripts.tar.gz file along with this document that has the scripts and sql files necessary for derby. For more details on each of the scripts present in the derbydotsscripts.tar.gz file, please see APPENDIX A. To help reduce the setup time, these scripts can be easily modified according to your individual setup. Unzip the derbydotsscripts.tar.gz into a directory called derby and execute the scripts from the same directory. Modify the myenv.ksh to set the DERBY_JARS_ROOT as well as the DOTS_JARS_ROOT environment variables appropriately. DERBY_JARS_ROOT refers to the root directory where the derby jars are located and DOTS_JARS_ROOT refers to the root directory where the DOTS related jars are located. Below, for each step the corresponding script that can be used is given enclosed in brackets. In case you are using the scripts, make sure that you have first run myenv.ksh or set the DERBY_JARS_ROOT and DOTS_JARS_ROOT variables in the shell window that you are executing from. ($. ./myenv.ksh)

§ Open a new shell window and if you are using the scripts, set DERBY_JARS_ROOT and DOTS_JARS_ROOT appropriately

($. ./myenv.ksh)

§ Obtain derby jars – derby.jar, derbytools.jar ( for embedded mode)

For network server mode, also need derbynet.jar

Set classpath to derby jars.

export DERBY_JARS="/home/perftest/derby"

export CLASSPATH="$DERBY_JARS/derby.jar:$DERBY_JARS/derbytools.jar"

($. ./cpderby.ksh )

§ Ensure JVM is in your path.

export PATH="/home/perftest/jdk1.4.2_04/bin:${PATH}"

($. ./jvm.ksh)

§ Create the database in the current directory and load the schema and procedures

SQL files

o Clean database directory if already present

rm –rf TESTDB

($ . ./clean.ksh)

o Create database (TESTDB in location /home/perftest/derby)

java org.apache.derby.tools.ij

ij> connect ‘jdbc:derby:TESTDB;create=true;user=u1;password=p1’;

o Create tables

Check out the createtable_derby.sql in derbydotsscripts.tar.gz

$ java org.apache.derby.tools.ij createtable_derby.sql

o Create procedures

Check out the createproc_derby.sql in derbydotsscripts.tar.gz

$ java org.apache.derby.tools.ij createproc_derby.sql

All the steps above or ($. ./load.ksh)

4.3. Start the database server

After the derby database is created in the 4.2., things to note:

To run Dots with Derby in embedded mode.

Since derby will run in embedded mode in same JVM as the DOTS client, derby.jar needs to be in the classpath when running the client program. Nothing else needs to be done.

To run Dots with Derby Network Server

Set classpath to derby.jar, derbynet.jar and the location where the DotsJavaProcedures.class is located (obtain it from derbydotsscripts.tar.gz). The DotsJavaProcedures.class contains the java procedures used in one of the test cases (BTCJ8).

export CLASSPATH=".:$DERBY_JARS_ROOT/derby.jar:$DERBY_JARS_ROOT/derbynet.jar"

Ensure that JVM is in your path

Start the network server ( it will accept connections from the local host)

$ java org.apache.derby.drda.NetworkServerControl start –h 0.0.0.0

(This example deals with the server and client on one machine, but if network server needs to accept connections from outside hosts, network server needs to run under security manager)

4.4. Configuring test run parameters for DOTS client

The DOTS client program picks up the test configuration from the config.ini file by default or a file specified with the –config.ini parameter. So the next step is to configure the config.ini file as needed for a test run.

This file contains a list of configurable properties: duration of the test run, summary when results should be logged, the results directory, the number of max connections to be used, amount of max CPU usage etc. For a complete list and explanation of the properties, check out the DOTS User guide. The options of setting the max number of database connections as well as the max CPU load can be changed for different test runs.

For Derby, database connection URL must be edited in the config.ini file.

Using Derby embedded mode:

set DriverClass , dbUrl, UserId and Password appropriately.

An example:

UserID = u1

Password = p1

DriverClass = org.apache.derby.jdbc.EmbeddedDriver

URL = jdbc:derby:TESTDB

If the TESTDB is not in the same directory where the DOTS client is run, then the database name in the URL must point to the exact path.

Using Derby in network server mode:

UserID = u1

Password = p1

DriverClass = com.ibm.db2.jcc.DB2Driver

URL = jdbc:derby:net://database_server_ip/TESTDB

Here TESTDB is in the same directory where the network server has been started. If not specify the absolute path for TESTDB on the server side.

The derbydotsscripts.tar.gz file has derby.ini file which has derby specific connection attributes filled in, in case of the embedded mode else for network server mode, the derbyNS.ini can be used and modified. .

Some common options that are worth noting here are

DURATION = 00:05

Duration you want the test to be run. The above setting sets the time for each run to be 5 minutes

SUMMARY_INTERVAL=

This value determines at the interval at which to gather the summary for the test run.

LOG_DIR=

This value determines where the test reports will be written out to.

The following two parameters are for the performance monitor component. Specify the IP address of the machine where the performance monitor is running (or going to be started for the test).

Default server_port is 8001.

SERVER_IP=9.30.134.81

SERVER_PORT=8001

4.5. Running the benchmark

1) Open a new shell, and start the performance monitor on the machine

on which the database system is.

Ensure classpath is set to PerfMon.jar

$java dots.perfmon.PerfMon –port 8001

(For convenience purposes, there is a script perfmon.ksh in derbydotsscripts.tar.gz that can be used to start the performance monitor. Make sure to have run myenv.ksh in the shell window before perfmon.ksh)

2) Ensure database is created and loaded and if running against network server, check if the server is running.

If running against Derby in embedded mode:

Set the classpath to

§ the derby jar,

§ to the java procedures implementation class file (DotsJavaProcedures.class in derbydotsscripts.tar.gz) and

§ Dots.jar

export CLASSPATH=".:$DERBY_JARS_ROOT/derby.jar:$DOTS_JARS_ROOT/Dots.jar"

($. ./cpclient.ksh)

To run a test by name BTCJ1,

java dots.framework.Dots -config derby.ini -case BTCJ1

($. ./run.ksh)

If running against Derby in network server mode,

On the dots client side, set the classpath to

§ the network server client jars (db2jcc.jar, db2jcc_license_c.jar)

§ Dots.jar

To run a test by name BTCJ1,

java dots.framework.Dots -config derbyNS.ini -case BTCJ1

4.6. Checking the output and investigating possible problems

On a successful run, go to the path pointed by LOG_DIR in the config.ini (here the test run uses derby.ini) file.

There will be files with three different extensions.

Example of running the BTCJ1 test

BTCJ1-2004-8-31-13-43-40-271.err

BTCJ1-2004-8-31-13-43-40-271.sum

BTCJ1-2004-8-31-13-43-40-271.log

One is an err file which has the errors encountered when running the test. The *.sum file has the summary of the test run and the *log file reports on CPU usage and other details. The *.err file is key file to look for possible errors.

Depending on the summary interval and the test duration, there will be more files, each prefixed with the test case name and the timestamp.

4.7. Cleaning up

The tests do not seem to do a clean shutdown of the Derby database. So for tests with Derby in embedded mode, make sure to cleanup the database after each run and start afresh. Or another possibility is to modify the test to do a shutdown.

Tests BTCJ6 and BTCJ7 create test files prefixed by blob* and clob*, these can be deleted before each test run if needed.

One way to shut down the performance monitor is to use Control –C.

5. Test Results with Derby

I gave a 1hr run, connections = 1, and database loaded after each test in embedded mode. All tests were executed consecutively. Test BTCJ6 and BTCJ7 use streams to insert blobs and clobs. According to the java specification and tutorial- the length passed in setCharacterStream must be equal exactly to the number of characters in the stream that is passed. But in this case the test passes a stream that is larger than the actual length passed in the setCharacterStream. Hence there are exceptions (IOExceptions) inserting blobs and clobs into table using streams. Because the java reference tutorial is publication from Sun, the problem seems to be with the test and not derby.

All other tests ran OK. Some results from my tests are given in derbydotsresults.tar.gz. If you unzip this file, you will be able to see two directories (results_derbyEmbeddedMode and results_derbyNetworkServer). The files under results_derbyEmbeddedMode are results from running DOTS using Derby in embedded mode and results_derbyNetworkServer has all the results from running DOTS using Derby in network server mode.

Also, I gave 6 hr runs for the advanced test cases ATCJ1 and ATCJ2 and they ran OK. In case of the network server, database was created and loaded once initially before the tests began. After that all tests were given for a run one after another. Tests in network server mode also ran ok, with no problems.

6. Summary

In this document, setup instructions have been described to help run Dots with Derby. As seen here, the test setup is relatively simple and it seems that it can easily be used/adapted to do testing of Derby on Linux platforms. DOTS is not available on windows platform as yet because of the dependency of the performance monitor component on the Linux kernel. The only problems that can be picked up by running this suite seem to be ones that will result in an exception to be thrown. Exceptions are logged into the results file with the err suffix.

In regards to using it as a performance benchmark to compare different database systems, here are some reasons I have come up with for not using it as a database performance benchmark:-

There are different queries issued in some tests for some database systems, so it will not be fair to compare without looking into the set of queries used in each test.
Also, there are some error case scenarios that are valid input values in the tests. There is no clear definition of how the error cases should be evaluated for comparison. The suite seems to lack clear performance metrics to be useful to compare performance of different database systems.
There is use of java.sql.Statement with explicit values used. For performance reasons, it is better to have prepared statements with dynamic parameters (?) instead of java.sql.Statement since in case of the latter; Derby will have to compile each statement.

Also, most related literature on the web that I found indicates that DOTS is generally used to do kernel testing. Some good points of using this test suite, is it is fairly simple to configure and run and the advanced test cases can easily be used for testing the database for long periods of time.

7. External Links

DOTS – howto document http://ltp.sourceforge.net/dotshowto.php#SEC1

DOTS download  http://sourceforge.net/project/showfiles.php?group_id=3382

Derby manuals http://db.apache.org/derby/manuals/index.html

Derby downloads http://db.apache.org/derby/derby_downloads.html

IBM JCC Driver http://www.ibm.com/developerworks/db2/downloads/jcc/

APPENDIX A: derbydotsscripts.tar.gz contents

Unzip the derbydotsscripts.tar.gz file to use the scripts and sql files. After unzipping, you should be able to see the following files:

File	Purpose
cpderby.ksh	Set classpath before creating database and loading the schema
jvm.ksh	To set java path
myenv.ksh	Set appropriate paths to your DOTS_JARS_ROOT and DERBY_JARS_ROOT
load.ksh	Create database, and load tables and procedures
clean.ksh	Clean the database
perfmon.ksh	Set classpath to the Perfmon.jar and also start the performance monitor
cpclient.ksh	Set classpath for DOTS client and derby in embedded mode
run.ksh	Sample script used to run one test
cpclientjcc.ksh	Set classpath for DOTS client as well as trying to connect to Derby using the JCC driver.
runNS.ksh	Sample script to run one dots test with derby in network server mode
createdb_derby.sql	To create derby database
createtable_derby.sql	Sql to create tables
createproc_derby.sql	Sql to create java procedures
DotsJavaProcedures.java	Java procedures implementation for DOTS test case BTCJ8
DotsJavaProcedures.class	Class file for DotsJavaProcedures.java
derby.ini	Sample config.ini file for derby ( with derby specific connection parameters)
derbyNS.ini	Sample config.ini file for derby network server ( with IBM JCC client specific connection parameters)

APPENDIX B: Sample result file with Derby

BTCJ1-2004-9-1-8-4-37-309.log

------------------------------------------

[Wed Sep 01 08:04:37 PDT 2004] Database Opensource Test Suite V1.0

[Wed Sep 01 08:04:37 PDT 2004] Start to run JDBC API Test Case - BTCJ1

[Wed Sep 01 08:04:37 PDT 2004] Initialization started

[Wed Sep 01 08:04:37 PDT 2004] Starting Performance Monitor client ...OK

[Wed Sep 01 08:04:37 PDT 2004] Client Socket: Socket[addr=eservcldscpe.ibmus2.ibm.com/9.72.134.80,port=8001,localport=33009]

[Wed Sep 01 08:04:38 PDT 2004] Testing Database Connections ...OK

[Wed Sep 01 08:04:38 PDT 2004] Starting Summary Writer ... OK

[Wed Sep 01 08:04:43 PDT 2004] Active Threads = 1 Average CPU Usage = 21%

[Wed Sep 01 08:05:38 PDT 2004] Writing summary to Summary File...

[Wed Sep 01 08:05:43 PDT 2004] Active Threads = 1 Average CPU Usage = 44%

[Wed Sep 01 08:06:38 PDT 2004] Writing summary to Summary File...

[Wed Sep 01 08:06:43 PDT 2004] Active Threads = 1 Average CPU Usage = 46%

[Wed Sep 01 08:07:38 PDT 2004] Writing summary to Summary File...

[Wed Sep 01 08:07:38 PDT 2004] 3 Minutes Average CPU Usage = 42%

[Wed Sep 01 08:07:43 PDT 2004] Active Threads = 2 Average CPU Usage = 54%

[Wed Sep 01 08:08:38 PDT 2004] Writing summary to Summary File...

[Wed Sep 01 08:08:43 PDT 2004] Active Threads = 2 Average CPU Usage = 75%

[Wed Sep 01 08:09:38 PDT 2004] Writing summary to Summary File...

[Wed Sep 01 08:09:38 PDT 2004] 3 Minutes Average CPU Usage = 47%

[Wed Sep 01 08:09:38 PDT 2004] Dots is Terminating ...

[Wed Sep 01 08:09:38 PDT 2004] Writing summary to Summary File...

[Wed Sep 01 08:04:37 PDT 2004] Start to run JDBC API Test Case - BTCJ1

[Wed Sep 01 08:04:37 PDT 2004] The Linux Kernel Version is 2.4.19-64GB-SMP

[Wed Sep 01 08:04:37 PDT 2004] The Database Server's CPU Count is 2

[Wed Sep 01 08:05:38 PDT 2004]

BTCJ1-2004-9-1-8-4-37-309.sum

-----------------------------------------

<Total Execution Time> 0 hours 1 minutes.

<Current Concurrent DB Connections> 1.

<JDBC APIs> Total number of QUERY 2559.

Total number of UPDATE 0.

Total number of INSERT 7597.

Total number of DELETE 0.

Total number of FAILED 0.

<Average CPU> Peak of this Interval: 49%

Average of this Interval: 39%

Peak of all: 49%

Average of all: 39%

<CPU0> Peak of this Interval: 66%

Average of this Interval: 40%

Peak of all: 66%

Average of all: 40%

<CPU1> Peak of this Interval: 82%

Average of this Interval: 38%

Peak of all: 82%

Average of all: 38%

<Memory> Peak of this Interval: 257M

Average of this Interval: 250M

Peak of all: 257M

Average of all: 250M

<Disk IO> Peak of this Interval: 430/s

Average of this Interval: 339/s

Peak of all: 430/s

Average of all: 339/s

<Page In> Peak of this Interval: 0/s

Average of this Interval: 0/s

Peak of all: 0/s

Average of all: 0/s

<Page Out> Peak of this Interval: 2494/s

Average of this Interval: 1964/s

Peak of all: 2494/s

Average of all: 1964/s

[Wed Sep 01 08:06:38 PDT 2004]

<Total Execution Time> 0 hours 2 minutes.

<Current Concurrent DB Connections> 1.

<JDBC APIs> Total number of QUERY 4684.

Total number of UPDATE 0.

Total number of INSERT 13908.

Total number of DELETE 0.

Total number of FAILED 0.

<Average CPU> Peak of this Interval: 46%

Average of this Interval: 44%

Peak of all: 49%

Average of all: 41%

<CPU0> Peak of this Interval: 73%

Average of this Interval: 40%

Peak of all: 73%

Average of all: 40%

<CPU1> Peak of this Interval: 80%

Average of this Interval: 48%

Peak of all: 82%

Average of all: 43%

<Memory> Peak of this Interval: 264M

Average of this Interval: 260M

Peak of all: 264M

Average of all: 254M

<Disk IO> Peak of this Interval: 359/s

Average of this Interval: 325/s

Peak of all: 430/s

Average of all: 332/s

<Page In> Peak of this Interval: 0/s

Average of this Interval: 0/s

Peak of all: 0/s

Average of all: 0/s

<Page Out> Peak of this Interval: 2104/s

Average of this Interval: 1886/s

Peak of all: 2494/s

Average of all: 1926/s

[Wed Sep 01 08:07:38 PDT 2004]

<Total Execution Time> 0 hours 3 minutes.

<Current Concurrent DB Connections> 1.

<JDBC APIs> Total number of QUERY 6491.

Total number of UPDATE 0.

Total number of INSERT 19276.

Total number of DELETE 0.

Total number of FAILED 0.

<Average CPU> Peak of this Interval: 47%

Average of this Interval: 45%

Peak of all: 49%

Average of all: 43%

<CPU0> Peak of this Interval: 79%

Average of this Interval: 49%

Peak of all: 79%

Average of all: 43%

<CPU1> Peak of this Interval: 82%

Average of this Interval: 41%

Peak of all: 82%

Average of all: 42%

<Memory> Peak of this Interval: 270M

Average of this Interval: 266M

Peak of all: 270M

Average of all: 258M

<Disk IO> Peak of this Interval: 301/s

Average of this Interval: 275/s

Peak of all: 430/s

Average of all: 314/s

<Page In> Peak of this Interval: 0/s

Average of this Interval: 0/s

Peak of all: 0/s

Average of all: 0/s

<Page Out> Peak of this Interval: 1724/s

Average of this Interval: 1592/s

Peak of all: 2494/s

Average of all: 1818/s

[Wed Sep 01 08:08:38 PDT 2004]

<Total Execution Time> 0 hours 4 minutes.

<Current Concurrent DB Connections> 2.

<JDBC APIs> Total number of QUERY 7897.

Total number of UPDATE 0.

Total number of INSERT 23450.

Total number of DELETE 0.

Total number of FAILED 0.

<Average CPU> Peak of this Interval: 78%

Average of this Interval: 72%

Peak of all: 78%

Average of all: 50%

<CPU0> Peak of this Interval: 78%

Average of this Interval: 73%

Peak of all: 79%

Average of all: 50%

<CPU1> Peak of this Interval: 78%

Average of this Interval: 71%

Peak of all: 82%

Average of all: 49%

<Memory> Peak of this Interval: 276M

Average of this Interval: 272M

Peak of all: 276M

Average of all: 262M

<Disk IO> Peak of this Interval: 289/s

Average of this Interval: 214/s

Peak of all: 430/s

Average of all: 289/s

<Page In> Peak of this Interval: 0/s

Average of this Interval: 0/s

Peak of all: 0/s

Average of all: 0/s

<Page Out> Peak of this Interval: 1673/s

Average of this Interval: 1242/s

Peak of all: 2494/s

Average of all: 1677/s

[Wed Sep 01 08:09:38 PDT 2004]

<Total Execution Time> 0 hours 5 minutes.

<Current Concurrent DB Connections> 2.

<JDBC APIs> Total number of QUERY 9054.

Total number of UPDATE 0.

Total number of INSERT 26886.

Total number of DELETE 0.

Total number of FAILED 0.

<Average CPU> Peak of this Interval: 78%

Average of this Interval: 74%

Peak of all: 78%

Average of all: 54%

<CPU0> Peak of this Interval: 77%

Average of this Interval: 74%

Peak of all: 79%

Average of all: 55%

<CPU1> Peak of this Interval: 79%

Average of this Interval: 74%

Peak of all: 82%

Average of all: 54%

<Memory> Peak of this Interval: 279M

Average of this Interval: 276M

Peak of all: 279M

Average of all: 264M

<Disk IO> Peak of this Interval: 226/s

Average of this Interval: 179/s

Peak of all: 430/s

Average of all: 268/s

<Page In> Peak of this Interval: 0/s

Average of this Interval: 0/s

Peak of all: 0/s

Average of all: 0/s

<Page Out> Peak of this Interval: 1311/s

Average of this Interval: 1039/s

Peak of all: 2494/s

Average of all: 1551/s

[Wed Sep 01 08:09:38 PDT 2004]

<Total Execution Time> 0 hours 5 minutes.

<Current Concurrent DB Connections> 2.

<JDBC APIs> Total number of QUERY 9069.

Total number of UPDATE 0.

Total number of INSERT 26931.

Total number of DELETE 0.

Total number of FAILED 0.

[Wed Sep 01 08:09:38 PDT 2004] Waiting for the active threads to exit ......

[Wed Sep 01 08:09:40 PDT 2004] All Database Access Threads exit.

APPENDIX C: derby.ini

#######################################################################

# Configuration File for DOTS

#######################################################################

# Specify the duration of running the test case

# Format: hh:mm

# Default is 24 hours and 0 minute

#######################################################################

DURATION = 00:05

#######################################################################

# Specify the output directory

# Default is /home/db2inst1/log

#######################################################################

LOG_DIR = /home/perftest/dots/derby/results

#LOG_DIR =

# Specify how many concurrent connections the test case will generate

# Default is 20

#######################################################################

CONCURRENT_CONNECTIONS= 1

#######################################################################

# Specify database server CPU utilization target

# Default is 90

#######################################################################

CPU_TARGET = 90

#######################################################################

# Specify if use auto mode to run test case

# When AUTO_MODE is set to yes, it will continously generate

# connections to database server until the CPU target is reached or

# maxium database connection number is reached which is specified by

# the target test database.

# When AUTO_MODE is set to no, it will just generate certain number

# of current connections which is specified by

# CONCURRENT_CONNECTIONS

# Default is no

#######################################################################

AUTO_MODE = no

#######################################################################

# Specify the time interval between two writes of test summary

# Default is 20 minutes

#######################################################################

SUMMARY_INTERVAL = 1

#######################################################################

# Database Connection Parameters

# UserID is the database user id of the database server

# Password is the database user password of the database server

# DriverClass is JDBC driver classes

# For Derby ( in embedded mode), driver is org.apache.derby.jdbc.EmbeddedDriver

# For Derby ( in network server mode) driver is com.ibm.db2j.jcc.DB2Driver (IBM JCC Universal driver)

# For Oracle, default is oracle.jdbc.driver.OracleDriver

# For DB2,default is COM.ibm.db2.jdbc.net.DB2Driver

# For Sybase,default is com.sybase.jdbc2.jdbc.SybDriver

# For MySQL, default is org.gjt.mm.mysql.Driver

# For PostgreSQL, default is org.postgresql.Driver

# URL is the URL required by JDBC driver

# For Derby in network server mode , default is jdbc:derby:net://DB_SERVER_IP/<DB_NAME>

# For Derby in embedded mode, default is jdbc:derby:TESTDB

# For Oracle, default is jdbc:oracle:thin:@<DB_SERVER_IP>:<JDBC_PORT>:<DB_NAME>

# For DB2, default is jdbc:db2://<DB_SERVER_IP>:<JDBC_PORT>/<DB_NAME>

# For Sybase, default is jdbc:sybase:Tds:<DB_SERVER_IP>:<JDBC_PORT>/<DB_NAME>

# For MySQL, default is jdbc:mysql://<DB_SERVER_IP>:<JDBC_PORT>/<DB_NAME>?user=<USER>&password=<PASS>

# For PostgreSQL, default is jdbc:postgresql://<DB_SERVER_IP>/<DB_NAME>

# For Derby in network server mode , default is jdbc:derby:net://DB_SERVER_IP/<DB_NAME>

# For Derby in embedded mode, default is jdbc:derby:TESTDB

#######################################################################

UserID = u1

Password = p1

DriverClass = org.apache.derby.jdbc.EmbeddedDriver

URL = jdbc:derby:TESTDB

#######################################################################

# Performance Monitor Parameters

# SERVER_IP is the IP address of database server

# SERVER_PORT is the port that Performance Monitor specifies

# Default port is 8001

#######################################################################

SERVER_IP = 9.72.134.80

SERVER_PORT = 8001

#######################################################################

# MAX_ROWS is the max rows test table can have

# Default is 500000

#######################################################################

MAX_ROWS= 500000

#######################################################################

# MAX_LOGFILESIZE is the max bytes of each log file

# Default is 104857600(100M),the maximum is 1073741824(1G)

# the minimum is 100K

#######################################################################

MAX_LOGFILESIZE = 10485760

#######################################################################

# CREATIONINTERVAL specify the thread creation interval.

# The Default is 3 minutes, the maximum is 5 minutes, minumum is 1 minutes

#######################################################################

CREATIONINTERVAL = 3

#######################################################################

# RUN_AUTO is optional. If RUN_AUTO is set to yes, Keyboard thread is

# disabled and the DOTS can run backgroud.

#######################################################################

RUN_AUTO = yes

APPENDIX D: derbyNS.ini

This is similar to derby.ini except for the database connection parameters. The entire derbyNS.ini file is available in derbydotsscripts.tar.gz.

#######################################################################

# Database Connection Parameters

# UserID is the database user id of the database server

# Password is the database user password of the database server

# DriverClass is JDBC driver classes

# For Derby ( in embedded mode), driver is org.apache.derby.jdbc.EmbeddedDriver

# For Derby ( in network server mode) driver is com.ibm.db2j.jcc.DB2Driver (IBM JCC Universal driver)

# For Oracle, default is oracle.jdbc.driver.OracleDriver

# For DB2,default is COM.ibm.db2.jdbc.net.DB2Driver

# For Sybase,default is com.sybase.jdbc2.jdbc.SybDriver

# For MySQL, default is org.gjt.mm.mysql.Driver

# For PostgreSQL, default is org.postgresql.Driver

# URL is the URL required by JDBC driver

# For Derby in network server mode , default is jdbc:derby:net://DB_SERVER_IP/<DB_NAME>

# For Derby in embedded mode, default is jdbc:derby:TESTDB

# For Oracle, default is jdbc:oracle:thin:@<DB_SERVER_IP>:<JDBC_PORT>:<DB_NAME>

# For DB2, default is jdbc:db2://<DB_SERVER_IP>:<JDBC_PORT>/<DB_NAME>

# For Sybase, default is jdbc:sybase:Tds:<DB_SERVER_IP>:<JDBC_PORT>/<DB_NAME>

# For MySQL, default is jdbc:mysql://<DB_SERVER_IP>:<JDBC_PORT>/<DB_NAME>?user=<USER>&password=<PASS>

# For PostgreSQL, default is jdbc:postgresql://<DB_SERVER_IP>/<DB_NAME>

#######################################################################

UserID = u1

Password = p1

DriverClass = com.ibm.db2.jcc.DB2Driver

URL = jdbc:derby:net://9.72.134.80/TESTDB