Saturday 28 July 2012

#ibmi hints and tips #14 : JDBC drivers

As an extension to my recent DB2 on IBM i post, here is a quick summary of the JDBC driver situation. As usual, please let me know if you spot any errors/omissions/lack of clarity.

Overall there are two options: DRDA/DB2 Connect, and the Java Toolbox.

DRDA/DB2 Connect JDBC drivers

Three different ones for different situations.

Where the Java is running on IBM i itself

Native DRDA JDBC driver:
  • no need to put a jar or zip file in your classpath
  • com.ibm.db2.jdbc.app.DB2Driver
  • jdbc:db2:(database-name-from-WRKRDBDIRE)
Very fast, resilient and secure, as just mentioned no need to find jar or add to classpath, but has some JDBC support limitations and is not IBM strategic.

If database is *LOCAL (and potentially if database is on another IBM i system, depending on WRKRDBDIRE configuration) there is no need to specify userid/password.

Where the Java is running on other platforms (chargeable)

If DB2 Connect client installed

  • db2java.zip
  • COM.ibm.db2.jdbc.app.DB2Driver [note subtle distinction between this and the driver class name mentioned above]
  • jdbc:db2:(name-from-DB2-Configuration-Assistant)

Otherwise

  • db2jcc.jar plus separate licence jar
  • com.ibm.db2.jcc.DB2Driver
  • jdbc:db2://(IBM-i-hostname):50000/(database-name-from-WRKRDBDIRE)

Wherever the Java is running

Served on IBM i by the *DDM TCP/IP server, unless *LOCAL (and unless WRKRDBDIRE is configured to use SNA).

Not really recommended (due to complexity, lack of IBM investment, and DB2 Connect cost) unless the application needs to be portable between DB2 platforms.

IBM i Toolbox for Java JDBC driver

Only one for all situations.
  • jt400.jar
  • com.ibm.as400.access.AS400JDBCDriver [sorry, it really is still called that]
  • jdbc:as400:(IBM-i-host-name)
No installed client required, can run on any platform, fewer JDBC support limitations than the DRDA driver, IBM strategic, free of charge.

Needs adding to classpath but otherwise very simple.

If running on IBM i itself, use /QIBM/ProdData/java400/jt400ntv.jar version, or optimise jt400.jar to level 40 with CRTJVAPGM.

Turn on the 'translate binary' option if you have tables with CCSID 65535 (as is common - next blog post will probably be on CCSIDs and code pages, incidentally).

Served on IBM i by the *DATABASE host server.

Recommended (much as I like using DRDA in other situations).

No comments:

Post a Comment