DBRE Against DB2 for z/OS

[grew]

I receive the following error when I try to reverse-engineer a DB2 for z/OS schema:

Connectivity to QDB2 databases is enabled by any of the following license files: [ db2jcc_license_cisuz.jar ]. ERRORCODE=-4472, SQLSTATE=42968

Here is my Roo session:

roo> project --topLevelPackage com.jpatprs
Created ROOT/pom.xml
Created SRC_MAIN_RESOURCES
Created SRC_MAIN_RESOURCES/log4j.properties
Created SPRING_CONFIG_ROOT
Created SPRING_CONFIG_ROOT/applicationContext.xml
roo> jpa setup --provider OPENJPA --database DB2_EXPRESS_C
Created SPRING_CONFIG_ROOT/database.properties
The DB2_EXPRESS_C JDBC driver is not available in public Maven repositories. Please adjust the pom.xml dependency to suit your needs
Updated SPRING_CONFIG_ROOT/applicationContext.xml
Created SRC_MAIN_RESOURCES/META-INF/persistence.xml
Updated ROOT/pom.xml [added dependencies com.ibm:db2jcc4:9.7.2, org.apache.openjpa:openjpa:2.1.1, org.hibernate:hibernate-validator:4.2.0.Final, javax.validation:validation-api:1.0.0.GA, cglib:cglib-nodep:2.2.2, javax.transaction:jta:1.1, org.springframework:spring-jdbc:${spring.version}, org.springframework:spring-orm:${spring.version}, commons-pool:commons-pool:1.5.6, commons-dbcp:commons-dbcp:1.3; added plugin org.codehaus.mojo:openjpa-maven-plugin:1.2]
roo> script --file db_props.roo
Updated SPRING_CONFIG_ROOT/database.properties
roo> database introspect --schema TPRS
[jcc][t4][10109][10354][4.7.85] The version of the IBM Universal JDBC driver in use is not licensed for connectivity to QDB2 databases.
To connect to this server, please obtain a licensed copy of the IBM DB2 Universal Driver for JDBC and SQLJ.
An appropriate license file db2jcc_license_*.jar for this target platform must be installed to the application class path.
Connectivity to QDB2 databases is enabled by any of the following license files: [ db2jcc_license_cisuz.jar ]. ERRORCODE=-4472, SQLSTATE=42968
roo> ! bash -c set
...
CLASSPATH=/home/grew/sqllib/java/db2java.zip:/home/grew/sqllib/java/db2jcc.jar:/home/grew/sqllib/java/sqlj.zip:/home/grew/sqllib/function:/home/grew/sqllib/java/db2jcc_license_cisuz.jar:/home/grew/sqllib/java/db2jcc_license_cu.jar:.
...
roo> quit

I'm at a loss to explain the error since I can see that the JAR file is part of my CLASSPATH and other utilities seem to find it.

Any thoughts on why I might be getting this error?

[Alan Stewart]

Have you OSGi-wrapped the DB2 jar for your particular version of DB2 ? As it is a licensed product, we can't host DB2 jars anywhere so it is up to the user to either use an OSGi-enabled driver jar or wrap it manually. The DB2 Connect driver added to your pom is for OpenJPA to talk to your database for your Roo-generated project This is very different from connecting to DB2 via the Roo shell which runs in a OSGi container (Apache Felix) Every jar used by the Roo shell needs to be an OSGi bundle.

The article at http://saber.b2b2000.com/display/BLU...ring+on+Oracle shows how to wrap an Oracle driver and the steps would be similar for DB2.

[grew]

Have you OSGi-wrapped the DB2 jar for your particular version of DB2 ? As it is a licensed product, we can't host DB2 jars anywhere so it is up to the user to either use an OSGi-enabled driver jar or wrap it manually. The DB2 Connect driver added to your pom is for OpenJPA to talk to your database for your Roo-generated project This is very different from connecting to DB2 via the Roo shell which runs in a OSGi container (Apache Felix) Every jar used by the Roo shell needs to be an OSGi bundle.

The article at http://saber.b2b2000.com/display/BLU...ring+on+Oracle shows how to wrap an Oracle driver and the steps would be similar for DB2.

@Alan,

Thanks for the pointer. I followed the advice at the Spring Roo DB2 introspection fails thread to create and starte a bundle for db2jcc4. I think that bundle was successful in that at least the DB2 drivers seemed to load. Based on your feedback I used the same procedure to create and start bundles for db2jcc_license_cisuz and db2jcc_license_cu. The headers are below. Unfortunately I still get the same error. Do I need to re-do the procedure adding the dependencies in the article you reference?

com-ibm-db2-roo-jdbc (84)
-------------------------
Bnd-LastModified = 1337883705197
Build-Jdk = 1.6.0
Built-By = gwiley
Bundle-Description = This bundle wraps the standard Maven artifact: db2jcc4-9.7.2.
Bundle-License = http://www-01.ibm.com/software/data/db2
Bundle-ManifestVersion = 2
Bundle-Name = com-ibm-db2-roo-jdbc
Bundle-SymbolicName = com.ibm.db2.roo.jdbc.db2jcc4
Bundle-Vendor = IBM (wrapped into an OSGi bundle by the Spring Roo project build system)
Bundle-Version = 9.7.2.0001
Created-By = Apache Maven Bundle Plugin
Export-Package = sqlj.runtime.profile;version="9.7.2.0001";uses:="sqlj.runtime.error,sqlj.runtime",com.ibm.db2.jcc.sqlj;version="9.7.2.0001";uses:="com.ibm.db2.jcc.resources,com.ibm.db2.jcc.am,sqlj.runtime.profile,com.ibm.db2.jcc.t4,com.ibm.db2.jcc,sqlj.runtime,javax.naming,sun.io,sqlj.runtime.profile.util,javax.sql,sqlj.runtime.ref,sqlj.runtime.profile.ref,com.ibm.db2.jcc.t2zos,COM.ibm.db2os390.sqlj.custom,sqlj.runtime.error",com.ibm.db2.jcc.mx.a;version="9.7.2.0001";uses:="com.ibm.db2.jcc.mx,com.ibm.db2.jcc",sqlj.runtime;version="9.7.2.0001";uses:="sqlj.runtime.profile,sqlj.runtime.error,javax.naming,javax.sql",com.ibm.db2.jcc.uw;version="9.7.2.0001";uses:="com.ibm.db2.jcc.resources,com.ibm.db2.jcc.am,com.ibm.db2.jcc.t2,com.ibm.db2.jcc.t4,com.ibm.db2.jcc,sqlj.runtime.ref,javax.transaction.xa,com.ibm.db2.jcc.a",sqlj.runtime.profile.util;version="9.7.2.0001";uses:="sqlj.runtime.profile,sqlj.runtime.profile.ref,sqlj.runtime,sqlj.runtime.error,sqlj.framework.error,sqlj.mesg,sun.misc,sqlj.framework.ide,sqlj.framework.options,sqlj.util.io,sqlj.tools",com.ibm.db2.jcc.b.a;version="9.7.2.0001";uses:="com.ibm.db2.jcc.b.c,org.xml.sax,com.ibm.db2.jcc.b,org.xml.sax.helpers,org.xml.sax.ext",com.ibm.db2.jcc.mx;version="9.7.2.0001",com.ibm.db2.jcc.am;version="9.7.2.0001";uses:="com.ibm.db2.jcc,com.ibm.db2.jcc.resources,com.ibm.db2.jcc.b.a,javax.xml.transform.stream,org.xml.sax,javax.xml.transform.sax,org.xml.sax.ext,javax.xml.transform,com.ibm.db2.jcc.b,javax.security.auth.callback,javax.xml.transform.stax,javax.xml.stream,javax.transaction.xa,com.ibm.db2.jcc.sqlj,com.ibm.db2.jcc.t4,com.ibm.db2.jcc.uw,javax.sql,sun.io,com.ibm.db2.jcc.t2,com.ibm.db2.jcc.t2zos,com.ibm.db2.jcc.a,com.ibm.db2.jcc.mx.a,sqlj.runtime,javax.management,org.xml.sax.helpers,javax.xml.parsers,org.w3c.dom,com.ibm.db2.jcc.b.b,javax.xml.transform.dom,org.ietf.jgss,sqlj.runtime.ref,javax.crypto.spec,javax.crypto,javax.crypto.interfaces,javax.security.auth.login,javax.security.auth,javax.naming,sun.security.action",com.ibm.db2.jcc.b.b;version="9.7.2.0001";uses:="com.ibm.db2.jcc.b.c,org.xml.sax,com.ibm.db2.jcc.b",com.ibm.db2.jcc.b.c;version="9.7.2.0001";uses:="org.xml.sax,com.ibm.db2.jcc.b,com.ibm.db2.jcc.b.a",com.ibm.db2.jcc.t2zos;version="9.7.2.0001";uses:="com.ibm.db2.jcc,com.ibm.db2.jcc.am,com.ibm.db2.jcc.t2,javax.naming,javax.transaction,sqlj.runtime.ref,org.ietf.jgss,com.ibm.db2.jcc.a,sqlj.runtime,javax.sql,com.ibm.websphere.interrupt",com.ibm.db2.jcc.a;version="9.7.2.0001";uses:="com.ibm.db2.jcc.am",com.ibm.db2.jcc.b;version="9.7.2.0001";uses:="com.ibm.db2.jcc.b.c,org.xml.sax,javax.xml.transform.stream,javax.xml.stream.events,javax.xml.namespace,javax.xml.transform,javax.xml.stream",sqlj.runtime.error;version="9.7.2.0001",com.ibm.db2.jcc.resources;version="9.7.2.0001";uses:="com.ibm.db2.jcc.am",COM.ibm.db2.app;version="9.7.2.0001";uses:="com.ibm.db2.jcc.uw,com.ibm.db2.jcc,sqlj.runtime.ref",com.ibm.db2.jcc.t2;version="9.7.2.0001";uses:="com.ibm.db2.jcc.am,com.ibm.db2.jcc.uw,com.ibm.db2.jcc.t2zos",com.ibm.db2.jcc.uw.classloader;version="9.7.2.0001";uses:="com.ibm.db2.jcc.uw.a,com.ibm.db2.jcc.resources,com.ibm.db2.jcc.am",com.ibm.db2.jcc.t4;version="9.7.2.0001";uses:="com.ibm.db2.jcc.am,com.ibm.db2.jcc,com.ibm.db2.jcc.resources,com.ibm.db2.jcc.a,javax.transaction.xa,javax.naming,org.ietf.jgss,com.ibm.db2.jcc.sqlj,sqlj.runtime.profile,javax.net.ssl,javax.net",sqlj.runtime.profile.ref;version="9.7.2.0001";uses:="sqlj.runtime.profile,sqlj.runtime.error,sqlj.runtime,sqlj.runtime.ref",com.ibm.db2.jcc;version="9.7.2.0001";uses:="com.ibm.db2.jcc.am,javax.naming,com.ibm.db2.jcc.t2,com.ibm.db2.jcc.t4,sqlj.runtime.ref,com.ibm.db2.jcc.t2zos,javax.naming.spi,org.ietf.jgss,javax.sql,com.ibm.db2.jcc.uw,javax.transaction.xa,com.ibm.db2.jcc.sqlj",sqlj.runtime.util;version="9.7.2.0001",sqlj.runtime.ref;version="9.7.2.0001";uses:="sqlj.runtime.error,sqlj.runtime.profile,sqlj.runtime,sqlj.runtime.util",COM.ibm.db2os390.sqlj.custom;version="9.7.2.0001";uses:="sqlj.runtime.profile,COM.ibm.db2os390.sqlj.jdbc,com.ibm.db2.jcc,COM.ibm.db2os390.sqlj.runtime,sqlj.runtime.profile.ref",COM.ibm.db2os390.sqlj.jdbc;version="9.7.2.0001";uses:="com.ibm.db2.jcc,com.ibm.db2.jcc.t2zos",com.ibm.db2.jcc.uw.a;version="9.7.2.0001";uses:="com.ibm.db2.jcc.resources,com.ibm.db2.jcc.am"
Import-Package = COM.ibm.db2os390.sqlj.runtime;resolution:=optional,com.ibm.websphere.interrupt;resolution:=optional,javax.crypto;resolution:=optional,javax.crypto.interfaces;resolution:=optional,javax.crypto.spec;resolution:=optional,javax.management;resolution:=optional,javax.naming;resolution:=optional,javax.naming.spi;resolution:=optional,javax.net;resolution:=optional,javax.net.ssl;resolution:=optional,javax.security.auth;resolution:=optional,javax.security.auth.callback;resolution:=optional,javax.security.auth.login;resolution:=optional,javax.sql;resolution:=optional,javax.transaction;resolution:=optional,javax.transaction.xa;resolution:=optional,javax.xml.namespace;resolution:=optional,javax.xml.parsers;resolution:=optional,javax.xml.stream;resolution:=optional,javax.xml.stream.events;resolution:=optional,javax.xml.transform;resolution:=optional,javax.xml.transform.dom;resolution:=optional,javax.xml.transform.sax;resolution:=optional,javax.xml.transform.stax;resolution:=optional,javax.xml.transform.stream;resolution:=optional,org.ietf.jgss;resolution:=optional,org.w3c.dom;resolution:=optional,org.xml.sax;resolution:=optional,org.xml.sax.ext;resolution:=optional,org.xml.sax.helpers;resolution:=optional,sqlj.framework.error;resolution:=optional,sqlj.framework.ide;resolution:=optional,sqlj.framework.options;resolution:=optional,sqlj.mesg;resolution:=optional,sqlj.tools;resolution:=optional,sqlj.util.io;resolution:=optional,sun.io;resolution:=optional,sun.misc;resolution:=optional,sun.security.action;resolution:=optional
Manifest-Version = 1.0
Tool = Bnd-1.15.0

com-ibm-db2-roo-jdbc (85)
-------------------------
Bnd-LastModified = 1337883721423
Build-Jdk = 1.6.0
Built-By = gwiley
Bundle-Description = This bundle wraps the standard Maven artifact: db2jcc_license_cisuz-9.7.2.
Bundle-License = http://www-01.ibm.com/software/data/db2
Bundle-ManifestVersion = 2
Bundle-Name = com-ibm-db2-roo-jdbc
Bundle-SymbolicName = com.ibm.db2.roo.jdbc.db2jcc_license_cisuz
Bundle-Vendor = IBM (wrapped into an OSGi bundle by the Spring Roo project build system)
Bundle-Version = 9.7.2.0001
Created-By = Apache Maven Bundle Plugin
Export-Package = com.ibm.db2.jcc.licenses;version="9.7.2.0001"
Import-Package = org.ietf.jgss;resolution:=optional
Manifest-Version = 1.0
Tool = Bnd-1.15.0

com-ibm-db2-roo-jdbc (86)
-------------------------
Bnd-LastModified = 1337883735439
Build-Jdk = 1.6.0
Built-By = gwiley
Bundle-Description = This bundle wraps the standard Maven artifact: db2jcc_license_cu-9.7.2.
Bundle-License = http://www-01.ibm.com/software/data/db2
Bundle-ManifestVersion = 2
Bundle-Name = com-ibm-db2-roo-jdbc
Bundle-SymbolicName = com.ibm.db2.roo.jdbc.db2jcc_license_cu
Bundle-Vendor = IBM (wrapped into an OSGi bundle by the Spring Roo project build system)
Bundle-Version = 9.7.2.0001
Created-By = Apache Maven Bundle Plugin
Export-Package = com.ibm.db2.jcc.licenses;version="9.7.2.0001"
Import-Package = org.ietf.jgss;resolution:=optional
Manifest-Version = 1.0
Tool = Bnd-1.15.0

[Alan Stewart]

Your error mentions db2jcc_license_cisuz.jar - have you wrapped this jar? Do any errors appear when the Roo shell is started after you have installed the wrapped jars?

[grew]

I have wrapped the db2jcc_license_cisuz.jar. The Roo shell starts with no errors reported.

The OSGi header for that bundle follows. I confirmed that db2jcc_license_cisuz-9.7.2.jar exists in my local Maven repository. This is most puzzling - thanks for all the pointers!

com-ibm-db2-roo-cisuz (90)
--------------------------
Bnd-LastModified = 1337943974633
Build-Jdk = 1.6.0
Built-By = gwiley
Bundle-Description = This bundle wraps the standard Maven artifact: db2jcc_license_cisuz-9.7.2.
Bundle-License = http://www-01.ibm.com/software/data/db2
Bundle-ManifestVersion = 2
Bundle-Name = com-ibm-db2-roo-cisuz
Bundle-SymbolicName = com.ibm.db2.roo.cisuz.db2jcc_license_cisuz
Bundle-Vendor = IBM (wrapped into an OSGi bundle by the Spring Roo project build system)
Bundle-Version = 9.7.2.0001
Created-By = Apache Maven Bundle Plugin
Export-Package = com.ibm.db2.jcc.licenses;version="9.7.2.0001"
Import-Package = org.ietf.jgss;resolution:=optional
Manifest-Version = 1.0
Tool = Bnd-1.15.0

[Alan Stewart]

It doesn't matter if the jar is in the maven repo or not - it just needs to be started from any location by the shell with the osgi start command . Do you see the jars activated with the "osgi ps" command in Roo?

[grew]

I see the jars for the two license files and the JDBC driver:

roo> osgi ps
START LEVEL 99
ID State Level Name
[ 0] [Active ] [ 0] System Bundle (3.2.2)
...
[ 90] [Active ] [ 1] com-ibm-db2-roo-cisuz (9.7.2.0001)
[ 91] [Active ] [ 1] com-ibm-db2-roo-cu (9.7.2.0001)
[ 92] [Active ] [ 1] com-ibm-db2-roo-jdbc (9.7.2.0001)

[Alan Stewart]

So what is the error now when you do a database introspect or database reverse engineer command?

[grew]

I tried uninstalling the bundles, quit and started roo, started the bundles, quit and started roo again. Still the same problem:

roo> osgi ps
START LEVEL 99
ID State Level Name
[ 0] [Active ] [ 0] System Bundle (3.2.2)
...
[ 93] [Active ] [ 1] com-ibm-db2-roo-cisuz (9.7.2.0001)
[ 94] [Active ] [ 1] com-ibm-db2-roo-cu (9.7.2.0001)
[ 95] [Active ] [ 1] com-ibm-db2-roo-jdbc (9.7.2.0001)

roo> database introspect --schema TPRS
[jcc][t4][10109][10354][4.7.85] The version of the IBM Universal JDBC driver in use is not licensed for connectivity to QDB2 databases.
To connect to this server, please obtain a licensed copy of the IBM DB2 Universal Driver for JDBC and SQLJ.
An appropriate license file db2jcc_license_*.jar for this target platform must be installed to the application class path.
Connectivity to QDB2 databases is enabled by any of the following license files: [ db2jcc_license_cisuz.jar ]. ERRORCODE=-4472, SQLSTATE=42968

[Alan Stewart]

You have resolved all the Roo wrapping and connectivity issues but unfortunately now it's in the realm of DB2 itself. Are your driver jars (that you wrapped) exactly the same as those that were shipped with your DB2 installation?
Nothing more I can do here, sorry.