Latest revision as of 18:52, 24 May 2008

Packaged Benchmark

See http://www.performiq.com.au/kb/images/JDBCBench.tar

JDBCTest

An IBM Test - see web page...

<html>

jdbctest.java tool

<form>Click the<input Type=Button Name="use" Value="Usage" ONCLICK="window.location=('jsread2.html#usage');" >button for information on how to use the tool.</form>

<form>Click the<input Type=BUTTON Name="code" Value="Code" ONCLICK="window.location=('ftp://ftp.software.ibm.com/software/websphere/info/tools/jdbctest');" >button for the tool's source code.</form>

Purpose:

The jdbctest.java tool is made available to IBM customers and support personnel to assist in debugging setup and install problems with the Advanced Edition of WebSphere Application Server.

Benefits of the tool:

The tool is available in source code only from the IBM FTP site: <a href="ftp://ftp.software.ibm.com/software/websphere/info/tools/jdbctest">ftp://ftp.software.ibm.com/software/websphere/info/tools/jdbctest</a>

Compile the tool (e.g., javac jdbctest.java) to use it.

Compiling the tool verifies that the java compile/install environment is setup correctly on the target machine.

Running the tool (e.g., java jdbctest) verifies that the java runtime/classpath setup is correct.
The tool requires the use of a database driver. The supported databases are:

DB2
Oracle
Sybase
Informix
Cloudscape
SQLServer

One of these drivers must be specified in the CLASSPATH since the tool dynamically loads a driver based on user input. [The tool prompts the user for that input].

The DB2 driver is typically in a jar file called: db2java.zip. This jar file has to be in the CLASSPATH if DB2 is used.

<a NAME="dberr"></a>NOTE:
Ensure you are specifying the correct db2java.zip file in your CLASSPATH.
Two db2java.zip files are available in the WebSphere Application Server environment. The db2java.zip file, located in the WebSphere\AppServer\lib directory, reflects the complimentary, limited-function version of DB2.
If your CLASSPATH points to this db2java.zip file, the jdbctest tool connection test will fail with error:
Unsatisfied link error SQL connect.
Your CLASSPATH must point to the db2java.zip file used by the full version DB2 product. This file is located in the
SQLLIB\java directory.

The Oracle driver is typically in a zip file called: classes111.zip.

The corresponding jar or zip file has to be added to the CLASSPATH for this tool to load the appropriate driver. This verifies that the database JDBC Driver is found and loadable.

The tool prompts for the database name (DBURL e.g.,jdbc:db2:wsa or jdbc:thin:@hostname:port:oracle_dbname), userid and password.

It uses this information to connect to the specified database.

This verifies that connectivity to the database is possible using the DBURL specified. This also verifies that the userid/password provided to the tool, are correct.

After connection is established, enter any valid SQL statement and it will be processed by the tool. [e.g.,INSERT into PERSONBEANTBL values(22,'Joe User',44)] or [select
- from PERSONBEANTBL].
This verifies that SQL statements are executed and results are displayed back to the user.
Entering maxconn at the command prompt tests the system limit for the number of possible simultaneous connections to the database.

<a NAME="usage"></a>How to use this tool:

The best way to demonstrate the use of this tool is through examples.

Meaning of the colors in the following examples:

input is blue
output is black
explanation notes are purple.

NOTE: WebSphere Application
Server must be installed before you can use this tool, but it does not
have to be running.

Compiling the tool:
javac -extdirs <WebSphere install root path>\lib jdbctest.java (compiles this tool and produces several class files)

Do you want to invoke the tool with with <a href="#db2">DB2</a> or <a href="#orcl">Oracle</a>? Choose your database.

<a NAME="db2"></a>Example: bnvoking the tool with DB2:
java jdbctest
JDBC Test starting...
Please respond:
        1 - For DB2
        2 - For DB2/Pure Network Client
        3 - For Oracle (thin)
        4 - For Oracle (oci8)
        5 - For Informix
        6 - For Sybase
        7 - For Cloudscape - (Local)
        8 - For Cloudscape - (RMI)
        9 - For SQLServer (Microsoft Driver)
        10 - For SQLServer (SequeLink driver)
        11 - For SQLServer (WebSphere driver)
1
Loading jdbc driver: COM.ibm.db2.jdbc.app.DB2Driver
COM.ibm.db2.jdbc.app.DB2Driver was loaded successfully
Please enter connection URL, e.g:
jdbc:db2:dbname or dbname
was
Please enter userid for connection to jdbc:db2:wsa
guest
userid is: 'guest'
Please enter password    =====> WARNING: PASSWORD NOT HIDDEN   <======
enter 'gui' instead of your password for a secure GUI prompt) (if you enter this literal characters gui you get a graphical password prompt)
guest
Connection Successful: COM.ibm.db2.jdbc.app.DB2Connection@201c70f
Connection took 120 milliseconds to complete (time it took to perform the database connect)
AutoCommit is: true
Create new statement...
Please enter sql statement to execute...(q to quit)...(or maxconn to test maximum connections possible to database)
select * from ejsadmin.node_table
Operation took 2 milliseconds to complete
Just tried true = stmt.execute("select * from ejsadmin.node_table"); (true means that there additional results to extract)
Getting result set...
4 column(s) in result
INSTANCE_ID   TYPE_ID   NAME   DEPLOYEDJARDIR
-----------------------------------
48,12,asdasd,e:\wsa\deployedEJBs
161,12,hany,/usr/WebSphere/AppServer/deployedEJBs

Create new statement...
Please enter sql statement to execute...(q to quit)...(or maxconn to test maximum connections possible to database)
q
Ok, quitting!

<a NAME="orcl"></a>Example: invoking the tool with Oracle:
java jdbctest
JDBC Test starting...
Please respond:
        1 - For DB2
        2 - For DB2/Pure Network Client
        3 - For Oracle (thin)
        4 - For Oracle (oci8)
        5 - For Informix
        6 - For Sybase
        7 - For Cloudscape - (Local)
        8 - For Cloudscape - (RMI)
        9 - For SQLServer (Microsoft Driver)
        10 - For SQLServer (SequeLink driver)
        11 - For SQLServer (WebSphere driver)
3
Loading jdbc driver: oracle.jdbc.driver.OracleDriver
oracle.jdbc.driver.OracleDriver was loaded successfully
Please enter connection URL, e.g:
jdbc:oracle:thin:@hostname:1521:dbname or thin:@hostname:1521:dbname
thin:@pig:1521:orcl
Please enter userid for connection to jdbc:oracle:thin:@pig:1521:orcl
hany
userid is: 'hany'
Please enter password    =====> WARNING: PASSWORD NOT HIDDEN   <======
enter 'gui' instead of your password for a secure GUI prompt) (if you enter this literal characters gui you get a graphical password prompt)
hany
Connection Successful: oracle.jdbc.driver.OracleConnection@201c665
Connection took 1462 milliseconds to complete (time it took to perform the database connect)
AutoCommit is: true
Create new statement...
Please enter sql statement to execute...(q to quit)...(or maxconn to test maximum connections possible to database)
select * from person
Operation took 20 milliseconds to complete
Just tried true = stmt.execute("select * from person"); (true means that there additional results to extract)
Getting result set...
3 column(s) in result
ID   NAME   AGE
-----------------------------------
1,Hany Salem,77
22,Barton,55

Create new statement...
Please enter sql statement to execute...(q to quit)...(or maxconn to test maximum connections possible to database)
q
Ok, quitting!
</html>

//
//  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!");

	}

}

Difference between revisions of "JDBC Benchmark"

Latest revision as of 18:52, 24 May 2008

Contents

Packaged Benchmark

JDBCTest

jdbctest.java tool

Purpose:

Benefits of the tool:

<a NAME="usage"></a>How to use this tool:

Navigation menu

@@ Line 1: / Line 1: @@
+=Packaged Benchmark=
 See http://www.performiq.com.au/kb/images/JDBCBench.tar
+=JDBCTest=
+An IBM Test - see web page...
+<html>
+<center>
+<h1>
+<font color="#FF6600">jdbctest.java tool</font></h1></center>
+<p><form>Click the<input Type=Button Name="use" Value="Usage"
+ONCLICK="window.location=('jsread2.html#usage');" >button
+for information on how to use the tool.</form>
+<p><form>Click the<input Type=BUTTON Name="code" Value="Code"
+ ONCLICK="window.location=('ftp://ftp.software.ibm.com/software/websphere/info/tools/jdbctest');" >button
+for the tool's source code.</form>
+<h3>
+<b><font color="#FF0000">Purpose:</font></b></h3>
+&nbsp;&nbsp;&nbsp; The jdbctest.java tool is made available to IBM customers
+and support personnel to assist in debugging setup and install problems
+with the Advanced Edition of WebSphere Application Server.
+<h3>
+<b><font color="#FF0000">Benefits of the tool:</font></b></h3>
+<ul>
+<li>
+<font color="#000000">The tool is available in source code only from the
+IBM FTP site:
+<a href="ftp://ftp.software.ibm.com/software/websphere/info/tools/jdbctest">ftp://ftp.software.ibm.com/software/websphere/info/tools/jdbctest</a></font></li>
+<p><br><font color="#000000">Compile the tool (e.g., <b><tt>javac jdbctest.java</tt></b>)
+to use it.</font>
+<p><font color="#000000">Compiling the tool verifies that the java compile/install
+environment is setup correctly on the target machine.</font>
+<li>
+<font color="#000000">Running the tool (e.g., <b><tt>java jdbctest</tt></b>)
+verifies that the java runtime/classpath setup is correct.</font></li>
+<li>
+<font color="#000000">The tool requires the use of a database driver.&nbsp;
+The supported databases are:</font></li>
+<ul>
+<li>
+<font color="#000000">DB2</font></li>
+<li>
+<font color="#000000">Oracle</font></li>
+<li>
+<font color="#000000">Sybase</font></li>
+<li>
+<font color="#000000">Informix</font></li>
+<li>
+<font color="#000000">Cloudscape</font></li>
+<li>
+<font color="#000000">SQLServer</font></li>
+</ul>
+<p><br><font color="#000000">One of these drivers must be specified in
+the CLASSPATH since the tool dynamically loads a driver based on user input.
+[<i>The tool prompts the user for that input</i>].</font>
+<p><font color="#000000">The DB2 driver is typically in a jar file called:
+<b>db2java.zip</b>. This jar file has to be in the CLASSPATH if DB2 is
+used.</font>
+<blockquote><a NAME="dberr"></a><b><font color="#00008B">NOTE:</font></b><font color="#000000">&nbsp;&nbsp;
+<font size=-1>Ensure you are specifying the correct <b>db2java.zip</b>
+file in your CLASSPATH.</font></font>
+<br><font color="#000000"><font size=-1>Two <b>db2java.zip</b> files are
+available in the WebSphere Application Server environment. The <b>db2java.zip</b>
+file, located in the <tt>WebSphere\AppServer\lib</tt> directory, reflects
+the complimentary, limited-function version of DB2.</font></font>
+<br><font color="#000000"><font size=-1>If your CLASSPATH points to this
+<b>db2java.zip</b> file, the jdbctest tool connection test will fail with
+error:</font></font>
+<br><font size=-1><i><font color="#FF0000">Unsatisfied link error SQL connect</font></i><font color="#000000">.</font></font>
+<br><font color="#000000"><font size=-1>Your CLASSPATH must point to the
+<b>db2java.zip</b> file used by the full version DB2 product. This file
+is located in the
+<tt>SQLLIB\java</tt> directory.</font></font></blockquote>
+<font color="#000000">The Oracle driver is typically in a zip file called:
+<b>classes111.zip.</b></font>
+<p><font color="#000000">The corresponding jar or zip file has to be added
+to the CLASSPATH for this tool to load the appropriate driver. This verifies
+that the database JDBC Driver is found and loadable.</font>
+<li>
+<font color="#000000">The tool prompts for the database name (DBURL e.g.,<b><tt>
+jdbc:db2:wsa</tt></b> or <b><tt>jdbc:thin:@hostname:port:oracle_dbname</tt></b>),
+userid and password.</font></li>
+<p><br><font color="#000000">It uses this information to connect to the
+specified database.</font>
+<p><font color="#000000">This verifies that connectivity to the database
+is possible using the DBURL specified. This also verifies that the userid/password
+provided to the tool, are correct.</font>
+<li>
+<font color="#000000">After connection is established, enter any valid
+SQL statement and it will be processed by the tool. [e.g.,<b><tt> INSERT
+into PERSONBEANTBL values(22,'Joe User',44)</tt></b>] or [<b><tt>select
+* from PERSONBEANTBL</tt></b>].</font></li>
+<p><br><font color="#000000">This verifies that SQL statements are executed
+and results are displayed back to the user.</font>
+<li>
+<font color="#000000">Entering <b><i>maxconn</i></b> at the command prompt
+tests the system limit for the number of possible simultaneous connections
+to the database.</font></li>
+</ul>
+<h3>
+<a NAME="usage"></a><b><font color="#FF0000">How to use this tool:</font></b></h3>
+<font color="#000000">&nbsp;&nbsp;&nbsp; The best way to demonstrate the
+use of this tool is through examples.</font>
+<p>Meaning of the colors in the following examples:
+<ul>
+<li>
+<font color="#3366FF">input is blue</font></li>
+<li>
+<font color="#000000">output is black</font></li>
+<li>
+<font color="#CC33CC">explanation notes are purple.</font></li>
+</ul>
+<blockquote><b><font color="#00008B">NOTE:</font></b>&nbsp; WebSphere Application
+Server must be installed before you can use this tool, but it does not
+have to be running.</blockquote>
+<font color="#CC33CC">Compiling the tool:</font>
+<br><font color="#3366FF">javac -extdirs &lt;WebSphere install root path>\lib
+jdbctest.java </font><font color="#CC33CC">(compiles this tool and produces
+several class files)</font>
+<p><font color="#CC33CC">Do you want to invoke the tool with with <a href="#db2">DB2</a>
+or <a href="#orcl">Oracle</a>? Choose your database.</font>
+<p><a NAME="db2"></a><font color="#CC33CC">Example: bnvoking the tool with
+DB2:</font>
+<br><font color="#3366FF">java jdbctest</font>
+<br><font color="#000000">JDBC Test starting...</font>
+<br><font color="#000000">Please respond:</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1
+- For DB2</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2
+- For DB2/Pure Network Client</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3
+- For Oracle (thin)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4
+- For Oracle (oci8)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5
+- For Informix</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6
+- For Sybase</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7
+- For Cloudscape - (Local)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8
+- For Cloudscape - (RMI)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9
+- For SQLServer (Microsoft Driver)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10
+- For SQLServer (SequeLink driver)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11
+- For SQLServer (WebSphere driver)</font>
+<br><font color="#3366FF">1</font>
+<br><font color="#000000">Loading jdbc driver: COM.ibm.db2.jdbc.app.DB2Driver</font>
+<br><font color="#000000">COM.ibm.db2.jdbc.app.DB2Driver was loaded successfully</font>
+<br><font color="#000000">Please enter connection URL, e.g:</font>
+<br><font color="#000000">jdbc:db2:dbname or dbname</font>
+<br><font color="#3366FF">was</font>
+<br><font color="#000000">Please enter userid for connection to jdbc:db2:wsa</font>
+<br><font color="#3333FF">guest</font>
+<br><font color="#000000">userid is: 'guest'</font>
+<br><font color="#000000">Please enter password&nbsp;&nbsp;&nbsp; =====>&nbsp;
+WARNING: PASSWORD NOT HIDDEN&nbsp;&nbsp; &lt;======</font>
+<br><font color="#000000">enter 'gui' instead of your password for a secure
+GUI prompt)&nbsp; </font><font color="#CC33CC">(if you enter this literal
+characters gui you get a graphical password prompt)</font>
+<br><font color="#3333FF">guest</font>
+<br><font color="#000000">Connection Successful: COM.ibm.db2.jdbc.app.DB2Connection@201c70f</font>
+<br><font color="#000000">Connection took 120 milliseconds to complete
+</font><font color="#CC33CC">(time it took to perform the database connect)</font>
+<br><font color="#000000">AutoCommit is: true</font>
+<br><font color="#000000">Create new statement...</font>
+<br><font color="#000000">Please enter sql statement to execute...(<b><i>q</i></b>
+to quit)...(or <b><i>maxconn</i></b> to test maximum connections possible
+to database)</font>
+<br><font color="#3333FF">select * from ejsadmin.node_table</font>
+<br><font color="#000000">Operation took 2 milliseconds to complete</font>
+<br><font color="#000000">Just tried true = stmt.execute("select * from
+ejsadmin.node_table");&nbsp; </font><font color="#CC66CC">(true means that
+there additional results to extract)</font>
+<br><font color="#000000">Getting result set...</font>
+<br><font color="#000000">4 column(s) in result</font>
+<br><font color="#000000">INSTANCE_ID&nbsp;&nbsp; TYPE_ID&nbsp;&nbsp; NAME&nbsp;&nbsp;
+DEPLOYEDJARDIR</font>
+<br><font color="#000000">-----------------------------------</font>
+<br><font color="#000000">48,12,asdasd,e:\wsa\deployedEJBs</font>
+<br><font color="#000000">161,12,hany,/usr/WebSphere/AppServer/deployedEJBs</font>
+<p><font color="#000000">Create new statement...</font>
+<br><font color="#000000">Please enter sql statement to execute...(<b><i>q</i></b>
+to quit)...(or <b><i>maxconn</i></b> to test maximum connections possible
+to database)</font>
+<br><font color="#3333FF">q</font>
+<br><font color="#000000">Ok, quitting!</font>
+<br>&nbsp;
+<p><a NAME="orcl"></a><font color="#CC33CC">Example: invoking the tool
+with Oracle:</font>
+<br><font color="#3333FF">java jdbctest</font>
+<br><font color="#000000">JDBC Test starting...</font>
+<br><font color="#000000">Please respond:</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1
+- For DB2</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2
+- For DB2/Pure Network Client</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3
+- For Oracle (thin)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4
+- For Oracle (oci8)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5
+- For Informix</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6
+- For Sybase</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7
+- For Cloudscape - (Local)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8
+- For Cloudscape - (RMI)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9
+- For SQLServer (Microsoft Driver)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10
+- For SQLServer (SequeLink driver)</font>
+<br><font color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11
+- For SQLServer (WebSphere driver)</font>
+<br><font color="#3333FF">3</font>
+<br><font color="#000000">Loading jdbc driver: oracle.jdbc.driver.OracleDriver</font>
+<br><font color="#000000">oracle.jdbc.driver.OracleDriver was loaded successfully</font>
+<br><font color="#000000">Please enter connection URL, e.g:</font>
+<br><font color="#000000">jdbc:oracle:thin:@hostname:1521:dbname or thin:@hostname:1521:dbname</font>
+<br><font color="#3366FF">thin:@pig:1521:orcl</font>
+<br><font color="#000000">Please enter userid for connection to jdbc:oracle:thin:@pig:1521:orcl</font>
+<br><font color="#3333FF">hany</font>
+<br><font color="#000000">userid is: 'hany'</font>
+<br><font color="#000000">Please enter password&nbsp;&nbsp;&nbsp; =====>&nbsp;
+WARNING: PASSWORD NOT HIDDEN&nbsp;&nbsp; &lt;======</font>
+<br><font color="#000000">enter 'gui' instead of your password for a secure
+GUI prompt)&nbsp; </font><font color="#CC33CC">(if you enter this literal
+characters gui you get a graphical password prompt)</font>
+<br><font color="#3366FF">hany</font>
+<br><font color="#000000">Connection Successful: oracle.jdbc.driver.OracleConnection@201c665</font>
+<br><font color="#000000">Connection took 1462 milliseconds to complete
+</font><font color="#CC33CC">(time it took to perform the database connect)</font>
+<br><font color="#000000">AutoCommit is: true</font>
+<br><font color="#000000">Create new statement...</font>
+<br><font color="#000000">Please enter sql statement to execute...(<b><i>q</i></b>
+to quit)...(or <b><i>maxconn</i></b> to test maximum connections possible
+to database)</font>
+<br><font color="#3333FF">select * from person</font>
+<br><font color="#000000">Operation took 20 milliseconds to complete</font>
+<br><font color="#000000">Just tried true = stmt.execute("select * from
+person"); </font><font color="#CC66CC">(true means that there additional
+results to extract)</font>
+<br><font color="#000000">Getting result set...</font>
+<br><font color="#000000">3 column(s) in result</font>
+<br><font color="#000000">ID&nbsp;&nbsp; NAME&nbsp;&nbsp; AGE</font>
+<br><font color="#000000">-----------------------------------</font>
+<br><font color="#000000">1,Hany Salem,77</font>
+<br><font color="#000000">22,Barton,55</font>
+<p><font color="#000000">Create new statement...</font>
+<br><font color="#000000">Please enter sql statement to execute...(<b><i>q</i></b>
+to quit)...(or <b><i>maxconn</i></b> to test maximum connections possible
+to database)</font>
+<br><font color="#3333FF">q</font>
+<br><font color="#000000">Ok, quitting!</font>
+<br>&nbsp;
+</html>
+<pre>
+//
+//  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!");
+	}
+}
+</pre>
 [[Category:Java]]
 [[Category:JDBC]]
 [[Category:Benchmarks]]

Difference between revisions of "JDBC Benchmark"

Latest revision as of 18:52, 24 May 2008

Packaged Benchmark

JDBCTest

jdbctest.java tool

Purpose:

Benefits of the tool:

<a NAME="usage"></a>How to use this tool:

Navigation menu

Search