Linking SQL Server to Heterogeneous Systems


MAK (Muthusamy Anantha Kumar)

The ability to link SQL
Server to heterogeneous systems such as Oracle, DB2, Sybase, etc., is
imminent. This article provides the basic and most important
information required to make a linked server connection to heterogeneous
systems from SQL Server. We are going to deal with three different RBBMS.

  • DB2
  • Sybase
  • Oracle

Prerequisite:

MSDTC service should be running in the SQL Server Box.

Linked server to DB2 database in mainframe S/390:

Install and Configure
Mainframe Client

The known ODBC client for connecting to Mainframe S/390
DB2 is Neon 32 bit drivers. The first step in this process is to install Neon
Client on the SQL Server box. Then create the ODBC system DSN to mainframe.
Provide the Database Source name, Host name, userID, password and port number
in the DSN Setup. In addition, provide the Subsystem information about your
mainframe in the DSN setup. Change the connection mode to “Transactional,” the
X/OPEN XA support to TWO-Phase commit, and X/OPEN XA Transaction manager to
MSDTC. Follow the Snapshots below.

Create Linked Server for DB2

Open
enterprise manager, right click on Linked server under the Security Folder, and
create a new linked server. Select Microsoft OLE DB Provider for the ODBC
Drivers. Give the same system ODBC name that you created for DB2 in the
textbox provided for Data Source. Finally, provide a Userid and password in the
provider string and security tab.

Test the
connection by running the following sql command in query analyzer.


Select * from openquery(DB2C,”Select column1 from table1″)

Latest Articles