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

  • 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

  • #2
    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.

    Comment


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

      Comment


      • #4
        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.

        Comment


        • #5
          Thanks Alan, that was helpful.

          Comment


          • #6
            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.

            Comment


            • #7
              Awesome... I'll give that a shot.

              Comment


              • #8
                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.

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        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.

                        Comment


                        • #13
                          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.

                          Comment


                          • #14
                            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.

                            Comment


                            • #15
                              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?

                              Comment

                              Working...
                              X