I am rather new to DBI and Perl in general. I am trying to replace a script
arrangement I have that copies data from a database on an AS/400 to a
matching database on an NT Server running DB2.
I am doing this Perl program on NT using ActiveState Perl.
The SQL statement I have been using on the DB2 server is as follows:
insert into gs.secmain select * from mansec2 where
myactt='1900-01-01-00.00.00.000000' and mytid=14 and mysort not in (select
ssort from gs.secmain)
This allows me to insert based on my criteria and prevents duplicates from
happening since ssort is a unique field.
The "file" mansec2 is an alias to a federated database connection on the NT
DB2 machine. This connection goes to the file scp021 on the AS/400.
This statement is in an SQL script that is called on the NT Server. I would
like to replace this in Perl.
What I have so far in Perl is:
$dbh=DBI->connect("dbi:DB2:asdatabase","username","password",
{LongReadLen=>102400 } );
$dbh2=DBI->connect("dbi:DB2:ntdatabase","username","password",
{RaiseError=>1, AutoCommit=>0});
$stmt="select * from ltl400v1a3.scp021 where
myactt='1900-01-01-00.00.00.000000' and mytid=14";
$sth=$dbh->prepare($stmt);
$sth->execute;
$stmt_ins="insert into gs.secmain(field1,...field12)
values(?,?,?,?,?,?,?,?,?,?,?)";
$sth_ins=$dbh2->prepare($stmt_ins);
while ((field1,...field12)=$sth->fetchrow_array) {
$sth_ins->execute(field1,...field12);
}
$sth_ins->finish;
$sth->finish;
$dbh2->disconnect;
$dbh->disconnect;
This works...the first time. Any subsequent executions cause errors due to
it trying to insert duplicate rows. How can I do something like my SQL
statement in Perl? I do not want to use the mansec2 alias because I believe
it may be causing me some problems and would like to get rid of it.
Any help would be greatly appreciated. Thanks.
William Beatty
Programmer/Analyst
Manitoulin Transport
