Configure Squirrel SQL to connect to IBM DB2

"Squirrel SQL" is a great light weight generic SQL client that I have started using for more than 7 years now.

This is how to configure Squirrel SQL 3.3.0 to connect to DB2 server, I have installed IBM DB2 admin client 9.7 - 64 bit software on my Windows 7 enterprise SP1. You will find two types of DB2 drivers APP & NET, most recommended way is to use type 4 JDBC driver (thin) that actually doesn't require client installation, but require proper jar files and associated license.

Steps:
1- Open Driver list from left menu, click the plus sign "Create a New Driver".
2- Type "IBM DB2 Universal Driver" in "Name" textbox.
3- Type "jdbc:db2://<server>:<port>/<dbname>" in "Example URL" textbox.
4- Type "com.ibm.db2.jcc.DB2Driver" in "Class Name" editable dropdown list.
5- Click "Extra Class Path" tab.
6- Click "Add" and select two jar files from %DB2_INSTALL%\java\db2jcc.jar & %DB2_INSTALL%\java\db2jcc_license_cu.jar
7- Click Ok, and we are done defining the driver.
8- Now create an alias for the DB using previous driver and providing URL, username, & password.

Your driver dialog should look like:


64 comments:

  1. How do you configure oracle driver for squirrel-sql-3.3.0?

    ReplyDelete
  2. Thanks for these steps. i did it :)

    ReplyDelete
  3. I get this error when I try to connect as above

    Error: DatabaseMetaData information is not known for server DB2/NTSQL09058 by this version of JDBC driver
    SQLState: null
    ErrorCode: -99999

    Do you know the reason?

    ReplyDelete
    Replies
    1. I have googled yr error, it seems some versions of DB2 client have some issues. I suggest u post yr question on IBM DB2 forum with exact versions of DB2 & Java

      Delete
    2. I am facing the same error...

      Can you help me if you found something....
      I have installed new version of squirrel-sql.

      Delete
    3. Try posting in this forum http://www.dbforums.com/db2/
      Please when you post, you need to mention exactly the following:
      1- Exact version of Operating system.
      2- Exact JDK version including (86 or 64) installed and used by SQuirrelSQL (Squirrel SQL is not your issue, i is between Java & DB2).
      3- Exact IBM DB2 client version

      Delete
  4. Thank You Ashraf Fouad....!

    It's helped me...

    ReplyDelete
  5. Hey thanks a lot. This was very very helpful

    ReplyDelete
  6. Thanks for the wonderful set of steps to configure SQuirrelSQL with DB2.

    ReplyDelete
  7. thanks for that post, v helpful

    ReplyDelete
  8. Can I build the insert statements using the result of select statement ?

    ReplyDelete
    Replies
    1. This is one of the best useful features, here are the steps:
      1- Open a session to the DB you would like to retrieve data, and switch to the "SQL" tab.
      2- Write a SQL script to get the data you would like to have insert for, something like: select * from branch
      3- Highlight the above SQL statement.
      4- Click "Session" tab, select "Scripts", in sub-menu select "Create Data script from SQL".

      I hope this helps and sorry for delayed response.

      Delete
  9. Hello
    I've been trying to connect to our iSeries from Squirrel for hours and hours now. When I found your descrption I thougt hat's it, but it still doesn't work...

    The driver I configured as you described. The JAR files I have in C:\Programs\IBM\SQLLIB\java
    I guess this directory was installed as part of the iSeries Navigator or the Emulator...

    My problem is to connect to the database. What is the exact JDBC URL, which port do I need, which Database name...?

    I tried this:

    jdbc:db2://server_name:8471/D008488t

    D0008488t is the database name as shown in the iSeries navigator. Is this the name I should use?
    Is port 8471 correct?

    When I try to connect it doesn't come back...

    I also tried to connect through the ODBC JDBC bridge. This basically works, but it the schema navigator it only
    shows my own library. I can't add any other schemes. This is why I wanted to try to connect using JDBC to
    see if this is a driver problem...

    ReplyDelete
    Replies
    1. I'm quoting from yr post "My problem is to connect to the database. What is the exact JDBC URL, which port do I need, which Database name...?"
      I think DB server name or IP & port & database name all this information you should have from your DBA.

      The strange here is that u can connect using ODBC, this means u have already created ODBC to DB2 which means u know above information !

      What is the error u r receiving?

      Delete
    2. Well, to configure the ODBC connection I didn't need to specify neither the port nor the database name...

      I'm not sure about the port. I checked with WRKSRVTBLE that the as-database port is 8471, so I guess that's correct. Server IP and DB name indeed I know.

      When I try to connect I don't receive an error message. The dialog box displays "connecting..." and that's all, nothing further happens and Squirrel hangs.

      Richard

      Delete
    3. Your DBA should provide all details, if your ODBC didn't require such information, it seems it depends on the DB2 client installed in your machine, in this case ODBC requires DB alias only that is already linked.
      Anyway, I have no previous experience in iSeries, but I think think link can get you the required information http://knowledgebase.progress.com/articles/Article/000028707

      Delete
  10. Thanks for your quick answers. I actually didn't expect any answers in this year anymore...
    I thought I was the only one sitting in the office and working today :-)

    The knowledge base article indeed helped me. In another IBM document I found that the service for JDBC is "as-database", but actually it's "drdb", which on our system listens on port 446.

    Now I get another error message that the license couldn't be found in the class path. Well, this is something I can check.

    Thanks again for your quick help.

    Richard

    ReplyDelete
  11. I configured DB2 to support SSL. When I try to connect Squirrel client with DB2, I get below error:

    java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2043][11550][4.16.53] Exception java.net.ConnectException: Error opening socket to server / on port with message: Connection refused: connect. ERRORCODE=-4499, SQLSTATE=08001

    Please help me to connect Squirrel SQL client with DB2 SSL enabled((DB2COMM=SSL)).

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. I get the following error when I try to connect server from Alias in Squirrel SQL client...any solutions would be appreciated...thanks in advance

    java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2043][11550][3.59.81] Exception java.net.ConnectException: Error opening socket to server /10.26.74.102 on port 50,004 with message: Connection timed out: connect. ERRORCODE=-4499, SQLSTATE=08001
    at java.util.concurrent.FutureTask.report(Unknown Source)
    at java.util.concurrent.FutureTask.get(Unknown Source)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
    Caused by: java.lang.RuntimeException: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2043][11550][3.59.81] Exception java.net.ConnectException: Error opening socket to server /10.26.74.102 on port 50,004 with message: Connection timed out: connect. ERRORCODE=-4499, SQLSTATE=08001
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:171)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104)
    ... 5 more
    Caused by: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2043][11550][3.59.81] Exception java.net.ConnectException: Error opening socket to server /10.26.74.102 on port 50,004 with message: Connection timed out: connect. ERRORCODE=-4499, SQLSTATE=08001
    at com.ibm.db2.jcc.am.dd.a(dd.java:319)
    at com.ibm.db2.jcc.am.dd.a(dd.java:337)
    at com.ibm.db2.jcc.t4.wb.a(wb.java:400)
    at com.ibm.db2.jcc.t4.wb.(wb.java:78)
    at com.ibm.db2.jcc.t4.a.y(a.java:317)
    at com.ibm.db2.jcc.t4.b.a(b.java:1801)
    at com.ibm.db2.jcc.am.jb.a(jb.java:588)
    at com.ibm.db2.jcc.am.jb.(jb.java:540)
    at com.ibm.db2.jcc.t4.b.(b.java:303)
    at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(DB2SimpleDataSource.java:214)
    at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:456)
    at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167)
    ... 7 more
    Caused by: java.net.ConnectException: Connection timed out: connect
    at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at com.ibm.db2.jcc.t4.x.run(x.java:34)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.ibm.db2.jcc.t4.wb.a(wb.java:386)
    ... 17 more

    ReplyDelete
    Replies
    1. It is quite obvious it is not a problem in squirrel SQL but in DB2 connection, please refer to https://www-304.ibm.com/support/docview.wss?uid=swg21403644

      I don't think IBM suggestion will work unless this DB2 instance is new and no-one connected to it before, and actually it is a silly mistake to DBA to make it, can you please check you opened proper connection to DB2 port and it is working?

      Delete
  14. Gracias, funcionó de maravilla.

    ReplyDelete
  15. AOA:
    I am having issues in connecting DB2 database. I can connect via SQL Developer and Toad Data Point, so I have correct credentials and connection strings. I tried to use these drivers but not success...
    db2jcc.jar
    db2jcc_license_cu.jar
    db2jcc4.jar

    I have Win 7 32bit
    Java 1.7
    SQuirrel Build_2231

    I am seeing this error:
    Could not find class com.ibm.db2.jcc.DB2Driver in neither the Java class path nor the Extra class path of the IBM DB2 Universal Driver driver definition:
    java.lang.ClassNotFoundException: com.ibm.db2.jcc.DB2Driver

    The Driver name is not showing Checked either.

    I will appreciate your help.

    Regards,
    Irfan

    ReplyDelete
    Replies
    1. Dear Irfan,
      Wa Alekom Al Salam Wa Rahmato Allah Wa Barkato, after a small invistigation I found this link showing the difference between db2jcc4.jar vs db2jcc.jar
      http://www-01.ibm.com/support/docview.wss?uid=swg21665324

      So it seems that both have the same classes within the jar, and I thnk if you are trying to include all 3 jars
      db2jcc.jar
      db2jcc_license_cu.jar
      db2jcc4.jar

      when u specify extra CLASSPATH you will get Java confused to determine the jar having the correct implementation of "com.ibm.db2.jcc.DB2Driver"

      My recommendation will be just follow the steps in the post and only use the 2 mentioned jars, then In Shaa Allah you will find check next to driver name.

      Delete
    2. Dear Ashraf, Salamun Alekum
      Sorry for being late in reply. I am using only two JAR files at a time *license* and one of other, NOT all three. I am not getting checked by using any combination. Just to let you know that I am able to connect to DB2 via "Oracle SQL Developer" using same JAR files. Thanks for your help

      Delete
    3. The error is very clear that it can't find the class so it is not being able to reach the jars. What is your complete path of the jar files? Does it contain spaces?

      Please paste the location or screen shot of your setup.

      Delete
    4. Thanks Ashraf,
      I am finally able to connect to DB2. I had to use "db2jcc_license_cu.jar and db2jcc4.jar" combination for JAR files. I can run query but cannot see schemas/tables. I had same issue in SQL Developer as well and I got following error in there. It looks like permission issue as my user does not have permission on "SYSCAT.SCHEMATA" table. I know you are not suppose to resolve it but I thought you might encounter this type of issue before and have any work around. Please note that my coworker has Toad for Data Point and he is able to connect to same DB2 with same credential and see all schemas. Thanks again for nice article and quick replies.


      An error was encountered performing the requested operation:

      DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=SYSCAT.SCHEMATA, DRIVER=4.11.77

      Vendor code -204

      Delete
    5. Happy that u started working.

      This is more DB2 related question, I didn't use DB2 since long back, you can either search the internet to find the error, or if you have question, I think this forum was of great help to me answering questions before and might help u:

      http://www.dbforums.com/forumdisplay.php?8-DB2&s=110e6b5274a85b3772c05af40fc8ea3a

      I advice to register & post yr question if u didn't find yr answer

      Delete
  16. Thanks man, this is still really useful, even in 2016.

    ReplyDelete
  17. Thank you! This worked like a charm! I was able to do this while sitting in a meeting -- that's when you know a guide was written very well!

    ReplyDelete
  18. Thanks man, quick and easy to follow guide. Helped a ton!

    ReplyDelete
  19. Hey Ashraf,
    I have hostname = z1u11udb.corp.cat.com
    port = 3708 and instance = z1u11
    What would my url = jdbc:db2://:/
    look like ?

    Thanks
    AB

    ReplyDelete
    Replies
    1. Should be:
      jdbc:db2://z1u11udb.corp.cat.com:3708/z1u11

      Delete
    2. I tried it out...
      I am getting the below exception -

      java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2057][11264][3.59.81] The application server rejected establishment of the connection.
      An attempt was made to access a database, z1u11, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004
      at java.util.concurrent.FutureTask.report(Unknown Source)
      at java.util.concurrent.FutureTask.get(Unknown Source)
      at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)
      at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)
      at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)
      at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
      at java.util.concurrent.FutureTask.run(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)
      Caused by: java.lang.RuntimeException: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2057][11264][3.59.81] The application server rejected establishment of the connection.
      An attempt was made to access a database, z1u11, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004
      at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:171)
      at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45)
      at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104)
      ... 5 more
      Caused by: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2057][11264][3.59.81] The application server rejected establishment of the connection.
      An attempt was made to access a database, z1u11, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004
      at com.ibm.db2.jcc.am.dd.a(dd.java:319)
      at com.ibm.db2.jcc.am.dd.a(dd.java:365)
      at com.ibm.db2.jcc.t4.ab.u(ab.java:1660)
      at com.ibm.db2.jcc.t4.ab.n(ab.java:527)
      at com.ibm.db2.jcc.t4.ab.a(ab.java:334)
      at com.ibm.db2.jcc.t4.ab.a(ab.java:113)
      at com.ibm.db2.jcc.t4.b.m(b.java:1235)
      at com.ibm.db2.jcc.t4.b.b(b.java:1108)
      at com.ibm.db2.jcc.t4.b.c(b.java:696)
      at com.ibm.db2.jcc.t4.b.b(b.java:682)
      at com.ibm.db2.jcc.t4.b.a(b.java:365)
      at com.ibm.db2.jcc.t4.b.(b.java:305)
      at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(DB2SimpleDataSource.java:214)
      at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:456)
      at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133)
      at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167)
      ... 7 more

      Delete
  20. hi I have this problem:

    someone can help me?

    java.lang.ClassNotFoundException: com.ibm.db2.jcc.DB2Driver
    at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:190)
    at ibm.DBData.getConnection(DBData.java:306)
    at ibm.IBMExtractUtilities.checkTargetTablesStatus(IBMExtractUtilities.java:2503)
    at ibm.GenerateExtract.initDBSources(GenerateExtract.java:1092)
    at ibm.GenerateExtract.(GenerateExtract.java:1113)
    at ibm.GenerateExtract.main(GenerateExtract.java:13472)
    [2017-09-26 15.01.56.659] Driver com.ibm.db2.jcc.DB2Driver could not be loaded. See console's output.

    ReplyDelete
    Replies
    1. This means it can't find the class that is located already in the 2 jar files you need to define in the extra classpath, please make sure you follow the steps correctly, and these two jars are accessable.

      Delete
    2. the problem is that I can't or I don't know how do it.
      Really this problem is because I am doing a migration for a database mysql to db2 and I only have access to the machine through SSH (only console), can you help me?
      I am searching documentacion but I dont found, only here.
      thanks from colombia! (excuse me for the english)

      Delete
    3. This error is not because u don't have access to server other than SSH, it is simply u didn't download & define jar files to yr PC not the server, you need to do this first. Either have these files from DB2 client or server installation CD or use this link to download such files:
      https://developer.ibm.com/answers/questions/264361/db2jccjar-db2jcc-license-cisuzjar-db2jcc-license-c.html

      Delete
  21. but I think that i have it:

    login as: root
    root@10.0.2.81's password:
    Last login: Tue Sep 26 20:53:25 2017 from 10.0.2.242
    [root@ITSTSMR01 ~]# locate db2jcc.jar
    /var/opt/IBM/DB2/V10.5/java/db2jcc.jar
    [root@ITSTSMR01 ~]# ^C
    [root@ITSTSMR01 ~]# cd /var/opt/IBM/DB2/V10.5/java/
    [root@ITSTSMR01 java]# ls
    42E32181339E47B2BF8145FD26612E69.properties db2java.zip db2jcc_license_cu.jar db2umplugin.jar ib6js.jar IBMUpdateUtil.jar sqlj4.zip
    Common.jar db2jcc4.jar db2policy.jar ib6core.jar ib6swing.jar IBMUpdateUtil.prop sqlj.zip
    db2dbgm.jar db2jcc.jar db2qgjava.jar ib6extra.jar ib6util.jar jdk64 us.jar
    [root@ITSTSMR01 java]#

    ReplyDelete
    Replies
    1. These jars exists in the server, you need to copy them to yr PC that u have installed squirrel sql to.
      I think yr PC is normally a windows PC or am I missing something?

      Delete
  22. really in my pc I don't have installed nothing because I work direct in the server by ssh.

    As I said I am working on a migration from a database from mysql to db2.

    I installed a program called IBMdatamovetool in the same directory that the I have installed db2 in the server but and I run it and this problem appear
    Driver com.ibm.db2.jcc.DB2Driver could not be loaded.

    really I don't Know what I have to do

    Thank for all your help

    ReplyDelete
    Replies
    1. Where did u install squirrel sql?

      Delete
    2. where I have to install? in the server of Mysql or db2? , really I have the two databases in the same machine

      Delete
    3. Squirrel SQL is lightweight client that provides UI, I thought you have installed it on your PC not the server. If you have SSH access to the server how you will manage to run the GUI of squirrel SQL?

      I recommend:
      1- Installing Squirrel SQL on your PC.
      2- Copy the two jar files to your PC.
      3- Add the classpath (steps 1 to 7).
      4- Open the ports to connect to MySQL, IBM DB2 from your PC, you can get the correct port number for your DBA or consult the documentation.
      5- Last step (8) defining the alias in squirrel SQL and connect to your DBs.

      Delete
  23. I tried but getting the error as

    java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2057][11264][3.69.24] The application server rejected establishment of the connection.
    An attempt was made to access a database, DEBTPBD9, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004

    ReplyDelete