On Tue, 17 Aug 2004, Bart Kersteter wrote:
> Here's the full main loop script, I'm still relatively new to DBI,
> so I may have messed things up a bit. I have gone through the
> perldocs a few times.
Your understanding of DBI looks pretty good to me. I'm not sure
what's causing the bound variable problems, but I have a few
suggestions below to improve the performance of your code.
> my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt")
> or die "Can't open oratab file";
>
> while (<$db_list>) {
> my @db_info = split(/:/);
> my $orasid = $db_info[0];
> my $server = $db_info[1];
> my $source_dbh = DBI->connect( "dbi:Oracle:$orasid", '<user>',
> <password>',
> {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;
>
> my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", '<user>',
> '<password>',
> {RaiseError => 1, AutoCommit => 0}) || die
> DBI->errstr;
It doesn't look to me like you need to reconnect to this database
each time through the loop. I think the above statement should come
right before this loop.
> my $source_sql = qq{ SELECT username, to_char(sample_Date, 'YYYY')
> , to_char(sample_date, 'IW') , sum(cpu) , sum(IO)
> from daily_user_stats
> group by username, to_CHAR(sample_date, 'IW')
> , to_char(sample_Date, 'YYYY')
> order by 1,2 };
>
> my $source_sth = $source_dbh->prepare( $source_sql ) or die "Can't
> prepare source statement: " . $source_dbh->errstr;
> $source_sth->execute;
> my ($userid, $year, $week, $cpu, $io);
> $source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);
>
>
> my $insert_sql = qq { insert into user_history_stats (database,
> server, year, week, username, cpu, io)
> values ('$orasid', '$server', ?, ?, ?, ?, ?)
> };
>
> my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
> prepare insert statement: " . $target_dbh->errstr;;
Likewise, these two statements (the 'my $insert_sql = ...' and the
'my $target_sth = ...') should follow the 'my $target_dbh = ...'
outside the loop.
> while ($source_sth->fetch) {
> $target_sth->bind_param(1, $year);
> $target_sth->bind_param(2, $week);
> $target_sth->bind_param(3, $userid);
> $target_sth->bind_param(4, $cpu);
> $target_sth->bind_param(5, $io);
>
> $target_sth->execute;
>
> }
I think this could be simplified to:
while ($source_sth->fetch) {
$target_sth->execute($year,$week,$userid,$cpu,$io);
}
> # Clean up and exit loop
> $target_dbh->commit();
> $source_dbh->disconnect();
> $target_dbh->disconnect();
And you'll need to move the $target_dbh->disconnect out of the loop
as well, if you take my above suggestions.
> }
One suggestion to track down the bound variable problem: What do you
see when you print out $userid, $year, $week, $cpu and $io right
after the call to $source_sth->fetch at the top of your while loop?
Perhaps these variables aren't getting populated. If this is the
case, perhaps you could try rewriting that loop another way, like
this:
while (my @info = $source_sth->fetchrow_array) {
$target_sth->execute(@info);
}
and lose the whole bind_columns approach altogether. This approach
is also more easily extensible: all you have to do to include more
stats is to change your $source_sql and $insert_sql statements and
@info here will expand or contract accordingly.
Hope that helps.
Take care,
Dave
/L\_/E\_/A\_/R\_/N\_/T\_/E\_/A\_/C\_/H\_/L\_/E\_/A\_/R\_/N\
Dave Cash Power to the People!
Frolicking in Fields of Garlic Right On-Line!
[EMAIL PROTECTED] Dig it all.
