Re: Informix data to Oracle

From: Tony <tonyxu89_at_yahoo.com>
Date: Thu, 13 May 1999 20:14:21 GMT
Message-ID: <01be9d7c$b6f13840$38434cc0@txu>

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_at_netcomuk.co.uk> wrote in article <7hci08$df6$1_at_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_at_netcomuk.co.uk)
> 
> 
> 

Received on Thu May 13 1999 - 15:14:21 CDT