The following shows you how I got an ODBC connection to DB2 on a Windows Server 2008R2. After doing a search on the Internet I found this page that really helped me a lot. They do it a bit different.
NEW
If you also want to install the IBM Control Center you might want to look at this page. This also installs the ODBC out-of-the-box.
If your need a 32 or 64-bit version for Windows and DB2 version 9.7. You should look for something like this.
On this page you can find and download the ODBC-drivers that fits your system. I you don’t know what version of DB2 that you’re running, you can contact your DBA. The DB2-version can be found using the command DB2level.
Choose either ODBC 32-bit or 64-bit for your DB2-version and download it for Windows.
If your need a 64-bit version of the ODBC-driver for Windows and DB2 version 9.7. You should look for something like this.
NB! You need to create an IBM-account to download the file.
Copy the content of ZIP-file containing the ODBC-driver that you just downloaded to c:\program files\IBM. The path is optional, you can choose a different path if you want to or are using a 32-bit ODBC-driver. The files and folders in c:\program files\IBM, should look something like this.
Start a Windows command-prompt as administrator. If you don’t know how to do this, you can take a look at this page.
Go to the folder c:\program files\IBM\bin and type db2oreg1 –i (if you later want to uninstall it type db2org1 –u).
Go to the folder c:\program files\IBM\bin and type db2oreg1 –setup (Thanks to Noel for this comment).
This will register the DB2 ODBC-driver in Data Sources (ODBC) on Windows. You can take a look at the drivers tab.
NB! If you have installed af 32-bit version of the ODBC-driver on a 64-bit system, then you have to look in the 32-bit version of the ODBC Administrator to find the driver. The 32-bit version of the ODBC Administrator can be found here: C:\Windows\SysWOW64\odbcad32.exe (Thanks to Grae for this comment)
Now you have to setup a ODBC-driver for DB2. You can either make a System or User DSN.
In this example i’m creating a User DSN. Go to the User DSN-tab and press the Add-button.
Choose the DB2 ODBC-driver and press the Finish-button.
Give the DSN a name that you can use in your programs. Here I have just given it the name DB2DB.
Then press the Add-button and go to the Advanced Settings-tab.
Here you need to press the Add-button to add the settings below.
Database: Should point to the database that you want to connect to.
Hostname: Should point to the server where your DB2-server is installed.
Port: The portnumber where the DB2-server on the Hostname is responding.
Protocol: The protocol used to connect to the DB2-server. There’s different options as you can se in the picture below.
The final settings should be the ones listed below.
Now you press the OK-button and you have a new DB2 ODBC connection and your done. You can now use this DB2 ODBC connection in your different programs.
NOTE
If you get the error described below in SAS. Then you most likely didn’t install the correct version 32/64-bit for your operating system. Install the correct bit-version of the Control Center from the IBM DB2 Client Server pack (further information at the start of this post).
The SAS/ACCESS Interface to DB2 cannot be loaded. The SASDBU code appendage could not be loaded
Thank you! I couldn’t find installation instructions anywhere on IBM’s terrible site!
Indeed!
Yes, of course
The IBM terrible and why even myself after 17 years working with Informix is giving up. The MySQL site alone makes me want to start working with that. So well put together and easy to follow. And trying to get my .Net developments in Visual Studio working with Informix or DB2 is always a headache.
This is awesome! Could not find any documentation even remotely close to this on the IBM website. One extra step I had to do was run “db2oreg1 -setup” after running “db2oreg1 -i” since the ODBC Administrator wizard had trouble creating the required config files on the fly when setting up the connection.
Great instructions, thanks!!! And the db2oreg1 –setup command in this comment also saved me, before running it none of my CLI properties would stick when configuring the ODBC Data Source.
Thanks, I have now added the “db2oreg1 -setup” command to the instructions.
Hello,
I was successfully able to install the driver Downloaded from the above link but Instead of IBM DB2 ODBC Driver I am getting IBM DATA SERVER for ODBC . I want IBM DB2 ODBC Driver . How to go about it . Your help is much appreciated . Thanks ,
Abhijeet
Hi Abhijeet,
If I understand your problem correcly. You need to download the DB2 Data Server Client.
I hope this helps you out.
Thanks a lot for your help . I just realised that I had somehow missed to thank you 🙂
Clear set of instructions – been looking over lots of IBM pages for something like this.
I’m trying to install the driver on several new Windows 7 (64 bit) PC’s. after doing the db2oreg1 commands I still don’t get the DB2 driver in the ODBC Administrator.
is there somewhere it could be blocked, or is v10.5 different ?
Dr John
Hi John
I must admit that I haven’t tried to install other versions of the driver or on any other OS.
I hope that someone reading your comment will be able to help you.
Hello,
Same here. I’ve tried to install it as adminitrator on Win7/64 and there’s no entry in drivers list.
Try the 32 bit version: C:\Windows\SysWOW64\odbcad32.exe
This only appies if you installed a 32 bit driver on a 64 system.
I had an auto-sandbox feature in my antivirus which made the driver not show up in the ‘actual’ system. I disabled my auto-sandbox and the drivers showed up in drivers list. DB2 10.5.* drivers (32/64bit)
When you run db2oreg1 you need to do this as administrator, otherwise the command helpfully fails silently. So click on the start button, right click on Command Prompt and select Run As Administrator. Then run db2oreg1.
Thx!! “Command Prompt and select Run As Administrator.”
Hello.
After I press the Finish-button I don’t see next window with “Data source name”.
Hi Victory
Sorry for the late response.I haven’t had that problem, what OS are you using?
I can see that another person is having the same problem.
After I press the Finish-button I don’t see next window with “Data source name”. to create a DSN
Hi Syed
I haven’t had that problem, what OS are you using?
I can see that another person is having the same problem.
ami me sucedia eso al intentar instalar este :
ibm_data_server_driver_for_odbc_cli_win64_v10.5.zip
registraba el driver pero no me dejaba crear la coneccion odbc
al probar este :
v10.5fp5_ntx64_odbc_cli.zip
y siguiendo las instruccione todo funciono correctamente
Gracias, me salvaste.
Windwos 2008 R2 64bit
Hmm, that’s strange. I used the same OS in this post. If I come across anything concerning this kind of error, I will post it here.
Hello Sir,
Thanks for putting such a nice article. It was been of great help.
I followed the step and did manage to get driver installed. I am trying to connect and getting below error.
SQL8002N
An attempt to connect to a host failed due to a missing DB2 Connect product or invalid license.
Please suggest:
a. Do we need DB2 connect ( version 9.7 64 bit) software ( Licensed version) so that the driver can work
b. If yes than what is the work around to connect to DB2 ( z/OS) using vbscript or .net using code directly.
Please note : We don’t have DB2 Connect v9.7 installed in our machine.
Hi Manish
I must admit, that I have absolutely no experience with z/OS.
I hope that someone reading this will be able to help you.
You’ll need to install the full client, then you can use the db2licm command.
..\bin\db2licm -a db2consv_ee.lic
Excelente tutorial, ya solo falta que lo ponga en practica. Luego les cuento como me fue.Saludos
Your example doesn’t show how you enter the username. My DB2 instance is running as an AD Domain user (i.e. contoso\zzztsmservice). The driver doesn’t seem to like the backslash for the username.
How is your DB2 server on Windows running, as a local user on the server, or as a Domain account?
This is a well written article, but I do not see the driver appearing in my odbc datasources. I am using a windows 7 x64 pro system. I followed all of the procedures in your article, and had no problem. I even downloaded the v9.7fp9_ntx64_odbc_cli.zip client, and repeated the procedure. No luck getting the driver to display in the odbc datasource module.
Any suggestions are greatly appreciated.
Strange, some has reported the same because of a 32-bit/64-bit problem. But it seems you have 64-bit all the way through. But you could try looking at the 32-bit version of the ODBC-administrator at C:\Windows\SysWOW64\odbcad32.exe and see if it appears there.
It´s a great and helpful manual.
Thank’s a lot.
Thank you so much.. 🙂
Thanks for this information it was very helpful!
Thank you very much for so detailed tutorial, I successfully connect to my DB2 database just following the steps, it’s awesome!!!
BTW, when I execute command db2oreg1 -setup, I got stuck for a while because there seemed nothing happened, but when I restart my computer, I see ODBC data source administrator has a new driver point to the location of DB2 ODBC driver with correct version.
after successful creation of DSN, I am accessing it from Visual Studio the same work properly but after deploying the ASP.NET web site on 2003 server below error occur.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Thanks for this!
THX
I have this problem, when I test the connection:”SQL1598N An attempt to connect to the database server failed because a licensing problem. SQLSTATE=42968″… I need help, thanks.
I have the same issue. Any resolution?
I have the same issue, did you resolve it?
There’s a db2consv_as.lic file you need to put in the License folder. I’m not sure where it comes from, it seems auto-generated.
Hello, I got licensing problem once I installed ODBC driver 64 bits… SQL158N An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968. Can you please help here?
Thanks,
Tanya
excellent ….great help…………..
Wow, IBM should be paying you big time, you saved my butt. IBM’s site sucks so bad in that it cannot provide this info.
Hi ,
We do followed the same procedure which was explained above , but i could not able to do test connection . I am getting licensing issue . Did the package comes with *.lic file too ?
Thanks.
Hi, I don’t think you need a licensfile. I have never been asked for a licensfile.
But we are receiving error saying that “SQL1598N – An attempt to connect to the database server failed because of a licensing issue . SQLSTATE=42968 “.
Any suggestions.??
Sorry, but I have never had that problem. I hope somebody reading this post will be able to help you.
Great article. A few notes on my experience in getting Excel 2013 to use ODBC to DB2 via MS Query on Win7 64bit… First, had to use the 32 bit driver (think MS Query may still be 32 bit). I used 10.5 and installed as above, using both the commands above to register it (being sure to run cmd shell as administrator). From there used 32 bit ODBC to confirm driver was available and to set up a *User* DSN as described above (Excel does not seem to see system DSN’s). Final key to the puzzle is to run Excel in compatibility mode for Win XP SP3 (right click on Excel and set).
Thanks a lot!!!!!!!!!!!!!!!!!!!!!!!!!
god bless with you!
고맙습니다.
Thank you, very useful!
Por si acaso alguien sabe que diferencia existe con el db2oreg1.exe y db2oreg132.exe
A couple of hints:
1. At the command prompt, it seems the command to install has changed. Type:
db2cli uninstall -setup
Note: To access help, type db2cli -help
2. In Windows 10 or Windows 8, installation must be run from an Administrator Command Prompt. Right click Command Prompt in Apps, and choose More > Run as administrator. If run from a regular Command Prompt, installation will appear to complete correctly, but the required registry entries will not be added, and the driver will not appear in odbcad32.exe.
Sorry for typo; the command is:
db2cli install -setup
Hey Bob..Thanks..You saved my day…above command works in Windows 10 with 64 bit installation.
This is a great document. Thank you so much 🙂
Thanks to this document I was able to finish my installation, so I want to give it an additional input in gratitude.
For some unknown reason my ODBC Administrator crashed and I have been forced to install de 32 bits version drivers. As per DB2 10.5.7 there are no 32 bits drivers for windows available for download, but that’s because they are included on the 64 bit download.
Which are db2cli.dll and db2odbc.dll (similar to the 64 bit ones but without the “o”).
In order to register the 32bit drivers we will have to use “db2oreg123” instead
I hope that this info may be helpful for someone else. Additional info can be found here:
http://www-01.ibm.com/support/docview.wss?uid=swg21384435
Sorry for the typo, I meant “db2oreg132”
Hi Raul. Thank you very much for this.
Hello Dear,
Very clear explanation which helped me a lot. but unfortunately I got messed up with somethings.
Added drive successfully, then to rename the “Host name” I deleted it and tried adding it once again but I’m unable to add the drive with same “Data Source Name”.
One more thing is that, deleted driver “database alias” still reflects in its dropdownlist.
Could you please suggest me something how to over this thing.
Thanks a lot. Good Day..!!
Thanks for this its useful. But how do i use it in my Oracle SQL Developer now?
Thanks for the post.
I need help to configure IBM DB2 ODBC Driver – DB2COPY1, where i need to setup through CMD line because I am automating through puppet. I dont wana use GUI.
DSN configured as: ABCD
USER ID: USER!@#
Password: nsfnda
Port Number: 3134
Hostname: dfg-qwer-app.novonet
Subsystem: ABC
I tried with these command
>db2cli registerdsn -add DATG_Print
>db2cli writecfg add -dsn DATG_Print -database BLUDB -host datg-db2-app.via.novonet -port 3134
Any help would be appreciated
I Have the same problem, I tried it with copiying the regestry keys, but they don’t contain all data stored in the GUI.
Have you found a way to automize that?
Great Instructions. Couldnt find it anywhere else even with IBM documentation
Many thanks for taking the time to share these details, it worked for connecting to an old DB2 v8.2 database.
In which place the DSN details will store.
Awesome… thansks for detailed steps
Hi,
Please advise,
I followed above all the steps and successfully installed the ODBC driver for DB2 but, I don’t know how to create a linked server using this data source in SQL Server. I trying to create a linked server from SQL Server management studio to connect DB2 database.
Thanks in advance.
I followed all the instructions but I am facing below error .
An attempt to connect to the database failed because of a licensing problem SQLSTATE =49268.. can anyone please suggest me what should I do to fix it.
Thanks for the guidance.
Hello,
I get the following error after following these instructions. I get
SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: “username”, Operation: “EXECUTE”, Object: “NULLID.SYSSTAT”, SQLSTATE=42501
Strangely, I am able to connect to db2 database when using Squirrel SQL Client, however, I need to figure out out the ODBC connection in order to interface with Power BI. Please advise on what can be done to resolve this issue. Thanks in advance.
When someone writes an post he/she keeps the image of a user in his/her mind that how a user can be aware of it.
So that’s why this article is amazing. Thanks!