Hi,
I have a perl script that connects to oracle and sapdb at the same time
and move data from one database to the other based on a list of
tablenames in a plain text file.
Performance is however an issue because it does plain select - insert
statements
I' m stil trying to get perl to work with the loader so imports are
faster but it segfaults every time.
There will be a small howto on the whole process which I hope to get out
soon.
I'am running everything on a linux machine, so I had to do a few other
things to make it work:
Installing unixODBC
-------------------
unixODBC has a nice GUI configtool. gODBCConfig (gnome) and
ODBCConfig(kde).
First setup your drivers, then setup a DSN.
This will generate 2 file odbcinst.ini and odbc.ini in the /etc/unixODBC
directory. The files look like:
====odbcinst.ini====
[ODBC]
Trace = No
TraceFile = /tmp/sql.log
ForceTrace = No
Pooling = Yes
[maxdb]
Description = maxdb driver
Driver = /var/maxdb/programs/lib/libsqlod.so
Setup = /usr/lib/unixODBC/libsapdbS.so.1
FileUsage = 1
CPTimeout =
CPReuse =
====odbcinst.ini====
====odbc.ini====
[eccentxd]
Description = maxdb
Driver = maxdb
ServerNode = localhost
ServerDB = eccentxd
====odbc.ini====
You can test if you ODBC configuration works with the supplied isql
tool. But in order to be able to connect with isql
the odbc.ini file must be located in your home directory as .odbc.ini.
(IT WON'T WORK ELSE)
>isql DSN UID PWD
Now for perl to be able to connect to sapdb; the odbc.ini file should be
in your /etc/directory. Make a symlink or copy it (more on this later).
Installing the perl ODBC module
-------------------------------
perl -MCPAN -e shell
install DBI
install DBD::ODBC
In order for perl to be able to connect to sapdb with DBD::ODBC an
environment variable needs to be set. Otherwise it wil segfault
export PERL_DL_NONLAZY=1
With PERL_DL_NONLAZY set to 1, Perl resolves all
unresolved symbols at startup time. I don't know if this has any
performance
impacts.
Installing the oracle client software to compile the DBD::Oracle module
------------------------------------------------------------------------
Not all the client software must be installed. Using the oracle
universal installer; choose client programs installation,
custom installation, select the following packages:
-Oracle network utilities (to setup the network communication)
-sqlplus (to test your own configuration)
-Oracle call interface (together with the next package needed to compile
the DBD::Oracle module)
-Oracle programmer (Pro*C/C++)
Go ahead with the installation.
Setup your service naming. (either as part of the installation process
or run the <ORACLE_HOME>/bin/netmgr tool)
Setup the needed environment variables: These are needed by perl to
compile. The ORACLE_USERID is needed by the make test step.
ORACLE_HOME="/home/oracle/OraHome1"
ORACLE_OWNER="oracle"
ORACLE_SID="sid"
ORACLE_USERID="userid/[EMAIL PROTECTED]"
installing DBD::Oracle.
-----------------------
perl -MCPAN -e shell
install DBD::Oracle
(DBI is a prerequisid)
With this set up, save the following script in a file and make
executable:
TEST THE SCRIPT FIRST, I CAN NOT BE HELD RESPONSABLE FOR ANY LOSS OF
DATA OR OTHER DISASTER !
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
my $ora_user = 'xxxx';
my $ora_passwd = 'xxxxx';
my $mdb_user = 'xxxxx';
my $mdb_passwd = 'xxxxx';
my %attr = (
RaiseError => 0,
PrintError => 1,
AutoCommit => 1,
ChopBlanks => 1,
ShowErrorStatement => 1,
);
my $oracle =
DBI->connect('DBI:Oracle:developng',"$ora_user","$ora_passwd", \%attr)
or die "Couldn't connect to database: " . DBI->errstr;
my $maxdb = DBI->connect('DBI:ODBC:eccentxd',"$mdb_user","$mdb_passwd",
\%attr)
or die "Couldn't connect to database: " . DBI->errstr;
my $table;;
open (FH, "<", "/tmp/tablesnames.txt") or die "couldn't open file for
reading: $!\n";
my @tableList =<FH>;
close(FH);
print "starting at: ";
system("date");
foreach $table (@tableList)
{
my $colNames = [];
my $colName;
my $colStr;
my $plcHldr;
my $columns;
my $plcHlders;
my $qry;
my $rows = []; # Cache for batches of rows
my $slice = []; # $slice is an array reference,
fetchall_arrayref uses "fetchrow_arrayref" to fetch each row as an
array ref.
my $row; # The fetched row
my $rc;
$table =~ s/\s//g;
print "SELECT * FROM $table \n";
my $ora_sth = $oracle->prepare("SELECT * FROM $table")
or die "Couldn't prepare statement: " . $oracle->errstr;
$ora_sth->execute;
$colNames = $ora_sth->{NAME_lc};
foreach $colName (@$colNames){
$colStr .= "$colName ";
$plcHldr .= "? ";
}
$columns = substr($colStr,0,length($colStr)-1);
$plcHlders = substr($plcHldr,0,length($plcHldr)-1);
$columns =~ s/\s/,/g;
$plcHlders =~ s/\s/,/g;
$qry = "insert into $mdb_user.$table ($columns) values
($plcHlders)";
print "$qry \n";
my $mdb_sth = $maxdb->prepare_cached("$qry")
or die "Couldn't prepare statement: " . $maxdb->errstr;
# $ora_sth->fetchall_arrayref($slice,1000) returns a reference
to an array where every element of the
array is a reference to a row
# since $slice is defined the reference to the row is actually a
reference to another array where each element is a column of the
row
INS: while($row = shift(@$rows) ||
shift(@{$rows=$ora_sth->fetchall_arrayref($slice,1000)}) || "0") {
#row is a reference to the array where each element
corresponds to a column of data.
# execute_array expects that the data is already bound to the place
holders in the prepared statement.
# thus there needs to be a way to bind the data
my $p_num = 0;
foreach my $col (@$row) {
$p_num++;
$rc = $mdb_sth->bind_param($p_num, $col);
}
$mdb_sth->execute; # or die last LINE ;
}
$ora_sth->finish;
$mdb_sth->finish;
} #END
$oracle->disconnect;
$maxdb->disconnect;
print "finished at:";
system("date");
On Sun, 2004-02-29 at 15:06, SRIKANTH PVM wrote:
> Hello All,
>
> My Client requires data migration from oracle db to maxdb. Any
> tool is available for migration oracle to maxdb?
>
> regards,
> Srikanth
>
>
> ________________________________________________
> Get your own "800" number
> Voicemail, fax, email, and a lot more
> http://www.ureach.com/reg/tag
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* System Engineer, Verzekeringen NV *
* www.verzekeringen.be *
* Oostkaai 23 B-2170 Merksem *
* 03/6416673 - 0477/340942 *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
