SQL Linked Server connation to Informix database

Setting up connection to Informix Database through SQL Management Studio was not so easy anyhow with a bit of work and research it could be accomplished.

Im using MS SQL 2014 stander edition, with Informix database. The system localz need to be set too as it depend on the Informix setting, in my case Informix was hosted on Linux (Red Hat 6 Server) with Arabic language.

First: Informix SDK and locals

First we need to install the Informix sdk on the machine that is running SQL Management Studio . In my case I downloaded the latest 64bit from IBM web site (clientsdk.4.10.FC4DE.WIN) and installed it.

And then as I need the correct locals (Arabic Language) I installed the “Informix International Language Supplement” in my case it was “Informix International Language Supplement 3.30.MC2_C511MML” and selected Arabic “ar_AE.1256”

Even though it did not find the Informix directory I only needed to provide the path (“C:\Program Files\IBM Informix Client SDK”) and everything went fine.

If you are here you can configure the odbc driver and test that all setting in your host are correctly buy testing the ODBC 64bit setting.

Secondly:

I run the following in command prompt

            regsvr32 ifxoledbc

And to to prepare the Informix server to work with OLEDB we need to run a script on the master database of the server itself.

The script file can be found in the directory for the Informix SDK that we installed in the first step, we need to copy it to the Linux server hosting the Informix database and run it by using the dbaccess command as below:

                      dbaccess sysmaster coledbp.sql

Finally: inked Server Setting on SQL Management Studio

Before starting the linked server you need to change a bit of the driver setting by expanding the provider node and right clicking on the ifxoledbc and selecting properties.

We need to check the Dynamic parameter (The first name) and allow inprocess as below:

Starting the linked server setting, I opened Microsoft SQL Management studio, when to Server objects and then linked servers and right clicked and selected new linked server.

Added the following setting

General Tab

Linked Server:                   AnyNameYouLike  (for me I used INFORMIX_DB)

Provider:                             IBM Informix OLEDB Provider

DProduct Name:              ifxoledbc

DataSource:                       DatabaseName@ServerNAme

Provider String:                                 DB_LOCALE=ar_AE.1256;CLIENT_LOCALE=ar_AE.1256

Security Tab:

Be Made using This Security context:

Remote login:                    your login to the Informix database

With password:                your password to the Informix database

Server Options Tab

Change the RPC and RPC OUT to true

Note: If you don’t have special language setting you can leave the provider string empty.

Thats it, you start writing your T-sql and if everthing whent write you will start seeing your data

SELECT * FROM [INFORMIX_DB].[databaseName].[informix].[TableName]

Reference:

I got a lot of help from these articles:

• http://stackoverflow.com/questions/24530257/informix-odbc-connection-to-64-bit-sql-server
• http://www-01.ibm.com/support/docview.wss?uid=swg21195578