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 Schema=admin;

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 conversions.

12. Click "Security" and provide credentials to get connected to the DB2 instance. 

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.