db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kathey Marsden <kmarsdende...@sbcglobal.net>
Subject Old Network Server improvement suggestion covered with cobwebs
Date Sat, 03 Jun 2006 00:34:14 GMT
I am doing some Spring cleaning of old todo items and found this 
pre-contribution peformance improvement suggestion for Network Server.  I am not sure if this
relevant or true with Derby Client or if claims of performance improvement with JCC ever were

I'll just throw it out there to see if someone is interested in looking to see its relevance
worthiness of a Jira issue for  Network Server when running with Derby client.   If nobody
interested it can just collect more cobwebs in the archives.  
Below is exactly what I have from way back when old URL's and all:

Suggestion: Change Network Server to support client sending parameters in Server encoding
Component: Network Server
JCC now can send the parameter 
data in the target server encoding. This should help improve 
our insert performance.  For now this feature is being disabled 
for Cloudscape because it causes hangs and other issues.

Program below shows scenario where JCC would send the 
overrides to DB2 to send in the target Server encoding.

import java.sql.*;
import java.util.Properties;
import com.ibm.db2j.util.JDBCDisplayUtil;
import java.math.BigDecimal;

import com.ibm.db2j.functionTests.TestUtil;
import com.ibm.db2j.testing.Formatters;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.ResultSetMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Clob;
import java.sql.Blob;

import com.ibm.db2j.tools.ijImpl.util;
import java.io.*;
import java.sql.PreparedStatement;
import java.util.Properties;

public class paramMeta{

  public static void main (String[] args) {
	  try {
		  String conntype = "db2jnet";
		  if (args.length >= 1)			
			  conntype = args[0].toLowerCase();
		  Connection conn = initConn(conntype);
		  Statement s = conn.createStatement();
		  System.out.println("Connected to database");

    catch (Throwable e) {
      System.out.println("exception thrown:");


	public static  Connection initConn(String conntype) throws Exception

		Connection connection = null;
		String driver = null;
		String url = null;
		Properties connInfo = new Properties();
		String password = System.getProperty("password");
		if (password == null)
			password = "db2admin";

		connInfo.put("user", "db2admin");
		connInfo.put("password", password);
		connInfo.put("retrieveMessagesFromServerOnGetMessage", "true");
		connInfo.put("traceFile","trace." + conntype +".out");

		if (conntype.equals("db2app"))
				driver = "COM.ibm.db2.jdbc.app.DB2Driver";
				url = "jdbc:db2:wombatE;create=true";

		else if (conntype.equals("db2jcc"))
				driver = "com.ibm.db2.jcc.DB2Driver";
				url = "jdbc:db2://localhost:50000/WOMBAT";
		else if (conntype.equals("db2jnet"))
			driver = "com.ibm.db2.jcc.DB2Driver";
			//url =  "jdbc:db2j:net://localhost:1527/wombat;create=true:fullyMaterializeLobData=true;retrieveMessagesFromServerOnGetMessage=true;";
			url = "jdbc:db2j:net://localhost:1527/wombat;create=true";
		else if (conntype.equals("db2j"))
			driver = "com.ibm.db2j.jdbc.DB2jDriver";
			url =  "jdbc:db2j:wombat;create=true";

			System.err.println("Invalid connection type: " + conntype);
			System.err.println("Usage: java atest [DB2jcc | DB2app | DB2jNet | DB2j]");
			return null;

		System.out.println("Initializing connection type: " + conntype);
		System.out.println("driver: " + driver);
		System.out.println("url: " + url);

		// Initialize Driver. 
		Driver driverClass = (Driver) Class.forName(driver).newInstance();

		System.out.println("Obtaining the connection");
		connection = DriverManager.getConnection(url,connInfo);

		DatabaseMetaData met = connection.getMetaData();
		System.out.println("Product Name: " + met.getDatabaseProductName());
		System.out.println("Driver Name: " + met.getDriverName());
		return connection;

	//Set up the test by creating the table used by the rest of the test.
	static void setUpTest(Statement s)
					throws SQLException {
		/* Create a table */
		try {
			s.execute("drop table t");
		catch(SQLException  se) {}

		s.execute("create table t ( "+
				  /* 1 */ "c char(5), "+
				  /* 2 */ "iNoNull int not null, "+
				  /* 3 */ "i int, "+
				  /* 4 */ "de decimal, "+
				  /* 5 */ "d date)");


	static void hangRepro (Connection con) throws SQLException

		ParameterMetaData paramMetaData = null;
		PreparedStatement ps = null;
		CallableStatement cs = null;
		Statement s = con.createStatement();

      //next testing a prepared statement
      ps = con.prepareStatement("insert into t values(?, ?, ?, ?, ?)");
      ps.setNull(1, java.sql.Types.CHAR);
      ps.setInt(2, 1);
      ps.setNull(3, java.sql.Types.INTEGER);
      ps.setBigDecimal(4,new BigDecimal("1"));
      ps.setNull(5, java.sql.Types.DATE);

      paramMetaData = ps.getParameterMetaData();
      System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
      // JCC seems to report these parameters as MODE_UNKNOWN, where as Cloudcape uses MODE_IN
      // JCC behaviour with network server matches its behaviour with DB2, so cann't do much...
      // getPrecision() returns 0 for CHAR/DATE/BIT types for Cloudscape. JCC shows maxlen


	static void dumpParameterMetaData(ParameterMetaData paramMetaData) throws SQLException {
		int numParam = paramMetaData.getParameterCount();
		for (int i=1; i<=numParam; i++) {
			try {
			System.out.println("Parameter number : " + i);
			System.out.println("parameter isNullable " + parameterIsNullableInStringForm(paramMetaData.isNullable(i)));
			System.out.println("parameter isSigned " + paramMetaData.isSigned(i));
			System.out.println("parameter getPrecision " + paramMetaData.getPrecision(i));
			System.out.println("parameter getScale " + paramMetaData.getScale(i));
			System.out.println("parameter getParameterType " + paramMetaData.getParameterType(i));
			System.out.println("parameter getParameterTypeName " + paramMetaData.getParameterTypeName(i));
			System.out.println("parameter getParameterClassName " + paramMetaData.getParameterClassName(i));
			System.out.println("parameter getParameterMode " + parameterModeInStringForm(paramMetaData.getParameterMode(i)));
			} catch (Throwable t) {
//print the parameter mode in human readable form
	static String parameterModeInStringForm(int mode){
		if (mode ==  ParameterMetaData.parameterModeIn)
		else if (mode ==  ParameterMetaData.parameterModeInOut )
		else if (mode ==  ParameterMetaData.parameterModeOut)
		else if (mode ==  ParameterMetaData.parameterModeUnknown)
				  return("ERROR: donot recognize this parameter mode");

	//print the parameter isNullable value in human readable form
	static String parameterIsNullableInStringForm(int nullabilityValue){
		if (nullabilityValue ==  ParameterMetaData.parameterNoNulls)
		else if (nullabilityValue ==  ParameterMetaData.parameterNullable)
		else if (nullabilityValue ==  ParameterMetaData.parameterNullableUnknown)
			return("ERROR: donot recognize this parameter isNullable() value");

View raw message