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 is
relevant or true with Derby Client or if claims of performance improvement with JCC ever were true.
I'll just throw it out there to see if someone is interested in looking to see
its relevance and
worthiness of a Jira issue for Network Server when running with Derby client. If nobody is
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
Detail:
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);
conn.setAutoCommit(false);
Statement s = conn.createStatement();
System.out.println("Connected to database");
setUpTest(s);
hangRepro(conn);
conn.rollback();
conn.close();
}
catch (Throwable e) {
System.out.println("exception thrown:");
System.out.println(e);
e.printStackTrace();
}
}
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("deferPrepares","false");
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";
}
else
{
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
//dumpParameterMetaData(paramMetaData);
ps.execute();
}
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) {
System.out.println(t.toString());
t.printStackTrace(System.out);
}
}
}
//print the parameter mode in human readable form
static String parameterModeInStringForm(int mode){
if (mode == ParameterMetaData.parameterModeIn)
return("PARAMETER_MODE_IN");
else if (mode == ParameterMetaData.parameterModeInOut )
return("PARAMETER_MODE_IN_OUT");
else if (mode == ParameterMetaData.parameterModeOut)
return("PARAMETER_MODE_OUT");
else if (mode == ParameterMetaData.parameterModeUnknown)
return("PARAMETER_MODE_UNKNOWN");
else
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)
return("PARAMETER_NO_NULLS");
else if (nullabilityValue ==
ParameterMetaData.parameterNullable)
return("PARAMETER_NULLABLE");
else if (nullabilityValue ==
ParameterMetaData.parameterNullableUnknown)
return("PARAMETER_NULLABLE_UNKNOWN");
else
return("ERROR: donot recognize this parameter isNullable()
value");
}
}
