By Gregory Suarez | Sr. Escalation Engineer | SQL Server

I was recently working with one of our customers when he indicated it would be great if the Microsoft Linux ODBC driver could be used to access his other database systems - in addition to Microsoft SQL Server.   Apparently, he liked the driver so much; the idea of having a single database client stack could simplify administration, reduce the memory footprint of his client application and further increase performance and throughput.  

At the time, I didn’t think much of this – but shortly after his comment, I realized this is something that’s easily accomplished.   After all, the driver is similar to what we currently have running on the Windows platform.  As long as an appropriate OLEDB provider is configured as a linked server within SQL Server everything should be good to go.

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.

* Note, the MS Linux ODBC does not support distributed transaction

See the following article for more details concerning linked servers: http://msdn.microsoft.com/en-us/library/ms188279.aspx

As a test, I decided to use the following components:

1.       Redhat Enterprise Linux 5.x client configured with Microsoft Linux ODBC Driver.

2.       SQL Server 2008 R2 configured with a linked server to IBM’s DB2 (using the Microsoft OLEDB Provider for DB2 )

3.       Sun’s Solaris 11 OS configured with IBM DB2 Version 9.7 Server (x64)


The goal is simply to return results from an IBM DB2 9.7 system running on a Solaris 11 Unix system to a Redhat Linux workstation configured with the Microsoft Linux ODBC driver.  Of course, Microsoft SQL Server is sitting in the middle.

From the Redhat Linux workstation, I created and executed the following script to create the linked server:

 createLinkedServer.sql

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'SOLARIS2')EXEC master.dbo.sp_dropserver @server=N'SOLARIS2', @droplogins='droplogins'

GO
EXEC master.dbo.sp_addlinkedserver @server = N'SOLARIS2', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', @datasrc=N'Solaris2', @provstr=N'Provider=DB2OLEDB;User ID=gregorys;Password=password1;Initial Catalog=TEST;Network Transport Library=TCPIP;Host CCSID=1252;PC Code Page=1252;Network Address=65.53.9.96;Network Port=50000;Package Collection=TEST;Default Schema=DB2INST1;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/6000;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;DateTime As Date=False;Auth Encrypt=False;AutoCommit=True;Authentication=Server;Decimal As Numeric=False;FastLoad Optimize=False;Derive Parameters=True;Persist Security Info=True;Data Source=TEST;Connection Pooling=False;'

/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SOLARIS2',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'pub', @optvalue=N'false'
GO 
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

sqlcmd -i ./createLinkedServer.sql -U xxx -P xxxx -S 65.53.9.94

Next, I created a stored procedure to execute the linked query:

createStoreProcedure.sql

USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Solaris2]
AS
BEGIN
SET NOCOUNT ON;
SELECT [EMPNO],[FIRSTNME],[MIDINIT] ,[LASTNAME],[WORKDEPT],[PHONENO] ,[HIREDATE],[JOB] ,[EDLEVEL] ,[SEX],[BIRTHDATE],[SALARY],[BONUS],[COMM] FROM [SOLARIS2].[TEST].[DB2INST1].[EMPLOYEE];

END
GO

sqlcmd -i ./createStoreProcedure.sql -U xxx -P xxxx -S 65.53.9.94

And finally, I executed the stored procedure.  

Below, we have Redhat Linux pulling data from Solaris/DB2 - compliments of the Microsoft Linux ODBC driver and SQL Server’s linked server functionality.

1> solaris2
2> go
EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT PHONENO HIREDATE         JOB      EDLEVEL SEX BIRTHDATE        SALARY      BONUS       COMM      
------ ------------ ------- --------------- -------- ------- ---------------- -------- ------- --- ---------------- ----------- ----------- -----------
000010 CHRISTINE    I       HAAS            A00      3978          1965-01-01 PRES          18 F         1933-08-24    52750.00     1000.00     4220.00
000020 MICHAEL      L       THOMPSON        B01      3476          1973-10-10 MANAGER       18 M         1948-02-02    41250.00      800.00     3300.00
000030 SALLY        A       KWAN            C01      4738          1975-04-05 MANAGER       20 F         1941-05-11    38250.00      800.00     3060.00
000050 JOHN         B       GEYER           E01      6789          1949-08-17 MANAGER       16 M         1925-09-15    40175.00      800.00     3214.00
000060 IRVING       F       STERN           D11      6423          1973-09-14 MANAGER       16 M         1945-07-07    32250.00      500.00     2580.00
000070 EVA          D       PULASKI         D21      7831          1980-09-30 MANAGER       16 F         1953-05-26    36170.00      700.00     2893.00
000090 EILEEN       W       HENDERSON       E11      5498          1970-08-15 MANAGER       16 F         1941-05-15    29750.00      600.00     2380.00
000100 THEODORE     Q       SPENSER         E21      0972          1980-06-19 MANAGER       14 M         1956-12-18    26150.00      500.00     2092.00
000110 VINCENZO     G       LUCCHESSI       A00      3490          1958-05-16 SALESREP      19 M         1929-11-05    46500.00      900.00     3720.00
000120 SEAN                 O'CONNELL       A00      2167          1963-12-05 CLERK         14 M         1942-10-18    29250.00      600.00     2340.00
000130 DOLORES      M       QUINTANA        C01      4578          1971-07-28 ANALYST       16 F         1925-09-15    23800.00      500.00     1904.00
000140 HEATHER      A       NICHOLLS        C01      1793          1976-12-15 ANALYST       18 F         1946-01-19    28420.00      600.00     2274.00
000150 BRUCE                ADAMSON         D11      4510          1972-02-12 DESIGNER      16 M         1947-05-17    25280.00      500.00     2022.00
000160 ELIZABETH    R       PIANKA          D11      3782          1977-10-11 DESIGNER      17 F         1955-04-12    22250.00      400.00     1780.00
000170 MASATOSHI    J       YOSHIMURA       D11      2890          1978-09-15 DESIGNER      16 M         1951-01-05    24680.00      500.00     1974.00
000180 MARILYN      S       SCOUTTEN        D11      1682          1973-07-07 DESIGNER      17 F         1949-02-21    21340.00      500.00     1707.00
000190 JAMES        H       WALKER          D11      2986          1974-07-26 DESIGNER      16 M         1952-06-25    20450.00      400.00     1636.00
000200 DAVID                BROWN           D11      4501          1966-03-03 DESIGNER      16 M         1941-05-29    27740.00      600.00     2217.00
000210 WILLIAM      T       JONES           D11      0942          1979-04-11 DESIGNER      17 M         1953-02-23    18270.00      400.00     1462.00
000220 JENNIFER     K       LUTZ            D11      0672          1968-08-29 DESIGNER      18 F         1948-03-19    29840.00      600.00     2387.00
000230 JAMES        J       JEFFERSON       D21      2094          1966-11-21 CLERK         14 M         1935-05-30    22180.00      400.00     1774.00
000240 SALVATORE    M       MARINO          D21      3780          1979-12-05 CLERK         17 M         1954-03-31    28760.00      600.00     2301.00
000250 DANIEL       S       SMITH           D21      0961          1969-10-30 CLERK         15 M         1939-11-12    19180.00      400.00     1534.00
000260 SYBIL        P       JOHNSON         D21      8953          1975-09-11 CLERK         16 F         1936-10-05    17250.00      300.00     1380.00
000270 MARIA        L       PEREZ           D21      9001          1980-09-30 CLERK         15 F         1953-05-26    27380.00      500.00     2190.00
000280 ETHEL        R       SCHNEIDER       E11      8997          1967-03-24 OPERATOR      17 F         1936-03-28    26250.00      500.00     2100.00
000290 JOHN         R       PARKER          E11      4502          1980-05-30 OPERATOR      12 M         1946-07-09    15340.00      300.00     1227.00
000300 PHILIP       X       SMITH           E11      2095          1972-06-19 OPERATOR      14 M         1936-10-27    17750.00      400.00     1420.00
000310 MAUDE        F       SETRIGHT        E11      3332          1964-09-12 OPERATOR      12 F         1931-04-21    15900.00      300.00     1272.00
000320 RAMLAL       V       MEHTA           E21      9990          1965-07-07 FIELDREP      16 M         1932-08-11    19950.00      400.00     1596.00
000330 WING                 LEE             E21      2103          1976-02-23 FIELDREP      14 M         1941-07-18    25370.00      500.00     2030.00
000340 JASON        R       GOUNOT          E21      5698          1947-05-05 FIELDREP      16 M         1926-05-17    23840.00      500.00     1907.00


Sometimes, it is easy to forget our implementation of the Linux based ODBC driver retains the same functionality found with our existing Windows based ODBC driver.

I hope this topic helps you understand some of the capabilities of the Microsoft Linux ODBC driver.