When I use the buildHistory sub routine. The info is just added to the current table. I need to clear the contents of the table and insert the new data. Is it better to drop table or delete table contents and how is it done?

#! /usr/local/bin/perl -w
# A perl program to do things with the alarm log

use FileHandle;
use IPC::Open2;
use Symbol;
# use Tk;
use Sybase::CTlib;
use Time::Local;
use strict;
my(%dispatch) = ();
my(%systemNames);
systemNames(); # fill in the hash
my $dbAlarm=new Sybase::CTlib 'ops','opsops','OPSYB1','';
$dbAlarm->ct_sql("use TomTest");
%dispatch = (help =>\&help,buildhistory=>\&buildHistory,createdbtables
+=>\&createDbTables);
my(@lines, $line);
my $count = 0;
#logMe();
my $numberOfArgs = @ARGV;
if($numberOfArgs == 0) {
  help();
  exit 1;
}
my($what) = shift(@ARGV);
my($whatLower) = $what;
$whatLower =~ tr/[A-Z]/[a-z]/;
if ($what eq undef)  {
  help();
  exit 1;
}
if(exists($dispatch{$whatLower})) {
  &{$dispatch{$whatLower}}(@ARGV);
  exit 0;
}
 help();
exit;

sub buildHistory {  # no input
  # for each entry in the notifAlarmLog Table build a history record
  my $sql = "SELECT distinct topic from notifAlarmEntry
             WHERE category like 'WARNING' OR category like 'OK NOW'";
  my(@sources, $source);
  @sources = $dbAlarm->ct_sql($sql);
  my ($TLCount) = 0; # Keeps track of Transaction Log (TL)
  my ($StartStopCount) = 0;
  my ($esequence,$eutime,$edtime,$esource,$eprocess,$ecategory,$etopic
+) = (0,1,2,3,4,5,6);
  foreach $source (@sources) {
    $TLCount++; # Incremnt TL
    $sql = "SELECT * from notifAlarmEntry where topic like '$source->[
+0]' ORDER by datetime";
    # print "$sql\n";
    my(@records,$recordA,$recordB);
    @records =  $dbAlarm->ct_sql($sql);
    if (($TLCount%1000) == 0) {
      $sql = "dump transaction TomTest with truncate_only";
      $dbAlarm->ct_sql($sql);
    }
    # these should now be in pairs, see that they are
    my($index);
    for ($index=0; $index<@records; ) {
      $recordA = $records[$index++];
      $recordB = $records[$index];
      $StartStopCount++; # Keeps track of each pair of Alarms (Start/S
+top)
      print "$StartStopCount alarm(s) occurred \n";
      my $result = GetPairs($recordA, $recordB);
      if($result < -1) { # maybe out of order
    $result = GetPairs($recordB, $recordA);
      }
      $index++ if($result == 0);
      print "Problem with $recordA->[$etopic] $recordA->[$esequence] $
+recordA->[$eutime]  $recordA->[$edtime] $recordA->[$esource] $recordA
+->[$eprocess] $recordA->[$ecategory] and $recordB->[$etopic] $recordB
+->[$esequence] $recordB->[$eutime]  $recordB->[$edtime] $recordB->[$e
+source] $recordB->[$eprocess] $recordB->[$ecategory] \n" if($result !
+= 0);
    }
  }
}

sub GetPairs { # two notifAlarmEntry records (Alarm: Start/Stop)
  # Input is two notifAlarmEntry records that should be an WARNING and
+ OK NOW
  # make a  history if they are OK else return an  error
  # return -1 if names are wrong (not same PS)
  # return -2 if 1st message is not WARNING
  # return -3 if 2nd message is not OK NOW
  # use strict;
  # my ($esequence,$eutime,$edtime,$esource,$eprocess,$ecategory,$etop
+ic) = (0,1,2,3,4,5,6);
  my ($recordA) = shift;
  my ($recordB) = shift;
  # print "record A $recordA->[0]  $recordA->[1]  $recordA->[2]  $reco
+rdA->[3] $recordA->[4] $recordA->[5] $recordA->[6] \n";
  # print "record B $recordB->[0]  $recordB->[1]  $recordB->[2]  $reco
+rdB->[3] $recordB->[4] $recordB->[5] $recordB->[6] \n";
  return -1 if($recordA->[6] ne $recordB->[6]);
  return -2 if($recordA->[5] ne 'WARNING');
  return -3 if($recordB->[5] ne 'OK NOW');
  # found a pair
  my ($f1,$adoName,$f3) = split(":",$recordA->[6]);
  my $SiteWideName= $main::systemNames{$adoName}  . " : $f3";
  # print ":$adoName: :$SiteWideName: \n";
  my $sql = "INSERT notifAlarmLog (name, AlarmStart, AlarmStop, UnixTi
+meStart, UnixTimeStop, SiteWideName) values ('$recordA->[6]','$record
+A->[2]', '$recordB->[2]', $recordA->[1], $recordB->[1], '$SiteWideNam
+e')";
  # print "$sql\n";
  $dbAlarm->ct_sql($sql);
  return 0;
}

sub systemNames {
  # fill in the systemName hash
  use strict;
  my $dbAdo=new Sybase::CTlib 'harmless','harmless','OPSYB1','paramPro
+pTable';
  $dbAdo->ct_sql("use serverAdo");
  my $sql = "SELECT name,systemName from adoInst where name like 'psWa
+tch%'";
  my(@rows,$row);
  @rows =  $dbAdo->ct_sql($sql);
  foreach $row (@rows) {
    $main::systemNames{$row->[0]} = $row->[1];
    # print "<$row->[0]> <$row->[1]> \n";
  }
}

sub createDbTables {
  use strict;
  my $tblName = "notifAlarmLog";
  print  "Create table name  $tblName\n";
  my $sql = "CREATE TABLE $tblName(
  name                           varchar(65)           not null,
  AlarmStart                     datetime              not null,
  AlarmStop                      datetime              not null,
  UnixTimeStart                  int                   not null,
  UnixTimeStop                   int                   not null,
  SiteWideName                   varchar(35)           null
)";
}

sub help {
  # display help for this tool
  print "NotifAlarmTool-  A program to do a variety of operations inte
+racting with the
                 Notif log.  The first argument is a command verb.  So
+me commands
                 require additional arguments.

List of commands

<buildHistory>   makes history records from the alarm records.

<createDbTables> Creates an alarm and an alarmHistory table in the
                 TomTest database.  Of course the old alarm and alarmH
+istory
                 records are gone.
";
}
[download]