Informix data to Oracle
Does anyone have any experience of moving a large-ish (~10GBytes) database
from an Informix to Oracle server?
I'm wondering if there's something you can do with the output from the
Informix dbexport utility, which produces a datbase schema and an ASCII
unload of each table in pipe-delimited format, and Oracle's SQL *Loader.
Any hints and tips would be gratefully received.
Neil (ntruby@netcomuk.co.uk)
Yes,
You can edit the dbexport output to create the DDL needed to create
Oracle tables. From what I recall you will have to delete the first two
lines in the file and change the storage clause. I think there is one
other global change you will have to make, but I can't remember what it is.
You can then take the same files and copy and edit the copies in order
to create the SQL Loader control files.
regards
Jerry Gitomer
----------------------------------------------------
Neil Truby wrote in message <7hci08$df6$1@taliesin.netcom.net.uk>...
>Does anyone have any experience of moving a large-ish (~10GBytes) database
>from an Informix to Oracle server?
>
>I'm wondering if there's something you can do with the output from the
>Informix dbexport utility, which produces a datbase schema and an ASCII
>unload of each table in pipe-delimited format, and Oracle's SQL *Loader.
>
>Any hints and tips would be gratefully received.
>
>Neil (ntruby@netcomuk.co.uk)
>
>
Hi Neil
You might want to try Openlink Virtuoso. This will allow you to connect to
both your Oracle and Informix databases via ODBC, UDBC, OLE/DB etc. When you
have linked both tables, you could then transfer from one to the other.
Just an idea as ODBC might be your best bet here if you can't get it working
with Oracle.
HTH
Best Regards
Neil Truby <ntruby@netcomuk.co.uk> wrote in message
news:7hci08$df6$1@taliesin.netcom.net.uk...
> Does anyone have any experience of moving a large-ish (~10GBytes) database
> from an Informix to Oracle server?
>
> I'm wondering if there's something you can do with the output from the
> Informix dbexport utility, which produces a datbase schema and an ASCII
> unload of each table in pipe-delimited format, and Oracle's SQL *Loader.
>
> Any hints and tips would be gratefully received.
>
> Neil (ntruby@netcomuk.co.uk)
>
>
I have help a group to move database and data from Informix to Oracle.
Following are the steps:
1. Convert database schema from Informix to Oracle. We are using ERWin to
finish the convert(with some manual modification). If you don't have ERWin,
you have to manually to convert the schema.
2. Create the database in Oracle;
3. Get the schema of each table from Oracle to prepare control file:
I wrote a short SQL script with following lines:
spool table1.lst;
desc table1;
spool off;
spool table2.lst;
desc table2
spool off;
.....
4. I wrote a Perl script to read each "lst" file and generate control file
with extension ctl for each table. I call this Perl script a.pl. I write a
shell script to run this a.pl as follows:
a.pl table1.lst
a.pl table2.lst
.....
The Perl script is like this:
#!/usr/local/bin/perl
if ( $#ARGV < 0 ) {
print "Usage: generate_control_file.pl table_schema_file\\n";
exit;
} elsif ( $#ARGV < 1 ) {
$tmp_table_name = $ARGV[0];
$_ = $ARGV[0];
($r1,$r2)=split(/\\./);
$table_name = lc($r1);
}
$num_line = 0;
$total_lines = 0;
open(DATA_FILE,"$tmp_table_name") || die("failed to open data file");
while(<DATA_FILE>){
$total_lines++;
}
close(DATA_FILE);
$total_lines--;
print $total_lines,"\\n";
open(DATA_FILE,"$tmp_table_name") || die("failed to open data file");
open(RPT,">$table_name.ctl") || die("failed to open result file");
print RPT "LOAD DATA\\n";
print RPT "INFILE \\'",$table_name,".unl","\\' \\n";
print RPT "INSERT INTO TABLE ",$table_name," \\n";
print RPT "FIELDS TERMINATED BY \\'\\|\\' \\n";
print RPT "( \\n";
while(<DATA_FILE>){
$num_line++;
if (($num_line > 2) && ($num_line <= $total_lines) ){
($r1,$r2,$r3)=split(' ',$_);
if ($num_line < $total_lines){
print RPT " ",$r1,",\\n";
} else {
print RPT " ",$r1,"\\n";
}
}
}
print RPT ") \\n";
5. Load each table by using SQL*Loader and the control files:
sqlldr user_id/password control=control_file_name.ctl log=log_file_name.log
For 50 tables, this method saved me a lot of time.
Tony
Neil Truby <ntruby@netcomuk.co.uk> wrote in article
<7hci08$df6$1@taliesin.netcom.net.uk>...
> Does anyone have any experience of moving a large-ish (~10GBytes)
database
> from an Informix to Oracle server?
>
> I'm wondering if there's something you can do with the output from the
> Informix dbexport utility, which produces a datbase schema and an ASCII
> unload of each table in pipe-delimited format, and Oracle's SQL *Loader.
>
> Any hints and tips would be gratefully received.
>
> Neil (ntruby@netcomuk.co.uk)
>
>
>
Hi Neil,
Try WinSQL Professional at http://www.imranweb.com/freesoft
Thanks
Neil Truby wrote:
> Does anyone have any experience of moving a large-ish (~10GBytes) database
> from an Informix to Oracle server?
>
> I'm wondering if there's something you can do with the output from the
> Informix dbexport utility, which produces a datbase schema and an ASCII
> unload of each table in pipe-delimited format, and Oracle's SQL *Loader.
>
> Any hints and tips would be gratefully received.
>
> Neil (ntruby@netcomuk.co.uk)
--
===========================================
Imran Hussain
MCP, MCSD
imranh@imranweb.com
FOR FREE Software http://www.imranweb.com/freesoft
===========================================