#! /usr/local/bin/sybperl 
#
# name: nightly.pl
# author: Todd Boss
# purpose: script meant to run from cron that runs update statistics,
#          issues an sp_recompile on every table in every server
#
# creation history
# date          author  purpose
# 12/10/96      T.Boss  creation  
# 1/6/97	T.Boss	modified to be more modular (takes server as
#                       parameter to sub populate_sapw), to run on all
#                       four supported servers
# 1/8/97	T.Boss	fixed syntax errors, modified to take parameter
#			and use front end to run all servers simultaneously
# 2/25/97	T.Boss	added Zeus's CC sql server to processing
# 3/11/97	T.Boss	added exclusion clause for tempdb,pubs2
# 4/2/97	T.Boss	added exclusion clause for sybsyntax
# 4/17/97	T.Boss	added crimson_dev to processing, deleted prod10 stuff
# 5/12/97	T.Boss	removed exclusion for sybsyntax...its running slow
# 7/8/97	T.Boss	moved to /export/syb_ops, added several servers,
#  removed zeus's CC, cleaned up SYBASE variables
# 7/30/97	T.Boss	added $pwdir, excluded model,sybsecurity
# 8/5/97	T.Boss	added new_crimson
# 8/18/97	t.Boss	fixed all passwords to read from server specific files
# 8/26/97	T.Boss	added master to db skip list

##########
# read in required files.  sql.pl used to return nice array of results.

require "sql.pl";

##########
# handle parameter passed to script (parameter is server name)
# if its blank, echo usage info and exit

$server=$ARGV[0];
if ($server eq "") {
   die "Usage: nightly.pl \n";
}                                   

##########
# set master variable: will always use /export/sybase/interfaces

$ENV{'SYBASE'} = "/export/sybase11";
$pwdir="/export/syb_ops";

##########
# subroutine defenitions

# sub populate_sa_pw takes as a parameter the servername, and reads the
# password file from my home dir...the password files are chmod 400 and
# only owned by me.

sub populate_sa_pw {
   local($server) = @_;
   if ($server eq "prod11") {
      $pwfile="$pwdir/.prod11sapw";
   }
   elsif ($server eq "prod") {
      $pwfile="$pwdir/.prodsapw";
   }
   elsif ($server eq "crimson") {
      $pwfile="$pwdir/.crimsonsapw";
   }
   elsif ($server eq "crimson_dev") {
      $pwfile="$pwdir/.crimson_devsapw";
   }
   elsif ($server eq "peter") {
      $pwfile="$pwdir/.petersapw";
   }
   elsif ($server eq "mdb") {
      $pwfile="$pwdir/.mdbsapw";
   }
   elsif ($server eq "syb_dicom_svr") {
      $pwfile="$pwdir/.syb_dicom_svrsapw";
   }
   elsif ($server eq "new_crimson") {
      $pwfile="$pwdir/.new_crimsonsapw";
   }
   else {
      die "Server name passed not valid...please check server name.\n";
   }

   open(PWFILE,"$pwfile");
   $sapw = <PWFILE>;
   chop($sapw);
   close(PWFILE);
}
   
#####################
# sub nightly logs into the server, gets the list of databases, then for
# each database gets the dbo.tablename (need both b/c some databases
# created with a different dbo than dbo) then issues the two commands
# on each table

sub nightly {
   $dbproc = &dblogin("sa", $sapw);
   &dbuse($dbproc, "master");

   @dbs = &sql($dbproc, "select name from sysdatabases order by name");
   #printf "\n\n@dbs\n\n";

   foreach $db (@dbs) {
      next if ($db =~ /tempdb|pubs2|model|sybsecurity|master/);
      printf "\n   ### $db ###\n";
      &sql($dbproc, "use $db");
      @tbl_owner = &sql($dbproc, "select u.name, s.name from sysusers u, 
                    sysobjects s where u.uid = s.uid and s.type = 'U'",".");  
      #printf "@tbl_owner\n";
      foreach $table (@tbl_owner) {
         &sql($dbproc, "update statistics $table");
         &sql($dbproc, "sp_recompile \"$table\"");
         #print "$table\n ";
      }
   } 

   &dbclose($dbproc);
}

##########
# Main processing

&populate_sa_pw($server);
$ENV{'DSQUERY'} = "$server";
printf "####### $ENV{'DSQUERY'} ########\n";
&nightly;