Close Encounters :: Microsoft OLE DB Provider for DB2

You sometime have to deal with ODBC Data Source.

You don’t want to, but you have to.

I’m currently finalizing a post about PolyBase, that typical blog post that takes 4 days instead of 4 hours to be over.

And it turns out I have to install an ODBC connector.

This scares me a bit because I was expecting PolyBase to be a replacement for Linked Server while instead Linked Server looks like the scaffolding at the very base of it.

But let’s not procrastinate and see how to set it up.

Download and Install

You need to download and install Microsoft OLE DB Provider for DB2 from the official link.

Once downloaded you can install it as a normal executable.

Setup

Once installed you can search for Data Access Tool on your computer.

This fella will be our new friend and it’s going to help us create a source string.

Now create a new Data Source for DB2.

The tool will help you create the connection string through a GUI. In the last few steps you can test if the connection works: this is the proof that the string we are going to use is working.

Once over go to Connection String to Copy/Paste the string which contains all the ingredients:

You now got to SSMS > Right click on Server Objects > Linked Servers > Right click on New Linked Server , and you are going to face this scenario: add all the ingredients as follow:

Linked Server: is how your Linked Server will be called

Provider: Set Microsoft OLE DB Provider for SQL Server

Product name: you can put whatever you want

Provider string: is the string you created wit the Data Access Tool, copy/paste it here

Catalog: Is the name of the DB2 database

Then go to the Security and under “Be made using this security context” add:

Remote login: your DB2 user

With password: your DB2 password

And you can now successfully query DB2 from SQL Server

That’s All Folks