2012-02-06

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:


24 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. Replies
    1. Make it apple juice; I don't drink ;)

      Delete
  10. 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
  11. 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
  12. 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