Thanks to all for your suggestions... I've finally gotten things working. The
following is a working example. The one issue I have left to handle is to add some
error chceking and parameterize a few things to make the script more transportable.
One little glitch is that the final fetch for the source handler appears to be an
empty set, and I'm getting the following error even with the check in place to see if
userid is empty:
Use of uninitialized value in string eq at ./collect_user_metrics.pl line 62, <GEN0>
line 1.
Thanks again for your help and suggestions.
Bart
*----------------------
use strict;
use DBI;
use IO;
use POSIX qw(uname);
my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt") or die "Can't
open oratab file";
my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", 'metrics', 'metr1cs',
{RaiseError => 1, AutoCommit => 1}) || die DBI->errstr;
my $insert_sql = qq{ insert into user_history_stats (database, server, year, week,
username, cpu, io)
values (?, ?, ?, ?, ?, ?, ?) };
my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't prepare insert
statement: " . $target_dbh->errstr;;
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", 'metrics', 'metr1cs',
{RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;
my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", 'metrics', 'metr1cs',
{RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;
# set up bind variables
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 );
while ($source_sth->fetch) {
if ( not $userid eq "" ) {
$target_sth->execute($orasid, $server, $year, $week, $userid, $cpu, $io);
}
}
# Clean up and exit loop
$target_dbh->commit();
$source_dbh->disconnect();
}
$target_dbh->disconnect();
Bart Kersteter
Senior DBA - Corporate Database
Assurant
576 Bielenberg Drive
Woodbury, MN 55125
[EMAIL PROTECTED]
(651) 361-5796
**************************************************************************************
This e-mail message and all attachments transmitted with it may contain legally
privileged and/or confidential information intended solely for the use of the
addressee(s). If the reader of this message is not the intended recipient, you are
hereby notified that any reading, dissemination, distribution, copying, forwarding or
other use of this message or its attachments is strictly prohibited. If you have
received this message in error, please notify the sender immediately and delete this
message and all copies and backups thereof.
Thank you.
**************************************************************************************
