How to know one is using a Type 4 Driver with DB2

[Brian Mulholland]

I am working on a J2EE app that has been around awhile. We seem to have finally convinced everyone of the need to upgrade to type 4 drivers. Problem is that I haven't done this in awhile and I seem to have forgotten...well, everything. So I have a couple questions:

1) Are there any known side effects of switching from type 2 to type 4 drivers in DB2 (ie code/formatting subtleties that one might depend upon). Someone has suggested that the app presently updates dates with mm/dd/yyyy and that type 4 had problems with that. Among my tasks is verifying this and other differences.

2) Pursuant to #1, I am writing a main-line to test various aspects in each driver, but have been having trouble. I seem to have finally gotten a connection with db2jcc.jar and the license jar instead of db2java.zip and the below code. The problem is that I need to be sure of which driver I am using (type 2 or 4). The DataBaseMetaData.getDriverVersion seems to be the VERSION of the driver, not it's TYPE. The below prints 2.11.65. How can I get certainty that I am using the type 4 or type 2 so that I can run my tests against each and be confident that they work in both? Is there a method to give me such certainty?

    
Class.forName("com.ibm.db2.jcc.DB2Driver"); 
conn = DriverManager.getConnection("jdbc:db2://1.2.3.4:60004/dbname", "me", "mypassword");
DatabaseMetaData dmd = conn.getMetaData();
System.out.print("Type:");
System.out.println(dmd.getDriverVersion());

3) In RAD, the data sources specified in the WAS admin console don't have any relationship to main() method DriverManager connections do they?

[Brian Mulholland]

Update:

So I configured a type 4 driver in my was admin console and the following came out in the console log:

[3/13/09 12:58:28:390 EDT] 0000001a InternalGener I DSRA8205I: JDBC driver name : IBM DB2 JDBC 2.0 Type 2
[3/13/09 12:58:28:390 EDT] 0000001a InternalGener I DSRA8206I: JDBC driver version : 08.02.0009
[3/13/09 12:58:28:421 EDT] 0000001a WSRdbDataSour I DSRA8208I: JDBC driver type : 4

I like how line 1 says Type 2 but line 3 says Type 4. Is there ANY way I can funtionally test to PROVE which is in use? The thing that makes this worse is that I've read that jcc supports both Type 2 and Type 4.

[Ulf Dittmer]

Type 2 drivers include a native code component, like a DLL or a shared library. Check the PATH for whether its constituent directories contain any such libraries.

[Brian Mulholland]

Ulf Dittmer wrote:Type 2 drivers include a native code component, like a DLL or a shared library. Check the PATH for whether its constituent directories contain any such libraries.

Hmm, when running in main() the classpath only includes the jcc and license jars rather than a directory reference. The same is true with my webapp's build path. I know there is some difference between CLASSPATH and PATH when working with javac, but within RAD/eclipse, I am unsure of the distinction or where to look for it.

[Ulf Dittmer]

PATH is a feature of the shell, not of any particular process running in it (like an IDE or javac). On a Unix-ish OS, type "echo $PATH" on the command line; it will print a list of the directories that make up the PATH. The OS will look in these directories for native libraries.

Also check the value of the Java system property "java.library.path"; it may have additional directories used just by the JVM.

[Brian Mulholland]

Now that I know, i should mention that I have found two differences between the type 2 CLI driver and the type 4 universal driver in DB2. This was part 1 of my initial post.

1) The CLi driver allows trailing semi-colons. "SELECT * FROM MYTABLE;" is fine in CLI, but throws exception in universal type 4.

2) The CLI driver allowed empty string binds to integer columns, but the universal does not. A stmt such as SELECT * FROM MYTABLE WHERE INTEGERFIELD=? which then has a setString() invoked and one passed a zero-length string ("") into that setString(), the CLI driver was at least not blowing up (we presume it treated this as zero), but under the universal driver, this situation produces an exception.

If we find more of these I will try to remember to post them here.

[Brian Mulholland]

We have found another difference between the CLI and Universal driver. The CLI driver allowed a Zerouary timestamp. "00.00.00" bound to a parameter going to a timestamp didn't produce an exception. It didn't produce a match for that select clause (thank goodness) but it allowed it, and type 4 doesn't.