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
