DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
You might want to write a single class that includes methods for tracing under the DriverManager interface, as well as the DataSource interface.
The following example shows such a class. The example uses IBM® Data Server Driver for JDBC and SQLJ type 4 connectivity.
public class TraceExample
{
public static void main(String[] args)
{
sampleConnectUsingSimpleDataSource();
sampleConnectWithURLUsingDriverManager();
}
private static void sampleConnectUsingSimpleDataSource()
{
java.sql.Connection c = null;
java.io.PrintWriter printWriter =
new java.io.PrintWriter(System.out, true);
// Prints to console, true means
// auto-flush so you don't lose trace
try {
javax.sql.DataSource ds =
new com.ibm.db2.jcc.DB2SimpleDataSource();
((com.ibm.db2.jcc.DB2BaseDataSource) ds).setServerName("sysmvs1.stl.ibm.com");
((com.ibm.db2.jcc.DB2BaseDataSource) ds).setPortNumber(5021);
((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDatabaseName("san_jose");
((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDriverType(4);
ds.setLogWriter(printWriter); // This turns on tracing
// Refine the level of tracing detail
((com.ibm.db2.jcc.DB2BaseDataSource) ds).
setTraceLevel(com.ibm.db2.jcc.DB2SimpleDataSource.TRACE_CONNECTS |
com.ibm.db2.jcc.DB2SimpleDataSource.TRACE_DRDA_FLOWS);
// This connection request is traced using trace level
// TRACE_CONNECTS | TRACE_DRDA_FLOWS
c = ds.getConnection("myname", "mypass");
// Change the trace level to TRACE_ALL
// for all subsequent requests on the connection
((com.ibm.db2.jcc.DB2Connection) c).setJccLogWriter(printWriter,
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL);
// The following INSERT is traced using trace level TRACE_ALL
java.sql.Statement s1 = c.createStatement();
s1.executeUpdate("INSERT INTO sampleTable(sampleColumn) VALUES(1)");
s1.close();
// This code disables all tracing on the connection
((com.ibm.db2.jcc.DB2Connection) c).setJccLogWriter(null);
// The following INSERT statement is not traced
java.sql.Statement s2 = c.createStatement();
s2.executeUpdate("INSERT INTO sampleTable(sampleColumn) VALUES(1)");
s2.close();
c.close();
}
catch(java.sql.SQLException e) {
com.ibm.db2.jcc.DB2ExceptionFormatter.printTrace(e,
printWriter, "[TraceExample]");
}
finally {
cleanup(c, printWriter);
printWriter.flush();
}
}
// If the code ran successfully, the connection should
// already be closed. Check whether the connection is closed.
// If so, just return.
// If a failure occurred, try to roll back and close the connection.
private static void cleanup(java.sql.Connection c,
java.io.PrintWriter printWriter)
{
if(c == null) return;
try {
if(c.isClosed()) {
printWriter.println("[TraceExample] " +
"The connection was successfully closed");
return;
}
// If we get to here, something has gone wrong.
// Roll back and close the connection.
printWriter.println("[TraceExample] Rolling back the connection");
try {
c.rollback();
}
catch(java.sql.SQLException e) {
printWriter.println("[TraceExample] " +
"Trapped the following java.sql.SQLException while trying to roll back:");
com.ibm.db2.jcc.DB2ExceptionFormatter.printTrace(e, printWriter,
"[TraceExample]");
printWriter.println("[TraceExample] " +
"Unable to roll back the connection");
}
catch(java.lang.Throwable e) {
printWriter.println("[TraceExample] Trapped the " +
"following java.lang.Throwable while trying to roll back:");
com.ibm.db2.jcc.DB2ExceptionFormatter.printTrace(e,
printWriter, "[TraceExample]");
printWriter.println("[TraceExample] Unable to " +
"roll back the connection");
}
// Close the connection
printWriter.println("[TraceExample] Closing the connection");
try {
c.close();
}
catch(java.sql.SQLException e) {
printWriter.println("[TraceExample] Exception while " +
"trying to close the connection");
printWriter.println("[TraceExample] Deadlocks could " +
"occur if the connection is not closed.");
com.ibm.db2.jcc.DB2ExceptionFormatter.printTrace(e, printWriter,
"[TraceExample]");
}
catch(java.lang.Throwable e) {
printWriter.println("[TraceExample] Throwable caught " +
"while trying to close the connection");
printWriter.println("[TraceExample] Deadlocks could " +
"occur if the connection is not closed.");
com.ibm.db2.jcc.DB2ExceptionFormatter.printTrace(e, printWriter,
"[TraceExample]");
}
}
catch(java.lang.Throwable e) {
printWriter.println("[TraceExample] Unable to " +
"force the connection to close");
printWriter.println("[TraceExample] Deadlocks " +
"could occur if the connection is not closed.");
com.ibm.db2.jcc.DB2ExceptionFormatter.printTrace(e, printWriter,
"[TraceExample]");
}
}
private static void sampleConnectWithURLUsingDriverManager()
{
java.sql.Connection c = null;
// This time, send the printWriter to a file.
java.io.PrintWriter printWriter = null;
try {
printWriter =
new java.io.PrintWriter(
new java.io.BufferedOutputStream(
new java.io.FileOutputStream("/temp/driverLog.txt"), 4096), true);
}
catch(java.io.FileNotFoundException e) {
java.lang.System.err.println("Unable to establish a print writer for trace");
java.lang.System.err.flush();
return;
}
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
}
catch(ClassNotFoundException e) {
printWriter.println("[TraceExample] " +
"IBM Data Server Driver for JDBC and SQLJ type 4 connectivity " +
"is not in the application classpath. Unable to load driver.");
printWriter.flush();
return;
}
// This URL describes the target data source for Type 4 connectivity.
// The traceLevel property is established through the URL syntax,
// and driver tracing is directed to file "/temp/driverLog.txt"
// The traceLevel property has type int. The constants
// com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS and
// com.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTS represent
// int values. Those constants cannot be used directly in the
// first getConnection parameter. Resolve the constants to their
// int values by assigning them to a variable. Then use the
// variable as the first parameter of the getConnection method.
String databaseURL =
"jdbc:db2://sysmvs1.stl.ibm.com:5021" +
"/sample:traceFile=/temp/driverLog.txt;traceLevel=" +
(com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS |
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTS) + ";";
// Set other properties
java.util.Properties properties = new java.util.Properties();
properties.setProperty("user", "myname");
properties.setProperty("password", "mypass");
try {
// This connection request is traced using trace level
// TRACE_CONNECTS | TRACE_DRDA_FLOWS
c = java.sql.DriverManager.getConnection(databaseURL, properties);
// Change the trace level for all subsequent requests
// on the connection to TRACE_ALL
((com.ibm.db2.jcc.DB2Connection) c).setJccLogWriter(printWriter,
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL);
// The following INSERT is traced using trace level TRACE_ALL
java.sql.Statement s1 = c.createStatement();
s1.executeUpdate("INSERT INTO sampleTable(sampleColumn) VALUES(1)");
s1.close();
// Disable all tracing on the connection
((com.ibm.db2.jcc.DB2Connection) c).setJccLogWriter(null);
// The following SQL insert code is not traced
java.sql.Statement s2 = c.createStatement();
s2.executeUpdate("insert into sampleTable(sampleColumn) values(1)");
s2.close();
c.close();
}
catch(java.sql.SQLException e) {
com.ibm.db2.jcc.DB2ExceptionFormatter.printTrace(e, printWriter,
"[TraceExample]");
}
finally {
cleanup(c, printWriter);
printWriter.flush();
}
}
}