Informix and SQL Server

Greetings all, I apologise for the lengthy rambling text but I have a situation in work that I have been asked to look into so I am coming here for some advice, here goes: We have the following setup. A SCO Unix box running IDS 9.21 A SUSE Linux box running 10.00 and lots of SQL Server boxes running all kinds of stuff. The SCO box has Third Party software running on it, written in Informix, and is also used for development work such as data extracts and reporting. So I still write SQL code, stored procedures and sometimes 4GL code on this box. Everything running on this SCO box looks at the Informix databases on the Linux box and we have no problems with this setup at all, it works fine. Some of the Informix databases on the Linux box are also accessible from some of the SQL Server boxes so I can write T-SQL code to select data direct from the Informix databases. But what I want to do is write Informix code to select data from any of our SQL Server databases, and I know I can do that if I had a version of Informix greater than 10. As the third party software company will not guarantee that their very old code will work on any Informix version above 9 I cannot upgrade the Informix version on the SCO box. So I was wondering that if I bought a runtime version of Informix 11 or 12 and put it onto a Virtual Machine and made it visible to the Linux box and also made it visible to a SQL Server box would I be able to use it as a sort of bridging database to allow SQL Server to write to it and our SCO box to read from it. I thought I would ask the experts to see if its a good idea or a non-starter or if there is a better way to achieve the same results. So please throw any questions, observations or ideas back at me. Thanks for reading The Mongoose

Hello. I am sorry, but your information is incorrect. The only way to Informix can "see" other RDBMS, like SqlServer, in your case, is using a separated product, named Informix Enterprise Gateway Manager (Informix EGM). It does not have anything to do with your Informix version (it´s not an upgrade to a more actual version, that will just be able to do what you need). You obviously should upgrade to, at least, version 11.70, or even 12.10, in order to have the new features, and an oficially supported engine, but that will not turn your Informix able to select and insert data, into another database systems. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: > Subject: Informix and SQL Server [31002] > Date: Mon, 29 Jul 2013 05:30:28 -0400 > > Greetings all, > > I apologise for the lengthy rambling text but I have a situation in work that > I have been asked to look into so I am coming here for some advice, here goes: > > We have the following setup. > > A SCO Unix box running IDS 9.21 > A SUSE Linux box running 10.00 > and lots of SQL Server boxes running all kinds of stuff. > > The SCO box has Third Party software running on it, written in Informix, and > is also used for development work such as data extracts and reporting. So I > still write SQL code, stored procedures and sometimes 4GL code on this box. > Everything running on this SCO box looks at the Informix databases on the > Linux box and we have no problems with this setup at all, it works fine. > > Some of the Informix databases on the Linux box are also accessible from some > of the SQL Server boxes so I can write T-SQL code to select data direct from > the Informix databases. > > But what I want to do is write Informix code to select data from any of our > SQL Server databases, and I know I can do that if I had a version of Informix > greater than 10. As the third party software company will not guarantee that > their very old code will work on any Informix version above 9 I cannot upgrade > the Informix version on the SCO box. > > So I was wondering that if I bought a runtime version of Informix 11 or 12 and > put it onto a Virtual Machine and made it visible to the Linux box and also > made it visible to a SQL Server box would I be able to use it as a sort of > bridging database to allow SQL Server to write to it and our SCO box to read > from it. > > I thought I would ask the experts to see if its a good idea or a non-starter > or if there is a better way to achieve the same results. > > So please throw any questions, observations or ideas back at me. > > Thanks for reading > > The Mongoose > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

We currently use ODBC drivers which allow our SQL Server boxes to read from our Informix databases, but the ODBC drivers for Informix version 9 do not allow us to read SQL server databases from our Informix box. We have been told that the ODBC drivers for later Informix versions will allow this to happen. Is that not the case?

No, that´s wrong. Informix ODBC drivers are intended to access from external engines to Infomix, only. For Informix to external engines, you should need EGM product, really. Sorry. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: > Subject: Re: RE: Informix and SQL Server [31005] > Date: Mon, 29 Jul 2013 07:33:24 -0400 > > We currently use ODBC drivers which allow our SQL Server boxes to read from > our Informix databases, but the ODBC drivers for Informix version 9 do not > allow us to read SQL server databases from our Informix box. > > We have been told that the ODBC drivers for later Informix versions will allow > this to happen. > > Is that not the case? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

I agree with Alexandre, however, you CAN access SQL Server databases from 4GL code if you are using one of the 4GL clone environments, 4Js Genero, Querix Lycia, or Aubit 4GL and Genero and Lycia can access both Informix and SQL Server (or other ODBC databases) concurrently (not sure about Aubit on this point). Genero is co-marketed by IBM so you can contact them if you want to give it a try. I represent the North American distributor for Querix, Advanced DataTools Corp. so you can contact us to arrange a demonstration ( www.advancedatatools.com) or download a trial copy of Lycia from the Querix web site (www.querix.com). All three of the clones are very close to 100% compatible with existing 4GL code and can add graphical features and web and mobile enablement to your applications. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 29, 2013 at 7:04 AM, Alexandre Marini <alexandre@briug.org>wrote: > Hello. > I am sorry, but your information is incorrect. > The only way to Informix can "see" other RDBMS, like SqlServer, in your > case, > is using a separated product, named Informix Enterprise Gateway Manager > (Informix EGM). > It does not have anything to do with your Informix version (it not an > upgrade to a more actual version, that will just be able to do what you > need). > > You obviously should upgrade to, at least, version 11.70, or even 12.10, in > order to have the new features, and an oficially supported engine, but that > will not turn your Informix able to select and insert data, into another > database systems. > > Regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > > IBM Information Management Informix Technical Professional > > IBM Infosphere DataStage Technical Professional > Informix Senior DBA - Orizon Brasil > BRIUG website administrator > Informix independent consultant > > > To: ids@iiug.org > > From: > > Subject: Informix and SQL Server [31002] > > Date: Mon, 29 Jul 2013 05:30:28 -0400 > > > > Greetings all, > > > > I apologise for the lengthy rambling text but I have a situation in work > that > > I have been asked to look into so I am coming here for some advice, here > goes: > > > > We have the following setup. > > > > A SCO Unix box running IDS 9.21 > > A SUSE Linux box running 10.00 > > and lots of SQL Server boxes running all kinds of stuff. > > > > The SCO box has Third Party software running on it, written in Informix, > and > > is also used for development work such as data extracts and reporting. > So I > > still write SQL code, stored procedures and sometimes 4GL code on this > box. > > Everything running on this SCO box looks at the Informix databases on the > > Linux box and we have no problems with this setup at all, it works fine. > > > > Some of the Informix databases on the Linux box are also accessible from > some > > of the SQL Server boxes so I can write T-SQL code to select data direct > from > > the Informix databases. > > > > But what I want to do is write Informix code to select data from any of > our > > SQL Server databases, and I know I can do that if I had a version of > Informix > > greater than 10. As the third party software company will not guarantee > that > > their very old code will work on any Informix version above 9 I cannot > upgrade > > the Informix version on the SCO box. > > > > So I was wondering that if I bought a runtime version of Informix 11 or > 12 > and > > put it onto a Virtual Machine and made it visible to the Linux box and > also > > made it visible to a SQL Server box would I be able to use it as a sort > of > > bridging database to allow SQL Server to write to it and our SCO box to > read > > from it. > > > > I thought I would ask the experts to see if its a good idea or a > non-starter > > or if there is a better way to achieve the same results. > > > > So please throw any questions, observations or ideas back at me. > > > > Thanks for reading > > > > The Mongoose > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01228148f6b24a04e2a552ca

Ok, so what you are saying is that even though an Openquery "select" statement in T-SQL will work the Openquery "Insert" statement will not, and there is no way to make it work.

No, you are confusing things. 1) your t-sql queries in external engine, acessing Informix, should not fail. The only prereq to access Informix engines, will be your Informix Client SDK version, since it´s compatible to a specific range of Informix versions. So, if you upgrade your Informix, you´ll need a newer Informix CSDK version, to read, or modify your Informix data. 2) the opposite way cannot be done directly. As I said, if you need to access SqlServer data in Informix side - select or update data located into your external database, from Informix programs, you should go through EGM or the other tools, suggested by Art Kagel in previous answer. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: > Subject: Re: RE: Informix and SQL Server [31008] > Date: Mon, 29 Jul 2013 08:10:10 -0400 > > Ok, > > so what you are saying is that even though an Openquery "select" statement in > T-SQL will work the Openquery "Insert" statement will not, and there is no way > to make it work. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks for the replies, it seems we were misinformed of the powers of ODBC drivers. If I explain what I am trying to accomplish can I ask if anyone can suggest a better way to do this, I will keep it simple: We have two systems, a SQL Server system and an Informix system and they share data. When the Informix system has data entered into it we use Openquery to copy it into a table in the sql server database. When the sql server system has data entered into it we have to extract it then use FTP to send it to the SCO box where a script runs and loads it a table in the Informix database. All we want is a better and quicker way to get the data to appear in the Informix database. Would EGM still be the correct thing to use to do this, if it is I will look into it.

Sorry about this, you are correct I am confusing the issue. I think I gave too much information at the start and should have just said this: I do not want Informix to do any of the work. I do not want Informix to access SQL Server data. I want SQL Server to insert data into an Informix table. Can I do that?

Yup, should work fine. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > THE MONGOOSE > Sent: Monday, July 29, 2013 8:09 AM > To: ids@iiug.org > Subject: Re: RE: Informix and SQL Server [31011] > > Sorry about this, you are correct I am confusing the issue. > > I think I gave too much information at the start and should have just > said > this: > > I do not want Informix to do any of the work. > I do not want Informix to access SQL Server data. > > I want SQL Server to insert data into an Informix table. > > Can I do that? > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

For that you SQL Server should work. It might be that you will need newer CSDK or iConnect (same product but the latter doesn't include development tools) on the SQL Server side. I don't know. However, if that is true, the newer drivers should be able to connect to your older server just fine though. The Informix protocols have not changed since v5.06, they have just been enhanced with new features needed by the newer engines. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 29, 2013 at 9:09 AM, <> wrote: > Sorry about this, you are correct I am confusing the issue. > > I think I gave too much information at the start and should have just said > this: > > I do not want Informix to do any of the work. > I do not want Informix to access SQL Server data. > > I want SQL Server to insert data into an Informix table. > > Can I do that? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c356e2fc0eb604e2a64a27

Sorry I went around the houses but I got there in the end. Now I know I should be able to do it I will speak to one of our SQL Server DBA's and see what they can come up with. Thanks everyone. I will keep checking this thread for a while just in case any more of you experts want to comment.