How to create a linked server with IBM DB2 OLE DB provider

Technote (FAQ)


Question

How do I create a linked server in Microsoft SQL Server Express 2005 using IBM's OLE DB provider?

Answer

In this example we demonstrate how to create a linked server to DB2 V9 by calling sp_addlinkedserver using SQL Server Management Studio Express 2005.

You can download SQL Server Express 2005 and Microsoft SQL Sever Management Studio Express 2005 from: http://www.microsoft.com/Sqlserver/2005/en/us/express.aspx

With SQL Server and DB2 properly installed and configured open Microsoft SQL Server Management Studio Express and execute sp_addlinkedserver to create a linked server to DB2.

    EXEC sp_addlinkedserver @server = N'MyLinkedServer',
    @srvproduct=N'DB2',
    @provider=N'IBMDADB2.DB2COPY1',
    @datasrc=N'SAMPLE',
    @provstr=N'Initial_Catalog=DB2user'

You need to know the unique programmatic identifier (PROGID) of the IBM DB2 OLE DB provider. A typical default installation of DB2 V9 will yield the following PROGID:
    IBMDADB2.DB2COPY1

You may also determine the PROGID from the providers list within Microsoft SQL Server Management Studio Express.

In the above exapmle we specify the default IBM DB2 OLE DB provider PROGID of
'IBMDADB2.DB2COPY1' for @provider.

Next, specify the DBALIAS of your cataloged database for @datasrc. In this example assume we have cataloged the Sample database with a DBALIAS of SAMPLE.

Optionally you can specify a default schema or catalog with @provstr=N'Initial_Catalog=mySchema'

For more information about sp_addlinkedserver view:
http://msdn.microsoft.com/en-us/library/ms190479.aspx

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.1, 9.5

Operating system(s):

Windows

Reference #:

1394344

Modified date:

2009-11-16

Translate my page

Machine Translation

Content navigation