Create a DB2-C Express 9.5 Linked Server in SQL Server 2008
In order to emulate a DB2/iSeries<->SQL Server setup that I deal with at work, I installed DB2 Express-C on SuSE Linux Enterprise Server 10. The DB2/Linux Server details, as well as a sample database and table name, are as follows:
| IBM DB2 Database | |
| Network Name | LUCAS |
| TCP Port | 50000 |
| Instance | DB2INST1 |
| Database | SAMPLE |
| Schema/Owner | DB2USER |
| Table | EMPLOYEES |
After the initial install and sample database/table creation, I set out to create a Linked Server in SQL Server 2008. Initially, I installed IBM Data Server Client client tools, which includes a few JDBC drivers, on the SQL Server. One of these providers is "IBM OLE DB Provider for DB2," showed up in the list of Linked Server Provider in SQL Server after the software was successfully installed.
Setting up the Linked Server was pretty straight-forward and initially seemed to work, using the following details:
| General | |
| Name | IBMDB2 |
| Provider | IBM OLE DB Provder for DB2 |
| Product Name | IBMADDB2.DB2COPY* |
| Data Source | SAMPLE |
| Provider String | <blank> |
| Location | LUCAS |
| Security Options | |
| Login Info | Your call. I mapped a local user to db2user. |
| Security Options | |
| All Options | Default |
| * IBMADDB2.DB2COPY is the name of the IBM DB2 provider in the Linked Server Provider list. | |
The server was created easily enough, but SQL Server's automatically generated SELECT statements kept erroring out. The syntax was as follows: SELECT fName, lName FROM IBMDB2..DB2USER.EMPLOYEES. Upon execution, the following error was returned:
Msg 7318, Level 16, State 1, Line 1 The OLE DB provider "IBMDADB2.DB2COPY1" for linked server "IBMDB2" returned an invalid column definition for table ""DB2USER"."EMPLOYEES"".
So I added in the database name (IBMDB2.SAMPLE.DB2.EMPLOYEES) to the SELECT statement, executed the query and received the error:
Msg 7313, Level 16, State 1, Line 1 An invalid schema or catalog was specified for the provider "IBMDADB2.DB2COPY1" for linked server "IBMDB2".
Past experience taught me that it was now probably time to look for a new driver. After some research, I found an IBM DB2 Driver in the SQL Server 2008 Feature Pack which can be found: OLEDB Provider for DB2 (DB2OLEDB.exe). According to Microsoft..
The Microsoft OLE DB Provider for DB2 version 2.0 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2008 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor.
After extracting the contents and clicking setup.exe as instructed, the full-on GUI-based installer failed. Ultimately, I had to run the install from the command line: setup.exe /L c:\HISInstall.log /S /INSTALLDIR C:\HIS and it worked flawlessly after a reboot that was required anyway by Windows Update.
Once the promising new driver was installed, it an additional 2 hours to figure out exactly what Linked Server parameters were required. I kept running into an error that said "The parameter is incorrect." Ultimately, I figured out how to set it up by running the Data Access Tool included with Microsoft's DB2 Provider. After I setup a successful connection to the DB2 Server, the Data Access Tool gave me the Provider String that I needed to create a successful connection.
| General | |
| Name | DB2 |
| Provider | Microsoft OLE DB Provider for DB2 |
| Product Name | DB2OLEDB2 |
| Data Source | LUCAS |
| Provider String | Provider=DB2OLEDB;Initial Catalog=SAMPLE;Network Transport Library=TCP;Host CCSID=37; PC Code Page=1252;Network Address=lucas;Network Port=50000;Package Collection=DB2USER; Default Schema=DB2USER;Process Binary as Character=False;Units of Work=RUW; DBMS Platform=DB2/MVS;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True; Connection Pooling=False;Derive Parameters=False; |
| Location | <grayed out> |
| Catalog | SAMPLE |
| Security Options | |
| Login Info | Your call. I mapped a local user to db2user. |
| System Options | |
| All Options | Default |
The Data Access Tool gives the username and password in clear text in the Provider String but I just removed that and chose to setup the username and password under the Security Options. Ultimately, this was the setup that worked for me. I am now able to query the data using a simple 4 part name: SELECT fName, lName FROM DB2.SAMPLE.DB2.EMPLOYEES. Success! If you'd like to create the Linked Server using only T-SQL, run the following command, suited to your environment, of course.
EXEC master.dbo.sp_addlinkedserver @server = N'DB2', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', @datasrc=N'LUCAS', @provstr=N'Provider=DB2OLEDB;Initial Catalog=SAMPLE;Network Transport Library=TCP; Host CCSID=37;PC Code Page=1252;Network Address=lucas;Network Port=50000;Package Collection=DB2USER; Default Schema=DB2USER;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/MVS; Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection Pooling=False; Derive Parameters=False;', @catalog=N'SAMPLE' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2',@useself=N'False', @locallogin=NULL,@rmtuser=N'db2user',@rmtpassword='########'
In my next post, I'll cover a more likely DB2<->SQL Server scenario: creating a linked server in SQL Server 2005 to IBM DB2 running on an AS400/iSeries server.