CODEWITHSUNDEEP
sql-serverodbcinformix
navya
navya

Reputation: 55

Import subsets of data from INFORMIX to SQL Server 2012

I have no experience with Informix. What is the best way to import subsets of data from INFORMIX to SQL Server 2012.

I need daily refresh and also a one-shot (for a part)

With ODBC drivers? which version?

thank you!

Upvotes: 0

Views: 660

Answers (2)

jsagrera
jsagrera

Reputation: 2013

4.10.xC9 would be more appropriate ;) JC9 is the suffix used for the JDBC driver. (UC9 is Unix 32-bit, TC9 is Windows 32-bits and FC9 64-bit on both Unix/Windows, there was also a HCx for stuff like HP 32 on 64)

To connect SQLServer to Informix, I suggest to create a SQLServer linked server (using either the ODBC driver or the OLEDB one). With a linked server you will be able to access the Informix tables as if they were another table within SQLServer.

Running something like this in the SQLServer managment query window:

  !!!!-------INFORMIX ODBC DSNLESS--------!!!!!
EXEC sp_dropserver 'test2' , @droplogins='droplogins';
EXEC master.dbo.sp_addlinkedserver @server = N'test2',
 @srvproduct=N'MSDASQL',
 @provider=N'MSDASQL',
 @datasrc=N'',
 @provstr='DRIVER={IBM INFORMIX ODBC DRIVER (64-bit)};SERVER=dubi1170fc3;DATABASE=stores7;'
EXEC sp_addlinkedsrvlogin 'test2',false,'sa','informix','ximrofni'

will create a linked server "test2" which you can use from SQLServer using the 4 part syntax like:

select * from test2.stores7.informix.systables

There is some info in https://www.redbooks.ibm.com/redbooks/pdfs/sg247884.pdf about linked servers (using OLEDB) and in this stackoverflow post ODBC connection from 64-bit SQL Server to Informix data source

the SQL syntax above assumes you have defined a 'dubi1170fc3' within setnet32

Upvotes: 0

Mark Jamison
Mark Jamison

Reputation: 21

Well, the easiest way would be to grab the latest ODBC drivers from IBM. 4.10.JC9 is most recent version.

As to grab a subset of data, I would need more information to answer less generically than below.

Informix supports Ansi SQL, so if you know how to write an ANSI style query to get the data from SQL Server, you can write a similar style query for Informix.

If you need/want a trickle feed you could even write a Trigger on the table, or tables, in question so that the data is extracted to a file on a daily basis so you could import at your leisure.

BTW, Which Version of Informix?

Upvotes: 2

Related Questions