How to create a SQL Server link server to IBM DB2

Problem

Creating linked servers is generally a pretty easy thing to do, but the creation of a link server with DB2 is not as simple. To create a linked server to DB2, it requires the SQL DBA to know or guess a lot of parameters that are not familiar or intuitive. In this tip we will walk through the process of setting iup a linked server to DB2.

Solution

In order to successfully create a link server with DB2, you need to download and install Microsoft OLDB Provider for DB2.

The easiest way to create the link server is to use the "Data Access Tool" application, which comes with the Microsoft OLDB Provider for DB2 driver installation. In addition, I recommend that you ask for assistance from your DB2 DBA.

Data Access Tool

The Data Access tool is very easy to use. As soon as the application is launched, click on File -> New -> Data Source and it will show the following screen. Unless the DB2 source platform is known you may need to consult with the DB2 DBA to assist in picking the correct platform.

In the next step, enter the DB2 IP address and TCP port used to connect to DB2.

In the next screen enter the Initial catalog (database you want to connect to), the Package Collection, the Default schema and Default qualifier. Again, if you are unsure consult your DB2 DBA.

Here is another set of parameters that are required to connect to DB2. If you are unsure which options to select, consult your DB2 DBA.

In the next input window we enter the DB2 user that will be used to impersonate our SQL User. This DB2 user will need to have appropriate access rights to DB2 and this user will need to be created by the DB2 DBA.

On this screen additional options can be selected. For more information on any of these items, you can click on the Help button.

At the end of the wizard, click on Connect to verify if we have connectivity with DB2. If so, click on Sample Query to make sure we can successfully query DB2.

click on Sample Query to make sure we can successfully query DB2

If we have successfully tested connectivity, we are now ready to create our SQL to DB2 link server.

Right click on the newly created data source and click on Display Connection String. In my case the connection string looks like the following:

Provider=DB2OLEDB;User ID=db2user;Password=mypassword;Initial Catalog=mydatabase;Network Transport Library=TCP; Host CCSID=37;PC Code Page=1208;Network Address=192.168.9.65;Network Port=446;Package Collection=MSDB2COL; Process Binary as Character=False;Connect Timeout=15;Units of Work=RUW;DBMS Platform=DB2/MVS;Defer Prepare=False; Rowset Cache Size=0;Max Pool Size=100;Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;

Create Linked Server

Finally, we can start the link server wizard.

Right click on Linked Servers and select "New Linked Server...".

On the New Linked Server window, the linked server can be any name. The provider has to be Microsoft OLDB Provider for DB2. Enter product name as DB2OLDB. In catalog, copy and paste the connection string that was created.

On the Security page, select "be made by using this security context" and enter the DB2 user name and password.

If everything works fine we should be able to see our DB2 link server as shown below.

Next Steps

The new DB2 UDB can be downloaded from the IBM website http://www.ibm.com/developerworks/downloads/im/udb/ and installed on a variety of platforms such as Windows, Linux and so on.
Read these additional tips about Linked Servers

About the author

Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips

Comments For This Article

Monday, October 4, 2021 - 5:49:25 AM - Francesco Mantovani	Back To Top (89303)
YES! YES! YES! YES! YES! This worked on SSMS latest version of October 2021! But please fix the last image: the string generated should be inserted into the "Provider string" not the "Catalog"

Wednesday, September 25, 2019 - 3:45:41 PM - Angela Love	Back To Top (82570)
Hi and thank you for this helpful post. We currently have a SQL Server linked server set up to IBM DB2 on mainframe using Shadow Direct drivers. We want to replace Shadow Direct with DB2 Direct drivers. What steps would we need to take? Is it simply modifying the Linked Server definition?

Tuesday, December 12, 2017 - 7:00:40 PM - Lonnie kendall

I am trying to create a linked server to an iSeries. Everything goes fine until I try to connect at the end. I then get the error "Cannot initialize the data source objectof OLE DB provider "DB2OLEDB" FOR LINKED SERVER iseries

(microsoft SQL Server, Error:7303)

Any suggestion on how to resolve this. I am a iSeries programer and working in c#.

Thursday, December 8, 2016 - 2:59:01 PM - Naveen Badey

Hi,

Thanks for this wonderful post. I was able to use it to a greater extent and I want to point out few things that worked out for me with some alterations.

I am connecting to a DB2 server hosted on Windows platform (DB2/NT). Please note the main correction I did being used the @PROVSTR parameter instead of @CATALOG

----------------------------------------------------

USE [master]

EXEC master.dbo.sp_addlinkedserver

@server = N'TESTSRV',

@srvproduct=N'DB2',

@provider=N'DB2OLEDB',

@datasrc=N'',

@provstr=N'Provider=DB2OLEDB;User ID=XXX;Password=XXX;Initial Catalog=TWS;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=;Network Port=50000;Package Collection=TWS;Default Schema=MDL;Process Binary as Character=False;Connect Timeout=15;Units of Work=RUW;DBMS Platform=DB2/NT;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;DateTime As Date=False;Auth Encrypt=False;Max Pool Size=100;Database Name=TWS;AutoCommit=True;Authentication=Server;Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;',

@catalog=N'SAMPLE'

----------------------------------------------------

Monday, October 17, 2016 - 10:48:08 AM - TigerJohn

Oops, tags got stripped.

Distributed Tranactions: Yes
Initial Catalog = DatabaseName
Package Collection = SchemaName
Default Schema = SchemaName
Connection Pooling = Yes
Derive Parameters = Yes

Monday, October 17, 2016 - 10:03:11 AM - TigerJohn

Just wanted to say this works great! I was having trouble getting the linked servers to show any tables using the traditional linked servers config, but after finding your post and applying a few tweaks, I managed to get exactly what I needed. Thanks!

For others who come along later, here's what I successfully used.

Distributed Tranactions:
Initial Catalog =
Package Collection =
Default Schema =
Connection Pooling =
Dervice Parameters =

Right-click new UDL, Locate. Open UDL file in notepad, copy entire "Provider=...." string.
Add new linked server.
Select 'Microsoft OLE DB Provider for DB2'.
Give it a name, paste connection string in "Provider String". Leave everything else blank.
OK!

Monday, May 26, 2014 - 6:34:05 AM - Klingo	Back To Top (31942)
The TestConnection on the Management Studio is successfull but when i look under the linked databases it does not show me tables, what can I do?

Tuesday, December 10, 2013 - 6:46:33 AM - sai krishna

Hello everyone.

I am just following the same process what you said here. When I click on 'Connect' button at Validation step(7th screenshot), I am getting the following error.

Could not connect to data source 'New Data Source':
A TCPIP socket error has occured (10061): No connection could be made because the target machine actively refused it.
SQLSTATE: 08S01, SQLCODE: 10061

Can anyone please help me how to overcome this issue.

Eagerly waiting for the reply. Thanks in advance.

Best Regards,

Sai.

Friday, June 14, 2013 - 10:45:53 AM - Andreas Kelm

Hello, I could download and install the DATA ACCESS TOOL, but during configuration the 
account information, I could not use an username with "_" like user_name, it fails :-(
Are there any possibility to use " ore [ in order to use such a username ?
Thanks Andreas

Friday, October 22, 2010 - 9:35:50 AM - Chris Gelhaus	Back To Top (10291)
Hello, I didnt see it mentioned in the tip so I thought I would mention it. Unless something has changed in SQL 2008, the OLEDB Driver for DB2 is only available for SQL Server Enterprise and Developer Editions. DBA's who have SQL Server Standard edition need to use any edition of IBM DB2 Connect. Thanks, Chris

Thursday, October 21, 2010 - 11:51:45 AM - Bob	Back To Top (10284)
This worked for me but I think you meant to paste the connection string into the Provider String field when creating the linked server. The Catalog field is to specify the initial catalog (database) to connect to. I'm using SQL 2008 R2 Developer SSMS.

Thursday, October 21, 2010 - 11:48:24 AM - Keith Lindsey	Back To Top (10283)
Might want to include that the ole db2 will not install on standard editions of sql 2005