EcalCondDB/analysis/sum_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 start_run end_run min_sum_events_over_lo min_sum_events_over_hi\n" unless ($#ARGV == 3);
0011
0012 my ($start_run, $end_run, $min_sum_lo, $min_sum_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 rdat.id1 SM, cry.id2 crystal, ang.id1 ieta, ang.id2 iphi,
0021                      sum(occ.events_over_low_threshold) sum_events_over_lo, sum(occ.events_over_high_threshold) sum_events_over_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 cry ON cry.logic_id = occ.logic_id AND cry.name = 'EB_crystal_number'
0031                 JOIN channelview ang ON ang.logic_id = cry.logic_id AND ang.name = 'EB_crystal_angle'
0032                WHERE ldef.location = 'H4B'
0033                  AND riov.run_num >= ?
0034              AND riov.run_num <= ?
0035             GROUP BY rdat.id1, cry.id2, ang.id1, ang.id2
0036               HAVING sum(occ.events_over_low_threshold) >= ?
0037                  AND sum(occ.events_over_high_threshold) >= ?
0038             ORDER BY SM, crystal ];
0039
0040 my $sth = $dbh->prepare_cached($sql);
0041
0042 $sth->execute($start_run, $end_run, $min_sum_lo, $min_sum_hi);
0043
0044 print join("\t", @{$sth->{NAME}}), "\n";
0045 while (my @row = $sth->fetchrow()) {
0046     print join("\t", @row), "\n";
0047 }
0048
0049
0050
0051 sub my_connect {
0052   my %args = @_;
0053   my $db = $args{db};
0054   my $user = $args{user};
0055   my $pass = $args{pass};
0056   my $port = $args{port} || 1521;
0057   my $db_opts = $args{db_opts};
0058
0059   # env
0060   $ENV{"ORACLE_HOME"} = '/afs/cern.ch/project/oracle/@sys/10103';
0061   $ENV{"TNS_ADMIN"} = '/afs/cern.ch/project/oracle/admin';
0062   $ENV{"NLS_LANG"} = "AMERICAN";
0063
0064   # here we use the TNS_ADMIN file and $db is the SID
0065   my $dsn;
0066   if ($db) {
0067     $dsn = "DBI:Oracle:$db";
0068   } else {
0069     die "Oracle needs to have database defined on connection!\n";
0070   }
0071
0072   my $dbh = DBI->connect($dsn, $user, $pass, $db_opts)
0073     or die "Database connection failed, $DBI::errstr";
0074
0075   $dbh->do(qq[ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS']);
0076
0077   return $dbh;
0078 }