Topic: DB2 Connectivity

You will have to do a manual driver install, ie. download the JDBC driver from IBM's website and install driver from file.

I haven't tried it out but I think it should work - at least there's a link to the IBM website within the manual driver installation menu in DataCleaner.

Thanks for the quick reply. I've got the DB2 driver. I'm unsure how about the connection string and the driver class values.

This is what I have for my Oracle connection:



I'm sure it's not the same for DB2.

Have you tried the DB2 templates in the "Open database" dialog? (The small button just right to the connection string text-field. There's a built in template that goes like this:

jdbc:db2:

But I would also consult the IBM documentation for their driver in order to create the correct string...

Ah I just saw that you also needed the class name. Once again I would go for the class name suggested by DataCleaner in the "Add database driver" dialog (the combo box contains a lot of class names):

com.ibm.db2.jdbc.app.DB2Driver

How do you get DB2 in the driver class list? I think that's where I'm finally stuck. I have a class for SQL Server, Oracle, Firebird and a couple of others, but I'm sure sure where the app gets its info to fill in the driver class drop down. Thanks!

Hi gdsteinmetz and franklin,

The driver class list is just a static list of most common drivers. We haven't verified that DB2 is working because I honestly couldn't figure out how to set up DB2 in the first place an no one have volunteered to try it out - so any feedback on how it works would be greatly appreciated!

gdsteinmetz - did you get it to work or do you still need anything?

I have not gotten it to work. We have most of our apps on Oracle, but we do have a major app on DB2. So I'm very interested in having DB2 work.

How do I go about getting my driver class to incorporate com.ibm.db2.jdbc.app.DB2Driver? Or is there something else I can do? I'm running v1.4.

I loaded version 1.5 and see that it has the DB2 class driver. When I try to register DB2 it gives me the following error: "java.lang.ClassNotFoundException: com.ibm.db2.jdbc.app.DB2Driver"

I'm using C:\Program Files\SQLLIB\java\DB2JCC.JAR, which is in my classpath.

Any clues?

Try to open the .jar file with winzip (or other zip tool) to see if the file "com/ibm/db2/jdbc/app/DB2Driver.class" is in there ...

If it exists - then you have not chose the correct .jar file from within DataCleaner.

If it doesn't exist - then you are using a different DB2 driver and I don't know what the correct class name (or connection string for that sake) would be. This is in the driver-vendors hands and I don't know the details for DB2, but I read somewhere that the DB2 driver class name was supposed to be "com.ibm.db2.jdbc.app.DB2Driver" which is why it has been added to DataCleaner's driver class-combobox.

The driver is in the jar file. Not sure why it's not picking it up.

Hmm, a !ClassNotFoundException indicates that it doesn't exist so I'm guessing that you are not registering the correct .jar file in DataCleaner.

To be absolutely clear about the procedure, here's what you should do:

• Open the Settings dialog.

• Click the "Database drivers" tab.

• There should be no registered drivers besides the "Hsqldb driver" and "Sun JDBC-ODBC bridge driver".

• Click the "Register database driver" button.

• Click the "Local JAR file ..." item in the popup-menu.

• Pick out the DB2 JAR file in the File Chooser dialog.

• Enter "com.ibm.db2.jdbc.app.DB2Driver" into the combobox of the dialog that shows up afterwards

.

That is the procedure I've been following. I did have an Oracle driver registered already, which worked fine. I removed it and tried the IBM one again and received the same error. Any other ideas?

I've downloaded a DB2-driver from here:
http://www-01.ibm.com/software/data/db2/express/additional-downloads.html

And when opening the .jar file with winzip I can see that the path "com/ibm/db2/jdbc/app/DB2Driver.class" does NOT exist...

On the contrary I can see that there is a .class file called "com/ibm/db2/jcc/DB2Driver.class", which provides a hint for the class name to be entered:

com.ibm.db2.jcc.DB2Driver

This seems to work on my computer.

I got it to work finally. Used the path: com.ibm.db2.jcc.DB2Driver


Thank you very much!

Great. Can you please also let me know the connectionString (you can leave out any confidential details of course), so I can update the template strings in DataCleaner?

I was able to get it to connect with the following command. Thanks for all the help!

jdbc:db2://servername:port/databasename

Perfect, I will add it to the connection string templates right away, so it will be included in the next release.

Okay, I'm coming back to DB2 but this time for the mainframe as opposed to open systems.

I tried using the same connection string and driver class. However, I receive an error when trying to open the database saying that it can't find the license file: db2jcc_license_cisuz.jar.

This file is in the classpath. Any clues?

Thanks!

Sorry, but no. It sounds like a driver configuration problem so I can only advise you to consult IBM's documentation on the subject.

i have loaded the db2 jdbc driver as above and connectted with database,but couldn't preview the table records ? why

preview can lead to following error: WHY?
An unexcepted exception occurred: Could not execute quer
Message:
dk.eobjects.metamodel.MetaModelException: Could not execute query
Level:
SEVERE
Stack Trace:
Could not execute query
dk.eobjects.metamodel.JdbcDataContextStrategy.executeQuery(JdbcDataContextStrategy.java:475)
dk.eobjects.metamodel.DataContext.executeQuery(DataContext.java:165)
dk.eobjects.datacleaner.gui.windows.PreviewDataWindow.(PreviewDataWindow.java:50)
dk.eobjects.datacleaner.gui.panels.ColumnSelectionPanel.previewData(ColumnSelectionPanel.java:194)
dk.eobjects.datacleaner.gui.panels.ColumnSelectionPanel.access$300(ColumnSelectionPanel.java:49)
dk.eobjects.datacleaner.gui.panels.ColumnSelectionPanel$2.actionPerformed(ColumnSelectionPanel.java:135)
javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
javax.swing.DefaultButtonModel.setPressed(Unknown Source)
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
java.awt.Component.processMouseEvent(Unknown Source)
javax.swing.JComponent.processMouseEvent(Unknown Source)
java.awt.Component.processEvent(Unknown Source)
java.awt.Container.processEvent(Unknown Source)
java.awt.Component.dispatchEventImpl(Unknown Source)
java.awt.Container.dispatchEventImpl(Unknown Source)
java.awt.Component.dispatchEvent(Unknown Source)
java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
java.awt.Container.dispatchEventImpl(Unknown Source)
java.awt.Window.dispatchEventImpl(Unknown Source)
java.awt.Component.dispatchEvent(Unknown Source)
java.awt.EventQueue.dispatchEvent(Unknown Source)
java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
java.awt.EventDispatchThread.pumpEvents(Unknown Source)
java.awt.EventDispatchThread.pumpEvents(Unknown Source)
java.awt.EventDispatchThread.run(Unknown Source)
DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=CL_SCHED.CLASS_CODE, DRIVER=3.57.82
com.ibm.db2.jcc.am.bd.a(bd.java:676)
com.ibm.db2.jcc.am.bd.a(bd.java:60)
com.ibm.db2.jcc.am.bd.a(bd.java:127)
com.ibm.db2.jcc.am.km.c(km.java:2506)
com.ibm.db2.jcc.am.km.d(km.java:2483)
com.ibm.db2.jcc.am.km.a(km.java:1963)
com.ibm.db2.jcc.t4.db.g(db.java:139)
com.ibm.db2.jcc.t4.db.a(db.java:39)
com.ibm.db2.jcc.t4.t.a(t.java:32)
com.ibm.db2.jcc.t4.sb.h(sb.java:135)
com.ibm.db2.jcc.am.km.eb(km.java:1934)
com.ibm.db2.jcc.am.km.a(km.java:2863)
com.ibm.db2.jcc.am.km.a(km.java:628)
com.ibm.db2.jcc.am.km.executeQuery(km.java:612)
dk.eobjects.metamodel.JdbcDataContextStrategy.executeQuery(JdbcDataContextStrategy.java:457)
dk.eobjects.metamodel.DataContext.executeQuery(DataContext.java:165)
dk.eobjects.datacleaner.gui.windows.PreviewDataWindow.(PreviewDataWindow.java:50)
dk.eobjects.datacleaner.gui.panels.ColumnSelectionPanel.previewData(ColumnSelectionPanel.java:194)
dk.eobjects.datacleaner.gui.panels.ColumnSelectionPanel.access$300(ColumnSelectionPanel.java:49)
dk.eobjects.datacleaner.gui.panels.ColumnSelectionPanel$2.actionPerformed(ColumnSelectionPanel.java:135)
javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
javax.swing.DefaultButtonModel.setPressed(Unknown Source)
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
java.awt.Component.processMouseEvent(Unknown Source)
javax.swing.JComponent.processMouseEvent(Unknown Source)
java.awt.Component.processEvent(Unknown Source)
java.awt.Container.processEvent(Unknown Source)
java.awt.Component.dispatchEventImpl(Unknown Source)
java.awt.Container.dispatchEventImpl(Unknown Source)
java.awt.Component.dispatchEvent(Unknown Source)
java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
java.awt.Container.dispatchEventImpl(Unknown Source)
java.awt.Window.dispatchEventImpl(Unknown Source)
java.awt.Component.dispatchEvent(Unknown Source)
java.awt.EventQueue.dispatchEvent(Unknown Source)
java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
java.awt.EventDispatchThread.pumpEvents(Unknown Source)
java.awt.EventDispatchThread.pumpEvents(Unknown Source)
java.awt.EventDispatchThread.run(Unknown Source)

As far as DB2 goes, you have to be a schema owner in order to view the data. One suggestion is to have a DBA create a flat-file for you. Not as convenient but you can still profile the data.

yes,it can profile the data .i connect to DB2 database with the dba userid.is it not the schema owner?

connet to db2 database with no user/password, it can preview data records in table now.thanks

How did you overcome the db2jcc_license_cisuz.jar isssue??
I'm trying to connect to a mainframe and have the license file in classpath but receives the same error

Unfortunately, I was never able to do that. My current route is to get extracts from systems as opposed to trying to make the connection work or ask for production access.

I was just thinking about a possible solution for this issue:

Maybe it has to do with classloader issues because the way that DataCleaner loads drivers at runtime is by assigning a new classloader for each driver. I'm not an expert on classloaders but I think it may mean that the driver classes cannot reach the content of an additional jar on the classpath (the license jar).

A way to get around this is:

• Remove the DB2 driver from DataCleaner (in the GUI).

• Put both the DB2 driver and license file on the classpath (edit the .cmd file to include a corresponding "-cp" argument).

• Add a static driver entry to datacleaner-config.xml with the DB2 driver class (copy/paste-and-edit the HSQLDB driver).

Let me know if it works

I'll try that next week. I managed to connect via ODBC but had the same catalog/schema issue as we had with SQL server.