Version: 1.1.0
Owner: David Barrera
IBM Linux Technology Center
11501 Burnet Road
Austin, TX 78758
Database Opensource Test Suite (DOTS) is a set of test cases designed for the purpose of stress testing and long run testing on database systems to measure database performance and reliability. It has two kinds of test cases - Basic Cases and Advanced Cases. The primary goal of Basic Cases is stress and long run database testing; the secondary goal is 100% JDBC API coverage. There are 8 test cases written in Java to cover JDBC API under the Basic Cases category. The goal of the Advanced Cases is modeling real-world business logic, stress and long run testing on database systems. There are 2 test cases written in Java under the Advanced Cases category.
| Server | Client | |
|---|---|---|
| Operating System | Linux 2.4.4 or later kernel | Linux 2.4.4 or later kernel |
| Database System | IBM DB2 UDB for Linux Enterprise Edition Release 7.2 or later | JDBC Driver: db2jdbc.zip(7.2)or db2jcc.jar(8.x) |
| Oracle 8i for Linux Enterprise Edition 8.1.7 or later | JDBC Driver: classes12.zip | |
| Sybase Adaptive Server for Linux Enterprise Evaluation Edition 12.5 | JDBC Driver: jconn2.jar | |
| Other Softwares | J2SE 1.3.1 for Linux | J2SE 1.3.1 for Linux |
| Category | Requirements |
|---|---|
| CPU | 700MHz or higher |
| Memory | 512MB or higher |
| Disk Capacity | 2GB or higher |
Notes:
1. DOTS was designed to run on any hardware platform supported by
any Linux distribution. It was developed and tested on a 32 bit Intel platform
running Linux.
2. The number of DOTS test cases you can simultaneously run
on a test client, will depend on the physical memory available.
There are two components that make up DOTS. The first component is the DOTS Client that runs on the test client machine. The second component is the Performance Monitor that runs on the test server machine. The DOTS client uses Sockets and JDBC calls to communicate with the Performance Monitor and the database server respectively. Performance Monitor gets system information from the server, such as CPU usage, memory usage, disk I/O, and sends it back to the test client. JDBC calls are used to perform database transactions on the server.
Neither DOTS Client nor the Performance Monitor has any GUI support in the current release. Users can start running test cases from the command line. DOTS Client can run any of the ten test cases. The test environment can be customized by modifying the configuration file. The format of the configuration file can be found in Appendix B.
DotsClient: DotsClient is the user interface to the DOTS application. When DotsClient is invoked, it instantiates the following objects: DotsConfig, DotsLogging, Keyboard Listener, Performance Client, Summary Writer and Testcase Threads.
DotsConfig: DotsConfig stores all the global static variables which are used by most modules.
DotsLogging: DotsLogging serves as the gateway to all the logging activities. It writes to the regular log, error log and summary log. Most modules of DotsClient use DotsLogging to write their output. For more details on the log file and log format, please refer to Appendix D.
Keyboard Listener: Keyboard Listener reads keyboard input and process it. If the user enters "STOP" from the keyboard, this will set the termination flag in DotsConfig to "true". At this point, the testcase and all its threads will be terminated.
Performance Client: Performance Client interacts with the Performance Monitor on the database server. It sends requests to the server, gets the response from the server and saves the data it gets to DotsConfig.
Summary Writer: Summary Writer writes a test summary to the summary file at a specified time interval. The interval time between two writes can be set in the configuration file.
Test Case Threads: Test Case Thread is an instance of a test case. Each instance of a thread has its own connection to the database server. Several threads may be started to impose enough workload on a given database server. The number of threads started are determined by the combination of the CURRENT_CONNECTIONS, AUTO_MODE and the CPU_TARGET variable in the configuration file. For the detail, please refer to Appendix B.
Performance Monitor: Performance Monitor runs on the database server and communicates with the Dots client via sockets. It collects system information and sends it back to the client every five seconds. When Performance Monitor is invoked, it first reads command line parameters and instantiates Performance config, Performance Reader and Connection Sockets. Then it creates a socket and listens to a specified port.
Performance Config: Performance Config stores performance data which is provided by PerformanceReader.
Performance Reader: Performance Reader reads performance data from the database server and saves the data into Performance Config.
Connection Sockets: Connection Socket instance is a thread which interact with one specific client. It gets client request and sends the performance data from Performance Config to the client.
The DOTS package comes in a gzipped tar file and contains five parts: source files (java suffixes)script files for creating database and tables, sample script files for tuning system and database parameter, configuration file and make file.
After uncompressing the gzipped tar file, a new directory named Dots will be created in the current directory. The structures are as follows:
-Dots/src/: contains the source files.
- Dots/scripts/: contains scripts for database operations that are used in the test server machine.
- Dots/sample/: contains scripts used to tune Linux system parameter and tune database parameter.
- Dots/makefile: make file used to compile the source code.
- Dots/config.ini: configuration file.
The example below assumes that Dots is installed in the /usr/tmp directory on both client and server machines. Dots can be installed in any directory.
1. Uncompress and untar the Dots.tar.gz file on the client test machine.
$ tar zxvf Dots.tar.gz
Note: Keep the directory structure intact. Compile the source code using JDK 1.3.1 or higher. Make sure that the path to JDK is in the $PATH variable. If not, add it in.
2. Run make to compile the source. Enter the following commands:
$cd /usr/tmp/Dots
$make
There will be two jar files - Dots.jar and Perfmon.jar and a directory named classes which contains the class files.
3. Copy the Perfmon.jar, scripts/createdb_<dbase>, scripts/createtable_<dbase>, and scripts/createproc_<dbase> to the server test system.
Note: <dbase> is either db2(DB2), ora(Oracle) or syb(Sybase).
The following instructions are used for configuring DOTS with IBM DB2:
1. Download and install IBM DB2 UDB for Linux on the server machine.
2. Log in as DB2 user, start the database service.
$db2start
3. Run the script to create the database and tables.
$cd /usr/tmp/Dots/scripts
$./createdb_db2
$./createtable_db2
$./createproc_db2
Make sure scripts have execute permission.
4. Set JDBC 2.0 environment variables. This step is necessary only if your DB2UDB is Release 7.x.x or 8.0
$cd $DB2_HOME/sqllib/java12
$. usejdbc2
Make sure the above commands execute successfully. If not, execute these commands manually: (*NOTE: Starting DB2v8.1, the usejdbc2 command need not be executed.)
$export CLASSPATH=$DB2_HOME/sqllib/java12/db2java.zip:$CLASSPATH
$export PATH=$DB2_HOME/sqllib/java12:$PATH
$export LD_LIBRARY_PATH =$DB2_HOME/sqllib/java12:$LD_LIBRARY_PATH
$DB2_HOME represents the home directory of DB2. Do not use the variable LD_LIBRARY_PATH if you are using Linux on a pSeries machine.
5. Start db2 jdbc service (necessary only for DB2 relase versions earlier than 8.1. DB2 v8.1 uses the TCP/IP listener port, for JDBC connections.
$db2jstrt 8083
Note: DB2 versions 8.1 or earlier: It is imperative that usejdbc2 gets sourced prior to starting the jdbc service. Port 8083 can be changed if it is taken by other applications. In this case, change the URL in config.ini to the same port number.
If your DB2UDB release version is 8.1 or above the TCP/IP port number for the DB2UDB has to be determined. Use the following steps to get the TCP/IP service name and its port number.
db2 get dbm cfg |grep -i TCP/IP
The value is the name of the TCP/IP Service Name. In DB2UDBv8.1/8.2 the name is db2c_db2inst1. After obtaining the name of the TCP/IP service, use the /etc/services file and find the port number for this TCP/IP service (in the DB2UDBv8.1/8.2, the port number for db2c_db2inst1 is 500001 in some Linux distributions). Note down the value (port number) of db2c_db2inst1. This is the tcp/ip port that DOTS clients will be using to connect to the DB2UDB server.
6. Start the Performance Monitor on the server machine.
$export CLASSPATH=/usr/tmp/Dots/Perfmon.jar:$CLASSPATH
$export PATH=/usr/<JDK version>/bin:$PATH
$java dots.perfmon.PerfMon -port <port number>
Note: The recommended port number is 8001. If it is taken, choose another and reflect the changes in the configuration file. The Performance Monitor requires a dedicated session. If for any reason the session is terminated, all test cases running are terminated.
You can retrieve usage with following command:
$java dots.perfmon.PerfMon -help or $java dots.perfmon.PerfMon -?
The following instructions are used for configuring DOTS with Oracle.
1. Download and install ORACLE 8i for Linux on the server.
2. Log in as Oracle system manager and start the Database service
- Open Oracle server:
$svrmgrl
svrmgr>connect internal
svrmgr>startup
svrmgr>exit
- Open NET8'listener:
$lsnrctl
lsn>start
lsn>exit
3. Create Database
Log in as Oracle system manager and use Oracle Database Configuration Assistant Tool to create, change or delete a database.
$startx
$cd $ORACLE_HOME/bin
$dbassist
$ORACLE_HOME represents the home directory of Oracle.
Then the Oracle Database Configuration Assistant window will pop up. You can create database following the instructions. The database name is "testdb".
4. Create tables and procedures:
$cd /usr/tmp/Dots/scripts
$./createtable_ora
$./createproc_ora
Make sure scripts have execute permissions.
5. Start the Performance Monitor on the server machine.
$export CLASSPATH=/usr/tmp/Dots/Perfmon.jar:$CLASSPATH
$export PATH=/usr/<JDK version>/bin:$PATH
$java dots.perfmon.PerfMon -port <port number>
Note: The recommended port number is 8001. If it is taken, choose another number and reflect the changes in the configuration file. The Performance Monitor requires a dedicated session. If for any reason the session is terminated, all test cases running are terminated.
You can retrieve usage with the following command:
$java dots.perfmon.PerfMon -help or $java dots.perfmon.PerfMon -?
The following instructions are used for configuring DOTS with Sybase.
1. Download and install Sybase Adaptive Server Enterprise Evaluation Version 12.5 for Linux on the server.
2. Log in as Sybase system manager and start the Database service
$cd $SYBASE_HOME/ASE/install
$startserver
$SYBASE_HOME represents the home directory of Oracle.
3. Run the script to create the database and tables.
$cd /usr/tmp/Dots/scripts
$./createdb_syb # create database
$./createtable_syb # create tables
$./createproc_syb # create stored procedures
Make sure scripts have execute permission.
4. Start the Performance Monitor on the server machine.
$export CLASSPATH=/usr/tmp/Dots/Perfmon.jar:$CLASSPATH
$export PATH=/usr/<JDK version>/bin:$PATH
$java dots.perfmon.PerfMon -port <port number>
Note: The recommended port number is 8001. If it is taken, choose another number and reflect the changes in the configuration file. The Performance Monitor requires a dedicated session. If for any reason, the session is terminated, all the test cases running gets terminated.
You can get usage by using following command:
$java dots.perfmon.PerfMon -help or $java dots.perfmon.PerfMon -?
The following instructions are used for configuring DOTS with MySQL.
1. Download and install MySQL 3.23.36 on the server.
$rpm -Uvh mysql-3.23.36-1.i386.rpm
$rpm -Uvh mysql-server-3.23.36-1.i386.rpm
2. Log in as root and start the Database service
$/etc/rc.d/init.d/mysqld start
3. Change to the directory which contains the script and run the script to create the database and tables.
$mysql < createdb_mysql #create database
and grant the user "dots" the privilege to access the database remotely.
$mysql <createtable_mysql # create tables
Make sure scripts have execute permission.
4. Start the Performance Monitor on the server machine.
$export CLASSPATH=/usr/tmp/Dots/Perfmon.jar:$CLASSPATH
$export PATH=/usr/<JDK version>/bin:$PATH
$java dots.perfmon.PerfMon -port <port number>
Note: The recommended port number is 8001. If it is taken, choose another number and reflect the changes in the configuration file. The Performance Monitor requires a dedicated session. If for any reason, the session is terminated, all the test cases running gets terminated.
You can get usage by using following command:
$java dots.perfmon.PerfMon -help or $java dots.perfmon.PerfMon -?
The following instructions are used for configuring DOTS with PostgreSQL.
useradd postgres2. Download and install the following PostgreSQL packages in the given order:
a) postgresql-7.2.2-1PGDG.i382.rpm3. Change the current directory to the home directory of the user postgres (/var/lib/pgsql).
b) postgresql-jdbc-7.2.2-1PGDG.i386.rpm
c) postgresql-libs-7.2.2-1PGDG.i386.rpm
d) postgresql-odbc-7.2.2-1PGDG.i386.rpm
e) postgresql-perl-7.2.2-1PGDG.i386.rpm
f) postgresql-python-7.2.2-1PGDG.i386.rpm
g) postgresql-server-7.2.2-1PGDG.i386.rpm
PATH=$PATH:/usr/lib/pgsql7. Execute the command: chown -Rf postgres.postgres /var/lib/pgsql
export PATH
PGLIB=/usr/lib/pgsql/:/var/lib/pgsql/data
PGDATA=/var/lib/pgsql/data
LD_LIBRARY_PATH=/usr/lib/pgsql
ENV=$HOME/.bashrc
export ENV PGLIB PGDATA LD_LIBRARY_PATH
Also you might have to add the following:
export PATH=:$PATH
export CLASSPATH=/Perfmon.jar:$FCLASSPATH
TYPE DATABASE IP_ADDRESS MASK AUTH_TYPEFor example to allow users from 9.3 network, to connect to any database:
host all 9.3.0.0 255.255.0.0 trust12. Start the server by using the commands: /usr/bin/postmaster -i -h <server-name> -p 5432 -N <max number of connections > -B <2 times max number of connections> -S -D /var/lib/pgsql/data > logfile 2>&1 &
/etc/rc.d/init.d/postgresql stop
pg_ctl stop -m <smart/fast>
(i) cd to the Dots/scripts directory14. Start the Performance Monitor on the server machine:
(ii) ./createdb_pg
(iii) ./createtable_pg
(iv) ./createproc_pg
You can get usage by using following command:
java dots.perfmon.PerfMon -help or java dots.perfmon.PerfMon -?
Note: The recommended port number is 8001. If it is taken, choose another
number and reflect the changes in the configuration file. The Performance
Monitor requires a dedicated session. If for any reason, the session is
terminated, all the test cases running gets terminated.
Download the JDBC driver from the database vendor's website and update the CLASSPATH variable accordingly.
1. For DB2
If your DB2UDB version is 8.1 or later then Copy the DB2 all the files (jdbc drivers) from the $DB2_HOME/sqllib/java12/ directory of the DB2 server to the DOTS direcotry on the client side. Modify the .bash_profile file to include the names of these driver files in the CLASS_PATH variable. Obtain the name of the TCP/IP Service of the DB2 server and its port number (see in the DB2 server configuration section of this document). In the db2.ini make the following changes, if you are using DB2UDBv8.1 or more recent DB2UDB releases:
DriverClass=com.ibm.db2.jcc.DB2Driver
URL = jdbc:db2://ip-address:port-number/TESTDB
If your DB2UDB version is 8.0 or earlier, DB2 JDBC driver file is db2java.zip. The driver is in the directory $DB2_HOME/sqllib/java12. Copy the db2java.zip file from the test server machine into the test client's DOTS local directory. Update the CLASSPATH variable with DB2 JDBC driver.
$export CLASSPATH=/usr/tmp/db2java.zip:$CLASSPATH
2. For Oracle:
Oracle JDBC driver file is classes12.zip (on Linux), and can be download it from the Oracle web site. Update the CLASSPATH variable with the Oracle JDBC driver.
$export CLASSPATH=usr/tmp/classes12.zip:$CLASSPATH
3. For Sybase:
Sybase JDBC driver file is jconn2.jar. The driver is in the directory $SYBASE_HOME/sqllib/java12. Copy the jconn2.jar file from the test server machine into the test client's DOTS local directory. Update the CLASSPATH variable with Sybase JDBC driver.
$export CLASSPATH=/usr/tmp/jconn2.jar:$CLASSPATH
You should also add DOTS client jar files to CLASSPATH
$export CLASSPATH=/usr/tmp/Dots/Dots.jar:$CLASSPATH
4. For MySQL:
MySQL JDBC driver file is mm.mysql-2.0.14. This driver can be downloaded at the web site: https://sourceforge.net/project/showfiles.php?group_id=15923. Unjar the file mm.mysql-2.0.14-you-must-unjar-me.jar and copy the directory mm.mysql-2.0.14 from the test server machine into the test client's DOTS local directory. Update the CLASSPATH variable with MySQL JDBC driver.
$export CLASSPATH=/usr/tmp/mm.mysql-2.0.14/:$CLASSPATH
Because MySQL 3.23 do not support Stored Procedure, The basic case BTCJ8 cannot run against MySQL.
You should also add DOTS client jar files to CLASSPATH
$export CLASSPATH=/usr/tmp/Dots/Dots.jar:$CLASSPATH
5. For PostgreSQL: Download and install the package: postgresql-jdbc-7.2.2-1PGDG.i386.rpm. Copy the following files from /usr/share/pgsql to the Dots/pgsql directory:
jdbc7.1-1.1.jarUpdate the CLASSPATH variable with the postgresql JDBC driver.
jdbc7.1-1.2.jar
jdbc7.2dev-1.1.jar
export CLASSPATH=$CLASSPATH:/dots/Dots/pgsql/jdbc7.1-1.2.jar:/usr/share/pgsql/jdbc7.1-1.2.jarYou should also add DOTS client jar files to
jdbc7.2dev-1.2.jar
$export CLASSPATH=/usr/tmp/Dots/Dots.jar:$CLASSPATH
DOTS client is configured through a text file called the DOTS Configuration File. This file may have any name you desire, but the default is config.ini. User can edit configuration file to customize working environments for DOTS. Various parameters are provided for user to control how test cases in DOTS are running against database systems. Please refer to Appendix B for details.
Prior to running any of the DOTS test cases, it is imperative that the Performance Monitor and the Database Services have been started in the test server machine.
1. Log in as the database user.
2. Execute any of the 10 test cases.
$java dots.framework.Dots [-config <config file name>] -case <test case name>
-config: Allows you to specify the config file. The default is config.ini in the current path.
<config file name> : If file name is specified without a path, the current path is assumed; otherwise specify full name.
-case: Specifies which case to run.
<test case name>: The 8 basic test cases are named BTCJ1 -- BTCJ8; the 2 advanced cases are named ATCJ1 and ATCJ2.
You can retrieve usage with the following command:
$java dots.framework.Dots -help or $java dots.framework.Dots -?
3. When running more than one test case, open another session and follow steps 1 and 2. The number of test cases and concurrent connections to the database is influenced on the amount of memory available on both the client and server machines. On a client system with 512 Megabytes of physical memory, a user can run 4 test cases with 25 concurrent connections. There are a couple of test cases, BTCJ7 and ATCJ2 that require a lot of memory. Run only one of these on a single client system.
There are 10 test cases (8 Basic Cases and 2 Advance Cases) in DOTS and they are all written in JAVA. Every test case is independent from each other
Note: For a function description of Performance Monitor, refer to section 2.3.
The main function of this test case is to get database meta data. Database meta data is the comprehensive information about the database, which includes driver name, driver version, database product name and version, transaction isolation level, columns in the table and so on. The case also retrieves data from table BASIC1, BASIC2, BASIC3. For details about the tables, refer to Appendix A.
This test case mainly uses SQL commands to execute database operations such as insert, update, select and delete. This test case uses tables BASIC1, BASIC2, BASIC3. For details about the tables, refer to Appendix A.
This test case works like BTCJ2, but sends multiple SQL statements to the database as a unit, or batch. It uses tables BASIC1, BASIC2, BASIC3. For details about the tables, refer to Appendix A.
This test case uses PreparedStatement to execute database operations such as insert, update, select and delete. The operations are based on a single table. It uses table BASIC4. For details about the tables, refer to Appendix A.
This test case uses PreparedStatement to execute database operations such as insert, update, select and delete. It uses tables BASIC1, BASIC2, BASIC3. For details about the tables, refer to Appendix A.
This test case mainly manipulates SQL3 data type CLOB (Character Large Object). It sends CLOBs to the database and accesses SQL CLOB values. This test case uses table BASIC5. For details about the tables, refer to Appendix A.
This test case manipulates SQL3 data type BLOB (Binary Large Object). It sends BLOB to the database and accesses an SQL BLOB values. This test case uses table BASIC6. For details about the tables, refer to Appendix A.
This test case uses CallableStatement to execute database operations such as insert, update, select and delete. This test case uses tables BASIC1, BASIC2, BASIC3. For details about the tables, refer to Appendix A.
There are 2 advanced test cases, which are modeling real-world business logic.
This test case simulates the database actions of new user registration, updating existing user information and user authentication.
Table Registry. Details refer to Appendix A.
This test case simulates the database actions of an online auction scenario. Buyers can search auction items for detailed information, and bid on the items (only for registered users). Sellers (registered users) will add/update items. Everyone can view bid history of one item.
This test case uses tables REGISTRY, ITEM and BID. For details about the tables, refer to Appendix A.
Q1: Why do I get " Database connection failed" when trying to run one of the test cases?
A1:Check the following settings:
1. Check if the database and its respective services have been started successfully.
2. Check if JDBC drivers were added to the CLASSPATH variable.
3. Check if the environment variables are correct.
4. For DB2, make sure client and server machine both use the db2 jdbc 2.0 driver. Check such parameters as DriverClass, URL, UserID and Password in configuration file.
Refer to the error file for detail description.
Q2: When running DOTS in a DB2 environment, there are Java.lang.AbstractMethodErrors. How can this be corrected?
A2: Both client and server should use the db2 jdbc 2.0 driver.
For the Server side problem, one possible reason is db2 JDBC 2.0 driver file should be in the CLASSPATH. Another possible reason, the JDBC 1.0 driver file is ahead of the JDBC 2.0 in the CLASSPATH variable.
To be safe, DOTS requires the JDBC 2.0 driver file db2java.zip at the beginning of the CLASSPATH. This rule applies to both test client and test server.
Q3: There are a lot of DB2 Exceptions in the error files. Some of these exception are: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2INST1.REGISTRY" from having duplicate rows for those columns. SQLSTATE=23505. Why?
A3: This problem is not a functional error but a data feed error. The advance cases are trying to simulate user registration and online auction. The key data that gets generated for insert into the database table follows a special rule. However, in a multithread environment, duplicate keys are a normal occurrence. The primary key mechanism in the target table does not allow duplicate keys. Thus, an exception gets generated. For example, in eBay or Yahoo, if you register as "MIKE" and a user MIKE already exists, it will return an error saying MIKE user id is already in use. This error is similar to this type of exception. However, the exceptions are not so numerous unless two or more instances of the same case run against the same database at the same time.
Q4: When running DOTS for several minutes in a DB2 environment, there are instances of java.lang.OutOfMemoryError in the log file. Why and how to solve?
A4: This error can be fixed by changing JVM configuration as follows:
java -Xms30m -Xmx500m -Xss9m dots.framework.Dots -config
config.ini -case <case name>
These three parameters are used to modify JVM default setting.
-Xms30m is to set initial Java heap size to 30M
-Xmx500m is to set maximum Java heap size to 500M
-Xss9m is to set thread stack size to 3M .
You can change the number according to client machine's configuration.
Q5: When running DOTS for several hours in an ORACLE environment, the error, ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool"," ", "sga heap", "state objects") appears in the error file. How can I fix this problem?
A5:It
is caused by lack of system resource. You can tune the OS parameters and
Oracle parameters.
To tune Linux system parameters, you can log in as root user and run the
script in directory /usr/tmp/Dots/sample:
$cd /usr/tmp/Dots/sample
$./syscfg
Notes: The file "syscfg" is only a sample. System paremeters depend on the hardware configuration. You can edit "syscfg" to modify the parameters.
To tune Oracle configuration parameters, you should modify the init*.ora file(* represents database name):
Open_cursor = 800
Db_block_buffers = 45056
Shared_poor_size = 314572800
Large_pool_size = 6144000
Processes = 800
Log_buffer = 1638400
Notes: Above parameters are only a sample. The paremeters depend on the hardware configuration and Linux system parameters.
Q6: After running DOTS for several minutes, DB2 crashed. How can I prevent DB2 from crashing?
A6: DOTS was designed for stress testing and/or long run testing on any given database systems to measure database performance and reliability. The disk space and memory allocated by the database manager are not sufficient to meet DOTS needs. You should tune the DB2 parameters and Linux parameters to achieve maximum performance.
To tune Linux system parameters, log in as root user and
enter the commands:
$cd /usr/tmp/Dots/sample
$./syscfg
To tune the following DB2 parameters, log in as DB2 user
and enter the commands:
$cd /usr/tmp/Dots/sample
$./db2cfg
Notes: The tuning in "syscfg" and "db2cfg" is for reference. The parameters of Linux system depend on the hardware configuration. The parameters of DB2 depend on both hardware configuration and Linux system parameters.
Q7: When running test case BTCJ3 in an Sybase environment, the error: "com.sybase.jdbc2.jdbc.SybBatchUpdateException: JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Your server command (family id #0, process id #165) encountered a deadlock situation. Please re-run your command " appears in the error file. Why?
A7: This exception is not a functional error. The occurence of this exception depends on how the database driver executes SQL commands. This exception occurs when Sybase responds to many SQL commands from DOTS client. Oracle and DB2 can handle these situation better.
Q8: When running DOTS with Sybase, the log: "server 15 task(s) are sleeping waitingr space to become available in the log segment for database tempdb" appears in the Sybase log. How to avoid this?
A8: You can avoid this problem by enlarging the size of tempdb and dump log automatically.
To do this, you should create a separate device and move tempdb to that device
following below steps:
#move tempdb to the device testdb_dev(assume testdb_dev is the device you
have created.)
>use master
>go
>alter database tempdb on testdb_dev
>go
>use tempdb
>go
#Add a new segment and extend the segment
>sp_addsegment tempdb_seg,tempdb,testdb_dev
>go
>sp_extendsegment tempdb_seg,tempdb,testdb_dev
>go
#Dump log automatically
>create procedure sp_thresholdtempdb as dump transaction tempdb with
no_log
>go
>sp_addthreshold tempdb,tempdb_seg,150,sp_thresholdtempdb
>go
But if we do so, we have not found way to delete that device later when
we re-run those scripts.
Q9: Is it necessary to use disk init to initialize a database device if I want to create a very large database for Sybase?
A9: Yes, it is necessary to use disk init to initialize a database device if you want to create a large database.
Q10: When running test case BTCJ6 or BTCJ7 on Sybase, we have experienced the error: "[Fri Oct 12 17:08:11 CDT 2001] BTCJ6.populateTable(): com.sybase.jdbc2.jdbc.SybSQLException: The name 'DOC' is illegal in this context. Only constants, constant expressions, or variables allowed here. Column names are illegal." or the error: "[Fri Oct 12 17:08:48 CDT 2001] BTCJ7.populateTable2(): com.sybase.jdbc2.jdbc.SybSQLException: The name 'PHOTO' is illegal in this context. Only constants, constant expressions, or variables allowed here. Column names are illegal." And server's CPU utilization is low.Why?
A10: BTCJ6 and BTCJ7 are designed to manipulate CLOB and BLOB respectively. Different databases use different implementations to leverage their unique features and strength, these two test cases can run against DB2 and Oracle but not for Sybase.
Q11: How about the performance of each case?
A11: Each case's performance depend on the configuration of both client machine and server machine. Below table is for reference:
DOTS Server - IBM eServer x200-52x/PIII 866/1280MB + IBM DB2 UDB for Linux Enterprise Edition 7.2
DOTS Client - IBM eServer x200-52x/PIII 866/640MB
| DOTS | Measure Parameters | BTCJ1 | BTCJ2 | BTCJ3 | BTCJ4 | BTCJ5 | BTCJ6 | BTCJ7 | BTCJ8 | ATCJ1 | ATCJ2 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Client resource | No. of Threads reach CPU target ~ after stable | 19 | 4 | 4 | 3 | 3 | 4 | 14 | 5 | 28 | 23 |
| Total Elapse Time reach CPU Target (min) | 54 | 9 | 9 | 10 | 6 | 9 | 39 | 12 | 81 | 66 | |
| Used ~ Total Memory after system stable (MB) | 103 | 100 | 101 | 150 | 101 | 103 | 108 | 102 | 102 | 104 | |
| Used CPU after system stable (%) | 35 | 10 | 10 | 10 | 10 | 10 | 94 | 20 | 30 | 90 | |
| Server resource | Used ~ Total Memory after system stable (MB) | 533 | 625 | 638 | 568 | 640 | 657 | 635 | 605 | 761 | 750 |
| Used CPU after system stable (%) | 80 | 96 | 89 | 89 | 96 | 88 | 88 | 97 | 60 | 43 | |
| Remarks | CPU Utilization Target (%) | 85 | 85 | 85 | 85 | 85 | 85 | 85 | 85 | 85 | 85 |
| Thread Creation Interval (min) | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
Before running the case, the resources are as belows:
| Used Memory - Idle | Used CPU - Idle | |
|---|---|---|
| DOTS Client Resource | 56M | 1% |
| DOTS Server Resource | 176M | 1% |
This document is provided on an "AS IS" basis, with no warranties of any kind, including, but not limited to, the implied warranties of merchantibility or fitness for particular purpose. The information contained in this document is subject to change without notice. It shall be the user's responsibility to take all appropriate fail-safe, backup, and other measures to ensure the safe use of the application. The author disclaims any liability for any damages caused by use of the DOTS program or this documentation.
| Column Name | Column Type | Column Description |
|---|---|---|
| ID_1 | CHAR(10) NOT NULL | PRIMARY KEY |
| RND_CHAR | VARCHAR(50) | |
| RND_FLOAT | FLOAT |
| Column Name | Column Type | Column Description |
|---|---|---|
| ID_2 | CHAR(10) NOT NULL | PRIMARY KEY |
| RND_INTEGER | INTERGER | |
| RND_TIME | TIME | |
| RND_TIMESTAMP | TIMESTAMP |
| Column Name | Column Type | Column Description |
|---|---|---|
| ID_1 | CHAR(10) NOT NULL | PRIMARY KEY |
| ID_2 | CHAR(10) NOT NULL | PRIMARY KEY |
| RND_DATE | DATE | |
| RND_INT | INTEGER |
| Column Name | Column Type | Column Description |
|---|---|---|
| ID_4 | CHAR(15) NOT NULL | PRIMARY KEY |
| NAME | VARCHAR(30) | |
| AGE | INTEGER | |
| SALARY | FLOAT | |
| DEPTNO | INTEGER |
| Column Name | Column Type | Column Description |
|---|---|---|
| ID_5 | CHAR(15) NOT NULL | PRIMARY KEY |
| NAME | VARCHAR(30) | |
| AGE | INTEGER | |
| SALARY | FLOAT | |
| DOC | CLOB(1000 K) |
| Column Name | Column Type | Column Description |
|---|---|---|
| ID_6 | CHAR(15) NOT NULL | PRIMARY KEY |
| NAME | VARCHAR(30) | |
| AGE | INTEGER | |
| SALARY | FLOAT | |
| PHOTO | BLOB(1000 K) |
| Column Name | Column Type | Column Description |
|---|---|---|
| UID | CHAR(15) NOT NULL | PRIMARY KEY |
| PASSWD | CHAR(15) | |
| CHAR(40) | ||
| ADDRESS | CHAR(200) | |
| PHONE | CHAR(15) |
| Column Name | Column Type | Column Description |
|---|---|---|
| ItemID | CHAR(15) NOT NULL | FOREIGN KEY |
| BIDERID | CHAR(15) | |
| BID_PRICE | FLOAT | |
| BID_TIME | DATE |
| Column Name | Column Type | Column Description |
|---|---|---|
| ITEM_ID | CHAR(15) NOT NULL | PRIMARY KEY |
| SELLERID | CHAR(15) NOT NULL | |
| DESCRIPTION | VARCHAR | |
| START_TIME | DATE | |
| END_TIME | DATE | |
| BID_PRICE | FLOAT | |
| BID_COUNT | INTEGER |
Users can use the configuration file to customize the working environment for DOTS. Various parameters are provided for users to control how test cases are running against a database server.
Detailed explanations for configuration parameters and their values are as follows:
UserID = db2inst1 - User ID for connection
Password = db2inst1 - Password for connection
DriverClass =Com.ibm.db2.jdbc.net.DB2Driver
URL = jdbc:db2://<IP Address>:8083/dotstest1
- URL for connection
UserID = postgres - User ID for connection
Password = "" - Password for connection
DriverClass = org.postgresql.Driver
/* CASE SENSITIVE*/
URL = jdbc:postgresql://<IP Address>/TESTDB
/* CASE SENSITIVE
*/ - URL for connection
Users can stop execution of a test case in DOTS by entering predefined key combinations (then pressing the enter key) at any time from the console window in which the test case is running. All active threads within this test case will be terminated. Test cases running in other console windows will not be affected.
Sample: The default is STOP.
Output of a test case in DOTS includes log file, error report, test summary and screen status update. Users can obtain the comprehensive information from output files (log file, error report and test summary) of a test case in DOTS.
Test cases in DOTS record database access activities and database response information in Log File.
The log file is named as " CASE-timestamp(yyyy-mm-dd-hh-mm-ss-mmm).log ".
For example: The log file name may be like BTCJ4-2001-9-29-11-8-25-745.log.
Sample Parts of Log File:
[Sat Sep 29 11:08:25 EDT 2001] Database Opensource Test
Suite V1.0
[Sat Sep 29 11:08:25 EDT 2001] Start to run JDBC API Test Case - BTCJ4
[Sat Sep 29 11:08:25 EDT 2001] Initialization started
[Sat Sep 29 11:08:25 EDT 2001] Starting Performance Monitor client ...OK
[Sat Sep 29 11:08:25 EDT 2001] Client Socket: Socket[addr=<server machine's
name>/<server machine's IP>,port=8001,localport=2592]
[Sat Sep 29 11:08:41 EDT 2001] Testing Database Connections ...OK
[Sat Sep 29 11:08:41 EDT 2001] Starting Summary Writer ... OK
[Sat Sep 29 11:08:41 EDT 2001] Starting Keyboard Thread ... OK
[Sat Sep 29 11:08:49 EDT 2001] Active Threads = 1 Average CPU Usage = 22%
[Sat Sep 29 11:09:49 EDT 2001] Active Threads = 1 Average CPU Usage = 10%
[Sat Sep 29 11:10:49 EDT 2001] Active Threads = 1 Average CPU Usage = 18%
[Sat Sep 29 11:11:45 EDT 2001] 3 Minutes Average CPU Usage = 13%
[Sat Sep 29 11:11:50 EDT 2001] Active Threads = 2 Average CPU Usage = 19%
[Sat Sep 29 11:12:50 EDT 2001] Active Threads = 2 Average CPU Usage = 33%
[Sat Sep 29 11:13:50 EDT 2001] Active Threads = 2 Average CPU Usage = 41%
[Sat Sep 29 11:14:45 EDT 2001] 3 Minutes Average CPU Usage = 35%
[Sat Sep 29 11:14:50 EDT 2001] Active Threads = 3 Average CPU Usage = 41%
[Sat Sep 29 11:15:51 EDT 2001] Active Threads = 3 Average CPU Usage = 57%
[Sat Sep 29 11:16:51 EDT 2001] Active Threads = 3 Average CPU Usage = 56%
[Sat Sep 29 11:17:46 EDT 2001] 3 Minutes Average CPU Usage = 56%
[Sat Sep 29 11:17:51 EDT 2001] Active Threads = 4 Average CPU Usage = 63%
[Sat Sep 29 11:18:51 EDT 2001] Active Threads = 4 Average CPU Usage = 72%
[Sat Sep 29 11:19:51 EDT 2001] Active Threads = 4 Average CPU Usage = 79%
[Sat Sep 29 11:20:47 EDT 2001] 3 Minutes Average CPU Usage = 76%
[Sat Sep 29 11:20:47 EDT 2001] CPU Target 75% is achieved now.
[Sat Sep 29 11:20:52 EDT 2001] Active Threads = 4 Average CPU Usage = 82%
[Sat Sep 29 11:21:52 EDT 2001] Active Threads = 4 Average CPU Usage = 88%
[Sat Sep 29 11:22:52 EDT 2001] Active Threads = 4 Average CPU Usage = 90%
[Sat Sep 29 11:23:52 EDT 2001] Active Threads = 4 Average CPU Usage = 91%
[Sat Sep 29 11:24:52 EDT 2001] Active Threads = 4 Average CPU Usage = 92%
[Sat Sep 29 11:25:47 EDT 2001] 5 Minutes Average CPU Usage = 86%
. . . . . .
. . . . . .
[Sat Sep 29 19:17:30 EDT 2001] Dots is Terminating ...
[Sat Sep 29 19:17:30 EDT 2001] Writing summary to Summary File...
Test Cases in DOTS record error messages received from the Database in Error Report.
The error file is named as " CASE-timestamp(yyyy-mm-dd-hh-mm-ss-mmm).err ".
For example: The error file name may be like BTCJ3-2001-07-06-09-10-20-332.err.
Sample Parts of Error Report:
[Thu Aug 23 10:17:44 CST 2001] BTCJ3.populateTables() java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/LINUX] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2INST1.BASIC1" from having duplicate rows for those columns. SQLSTATE=23505
Test Cases in DOTS record summary and statistic information of a test case execution in Test Summary.
The summary file is named as " CASE-timestamp(yyyy-mm-dd-hh-mm-ss-mmm).sum".
For example: The summary file name may be like BTCJ4-2001-9-29-11-8-25-745.sum.
Sample Parts of Test Summary:
[Sat Sep 29 11:08:25 EDT 2001] Start to run JDBC API Test
Case - BTCJ4
[Sat Sep 29 11:08:25 EDT 2001] The Linux Kernel Version is 2.4.4-64GB-SMP
[Sat Sep 29 11:08:25 EDT 2001] The Database Server's CPU Count is 8
[Sat Sep 29 11:28:41 EDT 2001]
<Total Execution Time> 0 hours 20 minutes.
<Current Concurrent DB Connections> 4.
<JDBC APIs> Total number of QUERY 28523.
Total number of UPDATE 12655.
Total number of INSERT 34223.
Total number of DELETE 0.
Total number of FAILED 0.
<Average CPU> Peak of this Interval: 97%
Average of this Interval: 61%
Peak of all: 97%
Average of all: 61%
<CPU0> Peak of this Interval: 99%
Average of this Interval: 62%
Peak of all: 99%
Average of all: 62%
<CPU1> Peak of this Interval: 98%
Average of this Interval: 61%
Peak of all: 98%
Average of all: 61%
. . . . . .
. . . . . .
<CPU7> Peak of this Interval: 97%
Average of this Interval: 61%
Peak of all: 97%
Average of all: 61%
<Memory> Peak of this Interval: 752M
Average of this Interval: 675M
Peak of all: 752M
Average of all: 675M
<Disk IO> Peak of this Interval: 305/s
Average of this Interval: 207/s
Peak of all: 305/s
Average of all: 207/s
<Page In> Peak of this Interval: 124/s
Average of this Interval: 1/s
Peak of all: 124/s
Average of all: 1/s
<Page Out> Peak of this Interval: 20896/s
Average of this Interval: 1019/s
Peak of all: 20896/s
Average of all: 1019/s
. . . . . .
[Sat Sep 29 19:17:30 EDT 2001] Waiting for the active threads to exit ......
[Sat Sep 29 19:17:30 EDT 2001] All Database Access Threads exit.
| Last modified on: August 02, 2006 - 17:13:54 UTC. |