---+ ODBC Bench HOWTO

%TOC%

---++ Introduction

The following is a HOWTO document for using ODBC-Bench to benchmark your
ODBC data-sources. To send feedback / comments / suggestions, please email
the maintainer at iodbc@openlinksw.com.

Prerequisites: basic Unix, Linux or MacOS X familiarity (creating
directories and users, using an editor, etc).


---++ Installing form pre-built binaries

Binary distributions can be downloaded from  [[http://oplweb.openlinksw.com/product/dldbench_cust.asp?dloadid=101646][OpenLink]] or the sourceforge.net  [[http://sourceforge.net/project/showfiles.php?group_id=90508][project file-list page]] for a variety of OS/platforms.

---++ Installing from Source


---+++ Requirements

To build ODBC-Bench, you must have:

   *  development tools: gcc (and dependencies), make etc; on MacOS X, this means having Xcode installed.
   *  an ODBC driver manager such as  [[http://www.iodbc.org/][iODBC]] or  [[http://www.unixodbc.org/][unixODBC]] installed
   *  libxml2 (greater than version 2.4.0), available from  [[http://www.xmlsoft.org/][XMLsoft]] .

MacOS X users please note: the version provided with MacOS X Jaguar is
libxml-2.2.x, which is not uptodate enough to build odbc-bench. You should
install a newer version, either from xmlsoft or with
[[http://fink.sourceforge.net/][Fink]] or
[[http://darwinports.opendarwin.org/][Darwinports]] . Users of Panther and
Tiger already have sufficient versions with their OS as standard.

Optionally, you can also build odbc-bench with support for a GUI, requiring

   *  GTK, available from  [[http://www.gtk.org/][GTK.org]] , complete with its requirements for the X window system etc.

Note: on the Mac, use the version provided by
[[http://fink.sourceforge.net/][Fink]] or
[[http://darwinports.opendarwin.org/][Darwinports]] . There is also a
project to port GTK to Carbon directly, thereby obviating the need to use X
windows, at [[http://gtk-osx.sourceforge.net/][gtk-osx]] , but experiments
show it to be in need of greater development before odbc-bench will run
with it, so this is not currently recommended.

These can normally also be obtained from your unix vendor, or as packages
or ports for GNU/Linux or FreeBSD distributions.


---+++ Downloading

You can either download the latest tarball (odbc-bench-1.0.0.tar.gz) or check out the sources from CVS hosted on SourceForge.

To unpack the tarball, run

<verbatim>
bash$ zcat odbc-bench-0.99.21.tar.gz | tar xvf -
</verbatim>

or to check out sources from CVS, run

<verbatim>
bash$ cvs -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/odbc-bench login
bash$ cvs -qz3 -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/odbc-bench co odbc-bench
</verbatim>

In the latter case, you also need to generate the configure script:

<verbatim>
bash$ sh bootstrap.sh
</verbatim>

---+++ Building


---++++ Linux / Unix

Building odbc-bench is simple. From the directory created in the above, run configure:


<verbatim>
bash$ ./configure --prefix=/usr/local/odbc-bench \
--with-iodbc=/usr/local/iODBC
</verbatim>

In this case, we favour using /usr/local/odbc-bench/ to keep all the files
together: you can omit the `--prefix' option and it will use /usr/local/ by
default. If you only want the commandline version to be installed, now
would be the time to specify `--disable-gui' in the above command as well.

Now run make, become root by your favoured method, and run make install:

<verbatim>
bash$ make
bash$ su root -c 'make install'
</verbatim>

---++++ Mac OS X

The above configure and make method does not work with GTK on MacOS X, so we recommend you build a native Carbon app version instead:


<verbatim>
bash$ tar xvpfz odbc-bench-1.0.0.tar.gz
...
bash$ cd odbc-bench-1.0.0
bash$ cd mac
bash$ make 
bash$ make
xcodebuild -buildstyle Deployment
=== BUILDING NATIVE TARGET ODBC Bench  USING BUILD STYLE Deployment ===

PBXCp "build/ODBC Bench.app/Contents/Info.plist" "build/odbc-bench.build/ODBC Bench.build/Info.plist"
mkdir -p "/Volumes/Firewire/Users/tim/C/odbc-bench-1.0.0/mac/build/ODBC Bench.app/Contents"
cd /Volumes/Firewire/Users/tim/C/odbc-bench-1.0.0/mac
/System/Library/PrivateFrameworks/DevToolsCore.framework/Resources/pbxcp -exclude .DS_Store -exclude CVS 
...
   * * BUILD SUCCEEDED **
bash$ sudo make install
Password:
sudo xcodebuild install -buildstyle Deployment install DSTROOT=/
=== BUILDING NATIVE TARGET ODBC Bench  USING BUILD STYLE Deployment ===

PBXCp "/Applications/ODBC Bench.app/Contents/Info.plist" "build/odbc-bench.build/ODBC Bench.build/Info.plist"                                           
...
SetMode a-w,a+rX "/Applications/ODBC Bench.app"
cd /Volumes/Firewire/Users/tim/C/odbc-bench-1.0.0/mac
/bin/chmod -RH a-w,a+rX "/Applications/ODBC Bench.app"
   * * BUILD SUCCEEDED **

=== BUILDING NATIVE TARGET ODBC Bench  USING BUILD STYLE Deployment ===
   * * BUILD SUCCEEDED **    
</verbatim>

---++ Running odbc-bench

This section guides you through setting ODBC-Bench to connect to a
datasource, configure a test and run it. You have the option of using the
GTK or Carbon GUIs or running it all from the commandline.


---+++Using the GUIs

Linux / Unix uers can run `odbc-bench-gtk' from the commandline to bring up
the GUI; MacOS X users should open /Applications/ODBC Bench.app to start
the application:

<img src="%ATTACHURLPATH%/start.png" alt="GUI main window">

The idea of ODBC Bench is that you build pools of tests - you can configure
combinations of TPC-A or TPC-C tests with various parameters and save them
out for later. By default, you have an untitled session into which you can
add tests to be run.

From here, under the Edit menu, select `new benchmark item'. You can choose
a name for the test-run, and whether it's to use the TPC-A or TPC-C model:

<img src="%ATTACHURLPATH%/test_type_tpca.png" alt="">

Having chosen a name and model, under the Edit menu choose `Login Details',
and specify a DSN, username and password to be used for the connection:

<img src="%ATTACHURLPATH%/test_login_details.png" alt="">

Now select Edit / Table Details to set up the schema:

<img src="%ATTACHURLPATH%/choosing_tpca_tables.png" alt="">

Having chosen which tables to use and how many rows with which to populate
them, proceed to connecting and creating them in the database with Action /
Create tables & Procedures:

<img src="%ATTACHURLPATH%/populating_tables.png" alt="">

Next, configure the nature of the test to be run. You can specify all
transactions to take place on one or several threads, whether to use array
parameters, whether to use direct execution of SQL queries, prepared
statements or stored procedures, whether to execute asynchronously, what
transaction-isolation level to use and what cursor model:

<img src="%ATTACHURLPATH%/complex_run_details.png" alt="">

From the Action menu, choose `Run Selected Tests', specify a duration and
output file, and press Start. If you defined a pool of multiple tests, you
can `Run All' instead:

<img src="%ATTACHURLPATH%/running_0.png" alt="">

As it progresses, you should see a completion-indicator for each thread:

<img src="%ATTACHURLPATH%/running_2.png" alt="">

Finally, the results are displayed:

<img src="%ATTACHURLPATH%/running_after.png" alt=""> 

You can build a list of tests - DSNs and test configurations - against
which to run odbc-bench, and then select them for running in bulk.


---+++ Using the CLI

The command-line interface to odbc-bench is invoked with the odbc-bench-cmd command. As an overview of the options it takes, this is the `--help' output:


<verbatim>
bash$ odbc-bench-cmd --help

OpenLink ODBC Benchmark Utility 0.99.22
Copyright (C) 2000-2003 OpenLink Software
Please report all bugs to &lt;odbc-bench@openlinksw.com>

This utility is released under the GNU General Public License (GPL)

Disclaimer: The benchmarks in this application are loosely based
on the TPC-A and TPC-C standard benchmarks, but this application
does not claim to be a full or precise implementation, nor are
the results obtained by this application necessarily comparable
to the vendor's published results.

Usage :

-d -dsn   - login dsn
-u -uid   - user id
-p -pwd   - password
-r -runs  - number of runs (default 1)
-m -time  - duration of the run (mins) (default 1)
-t -threads    - number of threads (default 1)
-P -arrayparm  - size for used array of parameters (default 1) 
-s -test       - exec | proc | prepare | runall (default 'exec' test)
-x -txn        - use transactions (default 'don't use transactions')
-1 -query      - do 100 row query
-a -async      - asynchronous execution
-c -cursor_type - cursor type = forward | static | keyset | dynamic | 
mixed (default 'forward')
-i -txn_isolation  - transaction isolation level = uncommitted | committed 
| repeatable | serializable
(default 'Driver Default')
-S -rowset_size    - rowset size (default 1)
-K -keyset_size    - keyset size (default 1)
-T -trav_count     - traversal count (default 1)
-C -create_tables  - create the tables and procedures, then exit
-D -drop_tables    - drop the tables and procedures, then exit
-rcreate_table     - create the results table, then exit
-rdrop_table       - drop the results table, then exit
-rdsn   - login dsn for result table
-ruid   - user id for result table
-rpwd   - password for result table
-rfile  - output filename for results data
-v      - print info on stdout (error messages, test results,
messages about the creation and closing connections)
-V      - print the same info on stdout as for -v and the test progress info
-R      - doesn't do rollbacks on deadlock
</verbatim>

A reasonable way to run it is as follows - corresponding to the above settings:


<verbatim>
bash$ odbc-bench-cmd -d "pglocal" -u tim -p "" -D
bash$ odbc-bench-cmd -d "pglocal" -u tim -p "" -C
bash$ odbc-bench-cmd -d "pglocal" -u tim -p "" -r 1 -t 4 -s prepare \
-c keyset -S 5 -K 5 -v |& tee run.log
</verbatim>

The first of these lines drops any pre-existing tables in the database. The
second re-creates them, and the third performs the run with semi-verbose
output tee'd into a logfile.

The output looks like:


<verbatim>
Connecting to CommandLine : DSN=&lt;pglocal> UID=&lt;tim>
Driver : 07.02.0005 (libpsqlodbc.so)
Connection to CommandLine opened

Connection to CommandLine closed

Connecting to CommandLine : DSN=&lt;pglocal> UID=&lt;tim>
Driver : 07.02.0005 (libpsqlodbc.so)
Connection to CommandLine opened

CommandLine - PostgreSQL(libpsqlodbc.so) - all 4 TPC-A Threads completed
successfully.
Calculating statistics:
SQL options used:                       4 Threads/Params
Transaction time:                       61.000000
Environmental overhead:                 -1.000000
Total transactions:                     7835
Transactions per second:                128.442627
% less than 1 second:                   100.000000
% 1 &lt; n &lt; 2 seconds:                    0.000000
Average processing time:                0.007786
Connection to CommandLine closed
</verbatim>

---++ References

   *  Overview of the [[http://www.tpc.org/tpca/default.asp][TPC-A]] benchmark specification
   *  Overview of the [[http://www.tpc.org/tpcc/default.asp][TPC-C]] benchmark specification
   *  The [[http://www.iodbc.org/][iODBC]] driver manager homepage
   *  The [[http://www.unixodbc.org/][unixODBC]] driver manager homepage
   *  [[http://www.openlinksw.com/][OpenLink Software]] (ODBC drivers, et al)
   *  [[http://www.microsoft.com/data/odbc/][Microsoft ODBC]] site