"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:
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:
How do you configure oracle driver for squirrel-sql-3.3.0?
ReplyDeleteDear,
DeleteYou can find the steps here
Thanks for these steps. i did it :)
ReplyDeleteI get this error when I try to connect as above
ReplyDeleteError: DatabaseMetaData information is not known for server DB2/NTSQL09058 by this version of JDBC driver
SQLState: null
ErrorCode: -99999
Do you know the reason?
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
DeleteI am facing the same error...
DeleteCan you help me if you found something....
I have installed new version of squirrel-sql.
Try posting in this forum http://www.dbforums.com/db2/
DeletePlease 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
Thank You Ashraf Fouad....!
ReplyDeleteIt's helped me...
Hey thanks a lot. This was very very helpful
ReplyDeleteAny time, happy to help.
ReplyDeleteThanks for the wonderful set of steps to configure SQuirrelSQL with DB2.
ReplyDeletethanks for that post, v helpful
ReplyDeleteCan I build the insert statements using the result of select statement ?
ReplyDeleteThis is one of the best useful features, here are the steps:
Delete1- 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.
Wow! I owe you a beer!
ReplyDeleteMake it apple juice; I don't drink ;)
DeleteHello
ReplyDeleteI'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...
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...?"
DeleteI 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?
Well, to configure the ODBC connection I didn't need to specify neither the port nor the database name...
DeleteI'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
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.
DeleteAnyway, 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
Thanks for your quick answers. I actually didn't expect any answers in this year anymore...
ReplyDeleteI 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
:) Happy holiday for u
ReplyDeleteI configured DB2 to support SSL. When I try to connect Squirrel client with DB2, I get below error:
ReplyDeletejava.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)).
This comment has been removed by the author.
ReplyDeleteI get the following error when I try to connect server from Alias in Squirrel SQL client...any solutions would be appreciated...thanks in advance
ReplyDeletejava.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
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
DeleteI 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?
Thank You!
ReplyDeleteu r welcome :)
DeleteGracias, funcionó de maravilla.
ReplyDeleteDe nada :)
DeleteThanks Ashraf! Works great.
ReplyDeleteThank you Ashraf
ReplyDelete:) welcome
DeleteAOA:
ReplyDeleteI 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
Dear Irfan,
DeleteWa 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.
Dear Ashraf, Salamun Alekum
DeleteSorry 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
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?
DeletePlease paste the location or screen shot of your setup.
Thanks Ashraf,
DeleteI 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
Happy that u started working.
DeleteThis 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
Thanks for your help
DeleteThanks man, this is still really useful, even in 2016.
ReplyDeleteThanks
DeleteThank 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:)
ReplyDeleteThank you, very helpful
ReplyDeleteThanks
ReplyDelete:)
DeleteThanks man, quick and easy to follow guide. Helped a ton!
ReplyDeleteWelcome :)
DeleteHey Ashraf,
ReplyDeleteI have hostname = z1u11udb.corp.cat.com
port = 3708 and instance = z1u11
What would my url = jdbc:db2://:/
look like ?
Thanks
AB
Should be:
Deletejdbc:db2://z1u11udb.corp.cat.com:3708/z1u11
I tried it out...
DeleteI 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
hi I have this problem:
ReplyDeletesomeone 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.
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.
Deletethe problem is that I can't or I don't know how do it.
DeleteReally 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)
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:
Deletehttps://developer.ibm.com/answers/questions/264361/db2jccjar-db2jcc-license-cisuzjar-db2jcc-license-c.html
but I think that i have it:
ReplyDeletelogin 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]#
These jars exists in the server, you need to copy them to yr PC that u have installed squirrel sql to.
DeleteI think yr PC is normally a windows PC or am I missing something?
really in my pc I don't have installed nothing because I work direct in the server by ssh.
ReplyDeleteAs 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
Where did u install squirrel sql?
Deletewhere I have to install? in the server of Mysql or db2? , really I have the two databases in the same machine
DeleteSquirrel 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?
DeleteI 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.
I tried but getting the error as
ReplyDeletejava.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