Help with DataSource config for DB2 needed for newbie

[john4474]

Greetings!

I'm new to JBoss and I'm trying to learn a little about EJB. We have JBoss 3.2.1 and IBM DB2 7.2.0 installed on our development system. I've been able to deploy various example beans successfully and now I'm working on an Entity bean of my own. To make the bean work, it needs to access the installed DB2 database.

My client application fails at the first call to any method inside the bean and JBoss logs the following message to jbossConsole (I've omitted the stack trace)

10:09:11,731 ERROR [STDERR] org.jboss.util.NestedSQLException: Failed to register driver for: COM.ibm.db2.jdbc.app.DB2Driver; - nested throwable: (java.sql.SQLException: java.lang.UnsatisfiedLinkError: no db2jdbc in java.library.path); - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: COM.ibm.db2.jdbc.app.DB2Driver; - nested throwable: (java.sql.SQLException: java.lang.UnsatisfiedLinkError: no db2jdbc in java.library.path))

I have read the messages in this forum and Googled for hints without a lot of luck. So far, I have deployed the DB2 JDBC driver by copying db2java.jar to the deployment directory. Some writers have hinted that this file needs to be placed in the JBOSS-HOME/lib directory, but that doesn't seem to make any difference. I've also deployed the following db2-ds.xml to the deployment directory:


<local-tx-datasource>
<jndi-name>DB2DS</jndi-name>
<connection-url>jdbc:db2:dbname</connection-url>
<driver-class>COM.ibm.db2.jdbc.app.DB2Driver</driver-class>
<user-name>x</usr-name>
y</usr-name>
<min-pool-size>0</min-pool-size>
<max-pool-size>10</max-pool-size>
<idle-timeout-minutes>15</idle-timeout-minutes>
<application-managed-security/>
</local-tx-datasource>


The JDBC driver I'm trying to use is a Type-2 driver. To the best of my knowledge, there isn't a Type-4 driver available for DB2 releases before 8.0

Any and all hints and tips appreciated.

Thanks!

[jonlee]

The .app driver requires the DB2 client installation as it uses the native libraries for DB2 connectivity. I believe this will work as long as the native libraries can be located from within the JVM. You may need to look into the DB2 documentation for this. It is not a configuration I have tried.

You can also employ the .net driver instead, COM.i
bm.db2.jdbc.net.DB2Driver.

In both cases, you will need the db2java.zip of db2java.jar in the server/instance/lib directory of your JBoss installation.

For the .net driver to work, you will need to start the JDBC net listener in your DB2. It will be something like sqllib/java12/db2jstrt 6789 to bind the listener to port 6789 (the standard DB2 JDBC listener port).

Your JDBC URL would be something like:
jdbc:db2://172.16.31.21:6789/MYDB

Hope that helps.

[jonlee]

I meant db2java.zip OR db2java.jar. I guess it will depend on your distribution.

Also be careful to ensure that the DB2 JDBC library (JAR) is the same one that came from your DB2 installation/distribution. We spent half a day trying to figure out why we kept breaking the JDBC pool when we tried to run a SQL statement. Turns out the 7.3 Linux driver was not compatible with the 7.3 AIX DB2 installation to which we were trying to connect.

[john4474]

> The .app driver requires the DB2 client installation
> as it uses the native libraries for DB2 connectivity.
> I believe this will work as long as the native
> libraries can be located from within the JVM. You may
> need to look into the DB2 documentation for this. It
> is not a configuration I have tried.
>
> You can also employ the .net driver instead, COM.i
> bm.db2.jdbc.net.DB2Driver.
>
> In both cases, you will need the db2java.zip of
> db2java.jar in the server/instance/lib
> directory of your JBoss installation.
>
> For the .net driver to work, you will need to start
> the JDBC net listener in your DB2. It will be
> something like sqllib/java12/db2jstrt 6789 to bind
> the listener to port 6789 (the standard DB2 JDBC
> listener port).
>
> Your JDBC URL would be something like:
> jdbc:db2://172.16.31.21:6789/MYDB
>
> Hope that helps.

I've used the NET driver before. Because the APP driver uses native libraries, only one servlet in a Tomcat server could access the database. I had hoped that establishing a single connection pool in the JBoss server would allow all of its deployed applications to access the higher performance APP driver.

Could this be an issue where JBoss can't find the native libraries it needs? Do I need special configuration information for this?

Thanks!

[jonlee]

It could be the issue, I suppose. But you are not even getting one connection, I think. The Java error is basically saying that the JVM can't bridge to the external library - the *.so in Unix world.

You would need special configuration information - the same as if you were a remote client to DB2. So install the DB2 client for the account with which you are starting JBoss - so you'll have the sqllib directory available locally.

The environment would then have for example (for an account JBoss):

LD_LIBRARY_PATH=:/home/JBoss/sqllib/lib

The install will probably create the executable paths as well but from memory, I don't think you need it.

Test that you can connect to the DB with the installed client.

Now if you are still having problems, try writing a simple Java JDBC client first (without JBoss) and see if you can connect. If you can, next try doing the same in a JBoss/Tomcat or JBoss/Jetty (a simple servlet) without using the connection pool. After that, when things are working, your environment should be confirmed and you should be able to use the connection pool, knowing everything is working.

I don't remember anything else special being covered in the DB2 training. I'd have to look through the training notes again to be certain.

Either driver shouldn't give you any constraints for number of connections. We've used the .net driver without any problems and you can have multiple sessions to DB2 from the admin client with multiple logins.

We have used 7.3 UDB and now 8.1 UDB. The only reason we upgraded from the original 7.1 release was due to lack of sequence support to problems with sequences. I think patch 6 finally fixed the sequence problem in the 7 series.

That's about it.

[john4474]

> It could be the issue, I suppose. But you are not
> even getting one connection, I think. The Java error
> is basically saying that the JVM can't bridge to the
> external library - the *.so in Unix world.
>
> You would need special configuration information -
> the same as if you were a remote client to DB2. So
> install the DB2 client for the account with which you
> are starting JBoss - so you'll have the sqllib
> directory available locally.
>
> The environment would then have for example (for an
> account JBoss):
>
> LD_LIBRARY_PATH=:/home/JBoss/sqllib/lib
>
> The install will probably create the executable paths
> as well but from memory, I don't think you need it.
>
> Test that you can connect to the DB with the
> installed client.
>
> Now if you are still having problems, try writing a
> simple Java JDBC client first (without JBoss) and see
> if you can connect. If you can, next try doing the
> same in a JBoss/Tomcat or JBoss/Jetty (a simple
> servlet) without using the connection pool. After
> that, when things are working, your environment
> should be confirmed and you should be able to use the
> connection pool, knowing everything is working.
>
> I don't remember anything else special being covered
> in the DB2 training. I'd have to look through the
> training notes again to be certain.
>
> Either driver shouldn't give you any constraints for
> number of connections. We've used the .net driver
> without any problems and you can have multiple
> sessions to DB2 from the admin client with multiple
> logins.
>
> We have used 7.3 UDB and now 8.1 UDB. The only reason
> we upgraded from the original 7.1 release was due to
> lack of sequence support to problems with sequences.
> I think patch 6 finally fixed the sequence problem in
> the 7 series.
>
> That's about it.

Thanks for all your help.

I've given up on the APP driver (for now) with the hope that I'll be able to upgrade to 8.x sometime in the near future. When I do that, I'll try to use the 'universal' driver that comes with 8.x.

I have a DataSource configured using the NET driver, but I've had some side-effects of having to restart both JBoss and DB2 after making changes to the db2-ds.xml file.

Thanks again!

[john4474]

The problem is solved now.

You can use the DB2 Type-2 JDBC driver with JBoss.

In the db2-ds.xml you deploy, the driver (for DB2 v7.2) should be:

COM.ibm.db2.jdbc.app.DB2Driver

and the URL should be of the form:

jdbc:db2:database-name

The database name must be in the local systems DB2 catalog. To make JBoss find all of the necessary libraries and executables, add the following line to the bottom of you run.conf file:

. ~db2-database-instance/sqllib/db2profile

This will put the necessary native library directories in LD_LIBRARY_PATH (Linux) and update the PATH to include the DB2 command interpreter which is required for the T-2 driver. To insure that the correct JDBC driver is used, I copied:

cp $DB2_HOME/V7.1/java12/db2java.zip $JAVA_HOME/jre/lib/ext/db2java.jar

Magic.

Thanks to everyone for their help and suggestions.