/* Copyright (c) 2001-2019, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


package org.hsqldb.test;

// nbazin@users - enhancements to the original code
// fredt@users - 20050202 - corrected getRandomID(int) to return a randomly distributed value
// fredt@users - 20180103 - refactored static variables to instance variables - added options
/*
 *  This is a sample implementation of the Transaction Processing Performance
 *  Council Benchmark B coded in Java and ANSI SQL2.
 *
 *  This version is using one connection per thread to parallellize
 *  server operations.
 * @author Mark Matthews (mark@mysql.com)
 */
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Vector;
import java.sql.CallableStatement;

class TestBench {

    /* tpc bm b scaling rules */
    public int              tps                 = 1;         /* the tps scaling factor: here it is 1 */
    public int              nbranches           = 1;         /* number of branches in 1 tps db       */
    public int              ntellers            = 10;        /* number of tellers in  1 tps db       */
    public int              naccounts           = 100000;    /* number of accounts in 1 tps db       */
    public int              nhistory            = 864000;    /* number of history recs in 1 tps db   */
    public int              rounds              = 10;        /* number of rounds to run the test     */
    public final static int TELLER              = 0;
    public final static int BRANCH              = 1;
    public final static int ACCOUNT             = 2;
    int                     failed_transactions = 0;
    int                     transaction_count   = 0;
    int                     n_clients           = 10;
    int                     n_txn_per_client    = 10;
    boolean                 check_updates       = false;
    boolean                 count_results       = false;
    boolean                 perform_special     = false;
    long                    start_time          = 0;
    String                  tableExtension      = "";
    String                  createExtension     = "";
    String                  shutdownCommand     = "";
    String                  txmode              = "mvcc";
    boolean                 useStoredProcedure  = false;
    boolean                 verbose             = true;
    boolean                 selectOnly          = false;
    MemoryWatcherThread     MemoryWatcher;

    /* main program,    creates a 1-tps database:  i.e. 1 branch, 10 tellers,...
     *                    runs one TPC BM B transaction
     * example command line:
     * -driver  org.hsqldb.jdbc.JDBCDriver -url jdbc:hsqldb:/hsql/jdbcbench/test -user SA -clients 20 -tpc 10000
     */
    public static void main(String[] Args) {

        String    DriverName         = "";
        String    DBUrl              = "";
        String    DBUser             = "";
        String    DBPassword         = "";
        boolean   initialize_dataset = true;
        TestBench bench              = new TestBench();

        for (int i = 0; i < Args.length; i++) {
            if (Args[i].equals("-clients")) {
                if (i + 1 < Args.length) {
                    i++;

                    bench.n_clients = Integer.parseInt(Args[i]);
                }
            } else if (Args[i].equals("-driver")) {
                if (i + 1 < Args.length) {
                    i++;

                    DriverName = Args[i];
                }
            } else if (Args[i].equals("-url")) {
                if (i + 1 < Args.length) {
                    i++;

                    DBUrl = Args[i];
                }
            } else if (Args[i].equals("-user")) {
                if (i + 1 < Args.length) {
                    i++;

                    DBUser = Args[i];
                }
            } else if (Args[i].equals("-password")) {
                if (i + 1 < Args.length) {
                    i++;

                    DBPassword = Args[i];
                }
            } else if (Args[i].equals("-tpc")) {
                if (i + 1 < Args.length) {
                    i++;

                    bench.n_txn_per_client = Integer.parseInt(Args[i]);
                }
            } else if (Args[i].equals("-init")) {
                initialize_dataset = true;
            } else if (Args[i].equals("-select")) {
                bench.selectOnly = true;
            } else if (Args[i].equals("-tps")) {
                if (i + 1 < Args.length) {
                    i++;

                    bench.tps = Integer.parseInt(Args[i]);
                }
            } else if (Args[i].equals("-rounds")) {
                if (i + 1 < Args.length) {
                    i++;

                    bench.rounds = Integer.parseInt(Args[i]);
                }
            } else if (Args[i].equals("-sp")) {
                bench.useStoredProcedure = true;
            } else if (Args[i].equals("-v")) {
                bench.verbose = true;
            }
        }

        if (DriverName.length() == 0 || DBUrl.length() == 0) {
            System.out.println(
                "usage: java TestBench -driver [driver_class_name] -url [url_to_db] -user [username] -password [password] [-v] [-init] [-tpc n] [-clients n]");
            System.out.println();
            System.out.println("-v          verbose error messages");
            System.out.println("-init       initialize the tables");
            System.out.println("-tpc        transactions per client");
            System.out.println("-clients    number of simultaneous clients");
            System.exit(-1);
        }

        System.out.println(
            "*********************************************************");
        System.out.println(
            "* TestBench v1.1                                        *");
        System.out.println(
            "*********************************************************");
        System.out.println();
        System.out.println("Driver: " + DriverName);
        System.out.println("URL:" + DBUrl);
        System.out.println();
        System.out.println("Scale factor value: " + bench.tps);
        System.out.println("Number of clients: " + bench.n_clients);
        System.out.println("Number of transactions per client: "
                           + bench.n_txn_per_client);
        System.out.println("Execution rounds: " + bench.rounds);
        System.out.println();

        if (DriverName.equals("org.hsqldb.jdbc.JDBCDriver")
                || DriverName.equals("org.hsqldb.jdbcDriver")) {
            if (!DBUrl.contains("mem:")) {
                bench.shutdownCommand = "SHUTDOWN";
            }
        }

        try {
            Class.forName(DriverName);
            bench.doBench(DBUrl, DBUser, DBPassword, initialize_dataset);
        } catch (Exception E) {
            System.out.println(E.getMessage());
            E.printStackTrace();
        }
    }

    public void doBench(String url, String user, String password,
                        boolean init) {

        Connection guardian = null;

        try {
            java.util.Date start = new java.util.Date();

            if (init) {
                System.out.println("Start: " + start.toString());
                System.out.print("Initializing dataset...");
                createDatabase(url, user, password);

                double seconds = (System.currentTimeMillis() - start.getTime())
                                 / 1000D;

                System.out.println("done. in " + seconds + " seconds\n");
                System.out.println("Complete: "
                                   + (new java.util.Date()).toString());
            }

            System.out.println("* Starting Benchmark Run *");

            MemoryWatcher = new MemoryWatcherThread();

            MemoryWatcher.start();

            guardian = connect(url, user, password);

            boolean checkResult = checkSumsDetails(guardian);

            if (!checkResult) {
                System.exit(0);
            }

            long startTime = System.currentTimeMillis();

            for (int i = 0; i < rounds; i++) {
                oneRound(url, user, password);
            }

            long tempTime = System.currentTimeMillis() - startTime;

            startTime = System.currentTimeMillis();
            guardian  = connect(url, user, password);

            checkSumsDetails(guardian);
            connectClose(guardian);
            System.out.println("Total time: " + tempTime / 1000D + " seconds");
            System.out.println("sum check time: "
                               + (System.currentTimeMillis() - startTime)
                               + " milliseconds");
        } catch (Exception E) {
            System.out.println(E.getMessage());
            E.printStackTrace();
        } finally {
            MemoryWatcher.end();

            try {
                MemoryWatcher.join();

                if (shutdownCommand.length() > 0) {
                    guardian = connect(url, user, password);

                    Statement Stmt = guardian.createStatement();

                    Stmt.execute(shutdownCommand);
                    Stmt.close();
                    connectClose(guardian);
                }
            } catch (Exception E1) {}

//            System.exit(0);
        }
    }

    void oneRound(String url, String user,
                  String password) throws InterruptedException, SQLException {

        Vector      vClient  = new Vector();
        Thread      Client   = null;
        Enumeration e        = null;
        Connection  guardian = null;

        //
        start_time = System.currentTimeMillis();

        for (int i = 0; i < n_clients; i++) {
            if (useStoredProcedure) {
                Client = new ClientThreadProcedure(
                    n_txn_per_client, url, user, password,
                    Connection.TRANSACTION_READ_COMMITTED);
            } else {
                Client =
                    new ClientThread(n_txn_per_client, url, user, password,
                                     Connection.TRANSACTION_READ_COMMITTED);
            }

            Client.start();
            vClient.addElement(Client);
        }

        /*
         ** Barrier to complete this test session
         */
        e = vClient.elements();

        while (e.hasMoreElements()) {
            Client = (Thread) e.nextElement();

            Client.join();
        }

        vClient.removeAllElements();
        reportDone();

        guardian = connect(url, user, password);

        if (count_results) {
            checkSumsDetails(guardian);
        }

        if (this.perform_special) {
            performSpecial(guardian);
        }

        connectClose(guardian);
    }

    public void reportDone() {

        long end_time = System.currentTimeMillis();
        double completion_time = ((double) end_time - (double) start_time)
                                 / 1000;

        System.out.println("\n* Benchmark Report *");
        System.out.println("\n--------------------");
        System.out.println("Time to execute " + transaction_count
                           + " transactions: " + completion_time
                           + " seconds.");
        System.out.println("Max/Min memory usage: "
                           + (MemoryWatcher.max / 1024) + " / "
                           + (MemoryWatcher.min / 1024) + " kb");
        System.out.println(failed_transactions + " / " + transaction_count
                           + " failed to complete.");

        double rate = (transaction_count - failed_transactions)
                      / completion_time;

        System.out.println("Transaction rate: " + rate + " txn/sec.");
        System.out.print((MemoryWatcher.max / 1024) + ";"
                         + (MemoryWatcher.min / 1024) + ";"
                         + failed_transactions + ";" + rate + "\n");

        transaction_count   = 0;
        failed_transactions = 0;

        MemoryWatcher.reset();
    }

    public synchronized void incrementTransactionCount() {
        transaction_count++;
    }

    public synchronized void incrementFailedTransactionCount() {
        failed_transactions++;
    }

    void createDatabase(String url, String user,
                        String password) throws Exception {

        Connection Conn = connect(url, user, password);
        String     s    = Conn.getMetaData().getDatabaseProductName();

        System.out.println("DBMS: " + s);

        try {
            Conn.setAutoCommit(false);
            System.out.println("In transaction mode");
        } catch (SQLException Etrxn) {}

        try {
            int       accountsnb = 0;
            Statement Stmt       = Conn.createStatement();
            String    Query;

//
//            Stmt.execute("SET WRITE_DELAY 1000 MILLIS;");
//            Stmt.execute("SET DATABASE DEFAULT TABLE TYPE CACHED");
//
            Query = "SELECT count(*) ";
            Query += "FROM   accounts";

            ResultSet RS = Stmt.executeQuery(Query);

            Stmt.clearWarnings();

            while (RS.next()) {
                accountsnb = RS.getInt(1);
            }

            Conn.commit();
            Stmt.close();

            if (accountsnb == (naccounts * tps)) {
                System.out.println("Already initialized");
                connectClose(Conn);

                return;
            }
        } catch (Exception E) {}

        System.out.println("Drop old tables if they exist");

        try {
            Statement Stmt = Conn.createStatement();
            String    Query;

            if (url.contains("hsqldb")) {
                try {
                    Query = "DROP PROCEDURE UPDATE_PROC";

                    Stmt.execute(Query);
                } catch (Exception e) {}
            }

            Query = "DROP TABLE history";

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "DROP TABLE accounts";

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "DROP TABLE tellers";

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "DROP TABLE branches";

            Stmt.execute(Query);
            Stmt.clearWarnings();
            Conn.commit();
            Stmt.close();
        } catch (Exception e) {}

        System.out.println("Creates tables");

        try {
            Statement Stmt = Conn.createStatement();
            String    Query;

            Query = "SET FILES SPACE 1";

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "CREATE TABLE branches ( "
                    + "Bid         INTEGER NOT NULL PRIMARY KEY, "
                    + "Bbalance    BIGINT," + "filler      VARCHAR(88))";    /* pad to 100 bytes */

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "CREATE TABLE tellers ("
                    + "Tid         INTEGER NOT NULL PRIMARY KEY,"
                    + "Bid         INTEGER," + "Tbalance    INTEGER,"
                    + "filler      VARCHAR(84))";                            /* pad to 100 bytes */

            if (createExtension.length() > 0) {
                Query += createExtension;
            }

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "CREATE TABLE accounts ("
                    + "Aid         INTEGER NOT NULL PRIMARY KEY, "
                    + "Bid         INTEGER, " + "Abalance    INTEGER, "
                    + "filler      VARCHAR(84))";                            /* pad to 100 bytes */

            if (createExtension.length() > 0) {
                Query += createExtension;
            }

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "SET TABLE accounts NEW SPACE";

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "CREATE TABLE history (" + "Tid         INTEGER, "
                    + "Bid         INTEGER, " + "Aid         INTEGER, "
                    + "delta       INTEGER, " + "tstime        TIMESTAMP, "
                    + "filler      VARCHAR(22))";                            /* pad to 50 bytes  */

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "SET TABLE history NEW SPACE";

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query =
                "CREATE PROCEDURE UPDATE_PROC(IN paid INT, IN ptid INT, IN pbid INT, IN pdelta INT, OUT pbalance INT) "
                + "MODIFIES SQL DATA DYNAMIC RESULT SETS 1 BEGIN ATOMIC "
                + "DECLARE account CURSOR WITH RETURN FOR SELECT Abalance FROM accounts WHERE  Aid = paid;"
                + "UPDATE accounts SET Abalance = abalance + pdelta WHERE  aid = paid;"
                + "OPEN account;"
                + "SELECT Abalance INTO pbalance FROM  accounts WHERE  Aid = paid;"
                + "UPDATE tellers SET Tbalance = Tbalance + pdelta WHERE  Tid = ptid;"
                + "UPDATE branches SET Bbalance = Bbalance + pdelta WHERE  Bid = pbid;"
                + "INSERT INTO history(Tid, Bid, Aid, delta) VALUES (ptid,pbid,paid,pdelta);"
                + "END";

            try {
                if (url.contains("hsqldb")) {
                    Stmt.execute(Query);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

/*
            Stmt.execute("SET TABLE ACCOUNTS SOURCE \"ACCOUNTS.TXT\"");
            Stmt.execute("SET TABLE BRANCHES SOURCE \"BBRANCHES.TXT\"");
            Stmt.execute("SET TABLE TELLERS SOURCE \"TELLERS.TXT\"");
            Stmt.execute("SET TABLE HISTORY SOURCE \"HISTORY.TXT\"");
*/
            Conn.commit();
            Stmt.close();
        } catch (Exception E) {
            System.out.println(
                "Delete elements in table in case Drop didn't work");
        }

        try {
            Statement Stmt = Conn.createStatement();
            String    Query;

            Query = "DELETE FROM history";

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "DELETE FROM accounts";

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "DELETE FROM tellers";

            Stmt.execute(Query);
            Stmt.clearWarnings();

            Query = "DELETE FROM branches";

            Stmt.execute(Query);
            Stmt.clearWarnings();
            Conn.commit();

            /* prime database using TPC BM B scaling rules.
             **  Note that for each branch and teller:
             **      branch_id = teller_id  / ntellers
             **      branch_id = account_id / naccounts
             */
            PreparedStatement pstmt = null;

            Query = "INSERT INTO branches(Bid,Bbalance) VALUES (?,0)";
            pstmt = Conn.prepareStatement(Query);

            System.out.println("Insert data in branches table");

            for (int i = 0; i < nbranches * tps; i++) {
                pstmt.setInt(1, i);
                pstmt.executeUpdate();
                pstmt.clearWarnings();

                if (i % 100 == 0) {
                    Conn.commit();
                }
            }

            pstmt.close();
            Conn.commit();

            Query = "INSERT INTO tellers(Tid,Bid,Tbalance) VALUES (?,?,0)";
            pstmt = Conn.prepareStatement(Query);

            System.out.println("Insert data in tellers table");

            for (int i = 0; i < ntellers * tps; i++) {
                pstmt.setInt(1, i);
                pstmt.setInt(2, i / ntellers);
                pstmt.executeUpdate();
                pstmt.clearWarnings();

                if (i % 100 == 0) {
                    Conn.commit();
                }
            }

            pstmt.close();
            Conn.commit();

            Query = "INSERT INTO accounts(Aid,Bid,Abalance) VALUES (?,?,0)";
            pstmt = Conn.prepareStatement(Query);

            System.out.println("Insert data in accounts table");

            for (int i = 0; i < naccounts * tps; i++) {
                pstmt.setInt(1, i);
                pstmt.setInt(2, i / naccounts);
                pstmt.executeUpdate();
                pstmt.clearWarnings();

                if (i % 10000 == 0) {
                    Conn.commit();
                }

                if ((i > 0) && ((i % 100000) == 0)) {
                    System.out.println("\t" + i + "\t records inserted");
                }
            }

            pstmt.close();
            Conn.commit();
            System.out.println("\t" + (naccounts * tps)
                               + "\t records inserted");

            // for tests
            if (shutdownCommand.length() > 0) {
                Stmt.execute(shutdownCommand);
                System.out.println("database shutdown");
            }

            Stmt.close();
        } catch (Exception E) {
            System.out.println(E.getMessage());
            E.printStackTrace();
        }

        connectClose(Conn);
    }    /* end of CreateDatabase    */

    public static int getRandomInt(int lo, int hi) {

        int ret = 0;

        ret = (int) (Math.random() * (hi - lo + 1));
        ret += lo;

        return ret;
    }

    
changed to generate correct own branch for each account id
/**
     * changed to generate correct own branch for each account id
     */
    public int getRandomID(int type) {

        int min     = 0,
            max     = naccounts * tps - 1;
        int account = getRandomInt(min, max);

        switch (type) {

            case TELLER :
                max = ntellers * tps - 1;

                return getRandomInt(min, max);
        }

        return account;
    }

    public static Connection connect(String DBUrl, String DBUser,
                                     String DBPassword) {

        try {
            Connection conn = DriverManager.getConnection(DBUrl, DBUser,
                DBPassword);

            return conn;
        } catch (Exception E) {
            System.out.println(E.getMessage());
            E.printStackTrace();
        }

        return null;
    }

    public static void connectClose(Connection c) {

        if (c == null) {
            return;
        }

        try {
            c.close();
        } catch (Exception E) {
            System.out.println(E.getMessage());
            E.printStackTrace();
        }
    }

    boolean checkSums(Connection conn) throws SQLException {

        Statement st1 = null;
        ResultSet rs  = null;
        int       bbalancesum;
        int       bbalancecount;
        int       tbalancesum;
        int       abalancesum;
        int       abalancecount;
        long      deltasum;
        long      deltacount;

        //
        boolean result = false;

        try {
            st1 = conn.createStatement();
            rs  = st1.executeQuery("select sum(bbalance) from branches");

            rs.next();

            bbalancesum = rs.getInt(1);

            rs.close();

            rs = st1.executeQuery("select sum(tbalance) from tellers");

            rs.next();

            tbalancesum = rs.getInt(1);

            rs.close();

            rs = st1.executeQuery(
                "select sum(abalance), count(abalance) from accounts");

            rs.next();

            abalancesum   = rs.getInt(1);
            abalancecount = rs.getInt(2);

            rs.close();

            rs = st1.executeQuery("select sum(delta) from history");

            rs.next();

            deltasum = rs.getInt(1);

            rs.close();

            rs = null;

            st1.close();

            st1 = null;

            conn.commit();

            if (abalancesum != bbalancesum || bbalancesum != tbalancesum
                    || tbalancesum != deltasum) {
                System.out.println("sums don't match!");
            } else {
                System.out.println("sums match!");

                result = true;
            }

            System.out.println("AC " + abalancecount + " A " + abalancesum
                               + " B " + bbalancesum + " T " + tbalancesum
                               + " H " + deltasum);
        } finally {
            if (st1 != null) {
                st1.close();
            }
        }

        return result;
    }

    
this is used for testing RowSet where sums are different.
this catches the transaction entries that are not correctly stored
/**
     * this is used for testing RowSet where sums are different.
     * this catches the transaction entries that are not correctly stored
     */
    boolean checkSumsDetails(Connection conn) throws SQLException {

        Statement st1 = null;
        ResultSet rs  = null;
        int       bbalancesum;
        int       bbalancecount;
        int       tbalancesum;
        int       abalancesum;
        int       abalancecount;
        long      deltasum;
        long      deltacount;
        boolean   result;

        try {
            st1 = conn.createStatement();
            rs = st1.executeQuery(
                "select sum(bbalance), count(bbalance) from branches");

            rs.next();

            bbalancesum   = rs.getInt(1);
            bbalancecount = rs.getInt(2);

            rs.close();

            rs = st1.executeQuery("select sum(tbalance) from tellers");

            rs.next();

            tbalancesum = rs.getInt(1);

            rs.close();

            rs = st1.executeQuery(
                "select sum(abalance), count(abalance) from accounts");

            rs.next();

            abalancesum   = rs.getInt(1);
            abalancecount = rs.getInt(2);

            rs.close();

            rs = st1.executeQuery(
                "select sum(delta), count(delta) from history");

            rs.next();

            deltasum   = rs.getLong(1);
            deltacount = rs.getLong(2);

            rs.close();

            rs = null;

            st1.close();

            st1 = null;

            conn.commit();

            if (abalancesum != bbalancesum || bbalancesum != tbalancesum
                    || tbalancesum != deltasum) {
                result = false;

                System.out.println("sums don't match!");
            } else {
                result = true;

                System.out.println("sums match!");
            }

            System.out.println("AC " + abalancecount + " A " + abalancesum
                               + " BC " + bbalancecount + " B " + bbalancesum
                               + " T " + tbalancesum + " HC " + deltacount
                               + " H " + deltasum);

            return result;
        } finally {
            if (st1 != null) {
                st1.close();
            }
        }
    }

    void performSpecial(Connection conn) throws SQLException {

        Statement st1 = null;
        ResultSet rs  = null;
        int       bbalancesum;
        int       tbalancesum;
        int       abalancesum;
        int       abalancecount;
        int       deltasum;

        try {
            st1 = conn.createStatement();

            st1.execute("set database transaction control " + txmode);
            System.out.println("tx control " + txmode);

            if (txmode.equals("mvcc")) {
                txmode = "locks";
            } else {
                txmode = "mvcc";
            }
        } finally {
            if (st1 != null) {
                st1.close();
            }
        }
    }

    class ClientThread extends Thread {

        int               ntrans = 0;
        Connection        Conn;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        PreparedStatement pstmt3 = null;
        PreparedStatement pstmt4 = null;
        PreparedStatement pstmt5 = null;

        public ClientThread(int number_of_txns, String url, String user,
                            String password, int transactionMode) {

            System.out.println(number_of_txns);

            ntrans = number_of_txns;
            Conn   = connect(url, user, password);

            if (Conn == null) {
                return;
            }

            try {
                Conn.setAutoCommit(false);
                Conn.setTransactionIsolation(transactionMode);
                prepareStatements();
            } catch (Exception E) {
                System.out.println(E.getMessage());
                E.printStackTrace();
            }
        }

        void prepareStatements() throws SQLException {

            String Query;

            Query =
                "UPDATE accounts SET Abalance = Abalance + ? WHERE  Aid = ?";
            pstmt1 = Conn.prepareStatement(Query);
            Query  = "SELECT Abalance FROM   accounts WHERE  Aid = ?";
            pstmt2 = Conn.prepareStatement(Query);
            Query =
                "UPDATE tellers SET Tbalance = Tbalance + ? WHERE  Tid = ?";
            pstmt3 = Conn.prepareStatement(Query);
            Query =
                "UPDATE branches SET Bbalance = Bbalance + ? WHERE  Bid = ?";
            pstmt4 = Conn.prepareStatement(Query);
            Query =
                "INSERT INTO history(Tid, Bid, Aid, delta) VALUES (?,?,?,?)";
            pstmt5 = Conn.prepareStatement(Query);
        }

        public void run() {

            int count = ntrans;

            while (count-- > 0) {
                int account = getRandomID(ACCOUNT);
                int branch  = account / naccounts;
                int teller  = getRandomID(TELLER);
                int delta   = TestBench.getRandomInt(-1000, 1000);

                if (selectOnly) {
                    doOneSelect(account);
                } else {
                    doOne(branch, teller, account, delta);
                }

                incrementTransactionCount();
            }

/*
            count = ntrans * 20;

            try {
                Conn.setReadOnly(true);
                while (count-- > 0) {
                    int account = TestBench.getRandomID(ACCOUNT);

                    pstmt2.setInt(1, account);
                    pstmt2.executeQuery();
                    Conn.commit();
                    incrementTransactionCount();
                }
            } catch (SQLException e) {}
*/
            try {
                if (pstmt1 != null) {
                    pstmt1.close();
                }

                if (pstmt2 != null) {
                    pstmt2.close();
                }

                if (pstmt3 != null) {
                    pstmt3.close();
                }

                if (pstmt4 != null) {
                    pstmt4.close();
                }

                if (pstmt5 != null) {
                    pstmt5.close();
                }
            } catch (Exception E) {
                System.out.println(E.getMessage());
                E.printStackTrace();
            }

            connectClose(Conn);

            Conn = null;
        }

        int doOneSelect(int aid) {

            int aBalance = 0;

            try {
                pstmt2.setInt(1, aid);

                ResultSet RS = pstmt2.executeQuery();

                if (RS.next()) {
                    aBalance = RS.getInt(1);
                }

                RS.close();

                return aBalance;
            } catch (Exception E) {
                if (verbose) {
                    System.out.println("Transaction failed: "
                                       + E.getMessage());
                    E.printStackTrace();
                }

                incrementFailedTransactionCount();
            }

            return 0;
        }

        /*
         **  doOne() - Executes a single TPC BM B transaction.
         */
        int doOne(int bid, int tid, int aid, int delta) {

            int updateCount;
            int aBalance = 0;

            if (Conn == null) {
                incrementFailedTransactionCount();

                return 0;
            }

            try {
                ResultSet RS;

                if (check_updates) {
                    pstmt2.setInt(1, aid);

                    RS = pstmt2.executeQuery();

                    if (!RS.next()) {
                        System.out.println("not found before " + aid);
                    } else {
                        aBalance = RS.getInt(1);

                        if (RS.next()) {
                            System.out.println("duplicate record - account "
                                               + aid);
                        }
                    }

                    RS.close();
                }

                pstmt1.setInt(1, delta);
                pstmt1.setInt(2, aid);
                pstmt1.executeUpdate();
                pstmt1.clearWarnings();
                pstmt2.setInt(1, aid);

                RS = pstmt2.executeQuery();

                if (RS.next()) {
                    int newBalance = RS.getInt(1);

                    if (check_updates) {

                        // needs to uncomment top block
                        if (newBalance != aBalance + delta) {
                            long temp = newBalance;

                            System.out.println("error - not updated " + aid);
                        }

                        if (RS.next()) {
                            System.out.println("duplicate record - account "
                                               + aid);
                        }
                    }
                } else {
                    System.out.println("not found after " + aid);

                    if (check_updates) {
                        RS = pstmt2.executeQuery();

                        RS.next();
                    }
                }

                RS.close();
                pstmt3.setInt(1, delta);
                pstmt3.setInt(2, tid);
                pstmt3.executeUpdate();
                pstmt3.clearWarnings();
                pstmt4.setInt(1, delta);
                pstmt4.setInt(2, bid);

                updateCount = pstmt4.executeUpdate();

                if (updateCount != 1) {
                    System.out.println("branches row not found " + aid);
                    pstmt4.executeUpdate();
                }

                pstmt4.clearWarnings();
                pstmt5.setInt(1, tid);
                pstmt5.setInt(2, bid);
                pstmt5.setInt(3, aid);
                pstmt5.setInt(4, delta);
                pstmt5.executeUpdate();
                pstmt5.clearWarnings();
                Conn.commit();

                if (updateCount != 1) {
                    checkSumsDetails(Conn);
                }

                return aBalance;
            } catch (Exception E) {
                if (verbose) {
                    System.out.println("Transaction failed: "
                                       + E.getMessage());
                    E.printStackTrace();
                }

                incrementFailedTransactionCount();

                try {
                    Conn.rollback();
                } catch (SQLException E1) {}
            }

            return 0;
        }    /* end of DoOne         */
    }    /* end of class ClientThread */

    class ClientThreadProcedure extends Thread {

        int               ntrans = 0;
        Connection        Conn;
        CallableStatement pstmt1 = null;

        public ClientThreadProcedure(int number_of_txns, String url,
                                     String user, String password,
                                     int transactionMode) {

            System.out.println(number_of_txns);

            ntrans = number_of_txns;
            Conn   = connect(url, user, password);

            if (Conn == null) {
                return;
            }

            try {
                Conn.setAutoCommit(false);
                Conn.setTransactionIsolation(transactionMode);
                prepareStatements();
            } catch (Exception E) {
                System.out.println(E.getMessage());
                E.printStackTrace();
            }
        }

        void prepareStatements() throws SQLException {

            String Query;

            Query  = "CALL UPDATE_PROC(?, ?, ?, ?, ?)";
            pstmt1 = Conn.prepareCall(Query);
        }

        public void run() {

            int count = ntrans;

            while (count-- > 0) {
                int account = getRandomID(ACCOUNT);
                int branch  = account / naccounts;
                int teller  = getRandomID(TELLER);
                int delta   = TestBench.getRandomInt(-1000, 1000);

                doOne(branch, teller, account, delta);
                incrementTransactionCount();
            }

            try {
                if (pstmt1 != null) {
                    pstmt1.close();
                }
            } catch (Exception E) {
                System.out.println(E.getMessage());
                E.printStackTrace();
            }

            connectClose(Conn);

            Conn = null;
        }

        /*
         **  doOne() - Executes a single TPC BM B transaction.
         */
        int doOne(int bid, int tid, int aid, int delta) {

            int aBalance = 0;

            if (Conn == null) {
                incrementFailedTransactionCount();

                return 0;
            }

            try {
                pstmt1.setInt(1, aid);
                pstmt1.setInt(2, tid);
                pstmt1.setInt(3, bid);
                pstmt1.setInt(4, delta);
                pstmt1.execute();
                pstmt1.getUpdateCount();

                if (pstmt1.getMoreResults()) {
                    ResultSet rs = pstmt1.getResultSet();

                    while (rs.next()) {
                        aBalance = rs.getInt(1);
                    }

                    rs.close();
                }

                pstmt1.clearWarnings();
                Conn.commit();

                return aBalance;
            } catch (Exception E) {
                if (verbose) {
                    System.out.println("Transaction failed: "
                                       + E.getMessage());
                    E.printStackTrace();
                }

                incrementFailedTransactionCount();

                try {
                    Conn.rollback();
                } catch (SQLException E1) {}
            }

            return 0;
        }    /* end of DoOne         */
    }    /* end of class ClientThread */

    class MemoryWatcherThread extends Thread {

        long    min          = 0;
        long    max          = 0;
        boolean keep_running = true;

        public MemoryWatcherThread() {

            this.reset();

            keep_running = true;
        }

        public void reset() {

            System.gc();

            long currentFree  = Runtime.getRuntime().freeMemory();
            long currentAlloc = Runtime.getRuntime().totalMemory();

            min = max = (currentAlloc - currentFree);
        }

        public void end() {
            keep_running = false;
        }

        public void run() {

            while (keep_running) {
                long currentFree  = Runtime.getRuntime().freeMemory();
                long currentAlloc = Runtime.getRuntime().totalMemory();
                long used         = currentAlloc - currentFree;

                if (used < min) {
                    min = used;
                }

                if (used > max) {
                    max = used;
                }

                try {
                    sleep(100);
                } catch (InterruptedException E) {}
            }
        }
    }    /* end of class MemoryWatcherThread */
}