General
================================
Example of a process, that is reading from ini file several DB connection strings, loops on these connection strings, and generated a report from data fetched from these DB connections.
================================
The flow:
================================
Prepare db_list.ini file, to define the databases connection string.
For each entry in db_list.ini file, connect to the listed DB and execute a SQL file.
Append output from each DB connection, to one file.
Read the generated file, and build a new formatted file.
Save the name of the generated file, in another file, so the generated file can be accessed by a mailing program.
DB related code
Configuration File:
db_list.ini
Code:
main_from_perl.bat
run_sql.bat
refresh_log_sql.sql
Report building code
Configuration File:refresh_report.ini
Code:
refresh_report_main.bat
refresh_report_main.pl
format_db_report.pl
Generated files:
gates_refresh.log
last_db_report.txt
refresh_log_report_20140423_0400000.rep
db_list.ini
userA/paswdA@connectionStringA
userB/paswdB@connectionStringB
userC/paswdC@connectionStringC
userD/paswdD@connectionStringD
userE/paswdE@connectionStringE
userF/paswdF@connectionStringF
userG/paswdG@connectionStringG
userH/paswdH@connectionStringH
userJ/paswdJ@connectionStringJ
userK/paswdK@connectionStringK
userK/paswdK@connectionStringK
main_from_perl.bat
ECHO OFF
SET inifile=%1%
SET REFRESH_LOG=%2%
SET TEMP_REFRESH_LOG=temp_gates_refresh_log_stats.txt
ECHO.
ECHO =============================================
ECHO main is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.
ECHO. 2>%REFRESH_LOG%
FOR /F "tokens=*" %%i IN (%inifile%) DO (
ECHO handling %%i
ECHO. 2>%TEMP_REFRESH_LOG%
call run_sql.bat %TEMP_REFRESH_LOG% %%i
TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO.
)
ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================
run_sql.bat
SET spool_to_file=%1
SET db_name=%2
REM ECHO got DB name: %db_name%
REM ECHO refresh_log_sql %db_name%
sqlplus -l -s %db_name% @refresh_log_sql %spool_to_file% %db_name%
refresh_log_sql.sql
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON
SPOOL &&1
SELECT '&&2' AS SITE_NAME, COUNT(*) AS REFRESH_LOG_ENTRIES
FROM REFRESH_LOG;
SPOOL OFF
EXIT;
/
refresh_report.iniSET db_name=%2
REM ECHO got DB name: %db_name%
REM ECHO refresh_log_sql %db_name%
sqlplus -l -s %db_name% @refresh_log_sql %spool_to_file% %db_name%
refresh_log_sql.sql
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON
SPOOL &&1
SELECT '&&2' AS SITE_NAME, COUNT(*) AS REFRESH_LOG_ENTRIES
FROM REFRESH_LOG;
SPOOL OFF
EXIT;
/
MAIN_HOME_WIN=L:\refresh_report
INI_FILE_FOR_DB=db_list.ini
BAT_CMD=main_from_perl.bat
DB_LOG=gates_refresh.log
REPORT_FILE=refresh_log_report
MAIL_CMD=sendMail.bat
MAIL_HEADER=mail_header.txt
MAIL_FOOTER=mail_footer.txt
LAST_DB_REPORT_FILE=last_db_report.txt
refresh_report_main.bat
perl refresh_report_main.pl
refresh_report_main.pl
#! /usr/bin/perl
#Daily process for reading refresh_log table on remote sites.
use DateTime;
use strict;
use warnings;
#===================
#Global Parameters
#===================
my $INI_FILE='refresh_report.ini';
my $LOG_FILE_BASE_NAME='Gates_Refresh_Log';
my $display_delimiter="======================================\n";
my %hashTable;
my $MAIN_HOME_WIN='';
my $BAT_CMD = '';
my $MAIL_CMD= '';
my $MAIL_HEADER='';
my $MAIL_FOOTER='';
my $MAIL_PATH='';
my $INI_FILE_FOR_DB;
my $DB_LOG='';
my $LOG_FILE_PATH='';
my $LOG_FILE_NAME='';
my $REPORT_FILE_PATH='';
my $REPORT_FILE='';
my $LAST_DB_REPORT_FILE='';
my $timestamp='';
my $logFile='';
my $dbLogFile='';
my $reportFile='';
my $mailCmd='';
my $mailHeaderFile='';
my $mailFooterFile='';
my $lastReportFile;
my $run_date='';
my $result='';
#==================================
# subs
#==================================
sub setRundate{
my $dateTime=DateTime->now;
my $month=$dateTime->month;
my $year=$dateTime->year;
if ($month < 10){ $month="0".$month ; }
my $day=$dateTime->day;
if ($day < 10){ $day="0".$day ; }
my $hh=$dateTime->hour;
if ($hh < 10){ $hh="0".$hh ; }
my $mm=$dateTime->minute;
if ($mm < 10){ $mm="0".$mm ; }
my $ss=$dateTime->second;
if ($ss == 60){ $ss=0; };
if ($ss == 61){ $ss=1; };
if ($ss < 10){ $ss="0".$mm ; }
$run_date=$dateTime->year.$month.$day;
$timestamp=$run_date."_".$hh.$mm.$ss;
print "Running time: ".$timestamp."\n";
}
sub fileFullPathNames{
$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE."_".$timestamp.".rep";
$dbLogFile=$LOG_FILE_PATH."\\".$DB_LOG;
$mailCmd=$MAIL_PATH."\\".$MAIL_CMD;
$mailHeaderFile=$MAIL_PATH."\\".$MAIL_HEADER;
$mailFooterFile=$MAIL_PATH."\\".$MAIL_FOOTER;
}
sub setLogFileName{
$logFile=$LOG_FILE_PATH."\\".$LOG_FILE_NAME."_".$timestamp.".log";
my $status=0;
#print "Will try to create now new folder: ".$LOG_FILE_PATH;
if (!-d "$LOG_FILE_PATH" ){
my $cmd = "mkdir "."\"".$LOG_FILE_PATH."\"";
$status=system($cmd);
}
if ($status == 0 ){
print "\n";
print $display_delimiter;
print "\n";
print "Log File: ".$logFile."\n";
print "\n";
print $display_delimiter;
print "\n";
}
$status;
}
sub readIniFile{
my $i_ini_file=shift;
my $line='';
my $key='';
my $value='';
my $match_str = "CUSTOMER_[0-9][0-9]";
#read from ini file
open(FILE_HANDLER, "$i_ini_file") or die ("Could not open ini file: "."$i_ini_file"."\n");
my $count=0;
foreach $line (<FILE_HANDLER>){
chomp($line);
if ($line =~ /^$/) {
#nothing. Ignore empty lines
}elsif($line =~ /^#/) {
#nothing. Ignore # comment lines lines
}else{
($key, $value)=split(/=/,$line);
$hashTable{$key} = $value;
}
}
close (FILE_HANDLER);
}
sub sendMail{
my @mailArg = "";
my $sendMailInd=0;
print "Running command: ".$mailCmd." to send contents of file: ".$reportFile;
@mailArg=($mailCmd,$reportFile);
system(@mailArg);
}
sub printMultipath{
my $str = shift;
my $log_handler = shift;
print $log_handler ($str);
print ($str);
}
sub setGeneralParams{
#from ini file
$MAIN_HOME_WIN = $hashTable{"MAIN_HOME_WIN"};
$INI_FILE_FOR_DB=$hashTable{"INI_FILE_FOR_DB"};
$BAT_CMD = $hashTable{"BAT_CMD"};
$DB_LOG=$hashTable{"DB_LOG"};
$REPORT_FILE=$hashTable{"REPORT_FILE"};
$MAIL_CMD = $hashTable{"MAIL_CMD"};
$MAIL_HEADER = $hashTable{"MAIL_HEADER"};
$MAIL_FOOTER = $hashTable{"MAIL_FOOTER"};
$LAST_DB_REPORT_FILE= $hashTable{"LAST_DB_REPORT_FILE"};
#concatinations
$LOG_FILE_PATH=$MAIN_HOME_WIN."\\"."Logs";
$REPORT_FILE_PATH=$MAIN_HOME_WIN."\\"."Reports";
$LOG_FILE_NAME=$LOG_FILE_BASE_NAME."_".$timestamp.".log";
$MAIL_PATH=$MAIN_HOME_WIN."\\"."Mail";
$lastReportFile=$MAIN_HOME_WIN."\\".$LAST_DB_REPORT_FILE;
#report
print $display_delimiter;
print "Running With General Parameters:"."\n";
print "MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n";
print "BAT_CMD: ".$BAT_CMD."\n";
print "INI_FILE_FOR_DB: ".$INI_FILE_FOR_DB."\n";
print "DB_LOG: ".$DB_LOG."\n";
print "Report: ".$REPORT_FILE."\n";
print $display_delimiter;
}
sub runOsCommand{
my $log_handler = shift;
my $cmd = shift;
my $ini_file = shift;
print "Running Command: ".$cmd."\n";
#$result=system($^X,$cmd);
my @SendArg=($cmd,$ini_file, $dbLogFile);
$result=system(@SendArg);
#print "Exit point after call to ".$cmd." Exist status is: ".$result."\n";
$result;
}
sub updateLastDbReport{
my $lastReportFile=shift;
my $reportFile=shift;
open OUT_FILE,">",$lastReportFile or die $!;
print OUT_FILE $reportFile."\n";
close OUT_FILE or die $!;
}
sub formatReportFile{
my $log_handler = shift;
my $sourceFile = shift;
my $targetFile = shift;
my $header=shift;
my $footer=shift;
$result=system($^X,"format_db_report.pl",$sourceFile,$targetFile, $header,$footer);
$result;
}
sub reportGeneralParameters{
my $log_handler = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("Input Parameters: \n",$log_handler);
printMultipath ("Run Date:".$run_date."\n",$log_handler);
printMultipath ("Log File: ".$logFile."\n",$log_handler);
printMultipath ("MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n",$log_handler);
printMultipath ("BAT_CMD: ".$BAT_CMD."\n",$log_handler);
printMultipath ("MAIL_CMD: ".$MAIL_CMD."\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
}
sub reportStartOfProcess{
my $log_handler = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("Refresh Log On Gates Checkup Process Started at: ".$timestamp."\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
}
sub reportEndOfProcess{
my $log_handler = shift;
my $status = shift;
printMultipath ("\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
if ($status == 0){
printMultipath ("Process has Finished Successfully"."\n" ,$log_handler);
}else{
printMultipath ("Error!!!! Process has Terminated with Error."."\n",$log_handler);
}
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
$status;
}
sub handleStatus{
my $status = shift;
my $method = shift;
my $log_handler = shift;
if ($status != 0){
if (defined $log_handler){
printMultipath ("Error in Routine: ".$method." Status: ".$status."\n",$log_handler);
}else{
print ("Error in Routine: ".$method." Status: ".$status."\n");
}
return $status;
}
$status;
}
#==================================
# The code starts here
#==================================
$result=main();
exit $result;
#==================================
# sub main
#==================================
sub main{
no warnings 'uninitialized';
setRundate();
readIniFile("$INI_FILE");
setGeneralParams();
$result=setLogFileName();
handleStatus($result, "setLogFileName",undef);
open my $LOG_HANDLER, '>', $logFile or die ("Could not open log file: ".$logFile);
fileFullPathNames();
reportStartOfProcess($LOG_HANDLER);
reportGeneralParameters($LOG_HANDLER);
my $cmd='"'.$MAIN_HOME_WIN."\\".$BAT_CMD.'"';
$result=runOsCommand($LOG_HANDLER,$cmd,$INI_FILE_FOR_DB);
handleStatus($result, $cmd,undef);
#$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,$mailHeaderFile,$mailFooterFile);
$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,"NULL","NULL");
handleStatus($result, "formatReportFile",undef);
updateLastDbReport($lastReportFile,$reportFile);
reportEndOfProcess($LOG_HANDLER, $result);
close $LOG_HANDLER;
#sendMail(); - mail is send from a server with access to mail server
}
format_db_report.pl
#====================================================
# Declaration
#====================================================
my $pl_script_name='';
my $sourceFile='';
my $targetFile='';
my $headerFile = '';
my $footerFile = '';
my $status='0';
my $header="GATE_NAME REFRESH_LOG_ENTRIES";
my $hliner="----------------------------- -------------------";
#====================================================
# Sub routines Start Here
#====================================================
sub reportInputParams{
print "Input Parameters:"."\n";
print "sourceFile = ".$sourceFile."\n";
print "targetFile = ".$targetFile."\n";
}
sub readAndWrite{
$status='0';
my $sourceFile = shift;
my $targetFile = shift;
my $headerFile = shift;
my $footerFile = shift;
my $i_line='';
my $o_line='';
my $line_counter=0;
my $header_ind='Y';
my @words;
my $db_name='';
my $enrties='';
#=================================
#Add Header
#=================================
open OUT_FILE,">",$targetFile or die $!;
close OUT_FILE or die $!;
if ($headerFile ne "NULL"){
open IN_FILE,$headerFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
print OUT_FILE "\n";
while ($i_line = <IN_FILE>){
print OUT_FILE $i_line;
}
print OUT_FILE "\n";
print OUT_FILE "\n";
print OUT_FILE "\n";
close IN_FILE or die $!;
close OUT_FILE or die $!;
}
#=================================
#Add Contents
#=================================
open IN_FILE,$sourceFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
print OUT_FILE $header."\n";
print OUT_FILE $hliner."\n";
while ($i_line = <IN_FILE>){
$line_counter+=1;
if ($line_counter == 1){
#do nothing - this is an empty line
}elsif($line_counter == 2){
#do nothing - this is header
}elsif($line_counter == 3){
#do nothing - this is underline
}elsif($line_counter == 4){
@words=split(" ",$i_line);
$db_name=@words[0];
$enrties=@words[1];
if ($enrties > 0){
print OUT_FILE sprintf("%-50s %-10s\n",$db_name,$enrties);
}
$line_counter=0;
}
}
close IN_FILE or die $!;
close OUT_FILE or die $!;
#=================================
#Add Footer
#=================================
if ($footerFile ne "NULL"){
open IN_FILE,$footerFile or die $!;
open OUT_FILE,">>",$targetFile or die $!;
print OUT_FILE "\n";
print OUT_FILE "\n";
print OUT_FILE "\n";
while ($i_line = <IN_FILE>){
print OUT_FILE $i_line;
}
print OUT_FILE "\n";
close IN_FILE or die $!;
close OUT_FILE or die $!;
}
$status;
}
#====================================================
# Code Start Here
#====================================================
$pl_script_name="format_db_report.pl";
$sourceFile = shift;
$targetFile = shift;
$headerFile = shift;
$footerFile = shift;
reportInputParams();
$status=readAndWrite($sourceFile, $targetFile, $headerFile, $footerFile);
exit $status;
#====================================================
# Code End Here
#====================================================
gates_refresh.log
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERA/USERA@CONNECTION_STRINGA 0
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERB/USERB@CONNECTION_STRINGB 0
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERC/USERC@CONNECTION_STRINGC 0
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERD/USERE@CONNECTION_STRINGE 0
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERF/USERF@CONNECTION_STRINGF 0
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERG/USERG@CONNECTION_STRINGG 90009
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERH/USERH@CONNECTION_STRINGH 1
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERJ/USERJ@CONNECTION_STRINGJ 0
GATE_NAME REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERK/USERK@CONNECTION_STRINGK 89999
last_db_report.txt
L:\refresh_report\Reports\refresh_log_report_20140423_0400000.repGATE_NAME REFRESH_LOG_ENTRIES
--------------------------------- -------------------
userA/paswdA@connectionStringA 90009
userB/paswdB@connectionStringB 1
userC/paswdC@connectionStringC 89999
userD/paswdD@connectionStringD 90036
userE/paswdE@connectionStringE 1
userF/paswdF@connectionStringF 3
userG/paswdG@connectionStringG 3
userH/paswdH@connectionStringH 128509
userJ/paswdJ@connectionStringJ 132225
userK/paswdK@connectionStringK 90442
No comments:
Post a Comment