Defining a DB2 as a linked server in SQL Server 2005
This example shows how to add a remote DB2 as a linked server, using the MS OLEDB provider for DB2 (I mentioned it yesterday).
EXEC sp_addlinkedserver
server='LINKEDDB2',
@srvproduct='Microsoft OLE DB Provider for DB2',
@catalog='DB2',
@provider='DB2OLEDB',
@provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'
You can also define a linked server using graphical admin tools: SQL Management Studio (SQL 2005) or SQL Enterprise Manager (SQL 2000).
It also works, of course, with Oracle, or other OLEDB-accessible data providers.
Find other connection strings for the MS OLEDB provider for DB2, and others at connectionstrings.com.
After you define the linked server, the basic syntax for accessing the remote data uses standard, four-part names:
SELECT * FROM LinkedServer.Catalog.Schema.Table