#!perl -w
#########################################################################
#  Script Name:       report.pl                                         #
#  Date Converted:    6-25-04                                           #
#  Date modified:     6-01-09                                           #
#  Converted by:      jlhghglh                                          # 
#  Last updated by:  lkjhkjhjhjh - Changed the threshold for the Table  #
#                    Space % utilized from 75 to 90.                    #
#                                                                       #
#########################################################################
# Frequency: Run once weekly on Monday.                                 #
#########################################################################
#############################
#   Variables and Modules.  #
#############################
unshift (@INC, "/db2/$DB2DBDFT/Scripts");
require 'variables.pl';
use Env;

$time           = scalar(localtime);
$now            = time;
$request        = 0;
$highutil       = 0;
$lowutil        = 0;
$largespace     = 0;
$used1          = 0;
$used2          = 0;
$used3          = 0;
$used4          = 0;
$used5          = 0;
$used6          = 0;
$totalA         = 0;
$totalU         = 0;
$file           = "/db2/$DB2DBDFT/Messages/report.txt";
$temp           = "/db2/$DB2DBDFT/Messages/temp.txt";
$base           = "/db2/$DB2DBDFT/Messages/growth.base";
@filesystems    = ("/db2/$DB2DBDFT/sapdata1",
 	           "/db2/$DB2DBDFT/sapdata2",
  		   "/db2/$DB2DBDFT/sapdata3",
  	           "/db2/$DB2DBDFT/sapdata4",
 		   "/db2/$DB2DBDFT/sapdata5",
 	           "/db2/$DB2DBDFT/sapdata6");

###########################
# Create new report file. #
###########################
open OUT, ">$file";
print OUT "Database: $DB2DBDFT\n";
print OUT "Time: $time\n\n";

### Filesystem overall ###
print OUT "Current File System Usage:\n";
print OUT "Filesystem             Size  Used Avail Use  Mounted on\n";
$rc = `df -h | grep /db2/$DB2DBDFT/sapdata1`;
print OUT "$rc\n";


#########################################################################
#                Filesystem Exceeding 90% Utilization.                  #
#########################################################################
foreach (@filesystems)
{
	######################################
	# Obtain % Used for each filesystem. #
	######################################
	$rc = `df -k | grep $_`;
	
	if ($rc =~ / (\d*)\%/)
	{	
		if ($1 > 90)
		{
			$mounts{$_}=$1;
			$request = 1;
		}
	}
}

if ($request == 1)
{
	print OUT "FILE SYSTEMS EXCEED 90 PCT UTILIZATION.\n";
	print OUT "A DISK REQUEST MAY NEED TO BE CREATED.\n";
	foreach (keys %mounts)
	{
		print OUT "$_	$mounts{$_}\n";
	}
}

#########################################################################
#                Growth Rates & Database Size.                          #
#########################################################################
open OUT2, ">$temp"; 
$rc = `db2 list tablespaces show detail`;
print OUT2 "$rc";
close OUT2;

########################################
# Calculate Current File System Sizes. #
########################################	
open IN, $temp;
while (<IN>) 
{
	if (/Tablespace ID *= (\w*)/)
	{
		$tsid = $1;
	}

	if (/Total pages *= (\w*)/)
	{
		$totalpages = $1;
	}	

	if (/Used pages *= (\w*)/)
	{
		$usedpages = $1;
	}	

	if (/Page size \(bytes\) *= (\w*)/)
	{
		$pagesize = $1;
		$allocsize = ($pagesize * $totalpages)/1024/1024/1024;
		$usedsize = ($pagesize * $usedpages)/1024/1024/1024;
		$totalA = $totalA + $allocsize;
		$totalU = $totalU + $usedsize;
	}

	if (/Number of containers *= (\w*)/)
	{
		$numcon = $1;
		$conused  = ($usedpages * $pagesize)/1024/1024/1024/$numcon;

		open CON, "db2 list tablespace containers for $tsid |";
		while (<CON>)
		{
       			if (/Name *= \/db2\/$DB2DBDFT\/(\w*)\//)
			{
               			$conpath = $1;
         			if ($conpath eq "sapdata1")
				{
					$used1  = $used1 + $conused;
				}

				if ($conpath eq "sapdata2")
				{
					$used2  = $used2 + $conused;
				}
		
				if ($conpath eq "sapdata3")
				{
					
					$used3  = $used3 + $conused;
				}
		
				if ($conpath eq "sapdata4")
				{
						
					$used4  = $used4 + $conused;
				}
		
				if ($conpath eq "sapdata5")
				{
					$used5  = $used5 + $conused;
				}

				if ($conpath eq "sapdata6")
				{
					$used6  = $used6 + $conused;
				}
 		       }
		}
		close CON;
	}	
}
close IN; 

##################################
# Read or create Baseline Sizes. #
################################## 
if (-e $base)
{
	print OUT "\nGROWTH RATES\n";
	open IN, $base;
	while (<IN>)
	{
		if (/Epoch: (\d*)/)
		{
			$basetime = $1;
		}
		
		if (/sapdata1	(\d*\.\d*)/)
		{
			$base1 = $1;
		}
		
		if (/sapdata2	(\d*\.\d*)/)
		{
			$base2 = $1;
		}

		if (/sapdata3	(\d*\.\d*)/)
		{
			$base3 = $1;
		}
		
		if (/sapdata4	(\d*\.\d*)/)
		{
			$base4 = $1;
		}

		if (/sapdata5	(\d*\.\d*)/)
		{
			$base5 = $1;
		}

		if (/sapdata6	(\d*\.\d*)/)
		{
			$base6 = $1;
		}

		if (/Used Data:	(\d*\.\d*)/)
		{
			$baseU = $1;
		}
	}
	close IN;

	###############################################
	#    	    Calculate Growth Rates.           #
	###############################################
	$days  = ($now - $basetime)/86400;
	$rate1 = ($used1 - $base1)/$days;
	$rate2 = ($used2 - $base2)/$days;
	$rate3 = ($used3 - $base3)/$days;
	$rate4 = ($used4 - $base4)/$days;
	$rate5 = ($used5 - $base5)/$days;
	$rate6 = ($used6 - $base6)/$days;
	$rateT = ($totalU - $baseU)/$days;

	printf OUT "/db2/$DB2DBDFT/sapdata1:	%.2f GB per day\n",$rate1;
	printf OUT "/db2/$DB2DBDFT/sapdata2:	%.2f GB per day\n",$rate2;
	printf OUT "/db2/$DB2DBDFT/sapdata3:	%.2f GB per day\n",$rate3;
	printf OUT "/db2/$DB2DBDFT/sapdata4:	%.2f GB per day\n",$rate4;
	printf OUT "/db2/$DB2DBDFT/sapdata5:	%.2f GB per day\n",$rate5;
	printf OUT "/db2/$DB2DBDFT/sapdata6:	%.2f GB per day\n",$rate6;
	printf OUT "Total System Growth Rate:   	%.2f GB per day\n",$rateT;
}
else
{
	###############################################
	#    Open File to Capture Base Growth Data.   #
	###############################################
	open OUT2, ">$base";
	print OUT2 "$time\n";
	print OUT2 "Epoch: $now\n";
	print OUT2 "Filesystem		Raw Data in GB\n";
	printf OUT2 "/db2/$DB2DBDFT/sapdata1	%.2f\n",$used1;
	printf OUT2 "/db2/$DB2DBDFT/sapdata2	%.2f\n",$used2;
	printf OUT2 "/db2/$DB2DBDFT/sapdata3	%.2f\n",$used3;
	printf OUT2 "/db2/$DB2DBDFT/sapdata4	%.2f\n",$used4;
	printf OUT2 "/db2/$DB2DBDFT/sapdata5	%.2f\n",$used5;
	printf OUT2 "/db2/$DB2DBDFT/sapdata6	%.2f\n",$used6;
	printf OUT2 "Total Used Data:	%.2f\n",$totalU;
	printf OUT2 "Total Allocated Data:	%.2f\n",$totalA;
	close OUT2;
}
unlink($temp);

print OUT "\nCURRENT DATABASE SIZE\n";
printf OUT "Total Used Data:	%.2f\n",$totalU;
printf OUT "Total Allocated Data:	%.2f\n",$totalA;

print OUT "\nBUFFERPOOL UTILIZATION\n";
$rc = `db2 -tx "SELECT SUBSTR(BP_NAME,1,14) AS BP_NAME, TOTAL_HIT_RATIO_PERCENT FROM SYSIBMADM.BP_HITRATIO where TOTAL_HIT_RATIO_PERCENT > 0 ORDER BY DBPARTITIONNUM"`;
printf OUT "$rc\n";

#########################################################################
#                 Gather Tablespace Utilization Info.                   #
#########################################################################
open IN, "db2 list tablespaces show detail |";
while (<IN>) 
{
	if (/Name *= (\S*)/)
	{
		$name= $1;
	}	

	if (/Type *= (\w*)/)
	{
		$type= $1;
	}

	if (/Total pages *= (\w*)/)
	{
		$totalpages = $1;
	}

	if (/Useable pages *= (\w*)/)
	{
		$useablepages = $1;
	}
		
	if (/Used pages *= (\w*)/)
	{	
		$usedpages = $1;
		$percentused = int(100 * ($usedpages/$useablepages));

		 #Changed to 90 to reduce the number of table spaces reported
		if ($percentused > 90 && $type ne "System")   
		{
			$highutil = 1;
			$highspaces{$name}= $percentused;
		}
	}

	if (/High water mark \(pages\) *= (\w*)/)
	{	
		$hwm = $1;
		if ($type ne "System" && $hwm > 7680)
		{
			$percentused = int(100 * ($hwm/$useablepages));
			if ($percentused < 65)
			{	
				$lowutil = 1;
				$lowspaces{$name}= $percentused;
			}
		}
	}

	if (/Page size \(bytes\) *= (\w*)/)
	{	
		$pagesize = $1;
		$size = int(($pagesize * $totalpages)/1024/1024/1024);
		if ($size > 50)
		{
			$largespace = 1;
			$largespaces{$name}= $size;
		}
	}		
}	
close IN;

#########################################################################
#                 Print Overutilized Tablespaces.                       #
#########################################################################
if ($highutil == 1)
{
	#part of the table space % utilized change, changed to 90%
	print OUT "\nTABLESPACES EXCEEDING 90 PCT UTILIZATION:\n";  
	foreach (keys %highspaces)
	{
		print OUT "$_	$highspaces{$_} PCT\n";
	}
	print OUT "REMINDER: CWLD tablespaces should not continually increase in size.\n";
}

#########################################################################
#                 Print Underutilized Tablespaces.                      #
#########################################################################
if ($lowutil == 1)
{
	print OUT "\nTABLESPACES BELOW 65 PCT UTILIZATION:\n";
	foreach (keys %lowspaces)
	{
		print OUT "$_	$lowspaces{$_} PCT\n";
	}
}

#########################################################################
#                 Print Tablespaces Over 50GB.                          #
#########################################################################
if ($largespace == 1)
{
	print OUT "\nTABLESPACES EXCEEDING 50GB:\n";
	foreach (keys %largespaces)
	{
		print OUT "$_	$largespaces{$_} GB\n";
	}
}

#########################################################################
#                	     Largest Table.                             #
#########################################################################
print OUT "\nLARGEST TABLE:\n";

$rc1 = `db2 "select name as a from sysibm.systables where npages = (select max(npages) from sysibm.systables)"`; 
if ($rc1 =~ /(\/\w\S+|\w\S+)/)
{
	print OUT "Table: $1\n";
}

$rc = `db2 "select npages from sysibm.systables where npages = (select max(npages) from sysibm.systables)"`;
if ($rc =~ /(\d\d*)/)
{
	$size = $1;
	$gb = (($size * 4096)/1024/1024/1024); 
	printf OUT "Table Size: %.2f GB\n", $gb;
}

#########################################################################
#                 Last Runtimes of other Scripts.                       #
#########################################################################
$a=0; $b=0; $c=0; $d=0;

print OUT "\nLAST RUNTIMES for Runstats, DBSTATC, Reorgchk and Reorgs.\n";  
open (IN, $RUNFILE) or die $!;
while (<IN>)
{
	if (/Runstats/ && $a == 0)
	{
		print OUT "$_";
		$a = 1;
	}

	if (/DBSTATC/ && $b == 0)
	{
		print OUT "$_";
		$b = 1;
	}

	if (/Reorgchk/ && $c == 0)
	{
		print OUT "$_";
		$c = 1;
	}
	
	if (/Reorgs/ && $d == 0)
	{
		print OUT "$_";
		$d = 1;
	}
}
close IN;

		
#########################################################################
#                	     Backup Information.                        #
#########################################################################
print OUT "\nBACKUP INFORMATION:";
$backup = `db2adutl query full database $DB2DBDFT`;
print OUT "$backup";
close OUT;

###############
# Email File. #
###############
open IN, $file;
read IN, $msg, 10000;
close IN;

`printf "$msg" | mail -s "$DB2DBDFT - Report" $EMAILNOTIFY`;
`/usr/local/bin/telalertc -host obfuscated_email.com -m "$file has been created." -subject "$DB2DBDFT - Report" $TELALERT_EMAIL`;