Share via

SQL Linked server using DB2OLEDB provider

  • Article

As SQL Server describes linked server in BOL (Books Online):A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages:

  • Remote server access.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly.

A linked server definition specifies the following objects:

  • An OLE DB provider - in this case DB2OLEDB.DLL
  • An OLE DB data source - in case of db2oledb its database object on the db2 server.

 Scenarios:

1) Remote server access: A typical organization has heterogeneous servers. some of the data might reside on SQL database whereas some other data on db2 servers .When in this case there is a need to perform operations on the data from DB2 server and all we have are the .Net developers and Microsoft SQL Server Programmer who has skills of SQL linked server. That programmer does not know how to get to DB2 database.

In this case the DB2 DBA will just provide the connection string to the SQL DBA. SQL DBA will install the db2oledb feature pack on the SQL server from https://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

And on this SQL instance DBA will create the Linked server to the DB2 data source. That’s it. Now programmer can access data from db2 data source , perform all CRUD operations - execute procedures, create database objects like tables, views, stored procedures etc.

 2)The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise :

lets continue from the scenario above : Consider this is a Automobile manufacturer and there is a "Sales" table on DB2 database that stores the data for Motorcycle sale and there is "Sales" table on SQL server that stores all sales data for the sale of trucks. Now problem the problem developer has is to do JOIN on the tables and get combined sales information .

Yes , Linked server allows you to execute the Queries on the same SQL server instance but Query will actually pull data from heterogeneous data sources and join them. the query will look like :

SELECT ORDER_NO, ORDER_DATE, ... from SALES CROSS JOIN LINKEDSERVER.CATALOG.DATASOURCE.DB2SALES order by ORDER_NO ;

Where SALES is a SQL table for trucks orders whereas DB2SALES is a sales table for Motorcycle orders placed.