Nothing much to add to this, but I thought I'd correct one or two errors on the Four J's comments:

RE: re: FWD: re: Informix to Oracle migration


>> 1/ The presence/absence of stored procedures, since any form of automated
>> migration of these is quite tricky.
Agreed

>> 2/ Volume of data, although this is typically a secondary consideration.
>> The quantity of data simply indicates the amount of time it will take to
>> load the data. You can typically convert the informix export files into
>> files suitable for oracle's SQL loader to load. This can usually load
>> data in an efficient form (off the top of my head you should be able to
>> load a 10GB database in a few hours).
Phew, he's got a fast machine.
He's right though it's not a long or difficult job.

>> 3/ Use of various Informix specific constructs.
>> A/ The major construct which tends to cause issues is the "MATCHES" clause
>> in WHERE conditions. This is because Oracle doesn't really have a direct
>> equivlent, so if the authors have done some cool and hoopy stuff with it
>> (for example SELECT * from tab1,tab2 where tab1.col matches tab2.col) you
>> can wind up with quite a headache.
>> Typically all MATCHES statements have to be converted to LIKE statements.
>> B/ ISOLATION level and locking
>> Oracle has a fundamentally different locking model to Informix.
>> Practically this means that statements like SET ISOLATION LEVEL are not
>> required/not supported. Programs which behave critically on locking
>> behaviour may also not work as expected.
>> C/ Other issues.
MATCHES are converted at runtime to LIKE automatically by our ODI.
So are some of the ISOLATION type commands. ( I think )
The locking is down to Oracle, so we would have the same potental issues
as Querix.

>> Most of what I have listed here are the issues you experience when porting
>> with Querix's Hydra compiler. At can't comment on other porting schemes
>> (such as those provided by 4js) other than to say that my undestanding is
>> that there is considerably more work required. Hydra uses on the fly SQL
>> translation to ensure that you don't have to rewrite all your dynamic
>> SQL.
I dispute (as you might expect) the "considerably more work required" bit, I find that our
fglprofile settings to connect to Oracle only take a couple of minutes to do!
We also use on the fly SQL translation.

>> In particular if you were using another tool, you may need to worry about
>> the behaviour of SERIAL columns, the use of ROWID within the program
>> (which exists on oracle, but is a CHAR type) and the behaviour of
>> temporary objects.
We handle SERIAL and temporary tables automatically.
ROWID are handled also but as with Querix you would need
to change the 4gl to handle the fact that they are chars in Oracle.

We're running various seminars on topics similar to this, and can put you in touch with several reference sites who are happy to talk about this. I can also send you our Database Migration Guide which will give you a good idea of the technical issues involved from a Four J's point of view.

On an Informix positive note, we also know of several companies who have proved they can migrate, and stick with Informix, but that's probably not what you want to hear on this issue.

Regards,

Peter Collett
Four J's Development Tools (EMEA) Ltd
Tel: +44 (0)1342 719193
Fax: +44 (0)1342 719195
mailtoeterc@4js.com
http://www.4js.com

sending to informix-list