Previously I posted a question so I could confirm our current (albeit archaic) version of Informix here:
How do you identify Informix version on Solaris?
(Thank you Jonathan and RET for clearing that up)
We are definitely planning on upgrading but are first discussing if it would make more sense to move to Oracle or Sybase at this time. What are your opinions on this? It's my belief that while all 3 RDBMs have their own uniqueness, they must all cover the same ground essentially. So how does one go about deciding what database to use?
The big kicker is I need to know if we upgrade Informix (currently using 7.13), will we need to modify our embedded sql C programs? If not, then it makes a lot of sense to stick with Informix. Because if we went with Sybase/Oracle etc, we'd have a lot of work on our hands to update the back-end programs.
But if switching to another database can offer great return in comparison then we will still consider it. I look forward to hearing your opinions.
I'm a biassed observer (I work for IBM on Informix) - treat my comments with due caution.
If your applications are written in Informix ESQL/C, you would have to do major surgery to move them to other systems. You would need to decide which alternative interface to use - your cross-platform choice (with C as the basic language) is ODBC, but Oracle provides the OCI and Sybase provides the TDS as alternatives.
By contrast, with Informix ESQL/C, an upgrade to the current version (Informix ClientSDK 3.50, containing ESQL/C 3.50, which is more recent than the ESQL/C 6.00 which you currently use) should be painless unless you've gone out of your way to write bad code.
Even simply migrating the data might be a little traumatic - not insuperably so. In part, the complexity depends on which data types you use. (Character strings migrate easily; date and time values less easily, for example.) But migrating the applications would require a lot of work, as you say, unless you were extraordinarily prescient and wrote a really good data abstraction layer.
An upgrade to Informix SE 7.26 would be a no-brainer - get the software, install it, point it at your existing database. You'd probably want to recompile your programs to use the more modern CSDK, but you could do that incrementally with care (two INFORMIXDIR values, one for the old code, one for the new).
An upgrade to Informix Dynamic Server (IDS) 11.50 would require you to export the data (DB-Export) from SE and import it (DB-Import) into IDS. This would be very simple too, once you have IDS up and running. Getting IDS up and running takes more effort than SE, but is not all that hard.
Clearly, my recommendation is to stay put with Informix. The decision is yours, of course.
With IDS, would we have to make code changes or just recompile?
IDS is very closely related to SE, but they are different. IDS provides an almost strict superset of SE functionality. The places I can think of where there are differences are primarily edge case stuff:
The main area that may cause issues is transaction management. IDS has unlogged, logged and 'LOG MODE ANSI' database, as does SE. In IDS, you are encouraged to use a logged database - that would be a strong recommendation. IDS provides atomic statements in a logged database - either the statement works as a whole or it fails as a whole. However, many SE applications are not written with transactions in mind. There are some things you can't do when there are transactions on the database, such as open a cursor for update outside the scope of a transaction. This is what tends to bite code migrating from SE to IDS. Also, you cannot lock a table except in a transaction, and you cannot unlock a table except by COMMIT or ROLLBACK.
How much of a problem this will be depends on what you were using in SE and how the programs were designed (if they were designed rather than thrown together). An IDS unlogged database and an SE unlogged database are very close - and you could migrate from one to the other. But IDS can do things (like replication) only when the databases are logged, and you should aim to be using logged databases.
However, the migration to CSDK 3.50 should be just a recompile unless you've managed to do some really excruciatingly awful things.