Linking SQL Server to Heterogeneous Systems

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")