Spring Roo DB2 introspection fails

[saihegde]

I have been trying to reverse engineer tables in DB2.
I did a mvn:install of the db2jcc driver jars but the introspection fails with the following

addon search --requiresDescription "#jdbcdriver,driverclass:com.ibm.db2.jcc.DB2Driver " found no matches
JDBC driver not available for 'com.ibm.db2.jcc.DB2Driver'

Am I missing anything?

Thanks!
Sai

[Alan Stewart]

You will need to wrap the DB2 driver to make it compatible with OSGi for use in Roo. The DB2 driver is a licensed piece of software and we can't host OSGi versions of the driver in any public Maven repository. Alternatively, I assume you have a support contract with IBM, so perhaps you can approach them to supply you with an OSGi-wrapped driver. After all they are one of the OSGi founders.

[saihegde]

Thanks Alan!
One question though, to work with Spring Roo do all components have to be OSGi compatible?
-Sai

[Alan Stewart]

Any component that needs to work in the Roo shell needs to be OSGi compliant.

Obviously, Roo-generated user projects do not have such a dependency. Thus, while Roo can generate a project to use DB2 as its database when the application is spun up in a web container or in unit tests, to reverse engineer a DB2 database, Roo itself needs to use an OSGI-enabled DB2 driver.

I have tried to OSGi-ify the driver from the DB2 community edition some time ago. There appears to be three jars required which made it tedious to do. I shall get back to it some time and at the very least provide the pom for our addon-creator to make the jars work.

[saihegde]

Thanks Alan, that was helpful.

[wallenborn]

The pom.xml apparently needs only very little tweaking. A solution for a very similar problem with Oracle JDBC drivers is described here, and Alan's suggestion in that thread can be translated to db2 straightforwardly. Using the db2 driver in db2jcc4.jar from the DB2 Express C version 9.7.2, i followed the same procedure, adding this Import-Package element in the generated pom.xml:

 <Import-Package>COM.ibm.db2os390.*;resolution:=optional,com.ibm.websphere.*;resolution:=optional,javax.*;resolution:=optional,org.w3c.*;resolution:=optional,org.xml.*;resolution:=optional,sqlj.*;resolution:=optional,org.ietf.jgss;resolution:=optional,sun.*;resolution:=optional</Import-Package>

and one "osgi start -url <filename>" later DBRE was working.

[saihegde]

Awesome... I'll give that a shot.

[Alan Stewart]

Using the db2 driver in db2jcc4.jar from the DB2 Express C version 9.7.2, i followed the same procedure, adding this Import-Package element in the generated pom.xml:

Great stuff!
Did you only require just the one jar? Can you post the full pom.xml for the wrapping? I will try it out and if OK, I will add it to the Roo wrapping directory.

[wallenborn]

Sure, here you are:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.ibm.db2.roo.jdbc</groupId>
	<artifactId>${pom.groupId}.${pkgArtifactId}</artifactId>
	<packaging>bundle</packaging>
	<version>${osgiVersion}</version>
	<name>com-ibm-db2-roo-jdbc</name>
	<description>This bundle wraps the standard Maven artifact: ${pkgArtifactId}-${pkgVersion}.</description>
	<properties>
		<pkgArtifactId>db2jcc4</pkgArtifactId>
		<pkgVersion>9.7.2</pkgVersion>
		<osgiVersion>${pkgVersion}.0001</osgiVersion>
		<pkgVendor>IBM</pkgVendor>
		<pkgDocUrl/>
		<pkgLicense>http://www-01.ibm.com/software/data/db2</pkgLicense>
		<repo.folder>com/ibm/db2/roo/jdbc</repo.folder>
		<google.code.project.name>${project.name}</google.code.project.name>
	</properties>

	<dependencies>
		<dependency>
			<groupId>com.ibm</groupId>
			<artifactId>db2jcc4</artifactId>
			<version>9.7.2</version>
			<optional>true</optional>
		</dependency>
	</dependencies>
	<scm>
		<connection>scm:svn:https://${google.code.project.name}.googlecode.com/svn/trunk</connection>
		<developerConnection>scm:svn:https://${google.code.project.name}.googlecode.com/svn/trunk</developerConnection>
		<url>http://code.google.com/p/${google.code.project.name}/source/browse</url>
	</scm>
	<distributionManagement>
		<repository>
			<id>Google Code</id>
			<url>dav:https://${google.code.project.name}.googlecode.com/svn/repo</url>
		</repository>
	</distributionManagement>
	<build>
		<extensions>
			<extension>
				<groupId>org.apache.maven.wagon</groupId>
				<artifactId>wagon-webdav-jackrabbit</artifactId>
				<version>1.0-beta-6</version>
			</extension>
		</extensions>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-gpg-plugin</artifactId>
				<version>1.1</version>
				<executions>
					<execution>
						<id>sign-artifacts</id>
						<phase>verify</phase>
						<goals>
							<goal>sign</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
			<plugin>
				<groupId>org.apache.felix</groupId>
				<artifactId>maven-bundle-plugin</artifactId>
				<extensions>true</extensions>
				<configuration>
					<remoteOBR>true</remoteOBR>
					<bundleUrl>httppgp://${google.code.project.name}.googlecode.com/svn/repo/${repo.folder}/${project.artifactId}/${project.version}/${project.artifactId}-${project.version}.jar</bundleUrl>
					<instructions>
						<Bundle-SymbolicName>${pom.artifactId}</Bundle-SymbolicName>
						<Export-Package>*;version=${pom.version}</Export-Package>
						<Bundle-Vendor>${pkgVendor} (wrapped into an OSGi bundle by the Spring Roo project build system)</Bundle-Vendor>
						<Bundle-DocURL>${pkgDocUrl}</Bundle-DocURL>
						<Bundle-License>${pkgLicense}</Bundle-License>
                                                <Import-Package>COM.ibm.db2os390.*;resolution:=optional,com.ibm.websphere.*;resolution:=optional,javax.*;resolution:=optional,org.w3c.*;resolution:=optional,org.xml.*;resolution:=optional,sqlj.*;resolution:=optional,org.ietf.jgss;resolution:=optional,sun.*;resolution:=optional</Import-Package>
					</instructions>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

The full procedure with db2jcc4.jar in the .m2 repository and a database named foo on localhost is:

1. Create 2 projects: wrapper and dbre.

2. In wrapper, start roo:

addon create wrapper --topLevelPackage com.ibm.db2.roo.jdbc --groupId com.ibm --artifactId db2jcc4 --version 9.7.2 --vendorName IBM --licenseUrl  http://www-01.ibm.com/software/data/db2
quit

3. Edit the pom.xml to include the Import-Package line

4. mvn bundle:bundle

5. Copy the jar from wrapper/target to wrapper (i'm too lazy to figure out the correct --url file syntax). Start roo again:

osgi start --url file:com.ibm.db2.roo.jdbc.db2jcc4-9.7.2.0001.jar
quit

6. In dbre, start roo:

project --topLevelPackage com.example
persistence setup --provider HIBERNATE --database DB2 --hostName localhost --databaseName foo --userName foo --password bar
dependency remove --groupId com.ibm --artifactId db2jcc --version 3.59.81
dependency add --groupId com.ibm --artifactId db2jcc4 --version 9.7.2
database introspect --schema foo
database reverse engineer --schema foo

and that's it. As far as i can tell, only the db2jcc4.jar is required.

[jharvey]

Thanks all for the helpful tips. I'm facing a similar issue, but can't get to a resolution.

The firm that I work for has an OSGi bundled version of the commercial DB2 drivers available internally, so I created a new Springroo project, and tried to load the OSGi driver.

The driver appears to have loaded.

START LEVEL 99
   ID   State         Level  Name
<snip>
[  65] [Active     ] [    1] Spring Roo - Shell (Eclipse Implementation) (1.1.0.RELEASE)
[  67] [Active     ] [    1] db2jcc (8.2.1)
[  69] [Active     ] [    1] license_cu (8.2.1)

But when I try to create a persistence setup I get an exception:

roo> persistence setup --provider HIBERNATE --database DB2 --hostName localhost --databaseName foo --userName foo --password bar
java.lang.IllegalStateException: Failed to convert 'DB2' to type JdbcDatabase for option 'database'

Any thoughts on what I'm missing here? Its driving me crazy!

Thanks in advance.