JDBC Benchmark
Revision as of 18:52, 24 May 2008 by PeterHarding (talk | contribs)
Packaged Benchmark
See http://www.performiq.com.au/kb/images/JDBCBench.tar
JDBCTest
An IBM Test - see web page...
//
// DISCLAIMER OF WARRANTIES.
// The following [enclosed] code is sample code created by IBM
// Corporation. This sample code is not part of any standard or IBM
// product and is provided to you solely for the purpose of assisting
// you in the development of your applications. The code is provided
// "AS IS". IBM MAKES NO WARRANTIES, EXPRESS OR IMPLIED, INCLUDING BUT
// NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
// FOR A PARTICULAR PURPOSE, REGARDING THE FUNCTION OR PERFORMANCE OF
// THIS CODE. THIS CODE MAY CONTAIN ERRORS. IBM shall not be liable
// for any damages arising out of your use of the sample code, even
// if it has been advised of the possibility of such damages.
//
import java.sql.*;
import javax.sql.*;
import java.io.*;
import java.awt.*;
import java.awt.event.*;
public class jdbctest {
public String driverclassname;
public String DBURL;
public String dbprefix = "jdbc:";
public Connection con;
public Statement stmt;
public BufferedReader instream;
public long operationTimer;
public String response = "0";
public String globaluid = null;
public String globalpwd = null;
public String dbhostname;
public String dbport;
public String dbname;
public ClassLoader loader=null;
public Class dsClass=null;
ConnectionPoolDataSource ds=null;
public static final int DATASOURCECONNECTIONS=10;
// Capture window events...
static private class FrameCloser extends WindowAdapter {
public void windowClosing(WindowEvent we) {
System.out.println("windowClosing..." + we);
we.getWindow().dispose();
System.exit(0);
}
}
// Capture key events...
static private class KeyInterceptor implements KeyListener {
public void keyTyped(java.awt.event.KeyEvent ke) {
}
public void keyPressed(java.awt.event.KeyEvent ke) {
}
public void keyReleased(java.awt.event.KeyEvent ke) {
// System.out.println("keyReleased..." + ke);
if (ke.getKeyCode() == KeyEvent.VK_ENTER) {
TextField tf = (TextField) ke.getComponent();
synchronized (tf) {
tf.notify();
}
} // if enter key is pressed
}
}
public static void main(String args[]) {
System.out.println("JDBC Test starting...");
jdbctest ct = new jdbctest();
ct.init(args); // Loads jdbc driver
ct.connect(); // sets connection
if (ct.con == null) {
System.out.println("Connection to " + ct.DBURL + " failed!");
System.exit(33);
}
System.out.println("Connection Successful: " + ct.con);
System.out.println(
"Connection took " + ct.operationTimer + " milliseconds to complete");
try {
System.out.println("AutoCommit is: " + ct.con.getAutoCommit());
} catch (SQLException s) {
s.printStackTrace();
System.out.println("Error code is: " + s.getErrorCode());
System.out.println("SQLState is: " + s.getSQLState());
System.exit(33);
}
do {
try {
if (!(ct.response.equals("11")) && !(ct.response.equals("10")))
{System.out.println("Create new statement...");
ct.stmt = ct.con.createStatement();}
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
System.exit(33);
}
} while (ct.process());
try {
if (ct.stmt != null) ct.stmt.close();
ct.con.close();
} catch (Exception e) {
e.printStackTrace();
}
// System.exit ???
System.exit(33);
}
public void init(String args[]) {
instream = new BufferedReader((new InputStreamReader(System.in)));
while (driverclassname == null) {
System.out.println(
"Please respond:\n\t1 - For DB2\n\t2 - For DB2/Pure Network Client\n\t3 - For Oracle (thin) \n\t4 - For Oracle (oci8)\n\t5 - For Informix\n\t6 - For Sybase\n\t7 - For Cloudscape - (Local)\n\t8 - For Cloudscape - (RMI)\n\t9 - For SQLServer (Microsoft Driver)\n\t10 - For SQLServer (SequeLink driver)\n\t11 - For SQLServer (WebSphere embedded Connect JDBC driver)\n\t12 - For SQLServer(Data Direct Technologies Connect JDBC driver)");
response = readLine();
if (response.equals("1")) {
driverclassname = "COM.ibm.db2.jdbc.app.DB2Driver";
dbprefix += "db2:";
} else
if (response.equals("2")) {
driverclassname = "COM.ibm.db2.jdbc.net.DB2Driver";
dbprefix += "db2:";
} else
if (response.equals("3")) { //Thin Driver
driverclassname = "oracle.jdbc.driver.OracleDriver";
dbprefix += "oracle:thin:";
} else
if (response.equals("4")) { //Thick Driver
driverclassname = "oracle.jdbc.driver.OracleDriver";
dbprefix += "oracle:oci8:";
} else
if (response.equals("5")) {
driverclassname = "com.informix.jdbc.IfxDriver";
dbprefix += "informix-sqli:";
} else
if (response.equals("6")) {
driverclassname = "com.sybase.jdbc2.jdbc.SybDriver";
dbprefix += "sybase:";
} else
if (response.equals("7")) {
driverclassname = "COM.cloudscape.core.JDBCDriver";
dbprefix += "cloudscape:";
} else
if (response.equals("8")) {
driverclassname = "COM.cloudscape.core.RmiJdbcDriver";
dbprefix += "cloudscape:rmi:";
} else
if (response.equals("9")) {
driverclassname = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
dbprefix += "microsoft:sqlserver:";
} else
if (response.equals("10")) {
// driverclassname = "com.merant.sequelink.jdbcx.datasource.SequeLinkDataSource";
driverclassname = "com.ddtek.jdbcx.sequelink.SequeLinkDataSource";
dbprefix += "sequelink:";
} else
if (response.equals("11")) {
driverclassname = "com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource";
dbprefix += "sequelink:";
} else
if (response.equals("12")) {
driverclassname = "com.ddtek.jdbcx.sqlserver.SQLServerDataSource";
dbprefix += "sequelink:";
} else
System.out.println("Invalid selection: specify an option 1-12.");
}
System.out.println("Loading jdbc driver: " + driverclassname);
try {
if (Integer.parseInt(response) < DATASOURCECONNECTIONS)
{
Class.forName(driverclassname);
System.out.println(driverclassname + " was loaded successfully");
}
else
{loader = Thread.currentThread().getContextClassLoader();
if (loader == null)
loader = ClassLoader.getSystemClassLoader();
dsClass=loader.loadClass(driverclassname);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println(
"Please modify you classpath to include the class: " + driverclassname);
System.out.println(
"To be sure that you have this class in your classpath, issue:\n javap "
+ driverclassname);
System.exit(33);
}
}
public void connect() {
while ((DBURL == null) && (Integer.parseInt(response) < DATASOURCECONNECTIONS)) {
System.out.println("Please enter connection URL info, e.g:");
if (driverclassname.endsWith(".app.DB2Driver")) {
System.out.println("jdbc:db2:dbname or dbname");
} else
if (driverclassname.endsWith(".net.DB2Driver")) {
System.out.println(
"jdbc:db2://<serverName>:<port>/<dbname> or //<serverName>:<port>/<dbName>");
} else
if (driverclassname.endsWith(".OracleDriver")) {
if (response.equals("4")) {
System.out.println(
"jdbc:oracle:oci8:@<hostname>:<port>:<dbname> or @<hostname>:<port>:<dbname>");
} else {
System.out.println(
"jdbc:oracle:thin:@<hostname>:<port>:<dbname> or @<hostname>:<port>:<dbname>");
}
} else
if (driverclassname.endsWith(".IfxDriver"))
System.out.println(
"jdbc:informix-sqli://<hostname>:<port>/<dbname>:INFORMIXSERVER=<ifxservername>");
else
if (driverclassname.endsWith(".SybDriver"))
System.out.println("jdbc:sybase:<dbProtocol>:<hostname>:<port>/<dbname>");
else
if (driverclassname.endsWith(".SQLServerDriver"))
System.out.println("jdbc:microsoft:sqlserver://<hostname>:<port>;DatabaseName=<dbname>");
else
if (driverclassname.endsWith(".JDBCDriver"))
System.out.println("jdbc:cloudscape:<dbname>;create=<true|false>");
else
if (driverclassname.endsWith(".RmiJdbcDriver"))
System.out.println("jdbc:cloudscape:rmi://<hostname>:<rmi port>/<dbname> or //<hostname>:<rmi port>/<dbname");
if (!(driverclassname.endsWith("SequeLinkDriver")))
DBURL = readLine();
else
DBURL = "";
}
if (DBURL==null) DBURL="";
if (!DBURL.startsWith(dbprefix))
DBURL = dbprefix + DBURL;
String userid = null;
{
if (Integer.parseInt(response) < 10)
System.out.println("Please enter userid for connection to " + DBURL);
else if (Integer.parseInt(response) < 13)
System.out.println("Please enter userid for connection to Microsoft SQL Server:");
userid = readLine();
System.out.println("userid is: '" + userid + "'");
}
String password = null;
if (userid != null && !userid.equals("")) {
System.out.println(
"Please enter password "
+ " =====> WARNING: PASSWORD NOT HIDDEN <====== ");
System.out.println(
"enter 'gui' instead of you password for a secure GUI prompt)");
password = readLine();
if (password.equalsIgnoreCase("gui"))
password = getPassword(userid);
}
globaluid = userid;
globalpwd = password;
try {
//per L3, DataSource approach is used with WS 4.x, so let's do it the same way
if (Integer.parseInt(response) >= DATASOURCECONNECTIONS) {
con = this.getDataSourceConnection(userid, password);
} else { //non-SQLServer
if (userid == null || userid.equals("")) {
long start = System.currentTimeMillis();
con = DriverManager.getConnection(DBURL);
operationTimer = System.currentTimeMillis() - start;
} else {
long start = System.currentTimeMillis();
con = DriverManager.getConnection(DBURL, userid, password);
operationTimer = System.currentTimeMillis() - start;
}
}
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
// See if we can help the user with diagnosing the problem:
boolean network_connect_problem = false;
if (driverclassname.equals("oracle.jdbc.driver.OracleDriver")) {
if ((se.getSQLState() == null) && (se.getErrorCode() == 17002)) {
System.out.println("Connection to the Oracle server failed.\n");
System.out.println(
"Unable to connect to @hostname:port specified in the DB URL.");
network_connect_problem = true;
}
}
if (driverclassname.equals("COM.ibm.db2.jdbc.net.DB2Driver")) {
if ((se.getSQLState() == "08S01") && (se.getErrorCode() == -99999)) {
System.out.println("Connection to the DB2 server failed.\n");
System.out.println(
"Unable to connect to //hostname:port specified in the DB URL.");
System.out.println(
"Verify that db2jstrt is running on the target host with the specified port as a parameter...");
network_connect_problem = true;
}
}
if (network_connect_problem) {
System.out.println(
"Verify that the host and port that you are connecting to are correct.");
System.out.println(
"Also verify that the host you are connecting to is listening on the specified port.");
System.out.println("Hint: use telnet hostname port.");
System.out.println(
"\tIf you get connection refused then the host is not listening on the specified port.");
System.out.println(
"\tIf telnet simply hangs then the host is listening on the port.");
System.out.println();
}
} // End catch connect exception
}
private Connection getDataSourceConnection(String userid, String password) {
PooledConnection pooledConn = null;
Connection conn = null;
try {
/*loader = Thread.currentThread().getContextClassLoader();
if (loader == null)
loader = ClassLoader.getSystemClassLoader();
if (response.equals("10"))
dsClass =
loader.loadClass("com.merant.sequelink.jdbcx.datasource.SequeLinkDataSource");
else if (response.equals("11"))
dsClass =
loader.loadClass("com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource");
dsClass=loader.loadClass(driverclassname);
*/
ds = (ConnectionPoolDataSource) (dsClass.newInstance());
System.out.println("enter database name:");
dbname = readLine();
dsClass.getMethod("setDatabaseName", new Class[] { String.class }).invoke(
ds,
new Object[] { dbname });
System.out.println("enter port number:");
dbport = readLine();
dsClass.getMethod("setPortNumber", new Class[] { int.class }).invoke(
ds,
new Object[] { new Integer(dbport)});
System.out.println("enter server name:");
dbhostname = readLine();
dsClass.getMethod("setServerName", new Class[] { String.class }).invoke(
ds,
new Object[] { dbhostname });
dsClass.getMethod("setUser", new Class[] { String.class }).invoke(
ds,
new Object[] { userid });
dsClass.getMethod("setPassword", new Class[] { String.class }).invoke(
ds,
new Object[] { password });
long start = System.currentTimeMillis();
pooledConn = ds.getPooledConnection();
conn = pooledConn.getConnection();
operationTimer = System.currentTimeMillis() - start;
// Any use of the Connection must be done AFTER the unlock, or else it will fail
// with a license verification error.
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
// See if we can help the user with diagnosing the problem:
System.out.println(
"Verify that the host and port that you are connecting to are correct.");
System.out.println(
"Also verify that the host you are connecting to is listening on the specified port.");
System.out.println("Hint: use telnet hostname port.");
System.out.println(
"\tIf you get connection refused then the host is not listening on the specified port.");
System.out.println(
"\tIf telnet simply hangs then the host is listening on the port.");
// System.out.println();
return (null);
} // End catch connect exception
catch (Throwable th) {
th.printStackTrace();
return (null);
} finally {
// if (conn != null) try { conn.close(); } catch (Throwable th) {}
// if (pooledConn != null) try { pooledConn.close(); } catch (Throwable th) {}
}
return (conn);
}
private Connection getDataSourceConnection() {
PooledConnection pooledConn = null;
Connection conn = null;
System.out.println("getting data source connection...");
try {
long start = System.currentTimeMillis();
pooledConn = ds.getPooledConnection();
conn = pooledConn.getConnection();
operationTimer = System.currentTimeMillis() - start;
// Any use of the Connection must be done AFTER the unlock, or else it will fail
// with a license verification error.
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
// See if we can help the user with diagnosing the problem:
System.out.println(
"Verify that the host and port that you are connecting to are correct.");
System.out.println(
"Also verify that the host you are connecting to is listening on the specified port.");
System.out.println("Hint: use telnet hostname port.");
System.out.println(
"\tIf you get connection refused then the host is not listening on the specified port.");
System.out.println(
"\tIf telnet simply hangs then the host is listening on the port.");
// System.out.println();
return (null);
} // End catch connect exception
catch (Throwable th) {
th.printStackTrace();
return (null);
} finally {
// if (conn != null) try { conn.close(); } catch (Throwable th) {}
// if (pooledConn != null) try { pooledConn.close(); } catch (Throwable th) {}
}
return (conn);
}
public String readLine() {
String response = null;
try {
response = instream.readLine();
} catch (IOException e) {
e.printStackTrace();
}
return response;
}
public boolean process() {
String instring = null;
if (!(response.equals("11")) && !(response.equals("10")))
{
while (instring == null) {
System.out.println(
"Please enter sql statement to execute...(q to quit)\n(or maxconn to test maximum connections possible to this database)");
instring = readLine();
if (instring.equals("q")) {
System.out.println("Ok, quitting!");
return false;
} else
if (instring.equals("maxconn")) {
System.out.println("testing for maximum connections...");
maxconn();
return true;
}
}
}
else //special case with DataDirect driver, test connect only
{
while ((instring == null) || (!instring.equals("q")) && (!instring.equals("maxconn"))) {
System.out.println(
"Please enter q to quit\n(or maxconn to test maximum connections possible to this database)");
instring = readLine();
}
if (instring.equals("maxconn")) {
System.out.println("testing for maximum connections...");
maxconn();
return true;
}
else
if (instring.equals("q")) {
System.out.println("Ok, quitting!");
return false; }
else System.out.println("Jdbctest only supports connection testing for this driver.");
}
boolean rc = false;
try {
long start = System.currentTimeMillis();
rc = stmt.execute(instring);
operationTimer = System.currentTimeMillis() - start;
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(
"Operation took " + operationTimer + " milliseconds to complete");
ResultSet rs = null;
System.out.println(
"Just tried " + rc + " = stmt.execute(\"" + instring + "\");");
if (rc) {
try {
System.out.println("Getting result set...");
rs = stmt.getResultSet();
ResultSetMetaData rsm = rs.getMetaData();
// Display names of columns fetched
int colcount = rsm.getColumnCount();
System.out.println(colcount + " column(s) in result");
int[] coltype = new int[colcount + 1]; // Do not slot 0
for (int i = 1; i < colcount + 1; i++) {
System.out.print(rsm.getColumnName(i) + " ");
coltype[i] = rsm.getColumnType(i);
}
System.out.println();
System.out.println("-----------------------------------");
while (rs.next()) {
for (int j = 1; j < colcount + 1; j++) {
if (j != 1)
System.out.print(",");
switch (coltype[j]) {
case Types.TINYINT :
System.out.print("" + rs.getShort(j));
break;
case Types.SMALLINT :
System.out.print("" + rs.getShort(j));
break;
case Types.INTEGER :
System.out.print("" + rs.getInt(j));
break;
case Types.BIGINT :
System.out.print("" + rs.getLong(j));
break;
case Types.FLOAT :
System.out.print("" + rs.getFloat(j));
break;
case Types.REAL :
System.out.print("" + rs.getDouble(j));
break;
case Types.DOUBLE :
System.out.print("" + rs.getDouble(j));
break;
case Types.NUMERIC :
System.out.print("" + rs.getInt(j));
break;
case Types.DECIMAL :
System.out.print("" + rs.getInt(j));
break;
case Types.CHAR :
// System.out.print(""+rs.getByte(j));
System.out.print("" + rs.getString(j));
break;
case Types.VARCHAR :
System.out.print("" + rs.getString(j));
break;
case Types.LONGVARCHAR :
System.out.print("" + rs.getString(j));
break;
case Types.DATE :
System.out.print("" + rs.getDate(j));
break;
case Types.TIME :
System.out.print("" + rs.getTime(j));
break;
case Types.TIMESTAMP :
System.out.print("" + rs.getTimestamp(j));
break;
case Types.BINARY :
case Types.BIT :
case Types.VARBINARY :
case Types.LONGVARBINARY :
byte b[] = rs.getBytes(j);
for (int n = 0; n < b.length; n++)
System.out.print("" + b[n] + "|");
break;
case Types.NULL :
System.out.print("-");
break;
case Types.OTHER :
System.out.print("OTHER");
break;
default :
System.out.print("UNKNOWN-TYPE");
}
}
System.out.println();
}
System.out.println();
rs.close();
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
}
}
return true;
}
String getPassword(String userid) {
Frame f;
Panel p;
Label l;
TextField tf;
// Create the frame
f = new Frame("Password Prompt for JDBC tester");
f.setLocation(400, 400);
f.setSize(350, 100);
// Reister ourselves for 'Close' events
f.addWindowListener(new FrameCloser());
// Create the panel
p = new Panel();
f.add(p, "Center");
l = new Label("Please enter password for Data Base user: " + userid);
tf = new TextField(20);
tf.setEchoChar('*');
p.add(l, "Center");
p.add(tf, "Center");
try {
f.show();
} catch (Exception ex) {
ex.printStackTrace();
System.out.println(
"Your ===> DISPLAY <=== Environment Variable is not exported properly. Please correct and retry.");
}
try {
Thread.sleep(1000);
} catch (InterruptedException ie) {
}
f.show();
tf.requestFocus();
tf.addKeyListener(new KeyInterceptor());
// System.out.println("Waiting for password to be entered...");
synchronized (tf) {
try {
tf.wait();
} catch (InterruptedException e) {
}
}
String password = tf.getText();
f.transferFocus();
f.dispose();
return password;
}
public void maxconn() {
Connection c[] = new Connection[125];
System.out.println("1 connection assumed...looping...");
int i;
for (i = 0; i < 125; i++)
try {
if (globaluid == null || globaluid.equals("")) {
if (Integer.parseInt(response) < DATASOURCECONNECTIONS)
{
long start = System.currentTimeMillis();
c[i] = DriverManager.getConnection(DBURL);
operationTimer = System.currentTimeMillis() - start;
}
else c[i]= getDataSourceConnection();
} else {
if (Integer.parseInt(response) < DATASOURCECONNECTIONS)
{
long start = System.currentTimeMillis();
c[i] = DriverManager.getConnection(DBURL, globaluid, globalpwd);
operationTimer = System.currentTimeMillis() - start;
}
else c[i]= getDataSourceConnection();
}
System.out.println("Connection Successful: " + c[i]);
System.out.println(
"Connection "
+ (i + 2)
+ " took "
+ operationTimer
+ " milliseconds to complete");
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
i--;
break;
}
catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
i--;
break;
}
System.out.println(
"Maximum connections to DB:" + DBURL + " is " + (i + 2) + ".");
System.out.println("closing all connections except initial connection...");
for (i = 0; i < 125; i++)
if (c[i] != null)
try {
c[i].close();
System.out.print(".");
} catch (SQLException se2) {
se2.printStackTrace();
System.out.println("Error code is: " + se2.getErrorCode());
System.out.println("SQLState is: " + se2.getSQLState());
}
System.out.println("Done!");
}
}