0001: package org.hsqldb.test;
0002:
0003: // nbazin@users - enhancements to the original code
0004: // fredt@users - 20050202 - corrected getRandomID(int) to return a randomly distributed value
0005: /*
0006: * This is a sample implementation of the Transaction Processing Performance
0007: * Council Benchmark B coded in Java and ANSI SQL2.
0008: *
0009: * This version is using one connection per thread to parallellize
0010: * server operations.
0011: * @author Mark Matthews (mark@mysql.com)
0012: */
0013: import java.io.FileOutputStream;
0014: import java.io.PrintStream;
0015: import java.sql.Connection;
0016: import java.sql.DriverManager;
0017: import java.sql.PreparedStatement;
0018: import java.sql.ResultSet;
0019: import java.sql.SQLException;
0020: import java.sql.Statement;
0021: import java.util.Enumeration;
0022: import java.util.Vector;
0023:
0024: class JDBCBench {
0025:
0026: /* tpc bm b scaling rules */
0027: public static int tps = 1; /* the tps scaling factor: here it is 1 */
0028: public static int nbranches = 1; /* number of branches in 1 tps db */
0029: public static int ntellers = 10; /* number of tellers in 1 tps db */
0030: public static int naccounts = 100000; /* number of accounts in 1 tps db */
0031: public static int nhistory = 864000; /* number of history recs in 1 tps db */
0032: public static final int TELLER = 0;
0033: public static final int BRANCH = 1;
0034: public static final int ACCOUNT = 2;
0035: int failed_transactions = 0;
0036: int transaction_count = 0;
0037: static int n_clients = 10;
0038: static int n_txn_per_client = 10;
0039: long start_time = 0;
0040: static boolean transactions = true;
0041: static boolean prepared_stmt = false;
0042: static String tableExtension = "";
0043: static String createExtension = "";
0044: static String ShutdownCommand = "";
0045: static String startupCommand = "";
0046: static PrintStream TabFile = null;
0047: static boolean verbose = false;
0048: MemoryWatcherThread MemoryWatcher;
0049:
0050: /* main program, creates a 1-tps database: i.e. 1 branch, 10 tellers,...
0051: * runs one TPC BM B transaction
0052: * example command line:
0053: * -driver org.hsqldb.jdbcDriver -url jdbc:hsqldb:/hsql/jdbcbench/test -user sa -clients 20
0054: */
0055: public static void main(String[] Args) {
0056:
0057: String DriverName = "";
0058: String DBUrl = "";
0059: String DBUser = "";
0060: String DBPassword = "";
0061: boolean initialize_dataset = false;
0062:
0063: for (int i = 0; i < Args.length; i++) {
0064: if (Args[i].equals("-clients")) {
0065: if (i + 1 < Args.length) {
0066: i++;
0067:
0068: n_clients = Integer.parseInt(Args[i]);
0069: }
0070: } else if (Args[i].equals("-driver")) {
0071: if (i + 1 < Args.length) {
0072: i++;
0073:
0074: DriverName = Args[i];
0075:
0076: if (DriverName
0077: .equals("org.enhydra.instantdb.jdbc.idbDriver")) {
0078: ShutdownCommand = "SHUTDOWN";
0079: }
0080:
0081: if (DriverName
0082: .equals("com.borland.datastore.jdbc.DataStoreDriver")) {
0083: }
0084:
0085: if (DriverName.equals("com.mckoi.JDBCDriver")) {
0086: ShutdownCommand = "SHUTDOWN";
0087: }
0088:
0089: if (DriverName.equals("org.hsqldb.jdbcDriver")) {
0090: tableExtension = "CREATE CACHED TABLE ";
0091: ShutdownCommand = "SHUTDOWN";
0092: startupCommand = "";
0093: }
0094: }
0095: } else if (Args[i].equals("-url")) {
0096: if (i + 1 < Args.length) {
0097: i++;
0098:
0099: DBUrl = Args[i];
0100: }
0101: } else if (Args[i].equals("-user")) {
0102: if (i + 1 < Args.length) {
0103: i++;
0104:
0105: DBUser = Args[i];
0106: }
0107: } else if (Args[i].equals("-tabfile")) {
0108: if (i + 1 < Args.length) {
0109: i++;
0110:
0111: try {
0112: FileOutputStream File = new FileOutputStream(
0113: Args[i]);
0114:
0115: TabFile = new PrintStream(File);
0116: } catch (Exception e) {
0117: TabFile = null;
0118: }
0119: }
0120: } else if (Args[i].equals("-password")) {
0121: if (i + 1 < Args.length) {
0122: i++;
0123:
0124: DBPassword = Args[i];
0125: }
0126: } else if (Args[i].equals("-tpc")) {
0127: if (i + 1 < Args.length) {
0128: i++;
0129:
0130: n_txn_per_client = Integer.parseInt(Args[i]);
0131: }
0132: } else if (Args[i].equals("-init")) {
0133: initialize_dataset = true;
0134: } else if (Args[i].equals("-tps")) {
0135: if (i + 1 < Args.length) {
0136: i++;
0137:
0138: tps = Integer.parseInt(Args[i]);
0139: }
0140: } else if (Args[i].equals("-v")) {
0141: verbose = true;
0142: }
0143: }
0144:
0145: if (DriverName.length() == 0 || DBUrl.length() == 0) {
0146: System.out
0147: .println("usage: java JDBCBench -driver [driver_class_name] -url [url_to_db] -user [username] -password [password] [-v] [-init] [-tpc n] [-clients n]");
0148: System.out.println();
0149: System.out.println("-v verbose error messages");
0150: System.out.println("-init initialize the tables");
0151: System.out.println("-tpc transactions per client");
0152: System.out
0153: .println("-clients number of simultaneous clients");
0154: System.exit(-1);
0155: }
0156:
0157: System.out
0158: .println("*********************************************************");
0159: System.out
0160: .println("* JDBCBench v1.1 *");
0161: System.out
0162: .println("*********************************************************");
0163: System.out.println();
0164: System.out.println("Driver: " + DriverName);
0165: System.out.println("URL:" + DBUrl);
0166: System.out.println();
0167: System.out.println("Scale factor value: " + tps);
0168: System.out.println("Number of clients: " + n_clients);
0169: System.out.println("Number of transactions per client: "
0170: + n_txn_per_client);
0171: System.out.println();
0172:
0173: try {
0174: Class.forName(DriverName);
0175:
0176: JDBCBench Me = new JDBCBench(DBUrl, DBUser, DBPassword,
0177: initialize_dataset);
0178: } catch (Exception E) {
0179: System.out.println(E.getMessage());
0180: E.printStackTrace();
0181: }
0182: }
0183:
0184: public JDBCBench(String url, String user, String password,
0185: boolean init) {
0186:
0187: Vector vClient = new Vector();
0188: Thread Client = null;
0189: Enumeration e = null;
0190: Connection guardian = null;
0191:
0192: try {
0193: if (init) {
0194: System.out.println("Start: "
0195: + (new java.util.Date()).toString());
0196: System.out.print("Initializing dataset...");
0197: createDatabase(url, user, password);
0198: System.out.println("done.\n");
0199: System.out.println("Complete: "
0200: + (new java.util.Date()).toString());
0201: }
0202:
0203: guardian = connect(url, user, password);
0204:
0205: if (startupCommand.length() != 0) {
0206: Statement statement = guardian.createStatement();
0207:
0208: statement.execute(startupCommand);
0209: statement.close();
0210: }
0211:
0212: System.out.println("* Starting Benchmark Run *");
0213:
0214: MemoryWatcher = new MemoryWatcherThread();
0215:
0216: MemoryWatcher.start();
0217:
0218: transactions = true;
0219: prepared_stmt = false;
0220: start_time = System.currentTimeMillis();
0221:
0222: for (int i = 0; i < n_clients; i++) {
0223: Client = new ClientThread(n_txn_per_client, url, user,
0224: password);
0225:
0226: Client.start();
0227: vClient.addElement(Client);
0228: }
0229:
0230: /*
0231: ** Barrier to complete this test session
0232: */
0233: e = vClient.elements();
0234:
0235: while (e.hasMoreElements()) {
0236: Client = (Thread) e.nextElement();
0237:
0238: Client.join();
0239: }
0240:
0241: vClient.removeAllElements();
0242: reportDone();
0243: checkSums(guardian);
0244:
0245: // debug - allows stopping the test
0246: if (!transactions) {
0247: throw new Exception("end after one round");
0248: }
0249:
0250: transactions = true;
0251: prepared_stmt = false;
0252: start_time = System.currentTimeMillis();
0253:
0254: for (int i = 0; i < n_clients; i++) {
0255: Client = new ClientThread(n_txn_per_client, url, user,
0256: password);
0257:
0258: Client.start();
0259: vClient.addElement(Client);
0260: }
0261:
0262: /*
0263: ** Barrier to complete this test session
0264: */
0265: e = vClient.elements();
0266:
0267: while (e.hasMoreElements()) {
0268: Client = (Thread) e.nextElement();
0269:
0270: Client.join();
0271: }
0272:
0273: vClient.removeAllElements();
0274: reportDone();
0275: checkSums(guardian);
0276:
0277: transactions = true;
0278: prepared_stmt = true;
0279: start_time = System.currentTimeMillis();
0280:
0281: for (int i = 0; i < n_clients; i++) {
0282: Client = new ClientThread(n_txn_per_client, url, user,
0283: password);
0284:
0285: Client.start();
0286: vClient.addElement(Client);
0287: }
0288:
0289: /*
0290: ** Barrier to complete this test session
0291: */
0292: e = vClient.elements();
0293:
0294: while (e.hasMoreElements()) {
0295: Client = (Thread) e.nextElement();
0296:
0297: Client.join();
0298: }
0299:
0300: vClient.removeAllElements();
0301: reportDone();
0302: checkSums(guardian);
0303:
0304: transactions = true;
0305: prepared_stmt = true;
0306: start_time = System.currentTimeMillis();
0307:
0308: for (int i = 0; i < n_clients; i++) {
0309: Client = new ClientThread(n_txn_per_client, url, user,
0310: password);
0311:
0312: Client.start();
0313: vClient.addElement(Client);
0314: }
0315:
0316: /*
0317: ** Barrier to complete this test session
0318: */
0319: e = vClient.elements();
0320:
0321: while (e.hasMoreElements()) {
0322: Client = (Thread) e.nextElement();
0323:
0324: Client.join();
0325: }
0326:
0327: vClient.removeAllElements();
0328: reportDone();
0329: checkSums(guardian);
0330: } catch (Exception E) {
0331: System.out.println(E.getMessage());
0332: E.printStackTrace();
0333: } finally {
0334: MemoryWatcher.end();
0335:
0336: try {
0337: MemoryWatcher.join();
0338:
0339: if (ShutdownCommand.length() > 0) {
0340: Statement Stmt = guardian.createStatement();
0341:
0342: Stmt.execute(ShutdownCommand);
0343: Stmt.close();
0344: connectClose(guardian);
0345: }
0346:
0347: if (TabFile != null) {
0348: TabFile.close();
0349: }
0350: } catch (Exception E1) {
0351: }
0352:
0353: // System.exit(0);
0354: }
0355: }
0356:
0357: public void reportDone() {
0358:
0359: long end_time = System.currentTimeMillis();
0360: double completion_time = ((double) end_time - (double) start_time) / 1000;
0361:
0362: if (TabFile != null) {
0363: TabFile.print(tps + ";" + n_clients + ";"
0364: + n_txn_per_client + ";");
0365: }
0366:
0367: System.out.println("\n* Benchmark Report *");
0368: System.out.print("* Featuring ");
0369:
0370: if (prepared_stmt) {
0371: System.out.print("<prepared statements> ");
0372:
0373: if (TabFile != null) {
0374: TabFile.print("<prepared statements>;");
0375: }
0376: } else {
0377: System.out.print("<direct queries> ");
0378:
0379: if (TabFile != null) {
0380: TabFile.print("<direct queries>;");
0381: }
0382: }
0383:
0384: if (transactions) {
0385: System.out.print("<transactions> ");
0386:
0387: if (TabFile != null) {
0388: TabFile.print("<transactions>;");
0389: }
0390: } else {
0391: System.out.print("<auto-commit> ");
0392:
0393: if (TabFile != null) {
0394: TabFile.print("<auto-commit>;");
0395: }
0396: }
0397:
0398: System.out.println("\n--------------------");
0399: System.out.println("Time to execute " + transaction_count
0400: + " transactions: " + completion_time + " seconds.");
0401: System.out.println("Max/Min memory usage: " + MemoryWatcher.max
0402: + " / " + MemoryWatcher.min + " kb");
0403: System.out.println(failed_transactions + " / "
0404: + transaction_count + " failed to complete.");
0405:
0406: double rate = (transaction_count - failed_transactions)
0407: / completion_time;
0408:
0409: System.out.println("Transaction rate: " + rate + " txn/sec.");
0410:
0411: if (TabFile != null) {
0412: TabFile.print(MemoryWatcher.max + ";" + MemoryWatcher.min
0413: + ";" + failed_transactions + ";" + rate + "\n");
0414: }
0415:
0416: transaction_count = 0;
0417: failed_transactions = 0;
0418:
0419: MemoryWatcher.reset();
0420: }
0421:
0422: public synchronized void incrementTransactionCount() {
0423: transaction_count++;
0424: }
0425:
0426: public synchronized void incrementFailedTransactionCount() {
0427: failed_transactions++;
0428: }
0429:
0430: void createDatabase(String url, String user, String password)
0431: throws Exception {
0432:
0433: Connection Conn = connect(url, user, password);
0434: ;
0435: String s = Conn.getMetaData().getDatabaseProductName();
0436:
0437: System.out.println("DBMS: " + s);
0438:
0439: transactions = true;
0440:
0441: if (transactions) {
0442: try {
0443: Conn.setAutoCommit(false);
0444: System.out.println("In transaction mode");
0445: } catch (SQLException Etrxn) {
0446: transactions = false;
0447: }
0448: }
0449:
0450: try {
0451: int accountsnb = 0;
0452: Statement Stmt = Conn.createStatement();
0453: String Query;
0454:
0455: Query = "SELECT count(*) ";
0456: Query += "FROM accounts";
0457:
0458: ResultSet RS = Stmt.executeQuery(Query);
0459:
0460: Stmt.clearWarnings();
0461:
0462: while (RS.next()) {
0463: accountsnb = RS.getInt(1);
0464: }
0465:
0466: if (transactions) {
0467: Conn.commit();
0468: }
0469:
0470: Stmt.close();
0471:
0472: if (accountsnb == (naccounts * tps)) {
0473: System.out.println("Already initialized");
0474: connectClose(Conn);
0475:
0476: return;
0477: }
0478: } catch (Exception E) {
0479: }
0480:
0481: System.out.println("Drop old tables if they exist");
0482:
0483: try {
0484: Statement Stmt = Conn.createStatement();
0485: String Query;
0486:
0487: Query = "DROP TABLE history";
0488:
0489: Stmt.execute(Query);
0490: Stmt.clearWarnings();
0491:
0492: Query = "DROP TABLE accounts";
0493:
0494: Stmt.execute(Query);
0495: Stmt.clearWarnings();
0496:
0497: Query = "DROP TABLE tellers";
0498:
0499: Stmt.execute(Query);
0500: Stmt.clearWarnings();
0501:
0502: Query = "DROP TABLE branches";
0503:
0504: Stmt.execute(Query);
0505: Stmt.clearWarnings();
0506:
0507: if (transactions) {
0508: Conn.commit();
0509: }
0510:
0511: Stmt.close();
0512: } catch (Exception E) {
0513: }
0514:
0515: System.out.println("Creates tables");
0516:
0517: try {
0518: Statement Stmt = Conn.createStatement();
0519: String Query;
0520:
0521: if (tableExtension.length() > 0) {
0522: Query = tableExtension + " branches (";
0523: } else {
0524: Query = "CREATE TABLE branches (";
0525: }
0526:
0527: Query += "Bid INTEGER NOT NULL PRIMARY KEY, ";
0528: Query += "Bbalance INTEGER,";
0529: Query += "filler CHAR(88))"; /* pad to 100 bytes */
0530:
0531: if (createExtension.length() > 0) {
0532: Query += createExtension;
0533: }
0534:
0535: Stmt.execute(Query);
0536: Stmt.clearWarnings();
0537:
0538: if (tableExtension.length() > 0) {
0539: Query = tableExtension + " tellers (";
0540: } else {
0541: Query = "CREATE TABLE tellers (";
0542: }
0543:
0544: Query += "Tid INTEGER NOT NULL PRIMARY KEY,";
0545: Query += "Bid INTEGER,";
0546: Query += "Tbalance INTEGER,";
0547: Query += "filler CHAR(84))"; /* pad to 100 bytes */
0548:
0549: if (createExtension.length() > 0) {
0550: Query += createExtension;
0551: }
0552:
0553: Stmt.execute(Query);
0554: Stmt.clearWarnings();
0555:
0556: if (tableExtension.length() > 0) {
0557: Query = tableExtension + " accounts (";
0558: } else {
0559: Query = "CREATE TABLE accounts (";
0560: }
0561:
0562: Query += "Aid INTEGER NOT NULL PRIMARY KEY, ";
0563: Query += "Bid INTEGER, ";
0564: Query += "Abalance INTEGER, ";
0565: Query += "filler CHAR(84))"; /* pad to 100 bytes */
0566:
0567: if (createExtension.length() > 0) {
0568: Query += createExtension;
0569: }
0570:
0571: Stmt.execute(Query);
0572: Stmt.clearWarnings();
0573:
0574: if (tableExtension.length() > 0) {
0575: Query = tableExtension + " history (";
0576: } else {
0577: Query = "CREATE TABLE history (";
0578: }
0579:
0580: Query += "Tid INTEGER, ";
0581: Query += "Bid INTEGER, ";
0582: Query += "Aid INTEGER, ";
0583: Query += "delta INTEGER, ";
0584: Query += "tstime TIMESTAMP, ";
0585: Query += "filler CHAR(22))"; /* pad to 50 bytes */
0586:
0587: if (createExtension.length() > 0) {
0588: Query += createExtension;
0589: }
0590:
0591: Stmt.execute(Query);
0592: Stmt.clearWarnings();
0593: Stmt.execute("SET TABLE ACCOUNTS SOURCE \"ACCOUNTS.TXT\"");
0594: Stmt.execute("SET TABLE BRANCHES SOURCE \"BBRANCHES.TXT\"");
0595: Stmt.execute("SET TABLE TELLERS SOURCE \"TELLERS.TXT\"");
0596: Stmt.execute("SET TABLE HISTORY SOURCE \"HISTORY.TXT\"");
0597:
0598: if (transactions) {
0599: Conn.commit();
0600: }
0601:
0602: Stmt.close();
0603: } catch (Exception E) {
0604: System.out
0605: .println("Delete elements in table in case Drop didn't work");
0606: }
0607:
0608: System.out
0609: .println("Delete elements in table in case Drop didn't work");
0610:
0611: try {
0612: Statement Stmt = Conn.createStatement();
0613: String Query;
0614:
0615: Query = "DELETE FROM history";
0616:
0617: Stmt.execute(Query);
0618: Stmt.clearWarnings();
0619:
0620: Query = "DELETE FROM accounts";
0621:
0622: Stmt.execute(Query);
0623: Stmt.clearWarnings();
0624:
0625: Query = "DELETE FROM tellers";
0626:
0627: Stmt.execute(Query);
0628: Stmt.clearWarnings();
0629:
0630: Query = "DELETE FROM branches";
0631:
0632: Stmt.execute(Query);
0633: Stmt.clearWarnings();
0634:
0635: if (transactions) {
0636: Conn.commit();
0637: }
0638:
0639: /* prime database using TPC BM B scaling rules.
0640: ** Note that for each branch and teller:
0641: ** branch_id = teller_id / ntellers
0642: ** branch_id = account_id / naccounts
0643: */
0644: PreparedStatement pstmt = null;
0645:
0646: prepared_stmt = true;
0647:
0648: if (prepared_stmt) {
0649: try {
0650: Query = "INSERT INTO branches(Bid,Bbalance) VALUES (?,0)";
0651: pstmt = Conn.prepareStatement(Query);
0652:
0653: System.out.println("Using prepared statements");
0654: } catch (SQLException Epstmt) {
0655: pstmt = null;
0656: prepared_stmt = false;
0657: }
0658: }
0659:
0660: System.out.println("Insert data in branches table");
0661:
0662: for (int i = 0; i < nbranches * tps; i++) {
0663: if (prepared_stmt) {
0664: pstmt.setInt(1, i);
0665: pstmt.executeUpdate();
0666: pstmt.clearWarnings();
0667: } else {
0668: Query = "INSERT INTO branches(Bid,Bbalance) VALUES ("
0669: + i + ",0)";
0670:
0671: Stmt.executeUpdate(Query);
0672: }
0673:
0674: if ((i % 100 == 0) && (transactions)) {
0675: Conn.commit();
0676: }
0677: }
0678:
0679: if (prepared_stmt) {
0680: pstmt.close();
0681: }
0682:
0683: if (transactions) {
0684: Conn.commit();
0685: }
0686:
0687: if (prepared_stmt) {
0688: Query = "INSERT INTO tellers(Tid,Bid,Tbalance) VALUES (?,?,0)";
0689: pstmt = Conn.prepareStatement(Query);
0690: }
0691:
0692: System.out.println("Insert data in tellers table");
0693:
0694: for (int i = 0; i < ntellers * tps; i++) {
0695: if (prepared_stmt) {
0696: pstmt.setInt(1, i);
0697: pstmt.setInt(2, i / ntellers);
0698: pstmt.executeUpdate();
0699: pstmt.clearWarnings();
0700: } else {
0701: Query = "INSERT INTO tellers(Tid,Bid,Tbalance) VALUES ("
0702: + i + "," + i / ntellers + ",0)";
0703:
0704: Stmt.executeUpdate(Query);
0705: }
0706:
0707: if ((i % 100 == 0) && (transactions)) {
0708: Conn.commit();
0709: }
0710: }
0711:
0712: if (prepared_stmt) {
0713: pstmt.close();
0714: }
0715:
0716: if (transactions) {
0717: Conn.commit();
0718: }
0719:
0720: if (prepared_stmt) {
0721: Query = "INSERT INTO accounts(Aid,Bid,Abalance) VALUES (?,?,0)";
0722: pstmt = Conn.prepareStatement(Query);
0723: }
0724:
0725: System.out.println("Insert data in accounts table");
0726:
0727: for (int i = 0; i < naccounts * tps; i++) {
0728: if (prepared_stmt) {
0729: pstmt.setInt(1, i);
0730: pstmt.setInt(2, i / naccounts);
0731: pstmt.executeUpdate();
0732: pstmt.clearWarnings();
0733: } else {
0734: Query = "INSERT INTO accounts(Aid,Bid,Abalance) VALUES ("
0735: + i + "," + i / naccounts + ",0)";
0736:
0737: Stmt.executeUpdate(Query);
0738: }
0739:
0740: if ((i % 10000 == 0) && (transactions)) {
0741: Conn.commit();
0742: }
0743:
0744: if ((i > 0) && ((i % 10000) == 0)) {
0745: System.out
0746: .println("\t" + i + "\t records inserted");
0747: }
0748: }
0749:
0750: if (prepared_stmt) {
0751: pstmt.close();
0752: }
0753:
0754: if (transactions) {
0755: Conn.commit();
0756: }
0757:
0758: System.out.println("\t" + (naccounts * tps)
0759: + "\t records inserted");
0760:
0761: // for tests
0762: Stmt.execute(ShutdownCommand);
0763: Stmt.close();
0764: } catch (Exception E) {
0765: System.out.println(E.getMessage());
0766: E.printStackTrace();
0767: }
0768:
0769: connectClose(Conn);
0770: } /* end of CreateDatabase */
0771:
0772: public static int getRandomInt(int lo, int hi) {
0773:
0774: int ret = 0;
0775:
0776: ret = (int) (Math.random() * (hi - lo + 1));
0777: ret += lo;
0778:
0779: return ret;
0780: }
0781:
0782: public static int getRandomID(int type) {
0783:
0784: int min = 0, max = 0;
0785:
0786: switch (type) {
0787:
0788: case TELLER:
0789: max = ntellers * tps - 1;
0790: break;
0791:
0792: case BRANCH:
0793: max = nbranches * tps - 1;
0794: break;
0795:
0796: case ACCOUNT:
0797: max = naccounts * tps - 1;
0798: break;
0799: }
0800:
0801: return (getRandomInt(min, max));
0802: }
0803:
0804: public static Connection connect(String DBUrl, String DBUser,
0805: String DBPassword) {
0806:
0807: try {
0808: Connection conn = DriverManager.getConnection(DBUrl,
0809: DBUser, DBPassword);
0810:
0811: return conn;
0812: } catch (Exception E) {
0813: System.out.println(E.getMessage());
0814: E.printStackTrace();
0815: }
0816:
0817: return null;
0818: }
0819:
0820: public static void connectClose(Connection c) {
0821:
0822: if (c == null) {
0823: return;
0824: }
0825:
0826: try {
0827: c.close();
0828: } catch (Exception E) {
0829: System.out.println(E.getMessage());
0830: E.printStackTrace();
0831: }
0832: }
0833:
0834: void checkSums(Connection conn) throws Exception {
0835:
0836: Statement st1 = null;
0837: ResultSet rs = null;
0838: int bbalancesum;
0839: int tbalancesum;
0840: int abalancesum;
0841: int deltasum;
0842:
0843: try {
0844: st1 = conn.createStatement();
0845: rs = st1.executeQuery("select sum(bbalance) from branches");
0846:
0847: rs.next();
0848:
0849: bbalancesum = rs.getInt(1);
0850:
0851: rs.close();
0852:
0853: rs = st1.executeQuery("select sum(tbalance) from tellers");
0854:
0855: rs.next();
0856:
0857: tbalancesum = rs.getInt(1);
0858:
0859: rs.close();
0860:
0861: rs = st1.executeQuery("select sum(abalance) from accounts");
0862:
0863: rs.next();
0864:
0865: abalancesum = rs.getInt(1);
0866:
0867: rs.close();
0868:
0869: rs = st1.executeQuery("select sum(delta) from history");
0870:
0871: rs.next();
0872:
0873: deltasum = rs.getInt(1);
0874:
0875: rs.close();
0876:
0877: rs = null;
0878:
0879: st1.close();
0880:
0881: st1 = null;
0882:
0883: if (abalancesum != bbalancesum
0884: || bbalancesum != tbalancesum
0885: || tbalancesum != deltasum) {
0886: System.out.println("sums don't match!");
0887: } else {
0888: System.out.println("sums match!");
0889: }
0890:
0891: System.out.println(abalancesum + " " + bbalancesum + " "
0892: + tbalancesum + " " + deltasum);
0893: } finally {
0894: if (st1 != null) {
0895: st1.close();
0896: }
0897: }
0898: }
0899:
0900: class ClientThread extends Thread {
0901:
0902: int ntrans = 0;
0903: Connection Conn;
0904: PreparedStatement pstmt1 = null;
0905: PreparedStatement pstmt2 = null;
0906: PreparedStatement pstmt3 = null;
0907: PreparedStatement pstmt4 = null;
0908: PreparedStatement pstmt5 = null;
0909:
0910: public ClientThread(int number_of_txns, String url,
0911: String user, String password) {
0912:
0913: ntrans = number_of_txns;
0914: Conn = connect(url, user, password);
0915:
0916: if (Conn == null) {
0917: return;
0918: }
0919:
0920: try {
0921: if (transactions) {
0922: Conn.setAutoCommit(false);
0923: }
0924:
0925: if (prepared_stmt) {
0926: String Query;
0927:
0928: Query = "UPDATE accounts ";
0929: Query += "SET Abalance = Abalance + ? ";
0930: Query += "WHERE Aid = ?";
0931: pstmt1 = Conn.prepareStatement(Query);
0932: Query = "SELECT Abalance ";
0933: Query += "FROM accounts ";
0934: Query += "WHERE Aid = ?";
0935: pstmt2 = Conn.prepareStatement(Query);
0936: Query = "UPDATE tellers ";
0937: Query += "SET Tbalance = Tbalance + ? ";
0938: Query += "WHERE Tid = ?";
0939: pstmt3 = Conn.prepareStatement(Query);
0940: Query = "UPDATE branches ";
0941: Query += "SET Bbalance = Bbalance + ? ";
0942: Query += "WHERE Bid = ?";
0943: pstmt4 = Conn.prepareStatement(Query);
0944: Query = "INSERT INTO history(Tid, Bid, Aid, delta) ";
0945: Query += "VALUES (?,?,?,?)";
0946: pstmt5 = Conn.prepareStatement(Query);
0947: }
0948: } catch (Exception E) {
0949: System.out.println(E.getMessage());
0950: E.printStackTrace();
0951: }
0952: }
0953:
0954: public void run() {
0955:
0956: while (ntrans-- > 0) {
0957: int account = JDBCBench.getRandomID(ACCOUNT);
0958: int branch = JDBCBench.getRandomID(BRANCH);
0959: int teller = JDBCBench.getRandomID(TELLER);
0960: int delta = JDBCBench.getRandomInt(0, 1000);
0961:
0962: doOne(branch, teller, account, delta);
0963: incrementTransactionCount();
0964: }
0965:
0966: if (prepared_stmt) {
0967: try {
0968: if (pstmt1 != null) {
0969: pstmt1.close();
0970: }
0971:
0972: if (pstmt2 != null) {
0973: pstmt2.close();
0974: }
0975:
0976: if (pstmt3 != null) {
0977: pstmt3.close();
0978: }
0979:
0980: if (pstmt4 != null) {
0981: pstmt4.close();
0982: }
0983:
0984: if (pstmt5 != null) {
0985: pstmt5.close();
0986: }
0987: } catch (Exception E) {
0988: System.out.println(E.getMessage());
0989: E.printStackTrace();
0990: }
0991: }
0992:
0993: connectClose(Conn);
0994:
0995: Conn = null;
0996: }
0997:
0998: /*
0999: ** doOne() - Executes a single TPC BM B transaction.
1000: */
1001: int doOne(int bid, int tid, int aid, int delta) {
1002:
1003: int aBalance = 0;
1004:
1005: if (Conn == null) {
1006: incrementFailedTransactionCount();
1007:
1008: return 0;
1009: }
1010:
1011: try {
1012: if (prepared_stmt) {
1013: pstmt1.setInt(1, delta);
1014: pstmt1.setInt(2, aid);
1015: pstmt1.executeUpdate();
1016: pstmt1.clearWarnings();
1017: pstmt2.setInt(1, aid);
1018:
1019: ResultSet RS = pstmt2.executeQuery();
1020:
1021: pstmt2.clearWarnings();
1022:
1023: while (RS.next()) {
1024: aBalance = RS.getInt(1);
1025: }
1026:
1027: pstmt3.setInt(1, delta);
1028: pstmt3.setInt(2, tid);
1029: pstmt3.executeUpdate();
1030: pstmt3.clearWarnings();
1031: pstmt4.setInt(1, delta);
1032: pstmt4.setInt(2, bid);
1033: pstmt4.executeUpdate();
1034: pstmt4.clearWarnings();
1035: pstmt5.setInt(1, tid);
1036: pstmt5.setInt(2, bid);
1037: pstmt5.setInt(3, aid);
1038: pstmt5.setInt(4, delta);
1039: pstmt5.executeUpdate();
1040: pstmt5.clearWarnings();
1041: } else {
1042: Statement Stmt = Conn.createStatement();
1043: String Query = "UPDATE accounts ";
1044:
1045: Query += "SET Abalance = Abalance + " + delta
1046: + " ";
1047: Query += "WHERE Aid = " + aid;
1048:
1049: int res = Stmt.executeUpdate(Query);
1050:
1051: Stmt.clearWarnings();
1052:
1053: Query = "SELECT Abalance ";
1054: Query += "FROM accounts ";
1055: Query += "WHERE Aid = " + aid;
1056:
1057: ResultSet RS = Stmt.executeQuery(Query);
1058:
1059: Stmt.clearWarnings();
1060:
1061: while (RS.next()) {
1062: aBalance = RS.getInt(1);
1063: }
1064:
1065: Query = "UPDATE tellers ";
1066: Query += "SET Tbalance = Tbalance + " + delta
1067: + " ";
1068: Query += "WHERE Tid = " + tid;
1069:
1070: Stmt.executeUpdate(Query);
1071: Stmt.clearWarnings();
1072:
1073: Query = "UPDATE branches ";
1074: Query += "SET Bbalance = Bbalance + " + delta
1075: + " ";
1076: Query += "WHERE Bid = " + bid;
1077:
1078: Stmt.executeUpdate(Query);
1079: Stmt.clearWarnings();
1080:
1081: Query = "INSERT INTO history(Tid, Bid, Aid, delta) ";
1082: Query += "VALUES (";
1083: Query += tid + ",";
1084: Query += bid + ",";
1085: Query += aid + ",";
1086: Query += delta + ")";
1087:
1088: Stmt.executeUpdate(Query);
1089: Stmt.clearWarnings();
1090: Stmt.close();
1091: }
1092:
1093: if (transactions) {
1094: Conn.commit();
1095: }
1096:
1097: return aBalance;
1098: } catch (Exception E) {
1099: if (verbose) {
1100: System.out.println("Transaction failed: "
1101: + E.getMessage());
1102: E.printStackTrace();
1103: }
1104:
1105: incrementFailedTransactionCount();
1106:
1107: if (transactions) {
1108: try {
1109: Conn.rollback();
1110: } catch (SQLException E1) {
1111: }
1112: }
1113: }
1114:
1115: return 0;
1116: } /* end of DoOne */
1117: } /* end of class ClientThread */
1118:
1119: class MemoryWatcherThread extends Thread {
1120:
1121: long min = 0;
1122: long max = 0;
1123: boolean keep_running = true;
1124:
1125: public MemoryWatcherThread() {
1126:
1127: this .reset();
1128:
1129: keep_running = true;
1130: }
1131:
1132: public void reset() {
1133:
1134: System.gc();
1135:
1136: long currentFree = Runtime.getRuntime().freeMemory();
1137: long currentAlloc = Runtime.getRuntime().totalMemory();
1138:
1139: min = max = (currentAlloc - currentFree);
1140: }
1141:
1142: public void end() {
1143: keep_running = false;
1144: }
1145:
1146: public void run() {
1147:
1148: while (keep_running) {
1149: long currentFree = Runtime.getRuntime().freeMemory();
1150: long currentAlloc = Runtime.getRuntime().totalMemory();
1151: long used = currentAlloc - currentFree;
1152:
1153: if (used < min) {
1154: min = used;
1155: }
1156:
1157: if (used > max) {
1158: max = used;
1159: }
1160:
1161: try {
1162: sleep(100);
1163: } catch (InterruptedException E) {
1164: }
1165: }
1166: }
1167: } /* end of class MemoryWatcherThread */
1168: }
|