Classics Forum

re: Informix to Oracle Migration

Posted By: Hubert Hoelzl
Date: Thursday, 7 April 2005, at 8:25 a.m.

In Response To: RE: SE on AIX 4.3.3 (bob.lovitt@asbury.edu)

So, why shouldn't you use Pro*C? It will compile Informix ESQL/c code (well, provided all your SQL statements are prepared), and provide you with a connection to Oracle. Surely that's all that is required, right? after all, Oracle is a database and Informix is a database, and there are standards and stuff like that.

Unfortunately, the problem is just not this simple. Informix 4GL and Informix ESQL/c are written for use with an Informix database. The datatypes available assume that the database behaves like an Informix database. Quite frankly, Oracle couldn't be any more different to Informix (well, it could - it could be DB2!).

Let's start with the datatypes. Intervals, Serials, Datetimes, Money and Date. None of these types exist in Oracle in the same sense that they exist in Informix. Yes, Oracle does have a 'DATE' type which on initial inspection looks like it could be used as either a date or a datetime, but it lacks the functional subtleties of the Informix counterpart. Firstly, there is no understanding of a datetime qualifier, so you are going to find datetime manipulations extremely difficult to handle in SQL, particularly where trunctaing/extending a datetime (even more so when extending/truncating a datetime into a host variable). Also, consider the Oracle 'date' column - what kind of variable do I expect when I perform:

DEFINE my_date LIKE some_table.some_date_column

is this a date or a datetime?

Serials can be emulated (after all, it is what Querix does to provide serial functionality agianst Oracle), but there are other various subtleties which do need to be taken into account. The datatype problem is something I could discuss for hours, but I think you get the picture.

Next, let's consider the SQL. I often hear about this thing called SQL-92 (yes, a 13-year old SQL standard). Every database adheres to it, so that means SQL portability, right? no. Informix SQL is not as portable as it should be, and Oracle has some very 'interesting' ideas about SQL syntax, also. Let's look at some starters; outer join syntax, built-in functions and operators (MDY(), MATCHES, DATE, etc), even the format of literal values. All of these things provide immense incompatibility problems.

Finally, we can consider other factors - ROWID. Informix considers a ROWID to be an integer, whereas Oracle uses a VARCHAR(32). While many systems don't actually make much use of a rowid, those which do use it heavily are going to be presented with immense difficulties (e.g. anything created by FourGen is heavily dependant on ROWID functionality), especially if you make many references to sqlca.sqlerrd[6].

Oracle's transactional model - this is a fun, and lengthy subject for discussion. The fact that Oracle implicitly commits on every statement is going to cause problems. The fact that Oracle forceably commits any transaction on a DDL statement (e.g. create temp table) is going to cause deeper problems. If you intend to lock tables, this may result in having to revise/implement transactions within your entire applications (after all, Oracle unlocks tables on a commit, and the 'lock table' statement itself causes a commit) so that all attempts to lock tables are embedded within explicit transactions.

I've only started to scratch the surface of the levels of incompatibility (and there are many other factors not covered here). All I am trying to stress is that using Oracle's Proc*C is only really a viable option if you are willing to rewrite large amounts of SQL,and restrict yourself to the datatypes available in Oracle. What I consider to be the major downside to this approach is that the end result would be a second copy of a source base which would never run against Informix, for the same reasons that the Informix branch would never run against Oracle.

What Hydra will provide you with is a means of using a common code base for *any* RDBMs, with the absolute minimal amount of SQL rewrite. If there is anything we can do to bridge the gap, we do it (after all, we've been working invisibly against Oracle for well over 8 years now, with several 100 million lines of 4GL/ESQL/c code working happily).


>Art S. Kagel wrote:
>> Hubert Hoelzl wrote:
>>
>>> Has Aubit now got an Esql/C compiler ?
>>>
>>>> Try aubit4gl....
>>>
>>>
>>> <SNIP>
>>
>> No but Orable does - ProC(?). Albeit not half as powerful as
>> Informix's, but it should be sufficient to compile any independent
>> ESQL/C code Girish has.
>>
>> Art S. Kagel

>When you find something Pro*C can't do let me know.
>--
>Daniel A. Morgan
>University of Washington
>damorgan@x.washington.edu
>(replace 'x' with 'u' to respond)




Hubert Hoelzl
Sales & Marketing Director Querix Ltd
_____________________________________________________________
Querix International Distribution:
Querix, z.H. Hubert Hoelzl, Ahornweg 2, 85464 Neufinsing, Germany
Tel: +44 2380 385 187 or +44 2380 232345 or +49 8121 429621 Fax: +44 2380 399 685
Email: h.hoelzl@querix.com Web: www.querix.com
_____________________________________________________________
Privileged/Confidential Information and/or Copyright Material may be contained in this e-mail.
The information and Material is intended for the use of the intended addressee and only in good faith.
If you are not the intended addressee, or the person responsible for delivering it to the intended addressee, you may not copy, re-produce or deliver it to anyone else or use it in any unauthorised manner.
To do so is prohibited and may be unlawful. If you receive this e-mail by mistake, advise the sender immediately by using the reply facility in your e-mail software.
Also, the views expressed in this document do not necessarily represent those of the management of QueriX (UK) Ltd.
Specification and prices are subject to change without notice. All trademarks & copyrights acknowledged.

Contact: Daniel Morgan Company: Email Address: damorgan@u.washington.edu ContactID: - CompanyID:

Messages In This Thread

• SE on AIX 4.3.3
  bob.lovitt@asbury.edu -- Wednesday, 15 January 2003, at 5:29 p.m.
  • RE: SE on AIX 4.3.3
    bob.lovitt@asbury.edu -- Thursday, 16 January 2003, at 10:21 a.m.
    • Re: SE on AIX 4.3.3
      Doug Lawry -- Thursday, 16 January 2003, at 3:52 p.m.
    • re: Informix to Oracle Migration
      Hubert Hoelzl -- Thursday, 7 April 2005, at 8:25 a.m.

Classics Forum is maintained by Administrator with WebBBS 5.12.