#!perl -w
##################################################################################
#  Script Name:  report.pl                                                       #
#  Converted by: Sharath Jayatheertha                                            #
#  Date modified: 12-Jan-2006                                                    #
#  Description:  Generates report on WBI databases.                              #
#  Last updated by: Erik Little                                                  #
#  Date Updated:  March 22 , 2010                                                #
#  Notes:  Modified Largest Table section to calculate the MB of the largest     #
#  table.                                                                        #
#                                                                                #
#                                                                                #
##################################################################################
# Bring in Global Variables.
##################################################################################
unshift (@INC, "/db2/$DB2DBDFT/Scripts");
require 'variables.pl';
use Env;

$file = "/db2/$DB2DBDFT/Messages/report.txt";
$temp = "/db2/$DB2DBDFT/Messages/temp.txt";

$time = scalar(localtime);
$request = 0;
$size = 0;

open FILE, ">$file";
print FILE "\t\t\t\tReport\n\n\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 FILE "File Systems Exceed 90 Pct Utilization.\n";
        print FILE "A Disk Request May Need To Be Created\n";
        foreach (keys %mounts)
        {
                print FILE "$_  $mounts{$_}\n";
        }
}
foreach $db (@dblst)
{
        $rc = `db2 connect to $db`;
        if($rc =~ /Database Connection Information/)
        {
                print FILE "\n--------------------------------------------------------------------------------------------------";
                print FILE "\n\t\t\t\t$db Database\n";
                print FILE "--------------------------------------------------------------------------------------------------\n";
                #########################################################################
                # Largest Table                                                         #
                #########################################################################

                undef $rc;
                undef $rc1;
                undef $size;
                undef $mb;
                $rc1 = `db2 "select name as a from sysibm.systables where npages = (select max(npages) from sysibm.systables)"`;
                if ($rc1 =~ /(\/\w\S+|\w\S+)/)
                {
                        printf FILE "Biggest Table:\nName: $1\n";
                        $rc = `db2 "select npages from sysibm.systables where npages = (select max(npages) from sysibm.systables)"`;
                        if ($rc =~ /(\d\d*)/)
                        {
                                $size = $1;
                                $mb = (($size * 4096)/1024/1024);
                                $result = sprintf("%.2f", $mb);
                                printf FILE "Size: $result MB\n";
                        }
                }

                #########################################################################
                #                 Gather Tablespace Utilization Info.                   #
                #########################################################################
                $largespace = 0;
                $highutil = 0;
                $dbsize = 0;
                undef $name;
                undef $type;
                undef $totalpages;
                undef $useablepages;
                undef $usedpages;
                undef $percentused;
                undef $highspaces;
                undef $size;
                undef $largespaces;
                undef $pagesize;
                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));
                                if (($percentused > 75) && ($type ne "System"))
                                {
                                        $highutil = 1;
                                        $highspaces{$name}= $percentused;
                                }
                        }
                        if (/Page size \(bytes\) *= (\w*)/)
                        {
                                $pagesize = $1;
                                $size = int(($pagesize * $totalpages)/1024/1024);
                                if($size > 200)
                                {
                                        $largespace = 1;
                                        $largespaces{$name}= $size;
                                }
                                $dbsize = $size + $dbsize;
                        }
                }
                close IN;

                print FILE "\nCURRENT DATABASE SIZE:\n";
                print FILE "Total Size : $dbsize MB\n";

                #########################################################################
                #                 Print Tablespaces Over 200MB.                         #
                #########################################################################
                if($largespace == 1)
                {
                        print FILE "\nTablespaces Exceeding 200MB:\n";
                        foreach (keys %largespaces)
                        {
                                print FILE "$_  $largespaces{$_} MB\n";
                        }
                        %largespaces = ();
                }

                #########################################################################
                #                 Print Overutilized Tablespaces.                       #
                #########################################################################
                if($highutil == 1)
                {
                        print FILE "\nTablespaces Exceeding 75 Pct Utilization:\n";
                        foreach (keys %highspaces)
                        {
                                print FILE "$_  $highspaces{$_} PCT\n";
                        }
                        %highspaces = ();
                }

                #####################################################################
                #                           Back up Information                     #
                #####################################################################
                $bkp = `db2 "list history backup all for $db " |tail -6`;
                open TEMP, ">$temp";
                print TEMP $bkp;
                close TEMP;

                print FILE "\nBackup Information:\n";
                $bkp_info = `sed -e :a -e "$d;N;2,3ba" -e "P;D" /db2/$DB2DBDFT/Messages/temp.txt`;
                if($bkp_info =~ /Start/)
                {
                        print FILE $bkp_info;
                }
                else
                {
                        print FILE "Backup Failed";
                }
                #unlink ($temp);

                print FILE "__________________________________________________________________________________________________\n";
                $rct = `db2 terminate`;
                if($rct !~ /TERMINATE command completed successfully/i)
                {
                        print FILE "Database Disconnection Error.\n";
                }
        }
        else
        {
                print FILE "Database Connection Error.\n";
        }
}

##########################################################
# Last Runtimes of Reorg's and Runstats                  #
##########################################################

print FILE "\nRuntime Information:\n";
if(-e $RUNFILE)
{
        open IN, $RUNFILE;
        while($lines=<IN>)
        {
                print FILE "Last Runstats and Reorgs under the $DB2DBDFT Instance: $lines\n";
        }
        close IN;
}
else
{
        print FILE "No Information Available For $DB2DBDFT Instance, Please check the Error log File.\n";
}
unlink($RUNFILE);

print FILE "\n\nReport was generated on $time\n\n";

close FILE;

&alert();

#========================================================================
#  Module: send_email
#  Description: DRM is sent an e-mail informing them of changes.
#========================================================================
sub alert()
{
        open IN, $file;
        read IN, $msg, 10000;
        close IN;
        `printf "$msg" | mail -s "$DB2DBDFT - Report" $EMAILNOTIFY`;
}