|
|
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
scripts to
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),
and re-load
using Oracle SQL*Loader:
2 - After loading tables, create the indexes, modifying the Informix "create
index" DDL.
3 - Convert the ex-Informix application to
change the
Informix SQL to Oracle SQL.
Adjusting Informix data types
Question: I
cannot translate this table from Informix to Oracle:
create
table test_table
(userid integer,
first_name varchar(128),
last_name varchar(128),
mod_date datetime year to
fraction(3),
num SERIAL PRIMARY KEY CONSTRAINT
num,
username varchar(32)
);
Answer: Note
that the constraint syntax is slightly different in Informix, and easy to
change. For details on using constraints, see the book "Easy
Oracle Jumpstart".
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
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |