Friday, October 24, 2008

SQL comparison updates: Informix, JDBC

A reader of my SQL implementation comparison page suggested that I keep a change log for the page. Currently, I don't want to keep a detailed log, but I'll add blog posts here when I've made changes which are somewhat significant, tagging the posts 'sqlpage'.

Today, I added a new section related to JDBC, because I'm tired of having to spend time looking up details like JDBC driver names and connection URLs in manuals. (I don't work much with Java, so I keep forgetting where to find this stuff when I need it.) The section is far from complete.

I also started coverage of the Informix database, as I've recently become responsible for an important Informix database at work. Informix was once a very important player in the database game, but the story goes that the Informix company suddenly started getting managed very badly -- and at some point IBM bought Informix. Since then, IBM's strategy for Informix has been rather unclear. I have a personal, very weakly founded theory: I think IBM is positioning Informix as the place where the exciting new developments happen first; and when the new features are known to work well, they are introduced to DB2. This may not be a bad role for Informix.

2 comments:

waveform said...

Hi Troels - couple of minor points with regard to the excellent comparison page: depending on one's perspective the join types table may need an update or two...

DB2 for i5/OS has supported CROSS JOIN and the USING clause for some time now. DB2 9.5 for LUW added CROSS JOIN as syntactic sugar for INNER JOIN ON 0=0 (but annoyingly haven't documented it, and it still doesn't implement the USING clause). DB2 for z/OS supports neither.

Also, for the JDBC section at the end, DB2 has several jar files:

db2java.zip is the (deprecated) JDBC Type 2 (hybrid) driver. This requires a full DB2 client installation to operate, and uses the URL format:

jdbc:db2:database[;param=value;param=value...]

Where database is the local alias of a database in the client's catalog. Full syntax here.

db2jcc.jar is the JDBC v3 Type 4 driver (the most commonly used?) and db2jcc4.jar is the JDBC v4 Type 4 driver. Being pure Java, only the driver jar itself is required for either of these two. However, when talking to a host database (DB2 for i5/OS or z/OS) a separate "license" jar is required (can't recall the filename off the top of my head). The URL format for these two is:

jdbc:db2://hostname[:port]/database[:param=value;param=value...]

Where hostname is the hostname of the DB2 server, :port is the (optional) port number (defaults to 446 if omitted for some bizarre reason, given the default most people use is 50000 or 60000), and database is the name of the database on the server (note, not the local database alias in the client catalog - as this driver doesn't use the client catalog). Full syntax here.

Other than that it's looking excellent!

Troels Arvin said...

@waveform: Thanks a lot for your comments; I hope to update the page soon.

I have no plans about describing DB2 for AS400 nor mainframe, as I don't ever use them.

As for the DB2 JDBC driver stuff: I generally try to focus on the most important aspects, and then leave the details to the documentation links. So I'll probably not expand much on the DB2 JDBC driver issue, except maybe include a note about the JDBC3 driver jar file name.