Informix to Oracle migration tips
Oracle Database Tips by Donald Burleson
Question: My management is sick of Informix and I've
been charged with migrating the Informix database onto Oracle.
migration from Informix Dynamic Server Version 7.30.UC10X3 running on
UnixWare to Oracle10g running on Redhat Linux. currently, I'm using the
Oracle Migration Workbench.
I see two approaches, a manual unload
and reload into Oracle, or using the Oracle Migration Workbench.
What's the best practice for migrating from Informix to Oracle?
Answer: First, it's a best practice to move from Informix to Oracle,
so you are off to a great start! First, make sure to completely verify the
migration on a test environment before doing it in production. Also, for
migrating the SQL, some consultancies have
change Informix SQL to Oracle SQL, so changing the Informix SQL is easy.
For each migration approach, I have these tips:
Using Oracle Migration Workbench (OMW) to
migrate from Informix to Oracle
There are sample capture scripts available for Informix with the Oracle
Migration Workbench. If you check under the installation directory for the
migration workbench, there should be a subdirectory for Informix that has
example capture scripts. The Oracle Workbench migration workbench provides
an editor when parsing data between environments. You may need to tweak some of
the code that does not convert cleanly and it's a best practice to scrub the
data before loading to Oracle just to be on the safe side.
Manual migrate from Informix to Oracle
If you don't want to use the Oracle Migration Workbench, you can simply
export and import, using standard Informix and Oracle utilities:
1 - spool the Informix rows to a flat file (with appropriate delimiters),
using Oracle SQL*Loader:
2 - After loading tables, create the indexes, modifying the Informix "create
3 - Convert the ex-Informix application to
Informix SQL to Oracle SQL.
Adjusting Informix data types
cannot translate this table from Informix to Oracle:
mod_date datetime year to
num SERIAL PRIMARY KEY CONSTRAINT
that the constraint syntax is slightly different in Informix, and easy to
change. For details on using constraints, see the book "Easy
As to the "Year to fraction"
datatype, the docs note:
"The Informix Dynamic Server
DATETIME data type has higher precision, YEAR to Fraction of Second, than
the Oracle DATE data type, Year to Second. The fractional second
information, if specified in the Informix Dynamic Server column definition,
is lost in the migration."
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.