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.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in DB2, SQL Server
5 comments on “Create a DB2-C Express 9.5 Linked Server in SQL Server 2008
  1. Gilbert says:

    Thanks, Chrissy. I just wanted to provide some feedback that this worked perfectly for me to connect to a remote DB2 v9.5 server on Windows 2003. Thanks for the post!

  2. Prashant Kulkarni says:

    for the sql server error The OLE DB provider IBMDADB2 for linked server returned an invalid column definition for table …there are two solutions
    1)use the following cli configuration key word ..I have tested with DB2 V91fp9 and V95fp5 to be working fine for select, update, delete ,insert 3 part linked server query For DB2 from CLP Prompt…

    set the CLI configuration keyword RETCATALOGASCURRSERVER=0

    db2 UPDATE CLI CFG FOR SECTION COMMON USING RetCatalogAsCurrServer 0.
    db2 terminate
    db2stop
    db2start

    NEXT at sql server end
    1)Close the sql server management studio where the 3 part linkedserver query is used
    2)go to administrative tools->services ..select sqlserver (MS SQL server) service , stop the service first and then start the service.This will enable to load new DB2 ibmdadb2 oledb and CLI instance as set earlier
    3) open the sql server management studio again and connect to sql server
    4)If linked server doesnot exisit then set up the linked server and run the 3 part query ..in this case LUW91 is linked server name ..PRN/SYSIBM is schema and EMP is the table.
    SELECT * FROM [LUW91]..[PRN].[EMP]
    SELECT * FROM [LUW91]..[SYSIBM].[SYSTABLES]you should able to see the results

    SQL Server needs a NULLABLE Catalog in order for it to proceed properly and to call openrowset function. hence above works

    2) in linked server ..go to IBMDADB2 provider properties check the box "level zero only" which calls another layer of oledb interface … but you need to specify the 2-parts name instead of 3-part naming.
    select * from MyLinkedServer…DEPARTMENT

    make sure that "allow in process" is checked in both of the solutions

    regards
    Prashant
    IBM DB2 OLEDB team

    • /****** Object: LinkedServer [DB5] Script Date: 10/28/2010 10:05:05 ******/
      EXEC master.dbo.sp_addlinkedserver @server = N'DB5', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', @datasrc=N'PMDV', @provstr=N'Provider=DB2OLEDB;User ID=admin;Password=password;Initial Catalog=PMUDV;Network Transport Library=TCPIP;Host CCSID=1208;PC Code Page=1252;Network Address=139.104.40.68;Network Port=55000;Package Collection=PMDV;Default Schema=FP_REPO;Process Binary as Character=False;Connect Timeout=2000;Units of Work=RUW;DBMS Platform=DB2/MVS;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;DateTime As Date=False;Auth Encrypt=False;Max Pool Size=100;AutoCommit=True;Authentication=Server;Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;', @catalog=N'PMDV'

      After creating the Linked server to DB2 using Microsoft OLEDB driver for DB2
      I'm getting the below error when executing below query:

      SELECT * FROM OPENQUERY(DB5,'SELECT * FROM PMDV.FP_REPO.TABLE_NAME')

      Error:
      OLE DB provider "DB2OLEDB" for linked server "DB3" returned message "A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42502, SQLCODE: -552".
      Msg 7321, Level 16, State 2, Line 1
      An error occurred while preparing the query "SELECT * FROM PMDV.FP_REPO.C_D_COST_CENTER" for execution against OLE DB provider "DB2OLEDB" for linked server "DB3".

      Could any one help please….
      Regards
      Ramesh
      call 6029108770

  3. Kjon says:

    You had betterl luck than I with the DB2 driver. After installing 9.5fp4 runtime client, any attempt to access the DB2 catalog via the linked server caused my SQL 2008 R1 instance to hang – taking the Management Studio connection with it.

  4. Grant says:

    How/where do I get a DB2 driver to create a linked server within SQL Server 2008 Standard? The DB2OLEDB.exe fails due to invalid version. It's looking for Enterprise or Developer.

1 Pings/Trackbacks for "Create a DB2-C Express 9.5 Linked Server in SQL Server 2008"
  1. […] the box “Allow Inprocess” under the IBMDA400 Provider options and this error went away. I then experimented with a few different SELECT statements to see which one was easiest. Ultimately, I ended up using the following queries to import the data […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*