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.
All other configuration is done in another ini file, including an option to define the output file format, html or text.
Te actual format, is not dynamic. It is hard codded in format_db_report.pl
================================
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.
The configuration files are two ini files:
db_list.ini - with all DB connection strings
report.ini - report configutation file
================================
Example of generated Report
================================
================================
List of files in process
================================
Configuration Files:
db_list.ini
report.ini
Output format Files:
header_file.txt
footer_file.txt
================================
Code: Flow
================================
report_main.bat => report_main.pl
report_main.pl => read db_list.ini
read report.ini
call main_from_perl.bat
main_from_perl.bat => run_sql.bat
run_sql.bat => sqlplus command
sqlplus => spool output to a file
report_main.pl => format_db_report.pl
format_db_report.pl => read output file from salplus
generates the report.
================================
List of programs
================================
ini files
db_list.ini
report.ini
bat files
report_main.bat
main_from_perl.bat
run_sql.bat
sqlplus files
tablespace_sql.sql
perl files
report_main.pl
format_db_report.pl
================================
Generated files:
================================
Formated Report: Tablespace_Storage_Report.html or Tablespace_Storage_Report.txt
Raw Data Report: report.txt
Log: Report_Log_<DATETIME>.log
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.
The configuration files are two ini files:
db_list.ini - with all DB connection strings
report.ini - report configutation file
================================
Example of generated Report
================================
================================
List of files in process
================================
db_list.ini
report.ini
Output format Files:
header_file.txt
footer_file.txt
================================
Code: Flow
================================
report_main.bat => report_main.pl
report_main.pl => read db_list.ini
read report.ini
call main_from_perl.bat
main_from_perl.bat => run_sql.bat
run_sql.bat => sqlplus command
sqlplus => spool output to a file
report_main.pl => format_db_report.pl
format_db_report.pl => read output file from salplus
generates the report.
================================
List of programs
================================
ini files
db_list.ini
report.ini
bat files
report_main.bat
main_from_perl.bat
run_sql.bat
sqlplus files
tablespace_sql.sql
perl files
report_main.pl
format_db_report.pl
================================
Generated files:
================================
Formated Report: Tablespace_Storage_Report.html or Tablespace_Storage_Report.txt
Raw Data Report: report.txt
Log: Report_Log_<DATETIME>.log
================================
Code
================================
ini files
db_list.ini
usera/passa@conn_str_a
userb/passb@conn_str_b
userc/passc@conn_str_c
userd/passd@conn_str_d
usere/passe@conn_str_e
userf/passf@conn_str_f
report.ini
MAIN_HOME_WIN=L:\tablespace_report\generate_report
LOG_FILE_PATH=L:\tablespace_report\generate_report\logs
REPORT_FILE_PATH=L:\tablespace_report\generate_report\reports
MAIL_PATH=L:\tablespace_report\generate_report\mail
INI_FILE_FOR_DB=db_list.ini
BAT_CMD=main_from_perl.bat
DB_LOG=report_log.log
REPORT_FILE=Tablespace_Storage_Report
MAIL_CMD=sendMail.bat
MAIL_HEADER=mail_header.txt
MAIL_FOOTER=mail_footer.txt
LAST_DB_REPORT_FILE=last_db_report.txt
HEADER_FILE=header_file.txt
FOOTER_FILE=footer_file.txt
FORMAT_STYLE=html
bat files
report_main.bat
perl report_main.pl
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 tablespace_sql %db_name%
sqlplus -l -s %db_name% @tablespace_sql %spool_to_file% %db_name%
sqlplus files
tablespace_sql.sql
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON
SPOOL &&1
SELECT '&&2' AS GATE_NAME,
TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END/1024/1024) AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;
SPOOL OFF
EXIT;
/
perl files
report_main.pl
#! /usr/bin/perl
#Main perl program.
#called from report_main.bat
#calling BAT_CMD from report.ini
use DateTime;
use strict;
use warnings;
#===================
#Global Parameters
#===================
my $INI_FILE='report.ini';
my $LOG_FILE_BASE_NAME='Report_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 $HEADER_FILE='';
my $FOOTER_FILE='';
my $FORMAT_STYLE='';
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 $headerFile='';
my $footerFile='';
my $lastReportFile;
my $run_date='';
my $result='';
my $runDate='';
#==================================
# 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 < 10){ $ss="0".$ss ; }
$run_date=$dateTime->year.$month.$day;
$timestamp=$run_date."_".$hh.$mm.$ss;
print "Running time: ".$timestamp."\n";
}
sub getRunDate{
my $run_date='';
my $dateTime=DateTime->now;
my $year=$dateTime->year;
my $month=$dateTime->month;
my $day=$dateTime->day;
if ($month < 10){ $month="0".$month ; }
my $day=$dateTime->day;
if ($day < 10){ $day="0".$day ; }
$run_date=$year."-".$month."-".$day;
$run_date;
}
sub fileFullPathNames{
#$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE."_".$timestamp.".rep";
$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE.".rep";
$dbLogFile=$LOG_FILE_PATH."\\".$DB_LOG;
$mailCmd=$MAIL_PATH."\\".$MAIL_CMD;
$mailHeaderFile=$MAIL_PATH."\\".$MAIL_HEADER;
$mailFooterFile=$MAIL_PATH."\\".$MAIL_FOOTER;
$headerFile=$MAIN_HOME_WIN."\\".$HEADER_FILE;
$footerFile=$MAIN_HOME_WIN."\\".$FOOTER_FILE;
}
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"};
$LOG_FILE_PATH = $hashTable{"LOG_FILE_PATH"};
$REPORT_FILE_PATH = $hashTable{"REPORT_FILE_PATH"};
$MAIL_PATH = $hashTable{"MAIL_PATH"};
$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"};
$HEADER_FILE = $hashTable{"HEADER_FILE"};
$FOOTER_FILE = $hashTable{"FOOTER_FILE"};
$FORMAT_STYLE = $hashTable{"FORMAT_STYLE"};
#concatinations
$LOG_FILE_NAME=$LOG_FILE_BASE_NAME."_".$timestamp.".log";
$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 "MAIL_CMD: ".$MAIL_CMD."\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 $formatStyle = shift;
my $header=shift;
my $footer=shift;
my $runDate=shift;
$result=system($^X,"format_db_report.pl",$sourceFile,$targetFile, $formatStyle, $header,$footer,$runDate);
$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 Successfuly"."\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();
$runDate=getRunDate();
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);
if ( $FORMAT_STYLE eq "text" ){
$result=formatReportFile($LOG_HANDLER, $dbLogFile, $reportFile, $FORMAT_STYLE, "NULL", "NULL", $runDate);
}elsif($FORMAT_STYLE eq "html" ){
$result=formatReportFile($LOG_HANDLER, $dbLogFile, $reportFile, $FORMAT_STYLE, $headerFile, $footerFile, $runDate);
}else{
print "Unknown format value in formatStyle: ".$FORMAT_STYLE."\n";
}
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 $formatStyle='';
my $headerFile = '';
my $footerFile = '';
my $runDate = '';
my $status='0';
my $header_dbName='Database Name';
my $header_tablespace='Tablespace';
my $header_freeSize='Free Size Mb';
my $header_usedSize='Used Size Mb';
my $header_totalSize='Max Size Mb';
my $header_freePct='Free %';
my $header_needMoreSpace='Add More Space';
my $header_underline="--------------------------------------------------";
#====================================================
# Sub routines Start Here
#====================================================
sub reportInputParams{
print "Input Parameters:"."\n";
print "sourceFile = ".$sourceFile."\n";
print "targetFile = ".$targetFile."\n";
print "formatStyle = ".$formatStyle."\n";
print "headerFile = ".$headerFile."\n";
print "footerFile = ".$footerFile."\n";
print "runDate = ".$runDate."\n";
}
sub formatStyleText{
$status='0';
my $sourceFile = shift;
my $targetFile = shift;
my $headerFile = shift;
my $footerFile = shift;
my $runDate = shift;
my $i_line='';
my $line_counter=0;
my $header_ind='Y';
my @words;
my $db_name='';
my $tablespace='';
my $usedSize='';
my $usedSize='';
my $totalSize='';
my $freePct='';
my $needMoreSpace='';
my $limitForAlert=5;
#=================================
#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 sprintf("%-50s %-30s %-12s %-12s %-12s %-8s %-14s\n",(substr $header_dbName,0,50), (substr $header_tablespace,0,30),(substr $header_freeSize,0,12), (substr $header_usedSize,0,12), (substr $header_totalSize,0,12),(substr $header_freePct,0,8), (substr $header_needMoreSpace,0,14));
print OUT_FILE sprintf("%-50s %-30s %-12s %-12s %-12s %-8s %-14s\n",(substr $header_underline,0,50),(substr $header_underline,0,30),(substr $header_underline,0,12),(substr $header_underline,0,12),(substr $header_underline,0,12),(substr $header_underline,0,8),(substr $header_underline,0,14));
while ($i_line = <IN_FILE>){
print $i_line;
if ($i_line =~ /^$/ ){
}else{
@words=split(" ",$i_line);
$db_name=@words[0];
$tablespace=@words[1];
$freeSize=@words[2];
$usedSize=@words[3];
$totalSize=@words[4];
$freePct=@words[5];
$needMoreSpace=@words[6];
if ( $needMoreSpace eq "Y"){
print OUT_FILE sprintf("%-50s %-30s %-12s %-12s %-12s %-8s %-14s\n",(substr $db_name,0,50),(substr $tablespace,0,30),(substr $freeSize,0,12),(substr $usedSize,0,12),(substr $totalSize,0,12),(substr $freePct,0,8),(substr $needMoreSpace,0,14));
}
}
}
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;
}
sub formatStyleHtml{
$status='0';
my $sourceFile = shift;
my $targetFile = shift;
my $headerFile = shift;
my $footerFile = shift;
my $i_line='';
my $line_counter=0;
my $header_ind='Y';
my @words;
my $db_name='';
my $tablespace='';
my $usedSize='';
my $usedSize='';
my $totalSize='';
my $freePct='';
my $needMoreSpace='';
my $colorGreen='#00FF00';
my $colorYellow='#FFFF66';
my $colorRed='#00FF00';
my $bgColor='';
my $htmlLine='';
#=================================
#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>){
$i_line =~ s/YYYY-MM-DD/$runDate/;
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>){
print $i_line;
if ($i_line =~ /^$/ ){
}else{
@words=split(" ",$i_line);
$db_name=@words[0];
$tablespace=@words[1];
$freeSize=@words[2];
$usedSize=@words[3];
$totalSize=@words[4];
$freePct=@words[5];
$needMoreSpace=@words[6];
if ( $needMoreSpace eq "Y" ){
$line_counter+=1;
if ($line_counter%2 ==0){
$bgColor=$colorGreen;
}else{
$bgColor=$colorYellow;
}
if ($freePct <= $limitForAlert){
$bgColor=$colorRed;
}
$htmlLine='';
$htmlLine='<TR BGCOLOR="'.$bgColor.'">';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$db_name.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$tablespace.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$freeSize.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$usedSize.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$totalSize.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$freePct.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$needMoreSpace.'</TD>';
$htmlLine=$htmlLine.'</TR>';
print OUT_FILE $htmlLine;
}
#$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;
}
sub readAndWrite{
my $sourceFile = shift;
my $targetFile = shift;
my $formatStyle = shift;
my $headerFile = shift;
my $footerFile = shift;
$status='0';
print "old name".$targetFile."\n";
$targetFile = substr($targetFile,0,index($targetFile,".rep")).".".$formatStyle;
print "new name".$targetFile."\n";
if ($formatStyle eq "text"){
$status=formatStyleText($sourceFile,$targetFile,$headerFile,$footerFile);
}
if ($formatStyle eq "html"){
$status=formatStyleHtml($sourceFile,$targetFile,$headerFile,$footerFile);
}
}
#====================================================
# Code Start Here
#====================================================
$pl_script_name="format_db_report.pl";
$sourceFile = shift;
$targetFile = shift;
$formatStyle = shift;
$headerFile = shift;
$footerFile = shift;
$runDate = shift;
#print "Inside ".$pl_script_name."\n";
reportInputParams();
$status=readAndWrite($sourceFile, $targetFile, $formatStyle, $headerFile, $footerFile);
exit $status;
#====================================================
# Code End Here
#====================================================
sendMail functionality
sendMail.bat
blat %1 -to user_a@mydomain.com, user_b@mydomain.com -i MIS@mydomain.com -subject "Tablespace Disk Usage Report"
Code
================================
ini files
db_list.ini
usera/passa@conn_str_a
userb/passb@conn_str_b
userc/passc@conn_str_c
userd/passd@conn_str_d
usere/passe@conn_str_e
userf/passf@conn_str_f
report.ini
MAIN_HOME_WIN=L:\tablespace_report\generate_report
LOG_FILE_PATH=L:\tablespace_report\generate_report\logs
REPORT_FILE_PATH=L:\tablespace_report\generate_report\reports
MAIL_PATH=L:\tablespace_report\generate_report\mail
INI_FILE_FOR_DB=db_list.ini
BAT_CMD=main_from_perl.bat
DB_LOG=report_log.log
REPORT_FILE=Tablespace_Storage_Report
MAIL_CMD=sendMail.bat
MAIL_HEADER=mail_header.txt
MAIL_FOOTER=mail_footer.txt
LAST_DB_REPORT_FILE=last_db_report.txt
HEADER_FILE=header_file.txt
FOOTER_FILE=footer_file.txt
FORMAT_STYLE=html
bat files
report_main.bat
perl report_main.pl
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 tablespace_sql %db_name%
sqlplus -l -s %db_name% @tablespace_sql %spool_to_file% %db_name%
sqlplus files
tablespace_sql.sql
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON
SPOOL &&1
SELECT '&&2' AS GATE_NAME,
TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END/1024/1024) AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;
SPOOL OFF
EXIT;
/
perl files
report_main.pl
#! /usr/bin/perl
#Main perl program.
#called from report_main.bat
#calling BAT_CMD from report.ini
use DateTime;
use strict;
use warnings;
#===================
#Global Parameters
#===================
my $INI_FILE='report.ini';
my $LOG_FILE_BASE_NAME='Report_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 $HEADER_FILE='';
my $FOOTER_FILE='';
my $FORMAT_STYLE='';
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 $headerFile='';
my $footerFile='';
my $lastReportFile;
my $run_date='';
my $result='';
my $runDate='';
#==================================
# 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 < 10){ $ss="0".$ss ; }
$run_date=$dateTime->year.$month.$day;
$timestamp=$run_date."_".$hh.$mm.$ss;
print "Running time: ".$timestamp."\n";
}
sub getRunDate{
my $run_date='';
my $dateTime=DateTime->now;
my $year=$dateTime->year;
my $month=$dateTime->month;
my $day=$dateTime->day;
if ($month < 10){ $month="0".$month ; }
my $day=$dateTime->day;
if ($day < 10){ $day="0".$day ; }
$run_date=$year."-".$month."-".$day;
$run_date;
}
sub fileFullPathNames{
#$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE."_".$timestamp.".rep";
$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE.".rep";
$dbLogFile=$LOG_FILE_PATH."\\".$DB_LOG;
$mailCmd=$MAIL_PATH."\\".$MAIL_CMD;
$mailHeaderFile=$MAIL_PATH."\\".$MAIL_HEADER;
$mailFooterFile=$MAIL_PATH."\\".$MAIL_FOOTER;
$headerFile=$MAIN_HOME_WIN."\\".$HEADER_FILE;
$footerFile=$MAIN_HOME_WIN."\\".$FOOTER_FILE;
}
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"};
$LOG_FILE_PATH = $hashTable{"LOG_FILE_PATH"};
$REPORT_FILE_PATH = $hashTable{"REPORT_FILE_PATH"};
$MAIL_PATH = $hashTable{"MAIL_PATH"};
$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"};
$HEADER_FILE = $hashTable{"HEADER_FILE"};
$FOOTER_FILE = $hashTable{"FOOTER_FILE"};
$FORMAT_STYLE = $hashTable{"FORMAT_STYLE"};
#concatinations
$LOG_FILE_NAME=$LOG_FILE_BASE_NAME."_".$timestamp.".log";
$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 "MAIL_CMD: ".$MAIL_CMD."\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 $formatStyle = shift;
my $header=shift;
my $footer=shift;
my $runDate=shift;
$result=system($^X,"format_db_report.pl",$sourceFile,$targetFile, $formatStyle, $header,$footer,$runDate);
$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 Successfuly"."\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();
$runDate=getRunDate();
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);
if ( $FORMAT_STYLE eq "text" ){
$result=formatReportFile($LOG_HANDLER, $dbLogFile, $reportFile, $FORMAT_STYLE, "NULL", "NULL", $runDate);
}elsif($FORMAT_STYLE eq "html" ){
$result=formatReportFile($LOG_HANDLER, $dbLogFile, $reportFile, $FORMAT_STYLE, $headerFile, $footerFile, $runDate);
}else{
print "Unknown format value in formatStyle: ".$FORMAT_STYLE."\n";
}
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 $formatStyle='';
my $headerFile = '';
my $footerFile = '';
my $runDate = '';
my $status='0';
my $header_dbName='Database Name';
my $header_tablespace='Tablespace';
my $header_freeSize='Free Size Mb';
my $header_usedSize='Used Size Mb';
my $header_totalSize='Max Size Mb';
my $header_freePct='Free %';
my $header_needMoreSpace='Add More Space';
my $header_underline="--------------------------------------------------";
#====================================================
# Sub routines Start Here
#====================================================
sub reportInputParams{
print "Input Parameters:"."\n";
print "sourceFile = ".$sourceFile."\n";
print "targetFile = ".$targetFile."\n";
print "formatStyle = ".$formatStyle."\n";
print "headerFile = ".$headerFile."\n";
print "footerFile = ".$footerFile."\n";
print "runDate = ".$runDate."\n";
}
sub formatStyleText{
$status='0';
my $sourceFile = shift;
my $targetFile = shift;
my $headerFile = shift;
my $footerFile = shift;
my $runDate = shift;
my $i_line='';
my $line_counter=0;
my $header_ind='Y';
my @words;
my $db_name='';
my $tablespace='';
my $usedSize='';
my $usedSize='';
my $totalSize='';
my $freePct='';
my $needMoreSpace='';
my $limitForAlert=5;
#=================================
#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 sprintf("%-50s %-30s %-12s %-12s %-12s %-8s %-14s\n",(substr $header_dbName,0,50), (substr $header_tablespace,0,30),(substr $header_freeSize,0,12), (substr $header_usedSize,0,12), (substr $header_totalSize,0,12),(substr $header_freePct,0,8), (substr $header_needMoreSpace,0,14));
print OUT_FILE sprintf("%-50s %-30s %-12s %-12s %-12s %-8s %-14s\n",(substr $header_underline,0,50),(substr $header_underline,0,30),(substr $header_underline,0,12),(substr $header_underline,0,12),(substr $header_underline,0,12),(substr $header_underline,0,8),(substr $header_underline,0,14));
while ($i_line = <IN_FILE>){
print $i_line;
if ($i_line =~ /^$/ ){
}else{
@words=split(" ",$i_line);
$db_name=@words[0];
$tablespace=@words[1];
$freeSize=@words[2];
$usedSize=@words[3];
$totalSize=@words[4];
$freePct=@words[5];
$needMoreSpace=@words[6];
if ( $needMoreSpace eq "Y"){
print OUT_FILE sprintf("%-50s %-30s %-12s %-12s %-12s %-8s %-14s\n",(substr $db_name,0,50),(substr $tablespace,0,30),(substr $freeSize,0,12),(substr $usedSize,0,12),(substr $totalSize,0,12),(substr $freePct,0,8),(substr $needMoreSpace,0,14));
}
}
}
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;
}
sub formatStyleHtml{
$status='0';
my $sourceFile = shift;
my $targetFile = shift;
my $headerFile = shift;
my $footerFile = shift;
my $i_line='';
my $line_counter=0;
my $header_ind='Y';
my @words;
my $db_name='';
my $tablespace='';
my $usedSize='';
my $usedSize='';
my $totalSize='';
my $freePct='';
my $needMoreSpace='';
my $colorGreen='#00FF00';
my $colorYellow='#FFFF66';
my $colorRed='#00FF00';
my $bgColor='';
my $htmlLine='';
#=================================
#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>){
$i_line =~ s/YYYY-MM-DD/$runDate/;
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>){
print $i_line;
if ($i_line =~ /^$/ ){
}else{
@words=split(" ",$i_line);
$db_name=@words[0];
$tablespace=@words[1];
$freeSize=@words[2];
$usedSize=@words[3];
$totalSize=@words[4];
$freePct=@words[5];
$needMoreSpace=@words[6];
if ( $needMoreSpace eq "Y" ){
$line_counter+=1;
if ($line_counter%2 ==0){
$bgColor=$colorGreen;
}else{
$bgColor=$colorYellow;
}
if ($freePct <= $limitForAlert){
$bgColor=$colorRed;
}
$htmlLine='';
$htmlLine='<TR BGCOLOR="'.$bgColor.'">';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$db_name.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$tablespace.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$freeSize.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$usedSize.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$totalSize.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$freePct.'</TD>';
$htmlLine=$htmlLine.'<TD colspan = "2">'.$needMoreSpace.'</TD>';
$htmlLine=$htmlLine.'</TR>';
print OUT_FILE $htmlLine;
}
#$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;
}
sub readAndWrite{
my $sourceFile = shift;
my $targetFile = shift;
my $formatStyle = shift;
my $headerFile = shift;
my $footerFile = shift;
$status='0';
print "old name".$targetFile."\n";
$targetFile = substr($targetFile,0,index($targetFile,".rep")).".".$formatStyle;
print "new name".$targetFile."\n";
if ($formatStyle eq "text"){
$status=formatStyleText($sourceFile,$targetFile,$headerFile,$footerFile);
}
if ($formatStyle eq "html"){
$status=formatStyleHtml($sourceFile,$targetFile,$headerFile,$footerFile);
}
}
#====================================================
# Code Start Here
#====================================================
$pl_script_name="format_db_report.pl";
$sourceFile = shift;
$targetFile = shift;
$formatStyle = shift;
$headerFile = shift;
$footerFile = shift;
$runDate = shift;
#print "Inside ".$pl_script_name."\n";
reportInputParams();
$status=readAndWrite($sourceFile, $targetFile, $formatStyle, $headerFile, $footerFile);
exit $status;
#====================================================
# Code End Here
#====================================================
sendMail functionality
sendMail.bat
blat %1 -to user_a@mydomain.com, user_b@mydomain.com -i MIS@mydomain.com -subject "Tablespace Disk Usage Report"
No comments:
Post a Comment