On Tue, 17 Aug 2004, Bart Kersteter wrote:
> 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.
This doesn't look like an error; it looks like a warning. Is making
your script die? Is line 62 the line where you check for $userid?
> *----------------------
> 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;
I think you forgot to excise the above line when you rewrote this.
> # 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;
I'm sure this is one of the error-checking things you were talking
about, but the above should probably be written:
$source_sth->execute or die "Can't execute source statement: " .
$source_dbh->errstr;
> my ($userid, $year, $week, $cpu, $io);
> $source_sth->bind_columns( \$userid, \$year, \$week, \$cpu, \$io );
>
> while ($source_sth->fetch) {
> if ( not $userid eq "" ) {
This could be written more simply as
if ( $userid ne '' ) {
or even
if ( $userid ) {
> $target_sth->execute($orasid, $server, $year, $week, $userid, $cpu, $io);
> }
> }
>
> # Clean up and exit loop
> $target_dbh->commit();
> $source_dbh->disconnect();
> }
>
> $target_dbh->disconnect();
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.
