#!perl -w
########################################################################
#  Script Name:       variables.pl                                     #
########################################################################
# This module contains Global Variables for use with DRM Perl Scripts. #
########################################################################

##################
#  System Type.  #
##################
$SYSTEM_TYPE = "SAP";

#################
# System Status #
#################
$STATUS = "TEST";
#$STATUS = "PROD";

if ($STATUS eq "TEST")
{
	#######################
	# Test Systems Paging #
	#######################
	$PAGERNOTIFY      = "";
	$TELALERT_PAGE    = "";
	$BASIS_PAGE       = "";
	
	##############################
	# Number of Backups to keep. #
	##############################
	$KEEP             = 3;

	################################
	# Tables to Exclude from Reorg #
	################################
	# APQD --> /CWLD/EVT_CUR excluded since dynamic tables.

	@EXCLUDE        = ('APQD',
			   'APQI',
			   'VBLOG',
			   'VBHDR',
		  	   'VBMOD',
			   'VBDATA',
			   'YXR_EVENTS',	
			   '/CWLD/EVT_CUR');	 
}
else
{
	##########
	# Paging #
	##########
	$PAGERNOTIFY      = "";
	$TELALERT_PAGE    = "";
	$BASIS_PAGE       = "";
	
	##############################
	# Number of Backups to keep. #
	##############################
	$KEEP             = 7;

	################################
	# Tables to Exclude from Reorg #
	################################
	# APQD --> /CWLD/EVT_CUR excluded since dynamic tables.

	@EXCLUDE        = ('APQD',
			   'APQI',
			   'VBLOG',
			   'VBHDR',
		  	   'VBMOD',
			   'VBDATA',
			   'YXR_EVENTS',	
			   '/CWLD/EVT_CUR');
}

#####################
# All Systems Email #
#####################
$EMAILNOTIFY      = "";
$TELALERT_EMAIL   = "";
$BASIS_EMAIL      = "";

###########################################
# Filesystem % Utilized Paging Threshold. #
###########################################
$FSTHRESH         = 95;

###########################################
# Tablespace % Utilized Paging Threshold. #
###########################################
$TBLSPCTHRESH     = 90;

#####################################
# Backup Threshold for log_archive. #
#####################################
$ARCHTHRESH       = 60;

############################################################
# List of client specific tables that need runstats daily. #
############################################################
@TABLES           = ('EKPO',
		     'VEPO',
                     'VBUP',
                     'VBUK',
                     'VAPMA',
                     'LIPS',
                     'LIKP',
                     'LTAP',
                     'LTAK',
                     'EIPO',
                     'VTTK',
                     'VTTS',
                     'VTTP',
                     'EIKP',
                     'ZSD_VEPO_LTAP',
                     'ZPXLE_PERF_DATES',
                     'ZLE_BDL_HDR',
                     'ZLE_BDL_ITM',
                     'VEKP',
                     'VBAK',
                     'VBAP',
                     'LIPSDG',
                     'ZLE_PACK_WITH',
                     'MAKT',
                     'MARC',
                     'MARA',
                     'MATERIALID',
                     'EKES',
                     'EKET',
                     'HUSSTAT',
                     'VLPMA',
                     'MARM',
                     'VBFA',
                     'HUSTOBJ',
                     'VEVW',
                     'MARD',
                     'EINA',
                     'STXL',
                     'STXH',
                     'VBEP',
                     'MLGN');

############################################
# Tables that should not receive runstats. #
############################################
@NOSTATS          = ('APQD',
                     'APQI',
                     'VBMOD',
                     'VBHDR',
                     'VBDATA',
                     'VBLOG');

###########################################
# Tables that should have specific stats. #
###########################################
@CWLDSTATS        = ('YXR_EVENTS', '/CWLD/EVT_CUR');

#########################
# List of Current DBA's #
#########################
$DBALIST          = "";

###############################
# File to keep Last Runtimes. #
###############################
$RUNFILE          = "/db2/$DB2DBDFT/Messages/runtime.txt";

#########################################
# Filesystems to check for Utilization. #
#########################################
@FILESYSTEMS      = ("/db2/db2as",
		   "/db2/$DB2INSTANCE",
 		   "/db2/$DB2DBDFT",
  		   "/db2/$DB2DBDFT/db2dump",
  	           "/db2/$DB2DBDFT/sapdatat", 
		   "/db2/$DB2DBDFT/sapdatat2", 
  	           "/db2/$DB2DBDFT/sapdata1",
 	           "/db2/$DB2DBDFT/sapdata2",
  		   "/db2/$DB2DBDFT/sapdata3",
  	           "/db2/$DB2DBDFT/sapdata4",
 		   "/db2/$DB2DBDFT/sapdata5",
 	           "/db2/$DB2DBDFT/sapdata6");

#########################################
# Filesystems to check for Utilization. #
#########################################
@LOGFS		  = ("/db2/$DB2DBDFT/log_dir",
  		   "/db2/$DB2DBDFT/log_archive");

################################## 
# Database Active Log Directory. #
################################## 
$LOGDIR           = "/db2/$DB2DBDFT/log_dir/";

############################################################## 
# Log Utilization Threshold at which Application is forced . #
############################################################## 
$HIGHLOGTHRESH    = 93;

######################################################### 
# Log Utilization Threshold at which Basis is notified. #
######################################################### 
$LOGTHRESH        = 88;

############################################################################
# The following module will automatically create a peregrine ticket
############################################################################
sub create_ticket()	
{
	$peregrinefile        	= "/db2/$DB2DBDFT/Messages/peregrine.txt";
	$PEREGRINENOTIFY 	= "";

	($SC_brief_description, $update_action)=@_;


	#################################################3
	# Tickets are only created if paging is turned on
	##################################################
	if ($STATUS eq "PROD")	
	{
		#################################
		# Add host variables for backups
		#################################
		$space_holder = '  -  ';
		chomp($peregrine_hostname = `hostname`);
		$SC_brief_description = join $space_holder,$peregrine_hostname,$SC_brief_description;

		if ($SC_brief_description =~ /ackup/)	
		{
			chomp($peregrine_TSM_node = `db2 get db cfg for $DB2DBDFT | grep TSM_NODENAME`);
			$update_action = join $space_holder,$SC_brief_description,$update_action,$peregrine_TSM_node;
		}
		else 
		{
			$update_action = join $space_holder,$SC_brief_description,$update_action;
		}

		if ($SC_brief_description =~ /Perform disk space and disk request processes/)	
		{
			$SC_cat_subcomponent = "OTHER";
		}
		else
		{
			$SC_cat_subcomponent = "SOX";
		}

		##############################
		# Define Peregreine variables
		##############################			
		$SC_event = "";
		$SC_type = "";
		$SC_category = "SOFTWARE";
		$SC_subcategory = "DATABASE MANAGEMENT";
		$SC_product_type = "DB2 DISTRIBUTED DATABASE - SAP";
		$SC_problem_type = "SUPPORT";
		$SC_assignment = "DISTRIBUTED DATABASE";
		$SC_assignee_name = "";
		$SC_severity_code = "5";
		$SC_site_category = "WORKGROUP";
		$SC_cause_code = "SOFTWARE";
		$SC_contact_name = "";
		$SC_ticket_owner = "AUTOMATED - DRM DISTRIBUTED GROUP";
		$SC_user_priority = "3";
		$SC_cat_alternate_last = "NULL";
		$SC_cat_alternate_first = "NULL";
		$SC_alternate_phone = "NULL";
		$SC_cat_alternate_company = "NULL";
		$SC_cat_confidential = "GREEN";
		$SC_different_from_contact = "false";		


		##############
		# Format file
		##############			
		open OUT_TICKET, ">$peregrinefile";
		print OUT_TICKET "SC_event:$SC_event \n";
		print OUT_TICKET "SC_type:$SC_type \n";
		print OUT_TICKET "SC_category:$SC_category \n";
		print OUT_TICKET "SC_subcategory:$SC_subcategory \n";
		print OUT_TICKET "SC_product.type:$SC_product_type \n";
		print OUT_TICKET "SC_problem.type:$SC_problem_type \n";
		print OUT_TICKET "SC_assignment:$SC_assignment \n";
		print OUT_TICKET "SC_severity.code:$SC_severity_code \n";
		print OUT_TICKET "SC_site.category:$SC_site_category \n";
		print OUT_TICKET "SC_cause.code:$SC_cause_code \n";
		print OUT_TICKET "update.action:$update_action \n";
		print OUT_TICKET "SC_contact.name:$ SC_contact_name \n";
		print OUT_TICKET "SC_ticket.owner:$SC_ticket_owner \n";
		print OUT_TICKET "SC_brief.description:$SC_brief_description \n";
		print OUT_TICKET "SC_cat.subcomponent:$SC_cat_subcomponent \n";
		print OUT_TICKET "SC_assignee.name:$SC_assignee_name \n";
		print OUT_TICKET "SC_user_priority:$SC_user_priority \n";
		print OUT_TICKET "SC_cat.alternate_last:$SC_cat_alternate_last \n";
		print OUT_TICKET "SC_cat.alternate_first:$SC_cat_alternate_first \n";
		print OUT_TICKET "SC_alternate.phone:$SC_alternate_phone \n";
		print OUT_TICKET "SC_cat_alternate.company:$SC_cat_alternate_company \n";
		print OUT_TICKET "SC_cat.confidential:$SC_cat_confidential \n";
		print OUT_TICKET "SC_different.from.contact:$SC_different_from_contact \n";
		close OUT_TICKET;

		#############
		# Email File
		#############
		open IN_ticket, $peregrinefile;
		read IN_ticket, $msg_ticket, 10000;
		close IN_ticket;

		if ($SC_brief_description eq "" && $update_action eq "")
		{
			`printf "$msg_ticket" | mail -s "FAILED Automated ticket FAILED $SC_brief_description $update_action" $EMAILNOTIFY`;
			print "Ticket was not sent to peregrine server: $SC_brief_description and $update_action are inputs.\n";
		}
		else	
		{
			`printf "$msg_ticket" | mail -s "Automated ticket from DRM Distributed Team" $PEREGRINENOTIFY`;
			print "\nTicket sent to $PEREGRINENOTIFY for creation.\n";
		}	
	}
}


##</code><code>##

#!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`;