Schemacrawler and DB2

[Marc Schoechlin]

Hi !

I'm currently trying to read out ddl-structures from a db2-database
with schemacrawler (http://schemacrawler.sourceforge.net/)
but without success. Some people in this newsgroup recommended this
tool for analyzing and diff-ing ddl-structures of databases.

My configuration looks like this:
---
.....
vk1xap04.host=127.0.0.1
vk1xap04.port=60020
vk1xap04.database=vk1xap04
vk1xap04.schemapattern=%
vk1xap04.user=vk1xap04
vk1xap04.password=<removed>
vk1xap04.driver=com.ibm.db2.jcc.DB2Driver
vk1xap04.url=jdbc:db2://${host}:${port}
....
---

I got the following error-message:
--
$ java -cp $CLASSPATH -jar `ls -1 schemacrawler-*.jar` \
-command=maximum_schema \
-outputformat=text \
-g /application/shared/install/schemacrawler/current/schemacrawler.config.properties \
-connection=vk1xap04

exception in thread "main" dbconnector.datasource.PropertiesDataSourceExcepti on: Driver class not found - com.ibm.db2.jcc.DB2Driver
at dbconnector.datasource.PropertiesDataSource.constr uctPropertiesDataSource(PropertiesDataSource.java: 202)
at dbconnector.datasource.PropertiesDataSource.<init> (PropertiesDataSource.java:153)
at dbconnector.Main.createDataSource(Main.java:215)
at schemacrawler.Main.doMain(Main.java:117)
at schemacrawler.Main.main(Main.java:82)
Caused by: java.lang.ClassNotFoundException: com.ibm.db2.jcc.DB2Driver
at java.net.URLClassLoader$1.run(URLClassLoader.java: 200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.j ava:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:3 06)
at java.lang.ClassLoader.loadClass(ClassLoader.java:2 51)
at java.lang.ClassLoader.loadClassInternal(ClassLoade r.java:319)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:242)
at dbconnector.datasource.PropertiesDataSource.constr uctPropertiesDataSource(PropertiesDataSource.java: 197)
... 4 more
--

This looks like a simple classpath problem - but the class is resolvable:
---
# positive test
java com.ibm.db2.jcc.DB2Driver
Exception in thread "main" java.lang.NoSuchMethodError: main
# negative test
java com.ibm.db2.jcc.DB2DriverXXXXXXXXXXX
Exception in thread "main" java.lang.NoClassDefFoundError: com/ibm/db2/jcc/DB2DriverXXXXXXXXXXX
---

Any ideas ?

Is there a another free tool which eases comparison of databases ?

Best regards

Marc Schoechlin
--
I prefer open/free file formats:
http://en.wikipedia.org/wiki/Open_fo...f_open_formats
http://en.wikipedia.org/wiki/Free_file_format

[Phil Sherman]

Your problem is shown in the first line of the error message - the type
4 jcc driver wasn't found.

Make sure that the *.jcc.jar file appears in your CLASSPATH. On my
system it's located at:
/home/db2inst1/sqllib/java/db2jcc.jar

If you properly executed the instance owner's db2profile script, you
should have had this included in your CLASSPATH variable. It's one of a
number of db2 items in the classpath.

Phil Sherman
Marc Schoechlin wrote:

Hi !

I'm currently trying to read out ddl-structures from a db2-database
with schemacrawler (http://schemacrawler.sourceforge.net/)
but without success. Some people in this newsgroup recommended this
tool for analyzing and diff-ing ddl-structures of databases.

My configuration looks like this:
---
....
vk1xap04.host=127.0.0.1
vk1xap04.port=60020
vk1xap04.database=vk1xap04
vk1xap04.schemapattern=%
vk1xap04.user=vk1xap04
vk1xap04.password=<removed>
vk1xap04.driver=com.ibm.db2.jcc.DB2Driver
vk1xap04.url=jdbc:db2://${host}:${port}
...
---

I got the following error-message:
--
$ java -cp $CLASSPATH -jar `ls -1 schemacrawler-*.jar` \
-command=maximum_schema \
-outputformat=text \
-g /application/shared/install/schemacrawler/current/schemacrawler.config.properties \
-connection=vk1xap04

exception in thread "main" dbconnector.datasource.PropertiesDataSourceExcepti on: Driver class not found - com.ibm.db2.jcc.DB2Driver
at dbconnector.datasource.PropertiesDataSource.constr uctPropertiesDataSource(PropertiesDataSource.java: 202)
at dbconnector.datasource.PropertiesDataSource.<init> (PropertiesDataSource.java:153)
at dbconnector.Main.createDataSource(Main.java:215)
at schemacrawler.Main.doMain(Main.java:117)
at schemacrawler.Main.main(Main.java:82)
Caused by: java.lang.ClassNotFoundException: com.ibm.db2.jcc.DB2Driver
at java.net.URLClassLoader$1.run(URLClassLoader.java: 200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.j ava:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:3 06)
at java.lang.ClassLoader.loadClass(ClassLoader.java:2 51)
at java.lang.ClassLoader.loadClassInternal(ClassLoade r.java:319)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:242)
at dbconnector.datasource.PropertiesDataSource.constr uctPropertiesDataSource(PropertiesDataSource.java: 197)
... 4 more
--

This looks like a simple classpath problem - but the class is resolvable:
---
# positive test
java com.ibm.db2.jcc.DB2Driver
Exception in thread "main" java.lang.NoSuchMethodError: main
# negative test
java com.ibm.db2.jcc.DB2DriverXXXXXXXXXXX
Exception in thread "main" java.lang.NoClassDefFoundError: com/ibm/db2/jcc/DB2DriverXXXXXXXXXXX
---

Any ideas ?

Is there a another free tool which eases comparison of databases ?

Best regards

Marc Schoechlin

[Marc Schoechlin]

Phil Sherman <ps******@ameritech.netschrieb:

Your problem is shown in the first line of the error message - the type
4 jcc driver wasn't found.

Make sure that the *.jcc.jar file appears in your CLASSPATH. On my
system it's located at:
/home/db2inst1/sqllib/java/db2jcc.jar

This file was already in my classpath (you see this in the test
beyond) - but it was not enough to
set the classpath varaible or to add the classpath with
"java -cp jar1.jar:jar2.jar" ...

>This looks like a simple classpath problem - but the class is resolvable:
---
# positive test
java com.ibm.db2.jcc.DB2Driver
Exception in thread "main" java.lang.NoSuchMethodError: main
# negative test
java com.ibm.db2.jcc.DB2DriverXXXXXXXXXXX
Exception in thread "main" java.lang.NoClassDefFoundError: com/ibm/db2/jcc/DB2DriverXXXXXXXXXXX
---

I tried an alternate way:
----
-------
CLASSPATH=/opt/IBM/db2/V8.1/java/db2java.zip:/opt/IBM/db2/V8.1/java/Common.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2fs.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2jcc.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2qgjava.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2policy.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2jcc_license_cisuz.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2jcc_javax.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2jcc_license_cu.jar
export CLASSPATH

java -cp `ls -1 schemacrawler-*.jar`:$CLASSPATH schemacrawler.Main \
-command=maximum_schema \
-outputformat=text \
-g /application/shared/install/schemacrawler/schemacrawler.config.properties \
-connection=$1
-------

Now i ran into another problem :-)

--- my config --------------------------------------------
vk1xap04.host=127.0.0.1
vk1xap04.port=60020
vk1xap04.database=vk1xap04
vk1xap04.schemapattern=vk1xap04
vk1xap04.user=vk1xap04
vk1xap04.password=<removed>
vk1xap04.driver=com.ibm.db2.jcc.DB2Driver
vk1xap04.url=jdbc:db2://${host}:${port}/${database}
---------------------------------------------------------

- error output ----------------------------------------------

-- database product: DB2/SUN SQL08024
-- driver: com.ibm.db2.jcc.DB2Driver - IBM DB2 JDBC Universal Driver
Architecture 2.8.46
-- connection: jdbc:db2://127.0.0.1:60020/vk1xap04
-- schema pattern: vk1xap04
------------------------------------------------------------------------
AllProceduresAreCallable false
AllTablesAreSelectable false
CatalogSeparator .
Exception in thread "main" java.lang.NullPointerException
at
schemacrawler.tools.schematext.BaseSchemaTextForma tter.handle(BaseSchemaTextFormatter.java:160)
at
schemacrawler.crawl.SchemaCrawler.crawlDatabaseInf o(SchemaCrawler.java:186)
at schemacrawler.crawl.SchemaCrawler.crawl(SchemaCraw ler.java:131)
at
schemacrawler.tools.ToolsExecutor.execute(ToolsExe cutor.java:126)
at schemacrawler.Main.doMain(Main.java:124)
at schemacrawler.Main.main(Main.java:82)
--------------------------------------------------------------------------

I´m have no idea how to solve this ....

Best regards

Marc Schoechlin

----

--
https://www.xing.com/profile/Marc_Schoechlin/
I prefer open/free file formats:
http://en.wikipedia.org/wiki/Open_fo...f_open_formats
http://en.wikipedia.org/wiki/Free_file_format
Contact me via jabber: [e-mail]

[Phil Sherman]

You apparently didn't look carefully at the error output. The initial
error message is SQL08024, with additional Java information showing the
Java stack trace. DB2 error messages can be looked up in the messages
manual, or, for a quick look, enter the command: db2 "? SQL08024"

The error message indicates that you've run into a licensing error. I'm
not sure what has happened at this point but you should check your
db2diag.log file for additional error information. Your configuration
states that you are using the loopback TCP/IP interface to connect to
the database so I'd suspect that the database is refusing to accept the
connection.

You could try a simple Java program (you write it) that connects to the
database and does a 'select count(*) from syscat.tables' to verify that
you can connect to the database and get something from it. Use the same
connection parameters; 127.0.0.1, port 60020, etc. and see if that
works. If it fails, you have a simple piece of code to experiment with
that you can easily modify.

Phil Sherman

Marc Schoechlin wrote:

Phil Sherman <ps******@ameritech.netschrieb:

>Your problem is shown in the first line of the error message - the type
4 jcc driver wasn't found.

Make sure that the *.jcc.jar file appears in your CLASSPATH. On my
system it's located at:
/home/db2inst1/sqllib/java/db2jcc.jar

This file was already in my classpath (you see this in the test
beyond) - but it was not enough to
set the classpath varaible or to add the classpath with
"java -cp jar1.jar:jar2.jar" ...

>>This looks like a simple classpath problem - but the class is resolvable:
---
# positive test
java com.ibm.db2.jcc.DB2Driver
Exception in thread "main" java.lang.NoSuchMethodError: main
# negative test
java com.ibm.db2.jcc.DB2DriverXXXXXXXXXXX
Exception in thread "main" java.lang.NoClassDefFoundError: com/ibm/db2/jcc/DB2DriverXXXXXXXXXXX
---

I tried an alternate way:
----
-------
CLASSPATH=/opt/IBM/db2/V8.1/java/db2java.zip:/opt/IBM/db2/V8.1/java/Common.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2fs.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2jcc.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2qgjava.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2policy.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2jcc_license_cisuz.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2jcc_javax.jar
CLASSPATH=$CLASSPATH:/opt/IBM/db2/V8.1/java/db2jcc_license_cu.jar
export CLASSPATH

java -cp `ls -1 schemacrawler-*.jar`:$CLASSPATH schemacrawler.Main \
-command=maximum_schema \
-outputformat=text \
-g /application/shared/install/schemacrawler/schemacrawler.config.properties \
-connection=$1
-------

Now i ran into another problem :-)

--- my config --------------------------------------------
vk1xap04.host=127.0.0.1
vk1xap04.port=60020
vk1xap04.database=vk1xap04
vk1xap04.schemapattern=vk1xap04
vk1xap04.user=vk1xap04
vk1xap04.password=<removed>
vk1xap04.driver=com.ibm.db2.jcc.DB2Driver
vk1xap04.url=jdbc:db2://${host}:${port}/${database}
---------------------------------------------------------

- error output ----------------------------------------------

-- database product: DB2/SUN SQL08024
-- driver: com.ibm.db2.jcc.DB2Driver - IBM DB2 JDBC Universal Driver
Architecture 2.8.46
-- connection: jdbc:db2://127.0.0.1:60020/vk1xap04
-- schema pattern: vk1xap04
------------------------------------------------------------------------
AllProceduresAreCallable false
AllTablesAreSelectable false
CatalogSeparator .
Exception in thread "main" java.lang.NullPointerException
at
schemacrawler.tools.schematext.BaseSchemaTextForma tter.handle(BaseSchemaTextFormatter.java:160)
at
schemacrawler.crawl.SchemaCrawler.crawlDatabaseInf o(SchemaCrawler.java:186)
at schemacrawler.crawl.SchemaCrawler.crawl(SchemaCraw ler.java:131)
at
schemacrawler.tools.ToolsExecutor.execute(ToolsExe cutor.java:126)
at schemacrawler.Main.doMain(Main.java:124)
at schemacrawler.Main.main(Main.java:82)
--------------------------------------------------------------------------

I´m have no idea how to solve this ....

Best regards

Marc Schoechlin

----