Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

How do I use the TRANTEST? I made some changes in the structure of a database and now I want to check if the results are satisfactory. I'm using ASA, OS Win. The TRANTEST should run via DOS? How do I use the parameters?

asked 16 Jan '13, 12:16

Walmir%20Taques's gravatar image

Walmir Taques
accept rate: 12%

edited 17 Jan '13, 07:22

Volker%20Barth's gravatar image

Volker Barth

In your ASA 9.0.2 installation there is a readme.txt file in the same directory as trantest.exe (i.e. samplesasaPerformanceTransaction) that explains how to use trantest. Trantest is an executable that you run from a command prompt - e.g. use cmd.exe or any other shell.

The basic procedure to using this tool is to (a) create the tables in your database, (b) write one or more procedures that performs the typical transaction(s) that you want to test, and then (c) use trantest to run concurrent calls to your procedure(s). The tool will then output various statistics about the performance of your test run.

The command line options to trantest are (i.e. output from running 'trantest.exe'):

| Usage: TRANTEST <switches>
| Switches:
|         -a <api>       API to use (ESQL or ODBC)
|         -c <str>       connection string
|         -d             display per-thread statistics
|         -f <file>      SQL script file
|         -g <num>       thread group id
|         -i <num>       isolation level
|         -k <num>       commit after 'num' transactions
|         -l <num>       limit for test in secs (default 300)
|         -m <num>       mean time between transactions for each thread (ms)
|         -n <list>      number of threads to run at each iteration
|                        (e.g. -n 1,5,10,20)
|         -o <file>      output results to file
|         -p <num>       number of machines (thread groups)
|         -r <num>       display refresh rate (seconds)
|         -t <str>       name of test to run
|         -u <num>       maximum desired response time (secs)
|         -w <num>       warm-up time (secs)
|         -x             prepare and drop on each execution

Example: Suppose I have procedure P1, P2, ... P3 created in my database that execute three different transactions, then I would create a file (say "mytest") which has the line:

call P{thread}()

and then I could run trantest for 60 seconds using (as an example):

trantest -c ... -f mytest -a esql -n 3 -m 1 -r 1 -k 1 -l 60

The output will look similar to (your numbers will be different!):

|   |         |         |      |Average |Maximum |Average |Percent|
|   |         |  Total  |      |Response|Response|Waiting | Under |
|   | #Trans. | #Trans. | T/sec|Time(ms)|Time(ms)|Time(ms)| 2 Secs|
          2177      2177 2214.6        0       16        0   100.0
          4469      4469 2237.9        0       16        0   100.0
          2188      6657 2210.9        0       16        0   100.0

You can play with the parameters to change the frequency of commits (-k), the refresh rate of the output (-r), number of simulated machines (-p), the isolation level used (-i), etc.


permanent link

answered 16 Jan '13, 13:41

Mark%20Culp's gravatar image

Mark Culp
accept rate: 41%

edited 16 Jan '13, 13:51

Ok.. I'm running and is displaying the following messages: (I'm certainly doing something wrong, but still can not detect).

C:\>trantest -c "uid=dba;pwd=xxx;eng=xxxxx" -f trantabs.sql -a odbc -n 3 -m 1 -r
1 -k 1 -l 60 -o result.txt
Error [thread 0]: Unable to connect
Error [thread 0]: Error executing statement

archive content TRANTABS.SQL is creating tables in the database:

if exists (select * from SYS.SYSTABLE where table_name='TT_Sync') then
    drop table TT_Sync
end if

if exists (select * from SYS.SYSTABLE where table_name='TT_RunDesc') then
    drop table TT_RunDesc
end if

if exists (select * from SYS.SYSTABLE where table_name='TT_Result') then
    drop table TT_Result
end if

// This table is used to synchronize startup when running several client
// machines.
create table TT_Sync(
    thread_group    smallint    primary key,
    status      char(10)    not null

create table TT_RunDesc(
    run_nbr     smallint    primary key default autoincrement,
    thread_count    smallint    not null,
    run_duration    smallint    not null,
    start_time      timestamp   default current timestamp,
    parms       long varchar

create table TT_Result(
    run_nbr     smallint    not null,
    thread_group    smallint    not null,
    thread_num      smallint    not null,
    trans       int     not null,
    avg_response    int     not null,
    max_response    int     not null,
    avg_waiting     int     not null,
    percent_under   int     not null,
    primary key (run_nbr, thread_group, thread_num)

alter table TT_Result
    add foreign key RunDesc (run_nbr) references TT_RunDesc (run_nbr)
(16 Jan '13, 15:12) Walmir Taques
Replies hidden

You need to pre-create the tables. Your TRANSTAB.SQL script should be executed against your database using dbisql prior to using trantest. I.e. Trantest's role is not to create the tables, it is to execute your sql queries/updates/deletes that you have in your transactions.

For example, in my sample above, mytest simply contained "call p{thread}()" (as stated above). All of my transaction logic was contained within the procedure p1, p2, p3 that I had already created in my database.

(16 Jan '13, 15:26) Mark Culp

Ok...thanks for the help.

(16 Jan '13, 15:34) Walmir Taques

Mark, I'd suggest to add your answer as part of the readme.txt of TRANTEST - I makes the usage much clearer, methinks...

(17 Jan '13, 04:15) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 16 Jan '13, 12:16

question was seen: 5,390 times

last updated: 17 Jan '13, 07:22