Creating a Linked Server to DB2
Applies to: Microsoft SQL Server 2008 R2 RTM - Express with Management Tools,
IBM DB2 9.7
We created the following linked server for testing purposes. We installed
Microsoft SQL Server 2008 R2 and DB2 9.7 on the same machine.
Please follow the steps below to create a linked server from SQL Server 2008 R2
via ODBC to an IBM DB2 database:
1. Let's create a System DSN source opening the ODBC Data Source Administrator.
2. Click System DSN tab and then click the Add button.
3. In the "Create New Data Source" window, choose "IBM DB2 ODBC DRIVER" from the
drivers available and click Finish.
4. In the "ODBC IBM DB2 Driver - Add" window, specify some name for the DSN in
the "Data Source Name" textbox, use the combo box to select the database on the
DB2 instance and provide some description in the "Description" textbox.
Once the DSN is created, we can create the linked server using the steps below.
5. Open SQL Server Management Studio and connect to the SQL Server. Expand
"Server Objects" and then "Linked Servers".
6. Right click on "Linked Servers" and select "New Linked Server".
7. In the "Linked Server" textbox, assign a name to the linked server.
8. Use the "Provider" combo box to select "Other data source" for "Server Type"
and then choose "IBM OLE DB Provider for DB2".
9. Type "ODBC Provider for DB2" for the "Product name".
10. Type DSN name in "Data source" textbox.
11. Type the following connection string on the "Provider string" text box:
Initial Catalog=SAMPLE;Data Source=SAMPLE;HostCCSID=1252;Network
Address=172.16.31.1;Network Port=50000;Package Collection=admin;Default
To understand better this string, let's describe parts of it.
Initial Catalog = the database name on the DB2 instance.
Data Source = Name of System DSN created
Network Address = IP address of the DB2 instance.
Network Port = Default port for connecting to DB2.
HostCCSID = Host Coded Character Set Identifier is how character data is sent to
the host and should match the DB2 database character set. In this case 1252
(ANSI - Latin). The DB2 instance resides on the same server where the SQL Server
2008 R2 instance is located HOSTCCSID=PC code page.
PC code page = character set used on the Windows computer to perform character
12. Click "Security" and provide credentials to get connected to the DB2
13. Click "Server Options" and enable the RPC Out option.
14. Click OK
15. Let's run some queries against the linked server we just created.