I’m using TOAD (v1.1.1.473) at work and have successfully connected to various Oracle databases.
What I need to do now is connected to a DB2 (iSeries) database, but no matter what I try, I cannot get it to work.
In the end, I downloaded a trial version of DBViz, and with the help of others, have managed to connect to the DB2 database. However, there are limitations to using the Trial version, so now I would like to connect using TOAD.
I thought it would be easy as I have the connection strings from my DBViz connection, but its not that simple.
Do I need to connect using the DB2 or ODBC? i’ve tried both - unsuccessfully.
Can anyone talk me thru it?
If you have a licensed version of TDA you can always contact tech support. I was having a connection issue similar with an ORacle 64-bit instance. They were able to help me troubleshoot the issue and get up and running in less than an hour.
For iSeries you need to use ODBC. Give that a try and if you have issues please post a screenshot of what is occurring. I can then get my DB2 specialist to help you.
I have attached the DBViz connection settings, which work perfectly fine.
But i'm not sure how to feed these settings into Toad.
I've tried (unsuccessfully) to set up an ODBC setting. The only DB2 driver I have visible is IBM DB2 ODBC DRIVER - TACOM11
If I try to set it up using a DB2 group, then I get an error - SQL1072N The node directory cannot be found
From the image I have attached, can you talk me thru my Toad connection?
I have attached the DBViz connection settings, which work perfectly fine.
But i'm not sure how to feed these settings into Toad.
I've tried (unsuccessfully) to set up an ODBC setting. The only DB2 driver I have visible is IBM DB2 ODBC DRIVER - TACOM11
If I try to set it up using a DB2 group, then I get an error - SQL1072N The node directory cannot be found
From the image I have attached, can you talk me thru my Toad connection?
From the TDA Connection Manager, Create a New Connection.
From the Group dropdown, select ODBC
To the right of the Data source name field, click on the Open ODBC Data Source Administrator icon. At this point you’ll need to create either a User or System DSN. Either will work. Choose System if you use more than one account to logon to your system.
Click on System DSN tab and click on the Add button
Select the IBM DB2 ODBC DRIVER - TACOM11 and click on finish.
On the Add driver dialog - supply a name for your data source - 8 Chars or less.
Click on the add button. Note you might have the Database already cataloged in the Database alias dropdown in which case you could select the alias and continue. From the message you’re getting it probably isn’t so we’ll continue with adding a new entry. The add button will appear disabled until you supply a name.
Once you’ve clicked on Add, the seetings dialog will be shown. Click on the TCP/IP tab. Enter your database name and alias. I’m assuming clfprd01 is the db name and not the system name. If clfprd01 is the system name, you’ll need to lookup the db name/alias. You can specifiy a different alias if you have multiple entries for the same DB name. The hostname field can be either the hostname or IP address of the system where clfprd01 is installed. The port number is 446.
Click Okay
From the ODBC Data Source Administrator, select the just created datasource and click on Configure. Enter your userid/password and click on connect. The connection should be successful. If not, we’ll need to diagnose which settings are incorrect.
Once the ODBC driver is configured, you will be able to select it from the Data source name dropdown from the TDA Connection Manager and connect using the same credentials you used to test the connection in step 11.
Hi Alan
Thanks for taking the time to respond. I’ve tried this twice and still not working - i’m getting an 08001 error (or something similar).
However, I have since found someone else in the company who has managed to successfully connect, and they tell me it only works with an IBM iSeries ODBC driver. Trouble is, he can’t find his driver, and I’ve been looking for 1 on the net for the best part of an hour and can’t seem to find one. who would have thought it would be so difficult to find…
You should be able to use the ODBC driver included with TDA. There are some prerequisite PTF’s and there could be some issues if you have multiple DB2 clients installed on your system. Let me know if you’d like to continue to work on getting the TACOM11 driver configured.
You can use the ISeries driver as well but I haven’t been able to find just the ODBC driver as a standalone download either I did find it is included with the IBM i Access for Windows. You’ll need to select a version compatible with your environment You’ll find them here:
Sorry.....didn't attach the screen shot to my last message.
I do have 1 question - now that I have added CFLPRD01 as a data source, I can't seem to delete it.
Even though I can delete it from the "Data Source Administrator" dialog, it is still present in the "ODBC IBM DB2 Drive - Add" dialog box
The error in the screenshot indicates a security processing error and reason code 15 indicates it’s at the server. So the good news is your ODBC driver is communicating with the server. Is your DB2 server running on Linux? It could be that the server is not able to decrypt the password. I’m assuming you’ve already verified the credentials you are using to connect are valid Can you let me know the OS the DB2 server is running on?
When you create a DSN for DB2 it will create entries in both the registry and in the DB2 catalog. It sounds as if you’ve successfully removed the system entry but not the DB2 catalog entry. If you haven’t already configured any other ODBC or native (.Net) connections to DB2 from TDA, the simplest thing to do would be to remove the DB2 catalog for TACOM11 and start clean.
We might still need to run a trace as well but I’ll wait to hear from you regarding the DB2 server OS before we make a call on next steps. If you’d like you can send your information to me directly at alan.bala@quest.com