Announcement Announcement Module
Collapse
No announcement yet.
Spring Roo DB2 introspection fails Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • saihegde
    started a topic Spring Roo DB2 introspection fails

    Spring Roo DB2 introspection fails

    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

  • kcbaltz
    replied
    Got it!

    I had to modify the db2jcc4.jar file that was produced. The META-INF/MANIFEST.MF file has an Import-Package section that, as I understand it, lists the dependencies of this jar/bundle, things it expects to need at runtime and have provide by other bundles. The Maven tool that builds this apparently gets some of it wrong. I stripped out the elements that appear to be contained in the jar already (e.g.COM.ibm.db2os390.*) and repackaged the jar. Once this was done, it loaded in and roo gave me no complaints. I'm using a version of db2jcc4.jar that comes with an internal version of "1.0" in the MANIFEST.MF it ships with naturally. I'm not sure if this is correct, it might actually be 9.7.x.

    Here's the working contents of the MANIFEST.MF I ended up with (Import-Package is near the bottom)

    Code:
    Manifest-Version: 1.0
    Bnd-LastModified: 1385351883463
    Bundle-ManifestVersion: 2
    Bundle-Name: db2jcc
    Bundle-SymbolicName: com.ibm.db2.roo.JDBC
    Bundle-Version: 1.0
    Created-By: 1.6.0_37 (Sun Microsystems Inc.)
    Export-Package: com.ibm.db2.jcc.mx;version="1.0",sqlj.runtime.ref;uses:=
     "sqlj.runtime.profile,sqlj.runtime,sqlj.runtime.util,sqlj.runtime.error
     ";version="1.0",com.ibm.db2.jcc.t2zos;uses:="com.ibm.db2.jcc,com.ibm.db
     2.jcc.am,com.ibm.db2.jcc.t2,com.ibm.zos.batch.container.support,com.ibm
     .zos.batch.container.support.transaction,javax.naming,sqlj.runtime.ref,
     javax.transaction,org.ietf.jgss,com.ibm.db2.jcc.a,sqlj.runtime,javax.sq
     l,com.ibm.websphere.interrupt";version="1.0",com.ibm.db2.jcc.b.c;uses:=
     "org.xml.sax.ext,org.xml.sax,com.ibm.db2.jcc.am,com.ibm.db2.jcc.b,com.i
     bm.db2.jcc.b.a";version="1.0",com.ibm.db2.jcc.b.a;uses:="com.ibm.db2.jc
     c.b,com.ibm.db2.jcc.b.c,org.xml.sax,org.xml.sax.ext,org.xml.sax.helpers
     ";version="1.0",com.ibm.db2.jcc.b.b;uses:="com.ibm.db2.jcc.b,com.ibm.db
     2.jcc.b.c,org.xml.sax";version="1.0",sqlj.runtime.profile;uses:="sqlj.r
     untime.error,sqlj.runtime";version="1.0",sqlj.runtime;uses:="sqlj.runti
     me.profile,javax.naming,javax.sql,sqlj.runtime.error";version="1.0",COM
     .ibm.db2os390.sqlj.jdbc;uses:="com.ibm.db2.jcc,com.ibm.db2.jcc.t2zos";v
     ersion="1.0",sqlj.runtime.error;version="1.0",com.ibm.db2.jcc.b;uses:="
     javax.xml.stream,org.xml.sax,com.ibm.db2.jcc.am,com.ibm.db2.jcc.b.c,jav
     ax.xml.stream.events,javax.xml.namespace,javax.xml.transform,javax.xml.
     transform.stream";version="1.0",com.ibm.db2.jcc.a;uses:="com.ibm.db2.jc
     c,com.ibm.db2.jcc.am";version="1.0",com.ibm.db2.jcc.osgi;uses:="org.osg
     i.framework";version="1.0",sqlj.runtime.profile.ref;uses:="sqlj.runtime
     .profile,sqlj.runtime,sqlj.runtime.error,sqlj.runtime.ref";version="1.0
     ",sqlj.runtime.profile.util;uses:="sqlj.runtime.profile,sqlj.runtime.pr
     ofile.ref,sqlj.runtime,sqlj.runtime.error,sqlj.framework.error,sqlj.mes
     g,sun.misc,sqlj.framework.ide,sqlj.framework.options,sqlj.util.io,sqlj.
     tools";version="1.0",sqlj.runtime.util;version="1.0",com.ibm.db2.jcc;us
     es:="com.ibm.db2.jcc.am,javax.naming,sqlj.runtime.ref,com.ibm.db2.jcc.t
     2zos,com.ibm.db2.jcc.t4,com.ibm.db2.jcc.t2,javax.naming.spi,org.ietf.jg
     ss,javax.sql,com.ibm.db2.jcc.uw,javax.transaction.xa,com.ibm.db2.jcc.sq
     lj,javax.xml.transform";version="1.0",com.ibm.db2.jcc.resources;uses:="
     com.ibm.db2.jcc.am";version="1.0",com.ibm.db2.jcc.am;uses:="com.ibm.db2
     .jcc,com.ibm.db2.jcc.t4,javax.transaction.xa,javax.security.auth.callba
     ck,javax.security.auth.login,javax.security.auth,com.ibm.db2.jcc.resour
     ces,com.ibm.db2.jcc.sqlj,sun.io,javax.naming,sun.security.action,org.ie
     tf.jgss,sqlj.runtime.ref,com.ibm.db2.jcc.uw,com.ibm.db2.jcc.t2zos,javax
     .management,com.ibm.db2.jcc.mx.a,sqlj.runtime,com.ibm.db2.jcc.a,javax.s
     ql,javax.xml.transform,javax.xml.transform.dom,org.w3c.dom,org.xml.sax.
     ext,javax.xml.transform.stream,com.ibm.db2.jcc.b,javax.xml.transform.sa
     x,com.ibm.db2.jcc.b.a,org.xml.sax,javax.xml.transform.stax,javax.xml.pa
     rsers,com.ibm.db2.jcc.b.b,javax.xml.stream,org.xml.sax.helpers,javax.cr
     ypto.interfaces,javax.crypto.spec,javax.crypto,com.ibm.db2.jcc.t2";vers
     ion="1.0",com.ibm.db2.jcc.sqlj;uses:="sqlj.runtime.profile,com.ibm.db2.
     jcc,com.ibm.db2.jcc.resources,com.ibm.db2.jcc.am,com.ibm.db2.jcc.t4,sql
     j.runtime,javax.naming,sqlj.runtime.profile.util,sun.io,javax.sql,sqlj.
     runtime.ref,sqlj.runtime.profile.ref,com.ibm.db2.jcc.t2zos,COM.ibm.db2o
     s390.sqlj.custom,sqlj.runtime.error";version="1.0",com.ibm.db2.jcc.mx.a
     ;uses:="com.ibm.db2.jcc.mx,com.ibm.db2.jcc";version="1.0",com.ibm.db2.j
     cc.uw.classloader;uses:="com.ibm.db2.jcc.uw.a,com.ibm.db2.jcc.resources
     ,com.ibm.db2.jcc.am";version="1.0",COM.ibm.db2os390.sqlj.custom;uses:="
     sqlj.runtime.profile,COM.ibm.db2os390.sqlj.runtime,COM.ibm.db2os390.sql
     j.jdbc,com.ibm.db2.jcc,sqlj.runtime.profile.ref";version="1.0",com.ibm.
     db2.jcc.t4;uses:="com.ibm.db2.jcc,com.ibm.db2.jcc.am,javax.transaction.
     xa,org.ietf.jgss,com.ibm.db2.jcc.resources,javax.naming,sqlj.runtime.pr
     ofile,com.ibm.db2.jcc.a,com.ibm.db2.jcc.sqlj,javax.net,javax.net.ssl";v
     ersion="1.0",COM.ibm.db2.app;uses:="sqlj.runtime.ref,com.ibm.db2.jcc,co
     m.ibm.db2.jcc.uw";version="1.0",com.ibm.db2.jcc.t2;uses:="com.ibm.db2.j
     cc.t2zos,com.ibm.db2.jcc.am,com.ibm.db2.jcc.uw";version="1.0",com.ibm.d
     b2.jcc.uw.a;uses:="com.ibm.db2.jcc.am,com.ibm.db2.jcc.resources";versio
     n="1.0",com.ibm.db2.jcc.uw;uses:="com.ibm.db2.jcc.resources,com.ibm.db2
     .jcc.am,sqlj.runtime.ref,com.ibm.db2.jcc,com.ibm.db2.jcc.t4,com.ibm.db2
     .jcc.t2,javax.transaction.xa,com.ibm.db2.jcc.a";version="1.0"
    Import-Package: javax.crypto,javax.crypto.interfaces,javax.crypto.spec,j
     avax.management,javax.naming,javax.naming.spi,javax.net,javax.net.ssl,j
     avax.security.auth,javax.security.auth.callback,javax.security.auth.log
     in,javax.sql,javax.transaction,javax.transaction.xa,javax.xml.namespace
     ,javax.xml.parsers,javax.xml.stream,javax.xml.stream.events,javax.xml.t
     ransform,javax.xml.transform.dom,javax.xml.transform.sax,javax.xml.tran
     sform.stax,javax.xml.transform.stream,org.ietf.jgss,org.osgi.framework,
     org.w3c.dom,org.xml.sax,org.xml.sax.ext,org.xml.sax.helpers
    Originally-Created-By: 1.6.0 (IBM Corporation)
    Private-Package: .
    Tool: Bnd-1.50.0

    Leave a comment:


  • kcbaltz
    replied
    After following Wallenborn's instructions, I'm getting an Unresolved Constraint error:

    Code:
    [com.ibm.db2.roo.JDBC [98]] FrameworkEvent ERROR
    org.apache.felix.log.LogException: org.osgi.framework.BundleException: Unresolved constraint in bundle com.ibm.db2.roo.J
    DBC [98]: Unable to resolve 98.0: missing requirement [98.0] package; (package=COM.ibm.db2os390.sqlj.runtime)
    Any idea how to fix this?

    Leave a comment:


  • marcus.rp
    replied
    I've also tried the same thing, and some variations of it. And I always stop on the step 4. mvn bundle:bundle with the following resulting message:

    [INFO] Scanning for projects...
    [INFO]
    [INFO] ------------------------------------------------------------------------
    [INFO] Building com-ibm-db2-roo-jdbc 9.7.2.0001
    [INFO] ------------------------------------------------------------------------
    [INFO]
    [INFO] --- maven-bundle-plugin:2.3.4:bundle (default-cli) @ com.ibm.db2.roo.jdbc.db2jcc ---
    [WARNING] Warning building bundle com.ibm.db2.roo.jdbc:com.ibm.db2.roo.jdbc.db2jcc:b undle:9.7.2.0001 : Split package MET
    A-INF
    Use directive -split-package:=(merge-first|merge-last|error|first) on Export/Private Package instruction to get rid of t
    his warning
    Package found in [Jar:., Jar:db2jcc]
    Reference from C:\WINDOWS\Profiles\mapereira\.m2\repository\com\i bm\db2jcc\9.7.2\db2jcc-9.7.2.jar
    Classpath [Jar:., Jar:db2jcc]
    [WARNING] Warning building bundle com.ibm.db2.roo.jdbc:com.ibm.db2.roo.jdbc.db2jcc:b undle:9.7.2.0001 : Did not find matc
    hing referal for com.ibm.websphere.*
    [WARNING] Warning building bundle com.ibm.db2.roo.jdbc:com.ibm.db2.roo.jdbc.db2jcc:b undle:9.7.2.0001 : Did not find matc
    hing referal for org.w3c.*
    [WARNING] Warning building bundle com.ibm.db2.roo.jdbc:com.ibm.db2.roo.jdbc.db2jcc:b undle:9.7.2.0001 : Did not find matc
    hing referal for org.xml.*
    [ERROR] Error building bundle com.ibm.db2.roo.jdbc:com.ibm.db2.roo.jdbc.db2jcc:b undle:9.7.2.0001 : Unresolved references
    to [com.ibm.jvm, com.ibm.jvm.classloader] by class(es) on the Bundle-Classpath[Jar:dot]: [com/ibm/db2/jcc/b/md.class, C
    OM/ibm/db2os390/dsn/JARLoader.class]
    [ERROR] Error(s) found in bundle configuration
    [INFO] ------------------------------------------------------------------------
    [INFO] BUILD FAILURE
    [INFO] ------------------------------------------------------------------------
    [INFO] Total time: 6.125s
    [INFO] Finished at: Mon Jan 14 13:09:05 EST 2013
    [INFO] Final Memory: 4M/15M
    [INFO] ------------------------------------------------------------------------
    [ERROR] Failed to execute goal org.apache.felix:maven-bundle-plugin:2.3.4:bundle (default-cli) on project com.ibm.db2.ro
    o.jdbc.db2jcc: Error(s) found in bundle configuration -> [Help 1]

    I'm still trying to search for a solution on other means, withouy success so far. Nevertheless, I thank you in advance for any help.

    Leave a comment:


  • Alan Stewart
    replied
    The DB2_EXPRESS_C option will give you the closest configuration for most DB2 platforms apart from AS/400. The DB2_400 option is unique in that it uses a completely different driver (jt400), which is not supplied by IBM.

    Leave a comment:


  • rogy.varghese
    replied
    So I followed wallenborn direction but got an issue on Step 6

    persistence setup --provider HIBERNATE --database DB2 --hostName localhost --databaseName foo --userName foo --password bar

    As Alan pointed out you will get only two options for DB2 i.e DB2_400 and DB2_EXPRESS_C.
    I went ahead to change DB2 in above command to DB2_400
    This allowed me to get persistence.xml and database.properties file that get roo automatically generates. I manually went and changed these files to reflect the actual values

    Changes in persistence.xml
    Change <property name="hibernate.dialect" value="org.hibernate.dialect.DB2400Dialect"/>
    to <property name="hibernate.dialect" value="org.hibernate.dialect.DB2Dialect"/>

    Changes in database.properties
    1. Change database.url=jdbc\:as400\://dummy/dummy
    to database.url=jdbc:db2://yourhost:yourport/databasename
    2. Change database.driverClassName=com.ibm.as400.access.AS40 0JDBCDriver
    to database.driverClassName=com.ibm.db2.jcc.DB2Driver

    After making this change, it was smooth sailing. Steps below just worked fine.

    database introspect --schema foo
    database reverse engineer --schema foo

    Hope this helps.

    Leave a comment:


  • Alan Stewart
    replied
    If you simply press TAB on the --database option in the persistence setup command you will see two options for DB2: DB2_400 and DB2_EXPRESS_C. There is no option for the string "DB2" itself

    Leave a comment:


  • jharvey
    replied
    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.
    Code:
    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:

    Code:
    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.

    Leave a comment:


  • wallenborn
    replied
    Sure, here you are:

    Code:
    <?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:

    Code:
    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:

    Code:
    osgi start --url file:com.ibm.db2.roo.jdbc.db2jcc4-9.7.2.0001.jar
    quit
    6. In dbre, start roo:

    Code:
    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.

    Leave a comment:


  • Alan Stewart
    replied
    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.

    Leave a comment:


  • saihegde
    replied
    Awesome... I'll give that a shot.

    Leave a comment:


  • wallenborn
    replied
    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:

    Code:
     <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.

    Leave a comment:


  • saihegde
    replied
    Thanks Alan, that was helpful.

    Leave a comment:


  • Alan Stewart
    replied
    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.
    Last edited by Alan Stewart; Mar 29th, 2011, 10:39 PM.

    Leave a comment:


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

    Leave a comment:

Working...
X