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.
**************************************************************************************

Reply via email to