Configuring Informix to access data on another server
0
votes
0
answers
85
views
I have inherited an Informix installation that contains a large amount of business logic in the form of procedures. The company also uses SQL Server, MySQL and Postgres for various roles. We're very interested in moving as far away from Informix as possible. Preferably to SQL server or Postgres. Migrating the data isn't something I'm worried about, but the business logic that is contained in thousands of very complex, Informix-dialect procedures is something that would be prohibitively expensive to migrate.
The idea I have, which may be somewhat unhinged, is to use Informix as an "application server" of sorts -- the business logic can't move, but perhaps the data can. So that would involve not rewriting any of these stored procedures, and leaving them running on Informix, but moving the data so that the tables that they reference can be accessed transparently, even though they reside on a different RDBMS.
MySQL has pluggable storage engines, SQL Server [has linked servers (supporting ODBC)](https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver16) , and there is a PostgreSQL [Foreign Data Wrapper](https://github.com/credativ/informix_fdw) that specifically connects to Informix. However, this would only allow those servers to query Informix... what I want to do is have Informix query any of those servers so that the Informix procedures don't have to be changed, but the data can be moved. So those "foreign data methods" have the right idea but the wrong direction.
The [Informix Documentation here](https://www.ibm.com/docs/en/informix-servers/14.10?topic=database-specifying-object-in-cross-server-query) specifies that a cross-server query can be made, and it mentions ANSI compliance, but I'm inclined to believe that they are referring to foreign servers *running Informix in ANSI-compliant mode* rather than any (mostly?) ANSI compliant database.
There does seem to be a mechanism for "user-defined access methods" which seems a bit like a "foreign data wrapper" in PostgreSQL:
> You can create user-defined primary access methods that provide SQL access to non-relational and other data that does not conform to built-in access methods. For example, a user-defined access method might retrieve data from an external location or manipulate specific data within a smart large object.
However I can't find any open-source (or other) user-defined access methods that will allow me to query a different RDBMS from Informix. It would be a huge help if anyone could point me in the right direction, or can recommend a tool that will correct translate Informix stored procedures to any other dialect (but I know that's a long shot!).
The speed of these queries is less relevant than in most applications because it's mostly done in batch processes, not interactively, so I'm not concerned about the performance hit from requiring Informix to query these other servers.
Asked by Hut8
(101 rep)
Apr 9, 2024, 02:35 PM