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), and there is a PostgreSQL Foreign Data Wrapper 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 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.

  • Informix can query data from a (very) limited set of alternative DBMS, but it wouldn't be fast. If the data volumes are large, you'd have immense network costs as the data is transferred (probably repeatedly) from the foreign servers to Informix. Your best bet is probably to rewrite the stored procedures — or stick with Informix, using Informix as the database of record. Commented Apr 9 at 19:14
  • @JonathanLeffler can you please link to information regarding Informix querying that limited set of alternative DBMS? Much appreciated.
    – Hut8
    Commented Apr 12 at 14:11
  • The term to search for is "Informix Enterprise Gateway Manager" (aka EGM). Be aware that the article Informix Tech Brief: Informix Enterprise Gateway Manager v7.2 dates from 2001 and refers to systems such as Windows 3.1. It isn't current, in other words. AFAIK, there is officially some support for EGM with DRDA for talking to DB2, and the ODBC version might be able to talk to multiple database types, and there is/was an Oracle version. But the whole idea is wrong — you will be extremely unhappy with the "performance". Commented Apr 17 at 18:01


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.