EcalCondDB/analysis/occupancy.pl

0001 #!/usr/bin/perl
0002
0003 use warnings;
0004 use strict;
0005
0006 use DBI;
0007 use DBD::Oracle qw(:ora_types);
0008
0009
0010 die "Usage:  occupancy.pl min_n_lo min_n_hi\n" unless ($#ARGV == 1);
0011
0012 my ($min_n_lo, $min_n_hi) = @ARGV;
0013
0014 my $dbh = my_connect(db => 'ecalh4db',
0015           user => 'read01',
0016           pass => 'oraread01',
0017           db_opts => { RaiseError => 1 }
0018           );
0019
0020 my $sql = qq[ SELECT riov.run_num run, miov.subrun_num subrun, rdat.id1 SM, cv.id2 crystal,
0021                      occ.events_over_low_threshold n_lo, occ.events_over_high_threshold n_hi
0022                 FROM run_iov riov
0023                 JOIN run_tag rtag ON rtag.tag_id = riov.tag_id
0024                 JOIN location_def ldef ON ldef.def_id = rtag.location_id
0025                 JOIN (SELECT iov_id, cv.id1 FROM run_dat rdat
0026                         JOIN channelview cv ON cv.logic_id = rdat.logic_id AND cv.name = cv.maps_to) rdat
0027                      ON rdat.iov_id = riov.iov_id
0028                 JOIN mon_run_iov miov ON miov.run_iov_id = riov.iov_id
0029             JOIN mon_occupancy_dat occ ON occ.iov_id = miov.iov_id
0030                 JOIN channelview cv ON cv.logic_id = occ.logic_id AND cv.name = cv.maps_to
0031                WHERE ldef.location = 'H4B'
0032                  AND occ.events_over_low_threshold >= ?
0033              AND occ.events_over_high_threshold >= ?
0034             ORDER BY run, SM, crystal];
0035
0036 my $sth = $dbh->prepare_cached($sql);
0037
0038 $sth->execute($min_n_lo, $min_n_hi);
0039
0040 print join("\t", @{$sth->{NAME}}), "\n";
0041 while (my @row = $sth->fetchrow()) {
0042     print join("\t", @row), "\n";
0043 }
0044
0045
0046
0047 sub my_connect {
0048   my %args = @_;
0049   my $db = $args{db};
0050   my $user = $args{user};
0051   my $pass = $args{pass};
0052   my $port = $args{port} || 1521;
0053   my $db_opts = $args{db_opts};
0054
0055   # env
0056   $ENV{"ORACLE_HOME"} = '/afs/cern.ch/project/oracle/@sys/10103';
0057   $ENV{"TNS_ADMIN"} = '/afs/cern.ch/project/oracle/admin';
0058   $ENV{"NLS_LANG"} = "AMERICAN";
0059
0060   # here we use the TNS_ADMIN file and $db is the SID
0061   my $dsn;
0062   if ($db) {
0063     $dsn = "DBI:Oracle:$db";
0064   } else {
0065     die "Oracle needs to have database defined on connection!\n";
0066   }
0067
0068   my $dbh = DBI->connect($dsn, $user, $pass, $db_opts)
0069     or die "Database connection failed, $DBI::errstr";
0070
0071   $dbh->do(qq[ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS']);
0072
0073   return $dbh;
0074 }