Making Microsoft SQL Linked Server and Informix Standard engine work

Abstract

Microsoft SQL Server is a popular choice for consolidation of report data from multiple database products, as its Linked Server feature – which is included in the base product – makes it possible to access tables in heterogeneous databases within T-SQL via OLE DB or ODBC.

However, the settings are not obvious that are required to make this work with Informix Standard Engine (SE) – the legacy version of Informix that uses C-ISAM to store tables (*.dat) and indexes (*.idx) in individual files (whereas Dynamic Server manages storage within its own “dbspaces”).

This article reveals how to make Microsoft SQL Linked Server and Informix Standard Engine work together.

Content

Firstly, download and install the latest Informix drivers for Windows:

www.informix.com
Downloads & Resources
Informix Client Software Development Kit (CSDK)
Windows 32-bit (4.10.TC5DE) or 64-bit (4.10.FC5DE) matching your SQL Server product

OLE DB is not available for Informix SE databases, so you have to use ODBC.

Start up the SQL Server Management Studio, and find “Linked Servers” in the Object Explorer. Set as follows:

Defaults are all unchecked.
Errors result unless “Allow inprocess” is set.
Other changes are required for full functionality.

Create data sources next (you only need one for multiple SE databases on the same host) using:

Windows Control Panel
- Administrative Tools
  - ODBC Data Sources (32-bit) or ODBC Data Sources (64-bit)

64-bit Windows 7 (or below) only shows an icon for 64-bit ODBC Data Sources. If you do need 32-bit, run:

Copy to Clipboard

An example completed System DSN using the IBM INFORMIX ODBC DRIVER (from an actual installation) is:

Server Name should be a meaningful legal Informix object name.

Host Name can be a name (if it resolves) or IP address.

Service is the port number on which “sqlexecd” is listening on the Informix server (there is not much point creating a name to use instead in the local Windows “services” file).

Protocol must be “sesoctcp”.

Database Name must the full path of the SE directory on the Informix server without the “.dbs” extension. Setting DBPATH does not work here!

User Id must be an operating system login on the Informix server with appropriate Informix access privileges.

The easiest way to create a Linked Server is by using T-SQL (not the GUI), for example:

Copy to Clipboard
Syntax HighlighterEXEC sp_addlinkedserver
@server = 'LAP',
@srvproduct = 'Microsoft OLE DB Provider for ODBC',
@provider = 'MSDASQL',
@datasrc = 'Millwheel',
@provstr = 'Driver={IBM INFORMIX ODBC DRIVER (64-bit)}; Server=lloyds_se; Database=/data/soldata/mwheel;';

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'LAP',
@useself = 'FALSE',
@rmtuser = 'odbc',
@rmtpassword = 'password';

SQL Server Linked Servers can make it equally easy to access tables in other database products using a correctly configured OLE DB provider. For example, instead of unloading data, copying the file produced and reloading into SQL Server, this can be achieved much more efficiently in terms of coding, maintenance and run time with:

If you have problems with SQL Server not locating Informix CSDK, set INFORMIXDIR to the correct installation directory:

Control Panel
- System
  - Advanced
    - Environment Variables
      - System Variables

You should now be able to explore Informix tables in Microsoft SQL Server Management Studio. However, due to limitations in ODBC rather than OLE DB, you cannot expand each table to show column names.

In T-SQL, you can use 4-part naming to reference Informix tables directly as long as you omit the catalog and provide the owner name. An actual example was:

Copy to Clipboard
Syntax HighlighterSELECT * FROM LAP..ray.haulage WHERE hl_carr_cd LIKE '99';

This is much more usable than wrapper functions such as the one below, which cannot employ nested loop joins between local and Informix tables in the same statement, etc.:

Copy to Clipboard
Syntax HighlighterSELECT * FROM OPENQUERY(LAP,'SELECT * FROM haulage WHERE hl_carr_cd LIKE "99"');

Conclusion

SQL Server Linked Servers can make it equally easy to access tables in other database products using a correctly configured data provider. For example, instead of unloading data, copying the file produced and reloading into SQL Server, this can be achieved much more efficiently in terms of coding, maintenance and run time with:

Copy to Clipboard
Syntax HighlighterINSERT INTO local-table remote-query-statement

Disclaimer

The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.