Informix to Oracle migration

A potential customer has asked me to provide an estimate of how long it will take to migrate their 4GL suite running on Informix "to Oracle". (I'm reminded here of Obnoxio's post of a while ago ridiculing another rather vague question: "I have a piece of string in my pocket. How long is it? It's red by the way ...") Seriously, though, what sort of things should we be asking beyond these?: 1. Complexity of schema (what sort of things, other than serial, are going to be difficult to convert?) 2. Number of lines of 4GL 3. Number of stored procedures and triggers 4. Total number of tables 5 Volume of data 6. Availability of original programmers/BAs 7. Quality of documentation Assuming they don't want to re-write their app, they have to use a third-party "universal" compiler like 4Js or Querix (or of course Websphere EGL, if they've the time to kill). Any other options? All (constructive) comments gratefully received. thank you. Capt. P

"Captain Pedantic" <theharlequin36@hotmail.com> wrote in message news:c1nda4$1k5p2g$1@ID-162943.news.uni-berlin.de... | A potential customer has asked me to provide an estimate of how long it will | take to migrate their 4GL suite running on Informix "to Oracle". | | (I'm reminded here of Obnoxio's post of a while ago ridiculing another | rather vague question: "I have a piece of string in my pocket. How long is | it? It's red by the way ...") | | Seriously, though, what sort of things should we be asking beyond these?: | | 1. Complexity of schema (what sort of things, other than serial, are going | to be difficult to convert?) | 2. Number of lines of 4GL | 3. Number of stored procedures and triggers | 4. Total number of tables | 5 Volume of data | 6. Availability of original programmers/BAs | 7. Quality of documentation | | Assuming they don't want to re-write their app, they have to use a | third-party "universal" compiler like 4Js or Querix (or of course Websphere | EGL, if they've the time to kill). Any other options? | | All (constructive) comments gratefully received. | | thank you. | Capt. P | | target platform? essential business functionality? -- most 'migrations' need to fix badly implemented logic quality of original design? -- how many kludges in the code? quality of data? -- how much cleaning will be required to move it to a normalized schema how old is the app and what has happened to the business since then? what are users currently doing with the app? what are they doing to work-around its limitations? what goals were never met with the original app that could be achieved with the 'migration'? what type of cutover period is anticipated? how will it be managed? i really don't believe in 'migrating' an application without good analysis of the current state (health, usability, correctness) of the application and data, and then coming up with a strategy for balancing redesign, repair, and redeployment -- usually the same (or perhaps less) expense that is involved in attempting a technology migration will provide a more correct application if possible, first give them an estimate for 'getting the string out of their pocket' ;-{ mcs

What sort of test enviroment does the customer have? To do a successful migration of any sorts teh customer needs an efficient means to do functional verification of migrated code (in an iterative, repeatable fashion). As well as a means to verify performance in a repeatable fashion. I've just gone through hell and back on a migration where the first was spotty and performance testing happened only on the production system. If you get involved make sure it's clear how corectness and performance are defined, so you have a clear target for disengagement Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab

Captain Pedantic wrote: > A potential customer has asked me to provide an estimate of how long it > will take to migrate their 4GL suite running on Informix "to Oracle". > > (I'm reminded here of Obnoxio's post of a while ago ridiculing another > rather vague question: "I have a piece of string in my pocket. How long > is > it? It's red by the way ...") > > Seriously, though, what sort of things should we be asking beyond these?: > > 1. Complexity of schema (what sort of things, other than serial, are going > to be difficult to convert?) > 2. Number of lines of 4GL > 3. Number of stored procedures and triggers > 4. Total number of tables > 5 Volume of data > 6. Availability of original programmers/BAs > 7. Quality of documentation > > Assuming they don't want to re-write their app, they have to use a > third-party "universal" compiler like 4Js or Querix (or of course > Websphere > EGL, if they've the time to kill). Any other options? Querix would probably be the best bet. Many years ago, someone I knew did it and raved about the ease of the migration and performance improvements, but then of course, they did a massive hardware upgrade at the same time. :o) > All (constructive) comments gratefully received. -- "C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule" - Coluche

Captain Pedantic wrote: > A potential customer has asked me to provide an estimate of how long it will > take to migrate their 4GL suite running on Informix "to Oracle". > > (I'm reminded here of Obnoxio's post of a while ago ridiculing another > rather vague question: "I have a piece of string in my pocket. How long is > it? It's red by the way ...") > > Seriously, though, what sort of things should we be asking beyond these?: > > 1. Complexity of schema (what sort of things, other than serial, are going > to be difficult to convert?) > 2. Number of lines of 4GL > 3. Number of stored procedures and triggers > 4. Total number of tables > 5 Volume of data > 6. Availability of original programmers/BAs > 7. Quality of documentation > > Assuming they don't want to re-write their app, they have to use a > third-party "universal" compiler like 4Js or Querix (or of course Websphere > EGL, if they've the time to kill). Any other options? > > All (constructive) comments gratefully received. > > thank you. > Capt. P After you migrate it who is going to perform DBA and developer jobs? If it is the former Informix people they will likely need substantial retraining. I am aware of classes for Informix-to-Oracle I can refer you to off you contact me off-line. -- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan@x.washington.edu (replace 'x' with a 'u' to reply)

On Fri, 27 Feb 2004 07:43:55 -0500, Mark C. Stock wrote: DO check out the Aubit4GL project. The latest release is working for most code with enough coverage that there is at least one large 4GL customer using it for production already. Automagic data and SQL dialect conversion included. The version on source forge is older but the Aubit site has the latest. Art S. Kagel > "Captain Pedantic" <theharlequin36@hotmail.com> wrote in message > news:c1nda4$1k5p2g$1@ID-162943.news.uni-berlin.de... | A potential customer > has asked me to provide an estimate of how long it will | take to migrate > their 4GL suite running on Informix "to Oracle". | | (I'm reminded here of > Obnoxio's post of a while ago ridiculing another | rather vague question: "I > have a piece of string in my pocket. How long is | it? It's red by the way > ...") > | > | Seriously, though, what sort of things should we be asking beyond these?: > | > | 1. Complexity of schema (what sort of things, other than serial, are going > | to be difficult to convert?) > | 2. Number of lines of 4GL > | 3. Number of stored procedures and triggers | 4. Total number of tables | > 5 Volume of data > | 6. Availability of original programmers/BAs | 7. Quality of documentation > | > | Assuming they don't want to re-write their app, they have to use a | > third-party "universal" compiler like 4Js or Querix (or of course Websphere > | EGL, if they've the time to kill). Any other options? | | All > (constructive) comments gratefully received. | | thank you. > | Capt. P > | > | > > target platform? > essential business functionality? -- most 'migrations' need to fix badly > implemented logic > quality of original design? -- how many kludges in the code? quality of > data? -- how much cleaning will be required to move it to a normalized > schema > how old is the app and what has happened to the business since then? what > are users currently doing with the app? what are they doing to work-around > its limitations? what goals were never met with the original app that could > be achieved with the 'migration'? > what type of cutover period is anticipated? how will it be managed? > > i really don't believe in 'migrating' an application without good analysis > of the current state (health, usability, correctness) of the application and > data, and then coming up with a strategy for balancing redesign, repair, and > redeployment -- usually the same (or perhaps less) expense that is involved > in attempting a technology migration will provide a more correct application > > if possible, first give them an estimate for 'getting the string out of > their pocket' > > ;-{ mcs

Captain Pedantic wrote: > > Seriously, though, what sort of things should we be asking beyond > these?: FourJs works very well and hasn't let us down. I notice with mild bemusement that Querix are currently asking people to beta-test their "dynamic 4gl" additions - ie compatibility with FourJs BDL, however FourJs have moved a long way further with their Genero product that allows great improvements in the user interface. However, the Genero does have some distinct issues to address since it does make some modest variations on the 4GL "standard" (necessary so as to provide the new nicies), so unless you have sufficient time, it's probably better to convert using FourJs BDL product, and THEN add glamour using Genero in a separate development. * Serials are less of a problem then you might think, since it's not a major problem to use a SEQUENCE and attach it with a trigger. FourJs manage to hide that particular issue very well. ** Oracle has a very small range of datatypes: * their only date is the equivalent of our DATETIME YEAR TO SECOND. So if you are using plain old DATE, or a DATETIME with a different range, you've got some problems. INTERVALS are even more distant. Luckily for DATE, their DATETIME does some moderately helpful shennanigans when the hh:mm:ss is set to 00:00:00 * true "unfixed point" decimals. I hesitate to say floating point because that usually means binary numerics for many people. Oracles DECIMAL always has a fixed point, be it implicit or defined. So if you have any DECIMAL(X) in Informix and you actually rely on the shifting decimal, then you usually need to declare DECIMAL(X*2, X) in Oracle Just To Be Sure. * Don't be tempted by the Optimists Lobby who might try to tell you to convery Informix CHARS to Oracle VARCHARS. Semantically, Informix CHAR = Oracle CHAR, and I-VARCHAR = O-VARCHAR. Neither CHAR = VARCHAR, and only a fool would try to replace one with the other. * true binary numerics - ie FLOAT and DOUBLE PRECISION cannot be found in Oracle. Their numerics are either integral or BCD. Of course, the BCD-based numerics are generally more appropriate for business use, but when people really want a binary representation... * Some Oracle SQL syntax is different, but if you use FourJs their product makes a good effort at mapping most constructs. They also have a conversion document that can be used as a solid guide. If you use rowid's then you need to store them in a char(18), since they are not integral in Oracle. Further, the sqlca member that supplies the last rowid after some statements cannot be used for obvious reasons, but they have supplied the larger stringy Oracle rowid via another mechanism. Best to isolate that in a library routine. Apart from storing rowid's temporarily in variables of a different, there's very little difference. If you ever want to use MS SQL then there are absolutely no rowids; but deal with that only when you have to! NOTE: in all good taste, nobody uses rowids in Informix anyway...

Andrew Hamm wrote: PS - i didn't notice the cross-posting until it was too late. If I've made any mistaiks about Oracle features (they might have changed in the last 2 years) then please be gentle ....

Captain Pedantic wrote: Since a few people have mentioned stored procedures; Informix SPL is fairly retarded, so although the syntax is different, you should be able to cover virtually all functionality. The chaps responsible for maintaining the SPL bemoan Informix's basic original SPL rather than the conversion effort. We probably should start to look at the more sophisticated Informix options for this rather than relying on the original SPL offered with the engines, but old habits die hard :-) one day...

Andrew Hamm wrote: > * true "unfixed point" decimals. I hesitate to say floating point because > that usually means binary numerics for many people. Oracles DECIMAL always > has a fixed point, be it implicit or defined. So if you have any DECIMAL(X) > in Informix and you actually rely on the shifting decimal, then you usually > need to declare DECIMAL(X*2, X) in Oracle Just To Be Sure. Watch out - if you wanted to store Avagadro's constant and the Planck constant in the same column, then even DECIMAL(38,y) runs into problems. Sometimes, you just need floating point; fixed point just doesn't work sufficiently well each time. [Cross-posting suppressed.] -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote: > > Watch out - if you wanted to store Avagadro's constant and the Planck > constant in the same column, then even DECIMAL(38,y) runs into > problems. Sometimes, you just need floating point; fixed point just > doesn't work sufficiently well each time. EXACTLY :-) You must have done some Real Maths in the last 15 years, to remember all that

Jonathan Leffler wrote: > > Watch out - if you wanted to store Avagadro's constant and the Planck > constant in the same column, then even DECIMAL(38,y) runs into > problems. Sometimes, you just need floating point; fixed point just > doesn't work sufficiently well each time. Actually, I was thinking more about Informix's DECIMAL(10) which is capable of storing from 0.123456789 to 123456789 (and slightly more:) because the point is allowed to wander in this non-ANSI data type. For this example, you would need a DECIMAL(20,10) in Oracle. If Captain is converting an ANSI database then he won't have this problem.

Andrew Hamm wrote: > Jonathan Leffler wrote: >>Watch out - if you wanted to store Avagadro's constant and the Planck >>constant in the same column, then even DECIMAL(38,y) runs into >>problems. Sometimes, you just need floating point; fixed point just >>doesn't work sufficiently well each time. > > Actually, I was thinking more about Informix's DECIMAL(10) which is > capable of storing from 0.123456789 to 123456789 (and slightly > more:) because the point is allowed to wander in this non-ANSI data > type. In a non-ANSI database, DECIMAL(10) can store value up to +/- 9.999999999E+126 and down to +/- 1.000000000E-126 (give or take small eccentricities and asymmetries - I won't bore you with the details; suffice to say I have a program that determines the actual exponent range for any given release of ESQL/C), and a bit smaller in absolute magnitude if it allows denormalized numbers to be used (you can't convert a string to a denormalized number, but I don't think the arithmetic checks for such denormalized values). DECIMAL(10) can handle quite decent approximations to both Avogadro's constant and the Planck constant, in other words, as well as storing the ratio of either Avogadro to Planck or Planck to Avogadro. Google sayeth: Avogadro's number is 6.02214199E+23, the Planck constant is 6.626068E-34 (units metres squared kilograms per second). The ratios are thus 1.1E-57 or 9.1E+56, handily within range of DECIMAL(10). > For this example, you would need a DECIMAL(20,10) in Oracle. Or in an Informix MODE ANSI database. > If Captain is converting an ANSI database then he won't have this problem. No - but he won't be able to ... either. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/