##################################################################
#TSM deduplication recovery script                               #
#Author: TSM Deduplication Development (Mike Sisco and Bruce Li) #
##################################################################


##################################################################
#Script Usage:                                                   #
# This script was created for help in resolving inconsistencies  #
# or corruption in the deduplication catalog. The script can     #
# be run in several analysis and cleanup modes dependent on the  #
# problem(s) being encountered.                                  #
##################################################################

#######################################################################
# V1.6.1 3/15/2016: Daniel E., Change min server level to 6.3.5.100   #
# V1.6   8/13/2015: Erick K., Fix MISSING_CHUNKS_EXTENDED             #
#                             Exclude DAMAGED_DATA from ALL           #
# V1.5   3/31/2015: Erick K., Tolerate NULL afbf.flags column         #
# V1.4   3/09/2015: Erick K., MISSING_CHUNKS SQL updates              #
# V1.3   2/26/2015: Erick K., ZERO_LENGTH_BASE_CHUNKS performance     #
# V1.2   2/23/2015: Erick K., More fixes and performance updates.     #
# V1.1   2/13/2015: Erick K., Fixes and performance updates.          #
# V1.0   9/10/2014: Howard Martin, updated to only allow the customer #
#                 to perform audit phase. No recovery phase.          #
#######################################################################

############################ START OF SCRIPT ####################################################

#!/usr/bin/perl
use 5.8.0;
use strict;
use bignum;
use Cwd;

#Common script variables
my $DEBUG = 1;
my $res = 0;
my $defLine;
my $symptomLine;
my $cleanupType;
my $index = 0;
my $myTime;
my $preview = 0;

#Variables for the db2 commands
my $db2Command;
my $db2Output;
my $db2Instance = "TSMDB1";
my $db2Alias    = "TSMDB1";
my $db2Schema   = "TSMDB1";

#Variables for the tsm commands
my $tsmCommand;
my $admin = "admin";
my $pass = "admin";
my $tsmCommandPre;

#Variables for the cleanup procedures
my @collection;
my @collectMatch;
my $cleanupItem;
my $commandItem;
my $poolId = 0;
my $poolName;
my $maxBfId = 9223372036854775807;
my $cleanupMacro = "deleteobject.mac";
my $showMacro = "showobject.mac";
my $showDamageOutput = "showdamagedoutput.out";
my $curWorkdir = getcwd;
my $tsmPath;
my $optionFile;

# Set up the defaults for the TSM CLI path and option file
if( $^O =~ /win/i )
{
  $tsmPath="\"C:\\Program Files\\Tivoli\\TSM\\baclient\"";
  $optionFile="\"C:\\Program Files\\Tivoli\\TSM\\baclient\\dsm.opt\"";
}
else
{
  $tsmPath="\"/opt/tivoli/tsm/client/ba/bin64\"";
  $optionFile="\"/opt/tivoli/tsm/client/ba/bin64/dsm.opt\"";
}


################################################################################
#Cleanup Content Section:                                                      #
#                                                                              #
# The intent of this section is to provide the                                 #
# cleanup content for a given corruption scenario                              #
#                                                                              #
# NOTE 1: Cleanup steps can be added here if not                               #
#         available for a given corruption scenario                            #
# NOTE 2: db2 is an indicator to tell the script it's                          #
#         db2 driven command.                                                  #
# NOTE 3: tsm is an indicator to tell the script it's                          #
#         a dsmadmc driven command.                                            #
################################################################################

###############################################################
# This hash list variable contains pair(s) of sub-symptom(s) and
# result(s) generated by the ProblemDetector
###############################################################
my %SQLsOutput;

###############################################################
# Symptom Correlation Section:
#
# Note 1: Each entry is a symptom and it contains all the
# analysis and cleanup that is should cover.
#
# Note 2: Any new symptom added here needs to also be
# added to the ProblemDetector section below.
###############################################################
my @ANR4895E = qw( ZERO_LENGTH_BASE_CHUNKS INVALIDATED_DEDUP_LINKS );
my @ANR1165E = qw( DAMAGED_DATA );
my @ANR1529I = qw( DAMAGED_DATA );
my @ANR1162W = qw( DAMAGED_DATA );
my @DAMAGED_DATA = qw( DAMAGED_DATA );
my @MISSING_EXTENT_ENTRIES = qw( CHUNKS_NOT_CATALOGED );
my @MISSING_SEGMENT_ENTRIES = qw(MISSING_STG_SEGMENT_FOR_CHUNK);
my @MISSING_AF_ENTRIES = qw( MISSING_STG_BITFILE_FOR_CHUNK );
my @ORPHANED_EXTENT = qw( ORPHANED_INVALIDATED_LINK );
my @MISSING_VOL_ENTRIES = qw( MISSING_VOL_FOR_CHUNK );
my @INVALIDATED_LINKS = qw( INVALIDATED_DEDUP_LINKS );
my @ZERO_LENGTH_CHUNKS = qw( ZERO_LENGTH_BASE_CHUNKS );
my @MISSING_CHUNKS = qw( BFBF_MISSING_CHUNKS );
my @MISSING_CHUNKS_EXTENDED = qw( BFBF_MISSING_CHUNKS_EXTENDED );

###############################################################
# The steps below are symptom specific cleanups. These are
# typically setup steps before going into restore/delete mode
###############################################################
my @ZERO_LENGTH_BASE_CHUNKS = (
"db2 \"update BF_BITFILE_EXTENTS set LINKBFID=$maxBfId where srvid=0 and poolid=<poolId> and linkbfid is not NULL and bfid!=$maxBfId and linkbfid in (select bfbe.bfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on ( bfbe.srvid=bfbf.srvid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid and bfbf.length>0) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.linkbfid is NULL and bfbf.srvid is NULL )\"",
);

my @INVALIDATED_DEDUP_LINKS = (
#Nothing symptom specific to do here but we reserve the right to add something!
);

my @ORPHANED_INVALIDATED_LINK = (
"db2 \"delete from bf_bitfile_extents bfbe where srvid=0 and poolid=<poolId> and linkbfid=$maxBfId and bfid not in (select bfbf.bfid from bf_aggregated_bitfiles bfbf where bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid)\""
);

my @CHUNKS_NOT_CATALOGED = (
#Nothing symptom specific to do here but we reserve the right to add something!
);

my @MISSING_STG_BITFILE_FOR_CHUNK = (
"db2 \"insert into AF_DAMAGED ( srvid, bfid, poolid, updator ) (select distinct bfbe.srvid, bfbe.superbfid, bfbe.poolid, 2 from bf_bitfile_extents bfbe inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_bitfiles afbf on ( bfbe.srvid=afbf.srvid and bfbe.superbfid=afbf.bfid and bfbe.poolid=afbf.poolid) left join af_damaged afda on (afda.srvid=bfbe.srvid and afda.bfid=bfbe.superbfid and afda.poolid=bfbe.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afbf.srvid is null and afda.srvid is null)\"",

"db2 \"update BF_BITFILE_EXTENTS set LINKBFID=$maxBfId where srvid=0 and poolid=<poolId> and linkbfid is not NULL and linkbfid in (select bfbe.bfid from bf_bitfile_extents bfbe inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_bitfiles afbf on ( bfbe.srvid=afbf.srvid and bfbe.superbfid=afbf.bfid and bfbe.poolid=afbf.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afbf.srvid is null)\"",
);

my @MISSING_STG_SEGMENT_FOR_CHUNK = (
"db2 \"insert into AF_DAMAGED ( srvid, bfid, poolid, updator ) (select distinct bfbe.srvid, bfbe.superbfid, bfbe.poolid, 2 from bf_bitfile_extents bfbe inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_segments afsg on ( bfbe.srvid=afsg.srvid and bfbe.superbfid=afsg.bfid and bfbe.poolid=afsg.poolid) left join af_damaged afda on (afda.srvid=bfbe.srvid and afda.bfid=bfbe.superbfid and afda.poolid=bfbe.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afsg.srvid is null and afda.srvid is null)\"",

"db2 \"update BF_BITFILE_EXTENTS set LINKBFID=$maxBfId where srvid=0 and poolid=<poolId> and linkbfid is not NULL and linkbfid in (select bfbe.bfid from bf_bitfile_extents bfbe inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_segments afsg on ( bfbe.srvid=afsg.srvid and bfbe.superbfid=afsg.bfid and bfbe.poolid=afsg.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afsg.srvid is null)\"",
);

my @MISSING_VOL_FOR_CHUNK = (
"db2 \"insert into AF_DAMAGED ( srvid, bfid, poolid, updator ) (select distinct bfbe.srvid, bfbe.superbfid, bfbe.poolid, 2 from bf_bitfile_extents bfbe inner join af_segments afsg on (afsg.srvid=bfbe.srvid and afsg.bfid=bfbe.superbfid and afsg.poolid=bfbe.poolid) left join as_volume_status asvs on (asvs.volid=afsg.volid) left join af_damaged afda on (afda.srvid=bfbe.srvid and afda.bfid=bfbe.superbfid and afda.poolid=bfbe.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.superbfid!=0 and afda.srvid is null and asvs.volid is null)\"",

"db2 \"update BF_BITFILE_EXTENTS set LINKBFID=$maxBfId where srvid=0 and poolid=<poolId> and linkbfid is not NULL and linkbfid in (select bfbe.bfid from bf_bitfile_extents bfbe inner join af_segments afsg on (afsg.srvid=bfbe.srvid and afsg.bfid=bfbe.superbfid and afsg.poolid=bfbe.poolid) left join as_volume_status asvs on (asvs.volid=afsg.volid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.superbfid!=0 and asvs.volid is null)\"",
);


###############################################################
# The steps below are for resolving specific corruption via a
# DELETE/RESTORE action
###############################################################

my @ZERO_LENGTH_BASE_CHUNKS_RESTORE = (
"db2 \"insert into AF_DAMAGED ( srvid, bfid, poolid, updator ) (select distinct bfbe.srvid, bfbe.superbfid, bfbe.poolid, 2 from bf_bitfile_extents bfbe inner join bf_aggregated_bitfiles bfbf on ( bfbe.srvid=bfbf.srvid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid and bfbf.length=0 and bfbf.flags in ( 0, 2 ) ) left join af_damaged afda on (afda.srvid=bfbe.srvid and afda.bfid=bfbe.superbfid and afda.poolid=bfbe.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and bfbe.linkbfid is NULL and afda.srvid is null)\"",

"tsm restore stgpool <poolName> w=y"

);

my @ZERO_LENGTH_BASE_CHUNKS_DELETE = (
"db2 \"select 'delete object ' || cast( bfbf.owner as char(24) ) || ' force=yes' from bf_bitfile_extents bfbe inner join bf_aggregated_bitfiles bfbf on ( bfbe.srvid=bfbf.srvid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid and bfbf.length=0 and bfbf.flags in ( 0, 2 ) ) left join af_bitfiles afbf on ( bfbe.srvid=afbf.srvid and bfbe.superbfid=afbf.bfid and bfbe.poolid=afbf.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and bfbe.linkbfid is NULL and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) group by bfbf.owner\" => $cleanupMacro",

"tsm -itemcommit macro $cleanupMacro",

"db2 \"delete from bf_bitfile_extents where srvid=0 and poolid=<poolId> and bfid!=$maxBfId and linkbfid is NULL and BFID in ( select bfbe.bfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on ( bfbe.srvid=bfbf.srvid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid and bfbf.length>0 ) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.linkbfid is NULL and bfbf.srvid is NULL )\""
);

my @MISSING_STG_BITFILE_FOR_CHUNK_DELETE = (
"db2 \"delete from as_segments where (volid,start,offset) in ( select afsg.volid,afsg.start,afsg.offset from af_segments afsg left join af_bitfiles afbf on (afbf.srvid=afsg.srvid and afbf.bfid=afsg.bfid and afbf.poolid=afsg.poolid) where afsg.srvid=0 and afsg.poolid=<poolId> and afbf.srvid is null )\"",

"db2 \"delete from af_segments where srvid=0 and poolid=<poolId> and bfid not in ( select bfid from af_bitfiles where srvid=0 and poolid=<poolId> )\"",

"db2 \"delete from as_volume_status where poolid=<poolId> and full!=-1 and maxocc!=0 and actocc!=0 and volid not in ( select volid from as_segments )\"",

"db2 \"delete from ss_volume_ids where poolid=<poolId> and volid not in ( select volid from as_volume_status where poolid=<poolId> )\"",

"db2 \"delete from af_vol_clusters where srvid=0 and poolid=<poolId> and volid not in (select volid from as_volume_status)\"",

"db2 \"delete from af_vol_partial_bitfiles where (volid,start,offset) in ( select afvp.volid,afvp.start,afvp.offset from af_vol_partial_bitfiles afvp inner join as_volume_status asvs on ( asvs.volid=afvp.volid and asvs.poolid=<poolId> ) left join af_segments afsg on ( afsg.volid=afvp.volid and afsg.start=afvp.start and afsg.offset=afvp.offset ) where afsg.volid is null )\"",

"db2 \"delete from af_vol_partial_bitfiles where srvid=0 and poolid=<poolId> and volid not in (select volid from as_volume_status)\"",

"db2 \"delete from bf_aggregate_attributes where srvid=0 and superbfid in (select bfbe.superbfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_bitfiles afbf on ( bfbe.srvid=afbf.srvid and bfbe.superbfid=afbf.bfid and bfbe.poolid=afbf.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afbf.srvid is null and bfbf.srvid is not null)\"",

"db2 \"delete from bf_queued_chunks where bfid in (select bfbe.bfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_bitfiles afbf on ( bfbe.srvid=afbf.srvid and bfbe.superbfid=afbf.bfid and bfbe.poolid=afbf.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afbf.srvid is null and bfbf.srvid is not null)\"",

"db2 \"delete from bf_dereferenced_chunks where srvid=0 and bfid in (select bfbe.bfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_bitfiles afbf on ( bfbe.srvid=afbf.srvid and bfbe.superbfid=afbf.bfid and bfbe.poolid=afbf.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afbf.srvid is null and bfbf.srvid is not null)\"",

"db2 \"delete from bf_bitfile_extents where srvid=0 and poolid=<poolId> and bfid in (select bfbe.bfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_bitfiles afbf on ( bfbe.srvid=afbf.srvid and bfbe.superbfid=afbf.bfid and bfbe.poolid=afbf.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afbf.srvid is null and bfbf.srvid is not null)\"",

"db2 \"delete from bf_aggregated_bitfiles where srvid=0 and superbfid in (select bfbf.superbfid from bf_aggregated_bitfiles bfbf left join bf_aggregate_attributes bfaa on (bfbf.srvid=bfaa.srvid and bfbf.superbfid=bfaa.superbfid ) where bfbf.srvid=0 and bfaa.srvid is null)\""
);

my @MISSING_STG_SEGMENT_FOR_CHUNK_DELETE = (
"db2 \"delete from as_segments where (volid,start,offset) in ( select assg.volid,assg.start,assg.offset from as_segments assg inner join as_volume_status asvs on ( asvs.volid=assg.volid and asvs.poolid=<poolId> ) left join af_segments afsg on ( afsg.volid=assg.volid and afsg.start=assg.start and afsg.offset=assg.offset ) where afsg.volid is null )\"",

"db2 \"delete from af_bitfiles where srvid=0 and poolid=<poolId> and bfid not in ( select bfid from af_segments where srvid=0 and poolid=<poolId> )\"",

"db2 \"delete from as_volume_status where poolid=<poolId> and full!=-1 and maxocc!=0 and actocc!=0 and volid not in ( select volid from as_segments )\"",

"db2 \"delete from ss_volume_ids where poolid=<poolId> and volid not in ( select volid from as_volume_status where poolid=<poolId> )\"",

"db2 \"delete from af_vol_clusters where srvid=0 and poolid=<poolId> and volid not in (select volid from as_volume_status)\"",

"db2 \"delete from af_vol_partial_bitfiles where (volid,start,offset) in ( select afvp.volid,afvp.start,afvp.offset from af_vol_partial_bitfiles afvp inner join as_volume_status asvs on ( asvs.volid=afvp.volid and asvs.poolid=<poolId> ) left join af_segments afsg on ( afsg.volid=afvp.volid and afsg.start=afvp.start and afsg.offset=afvp.offset ) where afsg.volid is null )\"",

"db2 \"delete from af_vol_partial_bitfiles where srvid=0 and poolid=<poolId> and volid not in (select volid from as_volume_status)\"",

"db2 \"delete from bf_aggregate_attributes where srvid=0 and superbfid in (select bfbe.superbfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_segments afsg on ( bfbe.srvid=afsg.srvid and bfbe.superbfid=afsg.bfid and bfbe.poolid=afsg.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afsg.srvid is null and bfbf.srvid is not null)\"",

"db2 \"delete from bf_queued_chunks where bfid in (select bfbe.bfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_segments afsg on ( bfbe.srvid=afsg.srvid and bfbe.superbfid=afsg.bfid and bfbe.poolid=afsg.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afsg.srvid is null and bfbf.srvid is not null)\"",

"db2 \"delete from bf_dereferenced_chunks where srvid=0 and bfid in (select bfbe.bfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_segments afsg on ( bfbe.srvid=afsg.srvid and bfbe.superbfid=afsg.bfid and bfbe.poolid=afsg.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afsg.srvid is null and bfbf.srvid is not null)\"",

"db2 \"delete from bf_bitfile_extents where srvid=0 and poolid=<poolId> and bfid in (select bfbe.bfid from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) left join af_segments afsg on ( bfbe.srvid=afsg.srvid and bfbe.superbfid=afsg.bfid and bfbe.poolid=afsg.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and afsg.srvid is null and bfbf.srvid is not null)\"",

"db2 \"delete from bf_aggregated_bitfiles where srvid=0 and superbfid in (select bfbf.superbfid from bf_aggregated_bitfiles bfbf left join bf_aggregate_attributes bfaa on (bfbf.srvid=bfaa.srvid and bfbf.superbfid=bfaa.superbfid ) where bfbf.srvid=0 and bfaa.srvid is null)\""
);

my @MISSING_VOL_FOR_CHUNK_DELETE = (
"db2 \"delete from BF_BITFILE_EXTENTS where srvid=0 and poolid=<poolId> and linkbfid is NULL and bfid in (select bfbe.bfid from bf_bitfile_extents bfbe inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid and bfbf.in ( 1, 3 ) ) inner join af_bitfiles afbf on (afbf.srvid=bfbf.srvid and afbf.poolid=bfbe.poolid and afbf.bfid=bfbf.superbfid and afbf.flags in (1,3,5,7,9,11,13) ) inner join af_segments afsg on (afsg.srvid=bfbe.srvid and afsg.bfid=bfbe.superbfid and afsg.poolid=bfbe.poolid) left join as_volume_status asvs on (asvs.volid=afsg.volid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.bfid!=$maxBfId and asvs.volid is null)\""
);

my @CHUNKS_NOT_CATALOGED_RESTORE = (

"db2 \"insert into AF_DAMAGED ( srvid, bfid, poolid, updator ) ( select distinct afbf.srvid, afbf.bfid, afbf.poolid, 2 from af_bitfiles afbf inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=afbf.srvid and bfbf.superbfid=afbf.bfid and bfbf.flags in ( 0, 2 ) and bfbf.bfid!=bfbf.owner ) left join bf_bitfile_extents bfbe on ( bfbe.srvid=bfbf.srvid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid ) LEFT JOIN af_damaged AS afda ON ( afbf.srvid=afda.srvid AND afbf.poolid=afda.poolid AND afbf.bfid=afda.bfid ) where afbf.srvid=0 and afbf.poolid=<poolId> and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) and bfbf.srvid=0 and bfbf.length=0 and bfbe.srvid is null AND afda.poolid IS NULL )\"",

"tsm restore stgpool <poolName> w=y"

);

my @CHUNKS_NOT_CATALOGED_DELETE = (

"db2 \"select 'delete object ' || cast( bfbf.owner as char(24) ) || ' force=yes' from af_bitfiles afbf inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=afbf.srvid and bfbf.superbfid=afbf.bfid and bfbf.flags in ( 0, 2 ) and bfbf.bfid!=bfbf.owner ) left join bf_bitfile_extents bfbe on ( bfbe.srvid=bfbf.srvid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid ) where afbf.srvid=0 and afbf.poolid=<poolId> and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) and bfbf.srvid=0 and bfbf.length=0 and bfbe.srvid is null group by bfbf.owner\" => $cleanupMacro",

"tsm -itemcommit macro $cleanupMacro",

"tsm show dedupdeleteinfo",

"db2 \"insert into AF_DAMAGED ( srvid, bfid, poolid, updator ) ( select distinct afbf.srvid, afbf.bfid, afbf.poolid, 2 from af_bitfiles afbf inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=afbf.srvid and bfbf.superbfid=afbf.bfid and bfbf.flags in ( 0, 2 ) and bfbf.bfid!=bfbf.owner ) left join bf_bitfile_extents bfbe on ( bfbe.srvid=bfbf.srvid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid ) LEFT JOIN af_damaged AS afda ON ( afbf.srvid=afda.srvid AND afbf.poolid=afda.poolid AND afbf.bfid=afda.bfid ) where afbf.srvid=0 and afbf.poolid=<poolId> and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) and bfbf.srvid=0 and bfbf.length=0 and bfbe.srvid is null AND afda.poolid IS NULL )\"",
);

my @BFBF_MISSING_CHUNKS_DELETE = (

"db2 \"select 'delete object ' || cast( bfbf.bfid as char(24) ) || ' force=yes' from af_bitfiles afbf inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=afbf.srvid and bfbf.superbfid=afbf.bfid and bfbf.flags in ( 0, 2 ) and bfbf.bfid=bfbf.owner ) left join bf_aggregated_bitfiles bfbf2 on ( bfbf.srvid=bfbf2.srvid and bfbf.owner!=bfbf2.bfid and bfbf.owner=bfbf2.owner and bfbf.superbfid=bfbf2.superbfid and bfbf2.flags in ( 0, 2 ) ) where afbf.srvid=0 and afbf.poolid=<poolId> and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) and bfbf2.srvid is null for read only\" => $cleanupMacro",

"tsm -itemcommit macro $cleanupMacro",

"tsm show dedupdeleteinfo",

);

my @BFBF_MISSING_CHUNKS_EXTENDED_DELETE = (

"db2 \"select 'delete object ' || cast( bfbf.owner as char(24) ) || ' force=yes' from af_bitfiles afbf inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=afbf.srvid and bfbf.superbfid=afbf.bfid and bfbf.flags in ( 0, 2 ) ) left join bf_bitfile_extents bfbe on ( bfbe.srvid=bfbf.srvid and bfbe.poolid=afbf.poolid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid and bfbf.bfid!=bfbf.owner ) where afbf.srvid=0 and afbf.poolid=<poolId> and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) and ( bfbe.srvid is not null or bfbf.bfid=bfbf.owner ) group by bfbf.owner having max( bfbf.digest_type )<=2 and sum( case when bfbf.bfid=bfbf.owner then bfbf.origlength-bfbf.length else 0 end ) > sum( cast( case when bfbf.bfid=bfbf.owner then 0 else bfbe.size end as bigint ) ) and count(*)>1 for read only\" => $cleanupMacro",

"tsm -itemcommit macro $cleanupMacro",

"tsm show dedupdeleteinfo",

);

my @RESTORE_DAMAGED_DATA = (

"tsm restore stgpool <poolName> w=y",

);

my @DELETE_DAMAGED_DATA = (
"db2 \"select 'delete object ' || cast( bfbf.bfid as char(24) ) || ' force=yes' from af_damaged afda inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=afda.srvid and bfbf.superbfid=afda.bfid and (bfbf.bfid=bfbf.owner or bfbf.owner is NULL) ) left join af_bitfiles afbf on ( afda.srvid=afbf.srvid and afda.bfid=afbf.bfid and afda.poolid=afbf.poolid ) where afda.srvid=0 and afda.poolid=<poolId> and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) group by bfbf.bfid\" => $cleanupMacro",

"tsm -itemcommit macro $cleanupMacro",

"tsm show dedupdeleteinfo",

);

my @SHOW_DAMAGED_DATA = (

"db2 \"select 'show bfo ' || bfid from af_damaged where poolid=<poolId>\" => $showMacro",

"tsm -itemcommit macro $showMacro => $showDamageOutput"
);

my @RESTORE_INVALID_CHUNKS = (

"db2 \"delete from AF_DAMAGED where srvid=0 and poolid=<poolId>\"",

#NOTE: This does not restore actual data...only attempts to re-link chunks
"tsm restore stgpool <poolName> w=y",

"db2 \"insert into AF_DAMAGED ( srvid, bfid, poolid, updator ) (select distinct 0, superbfid, poolid, 2 from bf_bitfile_extents where srvid=0 and poolid=<poolId> and linkbfid=$maxBfId and bfid!=$maxBfId )\"",

#NOTE: This WILL attempt to restore data since we added entries to damaged table above
"tsm restore stgpool <poolName> w=y"
);

my @DELETE_INVALID_CHUNKS = (
"db2 \"delete from AF_DAMAGED where srvid=0 and poolid=<poolId>\"",

#NOTE: This does not restore actual data...only attempts to re-link chunks
"tsm restore stgpool <poolName> w=y",

"db2 \"select 'delete object ' || cast( bfbf.owner as char(24) ) || ' force=yes' from bf_bitfile_extents bfbe inner join bf_aggregated_bitfiles bfbf on ( bfbe.srvid=bfbf.srvid and  bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid ) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.linkbfid=$maxBfId group by bfbf.owner\" => $cleanupMacro",

"tsm -itemcommit macro $cleanupMacro",

"tsm show dedupdeleteinfo",

"db2 \"insert into AF_DAMAGED ( srvid, bfid, poolid, updator ) (select distinct 0, superbfid, poolid, 2 from bf_bitfile_extents where srvid=0 and poolid=<poolId> and linkbfid=$maxBfId and bfid!=$maxBfId )\""
);

###############################################################
# Analysis Detection Section:
#
# This hash list contains pairs of (sub-symptom
# => SQLs to detect )
#
# Note 1: Any new detection sql statement should be added here.
#
# Note 2: The new statement should also be added to the symptom
#         correlation section if it can be mapped as such
###############################################################
my %SQLstoDetector =
(
'DAMAGED_DATA' => "\"select count(*) from af_damaged where srvid=0 and poolid=<poolId> for read only with ur\"",
'ZERO_LENGTH_BASE_CHUNKS' => "\"select count(*) from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on ( bfbe.srvid=bfbf.srvid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid and bfbf.length>0 ) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.linkbfid is NULL and bfbe.bfid!=$maxBfId and bfbf.srvid is NULL for read only with ur\"",
'CHUNKS_NOT_CATALOGED' => "\"select count(*) from af_bitfiles afbf inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=afbf.srvid and bfbf.superbfid=afbf.bfid and bfbf.flags in (0,2) and bfbf.bfid!=bfbf.owner ) left join bf_bitfile_extents bfbe on ( bfbe.srvid=bfbf.srvid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid ) where afbf.srvid=0 and afbf.poolid=<poolId> and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) and bfbf.srvid=0 and bfbf.length=0 and bfbe.srvid is null for read only with ur\"",
'INVALIDATED_DEDUP_LINKS' => "\"select count(*) from bf_bitfile_extents bfbe inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.linkbfid=$maxBfId for read only with ur\"",
'MISSING_STG_BITFILE_FOR_CHUNK' => "\"select count(*) from bf_bitfile_extents bfbe left join af_bitfiles afbf on ( bfbe.srvid=afbf.srvid and bfbe.superbfid=afbf.bfid and bfbe.poolid=afbf.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.superbfid!=0 and afbf.srvid is null for read only with ur\"",
'MISSING_STG_SEGMENT_FOR_CHUNK' => "\"select count(*) from bf_bitfile_extents bfbe left join af_segments afsg on ( bfbe.srvid=afsg.srvid and bfbe.superbfid=afsg.bfid and bfbe.poolid=afsg.poolid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.superbfid!=0 and afsg.srvid is null for read only with ur\"",
'MISSING_VOL_FOR_CHUNK' => "\"select count(*) from bf_bitfile_extents bfbe inner join af_segments afsg on (afsg.srvid=bfbe.srvid and afsg.bfid=bfbe.superbfid and afsg.poolid=bfbe.poolid) left join as_volume_status asvs on (asvs.volid=afsg.volid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.superbfid!=0 and asvs.volid is null for read only with ur\"",
'ORPHANED_INVALIDATED_LINK' => "\"select count(*) from bf_bitfile_extents bfbe left join bf_aggregated_bitfiles bfbf on (bfbf.srvid=bfbe.srvid and bfbf.bfid=bfbe.bfid and bfbf.superbfid=bfbe.superbfid) where bfbe.srvid=0 and bfbe.poolid=<poolId> and bfbe.linkbfid=$maxBfId and bfbf.srvid is null for read only with ur\"",
'BFBF_MISSING_CHUNKS' => "\"select count(*) from af_bitfiles afbf inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=afbf.srvid and bfbf.superbfid=afbf.bfid and bfbf.flags in ( 0, 2 ) and bfbf.bfid=bfbf.owner ) left join bf_aggregated_bitfiles bfbf2 on ( bfbf.srvid=bfbf2.srvid and bfbf.owner!=bfbf2.bfid and bfbf.owner=bfbf2.owner and bfbf.superbfid=bfbf2.superbfid and bfbf2.flags in ( 0, 2 ) ) where afbf.srvid=0 and afbf.poolid=<poolId> and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) and bfbf2.srvid is null for read only with ur\"",
'BFBF_MISSING_CHUNKS_EXTENDED' => "\"select count(*) from ( select 1 from af_bitfiles afbf inner join bf_aggregated_bitfiles bfbf on (bfbf.srvid=afbf.srvid and bfbf.superbfid=afbf.bfid and bfbf.flags in ( 0, 2 ) ) left join bf_bitfile_extents bfbe on ( bfbe.srvid=bfbf.srvid and bfbe.poolid=afbf.poolid and bfbe.bfid=bfbf.bfid and bfbe.superbfid=bfbf.superbfid and bfbf.bfid!=bfbf.owner ) where afbf.srvid=0 and afbf.poolid=<poolId> and ( afbf.flags is null or afbf.flags in (0,2,4,6,8,10,12,14) ) and ( bfbe.srvid is not null or bfbf.bfid=bfbf.owner ) group by bfbf.owner having max( bfbf.digest_type )<=2 and sum( case when bfbf.bfid=bfbf.owner then bfbf.origlength-bfbf.length else 0 end ) > sum( cast( case when bfbf.bfid=bfbf.owner then 0 else bfbe.size end as bigint ) ) and count(*)>1 ) for read only with ur\"",
);




#################################################################################
#                     MAIN PROCESSING SECTION                                   #
# The main processor section is responsible for setting up the environment      #
# (TSM/DB2), establishing the audit mode, running the analysis mode and then    #
# either developing a REPORT or executing the requested cleanup.                #
#                                                                               #
#################################################################################

# First things first, make sure the environment is ready for the audit
print "***********************************************************************\n";
print "Welcome to dedupAuditTool with preview bug fixed!\n";
print "The runtime perl version: $] \n\n";
print "Attention!! \n";
print "\n";
print "Before proceeding with the cleanup procedure, make sure the\n";
print "following statements are true:\n";
print "\n";
print "- A current FULL database backup is available!\n";
print "\n";
print "- All data movement and deletion activity in the deduplicated\n";
print "  pool has been quiesced!\n";
print "  This would include the following processes:\n";
print "  Expiration\n";
print "  Reclamation\n";
print "  Migration\n";
print "  Move Data\n";
print "  Move NodeData\n";
print "  Backup/Archive\n";
print "\n";
print "- The Tivoli Storage Manager server is at 6.3.5.100 or higher!\n";
print "************************************************************************\n";
sleep( 5 );
print "Continue with the deduplication audit? (Y/N): <def: N>:";
chomp( $defLine = <STDIN> );
if( $defLine =~ /^Y$/i )
{
}
else
{
  exit 0;
}
print "\n";

print "\n";
print "***********************************************************\n";
print "Phase 1: Environment Setup\n";
print "***********************************************************\n";

###############################################################
#Step 1: Make sure that a connection can be made to DB2/TSM
###############################################################
if( ($res = &DB2Connect) < 0 )
{
  print "db2 connection failed, exiting audit utility.\n";
  exit -1;
}

if( ($res = &TSMConnect) < 0 )
{
  print "tsm connection failed, exiting audit utility.\n";
  exit -1;
}


###############################################################
#Step 2: Make sure that the poolid/poolname can be found in DB2
###############################################################
$res = &GetDedupPool;
if( $res < 0 )
{
  print "Could not successfully locate deduplicated storage pools.\n";
}

print( "Input the poolid or poolname to process <def: deduppool>: " );
chomp( $defLine = <STDIN> );
if( $defLine =~ /\S/ ) {;}
else {$defLine="deduppool";}

$res = &ConvertPool($defLine);
if( $res == -100000 )
{
  print "convert pool failed( res $res), exiting audit utility.\n";
  exit -1;
}
else
{
  print "Conversion between poolid($poolId) AND poolname($poolName) succeeded\n";
}

###############################################################
#Step 3: Determine which audit mode is required
###############################################################
print "\n";
print "***********************************************************\n";
print "Phase 2: Dedup Audit Tool Setup\n";
print "***********************************************************\n";
print "\n";
print "Auditing with script";
{
  print "\n";
  print( "Would you like to see all available symptoms that can be audited? <def: Y>:");
  chomp( $defLine = <STDIN> );
  if( $defLine =~ /^N$/i )
  {
  }
  else
  {
    print "\n";
    print "*********AUDIT SYMPTOMS*********\n";
    print ("ANR1165E\n");
    print ("ANR1529I\n");
    print ("ANR1162W\n");
    print ("DAMAGED_DATA\n");
    print ("ALL (INCLUDES EVERYTHING BELOW):\n");
    print ("ANR4895E\n");
    print ("MISSING_EXTENT_ENTRIES\n");
    print ("MISSING_SEGMENT_ENTRIES\n");
    print ("MISSING_AF_ENTRIES\n");
    print ("ORPHANED_EXTENT\n");
    print ("MISSING_VOL_ENTRIES\n");
    print ("INVALIDATED_LINKS\n");
    print ("ZERO_LENGTH_CHUNKS\n");
    print ("MISSING_CHUNKS\n");
    print ("MISSING_CHUNKS_EXTENDED\n");
    print "*********AUDIT SYMPTOMS*********\n";
    print "\n";
  }
  print( "Input the symptom, or error message, you want to check <def: ANR4895E>: " );
  chomp( $symptomLine = <STDIN> );
  if( $symptomLine =~ /\S+/ )
  {
    $symptomLine = $symptomLine;
  }
  else
  {
    $symptomLine = "ANR4895E";
  }


  # Before going into the audit, let's see if there are background deletions active
  print "\n";
  print "Checking to see if there are active dedup deletions occurring.\n";
  print "If there are, the audit will wait until the deletions are finished.\n";
  print "\n";
  $res = &CommandProcess( "tsm show dedupdeleteinfo", "0" );
  if( $res < 0 )
  {
    print "CommandProcess failed, exit\n";
    exit -1;
  }
  $res = &ProblemDetector( $symptomLine );
  if( $res < 0 )
  {
    print "The DB2 SQL used by the analyzer has failed!";
    exit -1;
  }
}

if( keys %SQLsOutput == 0 )
{
  print "\n";
  print "No problems were detected by the dedup audit tool for $poolName!\n";
  exit 0;
}


###############################################################
#Step 4: Generating Audit File.
###############################################################

#This sort function is used to ensure that the DAMAGED_DATA is processed first.
@collection = sort {length $a <=> length $b} keys %SQLsOutput;

print "\n";
print "***********************************************************\n";
print "Phase 4: Problem Found: Generating Audit File\n";
print "***********************************************************\n";
print "\n";


&ReportGenerator;
print "\n";
print "***********************************************************\n";
print "***********  IMPORTANT ************************************\n";

print " Contact IBM service and provide the report file. If\n";

print " recovery is required, another script will be provided to\n";


print " repair the dedup storage pool.\n";
print "***********************************************************\n";
print "***********************************************************\n";
exit 0;

###############################################################
#Step 5: Time to do some cleanup for the given storage pool.
###############################################################

CLEANUP: foreach $cleanupItem ( @collection )
{
  print "\n";
  print "Perform $cleanupItem recovery procedure? (Y/N):< def: Y> ";
  chomp( $defLine = <STDIN> );
  if( $defLine =~ /^N$/i )
  {
    print "Skipping the recovery for $cleanupItem\n";
    print "\n";
    next;
  }
  else
  {
    print "\n";
    print "***********************************************************\n";
    print "Starting recovery procedure for $cleanupItem\n";
    print "***********************************************************\n";
    print "\n";
  }

  $index = 0;
  print "Perform restore/delete[B], restore[R], delete[D], preview[P] operation <def: R>: ";
  chomp( $cleanupType = <STDIN> );

  if( $cleanupType =~ /\S+/ )
  {

  }
  else
  {
    $cleanupType = "R";
  }

  #If preview mode is selected, we will show all potential steps
  if ($cleanupType =~ /^P$/i)
  {
    $preview = 1;
  }
  else
  {
    $preview = 0;
  }

  print "The final preview is $preview\n" if( $DEBUG );

  # Override the cleanup type here if in preview mode
  $cleanupType = "B" if( $preview == 1 );

  if ( $preview == 1 )
  {
    print "\n";
    print "Would you like to also generate an audit report while previewing? <def: Y>: ";
    chomp( $defLine = <STDIN> );

    if( $defLine =~ /^N$/i )
    {
    }
    else
    {
      &ReportGenerator;
    }
    print "\n";
  }


  ###############################################################
  #Prepare the show output header. This will include cleanup
  #execution time and the actual show output, if any. This only
  #occurs when we are not in preview mode.
  ###############################################################
  if ( $preview == 0 )
  {
    chdir $curWorkdir || die "Can't change to directory $curWorkdir $!";
    open (my $REPORT_FH,'+>>', $showDamageOutput ) || die "Open file $showDamageOutput error $!";
    $myTime = localtime();
    print $REPORT_FH "*****************************************************\n";
    print $REPORT_FH "TIMESTAMP OF THE CLEANUP EXECUTION: $myTime          \n";
    print $REPORT_FH "                                                     \n";
    print $REPORT_FH "SHOW DAMAGED OUTPUT OF $cleanupItem                  \n";
    print $REPORT_FH "*****************************************************\n";
    close( $REPORT_FH );
  }

  $res = 0;

  print "\n";
  print "Performing DB setup required for recovery operation of the $cleanupItem.\n";

  ######################################################
  #Each symptom and recovery action must be available
  #in this section.
  #####################################################
  if( $cleanupItem =~ /ZERO_LENGTH_BASE_CHUNKS/ )
  {
    foreach( @ZERO_LENGTH_BASE_CHUNKS )
    {
      $res = &CommandProcess( $_, $preview );
      if( $res < 0 )
      {
        print "CommandProcess failed, exit\n";
        exit -1;
      }
    }

    if ( $cleanupType =~ /^B$/i || $cleanupType =~ /^R$/i )
    {
      foreach( @ZERO_LENGTH_BASE_CHUNKS_RESTORE )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }

    if ( $res == 0 && ( $cleanupType =~ /^B$/i || $cleanupType =~ /^D$/i ) )
    {
      foreach( @ZERO_LENGTH_BASE_CHUNKS_DELETE )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }
  }

  #Nothing symptom specific to do here...just execute cleanup below
  elsif( $cleanupItem =~ /INVALIDATED_DEDUP_LINKS/ )
  {
    $res = 0;
  }

  elsif( $cleanupItem =~ /DAMAGED_DATA/ )
  {
    if ( $cleanupType =~ /^B$/i || $cleanupType =~ /^R$/i )
    {
      foreach( @RESTORE_DAMAGED_DATA )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }
    else
    {
      $res = 0;
    }

    if ( $res == 0 && ( $cleanupType =~ /^B$/i || $cleanupType =~ /^D$/i ) )
    {
      foreach( @DELETE_DAMAGED_DATA )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }

    $res = -1;
  }

  elsif ( $cleanupItem =~ /ORPHANED_INVALIDATED_LINK/ )
  {
    foreach( @ORPHANED_INVALIDATED_LINK )
    {
      $res = &CommandProcess( $_, $preview );
      if( $res < 0 )
      {
        print "CommandProcess failed, exit\n";
        exit -1;
      }
    }

    # Set res to non-zero since we want to skip cleanup steps #
    $res = -1;
    print "\n";
    print "Extraneous dedup chunks have been successfully cleaned.\n"
  }

  elsif( $cleanupItem =~ /CHUNKS_NOT_CATALOGED/ )
  {
    if ( $cleanupType =~ /^B$/i || $cleanupType =~ /^R$/i )
    {
      foreach( @CHUNKS_NOT_CATALOGED_RESTORE )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }
    else
    {
      $res = 0;
    }

    if ( $res == 0 && ( $cleanupType =~ /^B$/i || $cleanupType =~ /^D$/i ) )
    {
      foreach( @CHUNKS_NOT_CATALOGED_DELETE )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }

    $res = -1;
  }

  elsif( $cleanupItem =~ /MISSING_STG_BITFILE_FOR_CHUNK/ )
  {
    if ( $cleanupType =~ /^D$/i || $cleanupType =~ /^B$/i)
    {
      foreach( @MISSING_STG_BITFILE_FOR_CHUNK )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }

      foreach( @MISSING_STG_BITFILE_FOR_CHUNK_DELETE )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }

      foreach( @DELETE_DAMAGED_DATA )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }
    else
    {
      print "This condition cannot be resolved by restoring from a copy pool.\n";
      print "Please re-run the cleanup in D (Deletion) mode to resolve this issue";
      print "\n";
    }
  }

  elsif( $cleanupItem =~ /MISSING_STG_SEGMENT_FOR_CHUNK/ )
  {
    if ( $cleanupType =~ /^D$/i || $cleanupType =~ /^B$/i)
    {
      foreach( @MISSING_STG_SEGMENT_FOR_CHUNK )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }

      foreach( @MISSING_STG_SEGMENT_FOR_CHUNK_DELETE )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }

      foreach( @DELETE_DAMAGED_DATA )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }
    else
    {
      print "This condition cannot be resolved by restoring from a copy pool.\n";
      print "Please re-run the cleanup in D (Deletion) mode to resolve this issue";
      print "\n";
    }
  }
  elsif( $cleanupItem =~ /BFBF_MISSING_CHUNKS_EXTENDED/ )
  {
    if ( $cleanupType =~ /^D$/i || $cleanupType =~ /^B$/i)
    {
      foreach( @BFBF_MISSING_CHUNKS_EXTENDED_DELETE )
      {
        $res = &CommandProcess( $_, $preview );
        if ( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }
    else
    {
      print "This condition cannot be resolved by restoring from a copy pool.\n";
      print "Please re-run the cleanup in D (Deletion) mode to resolve this issue";
      print "\n";
    }
  }

  elsif( $cleanupItem =~ /BFBF_MISSING_CHUNKS/ )
  {
    if ( $cleanupType =~ /^D$/i || $cleanupType =~ /^B$/i)
    {
      foreach( @BFBF_MISSING_CHUNKS_DELETE )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }
    else
    {
      print "This condition cannot be resolved by restoring from a copy pool.\n";
      print "Please re-run the cleanup in D (Deletion) mode to resolve this issue";
      print "\n";
    }
  }

  elsif( $cleanupItem =~ /MISSING_VOL_FOR_CHUNK/ )
  {
    if ( $cleanupType =~ /^D$/i || $cleanupType =~ /^B$/i)
    {
      foreach( @MISSING_VOL_FOR_CHUNK )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }

      foreach( @MISSING_VOL_FOR_CHUNK_DELETE )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }

      foreach( @DELETE_DAMAGED_DATA )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }
    else
    {
      print "This condition cannot be resolved by restoring from a copy pool.\n";
      print "Please re-run the cleanup in D (Deletion) mode to resolve this issue";
      print "\n";
    }
  }

  else
  {
    print "$cleanupItem is not available for recovery action at this time";
  }


  ######################################################################
  #Before wiping out the existing damaged entries, generate SHOW BFO
  # for all currently marked damage (so we don't lose track when dealing
  # with multiple issues.
  ######################################################################
  if ( $preview == 0 )
  {
    foreach( @SHOW_DAMAGED_DATA )
    {
      if( &CommandProcess( $_, $preview ) < 0 )
      {
        print "CommandProcess failed, exit\n";
        exit -1;
      }
    }

    $res = &CommandProcess( "db2 \"delete from AF_DAMAGED where srvid=0 and poolid=<poolId>\"", $preview );
    if( $res < 0 )
    {
      print "CommandProcess failed, exit\n";
      exit -1;
    }

    print "\n";
    print "Check the $showDamageOutput file for any remaining damaged files.\n";
    print "\n";
  }

  ######################################################################
  # This is the final cleanup section dependent on action(s) performed #
  # above and what the user has requested. The script will either DEL  #
  # or RESTORE ( or BOTH ) on invalid dedup data at this point.        #
  ######################################################################
  if ( $res == 0 )
  {
    print "\n";
    print "\n";
    print "Performing the recovery operation for $cleanupItem corruption\n";
    print "\n";
    if ( $cleanupType =~ /^B$/i )
    {
      foreach( @RESTORE_INVALID_CHUNKS )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }

      foreach( @DELETE_INVALID_CHUNKS )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }

    }
    elsif ( $cleanupType =~ /^D$/i )
    {
      foreach( @DELETE_INVALID_CHUNKS )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }

    }
    else
    {
      foreach( @RESTORE_INVALID_CHUNKS )
      {
        $res = &CommandProcess( $_, $preview );
        if( $res < 0 )
        {
          print "CommandProcess failed, exit\n";
          exit -1;
        }
      }
    }

    foreach( @SHOW_DAMAGED_DATA )
    {
      $res = &CommandProcess( $_, $preview );
      if( $res < 0 )
      {
        print "CommandProcess failed, exit\n";
        exit -1;
      }
    }

    if ( $res == 0 && ( $preview == 0 ) )
    {
      print "\n";
      print "Check current entries in $showDamageOutput for any remaining invalid objects.\n";
      print "\n";
    }
  }
}

#################################################################################
# Below are the PRIMARY sub routines used by the main                           #
# processing section to get the environment setup                               #
#################################################################################

###############################################################
# Problem Detection Section (Analysis)
# Function: The problem detection section will run the
#           requested sql statements to determine if the database
#           has any corruption in the deduplication layer. The result
#           of this analysis phase will be stored in the %SQLsOutput
#           variable for further cleanup phase action.
#
# Input:
#   <Symptom: default ALL>
# Note1: If a new symptom is added, it must be added as an ELSIF to
#        the block below.
# Output:
#
#       Success: 0
#       Error: -1 -2
###############################################################

sub ProblemDetector
{
  my $key;
  my $value;
  my $index = 0;
  my @hashList;

  if( @_ != 1 )
  {
    print "ProblemDetector function should be invoked as: ProblemDetector <symptom>\n";
    return -1;
  }

  print "\n";
  print "***********************************************************\n";
  print "Phase 3: Auditing Deduplicated Storage Pool $poolName\n";
  print "***********************************************************\n";

  if( $_[0] =~ /^all$/i )
  {
    print "The dedup audit tool is running in ALL mode";
    print "\n";

    $index = 0;
    print "\n";
    while( ($key, $value) = each %SQLstoDetector )
    {
      if ( $key =~ /^DAMAGED_DATA$/ )
      {
        next;
      }
      
      $index ++;
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;

      $db2Output = &DB2Command( $db2Command );

      #test
      #$db2Output = 1;

      if( $db2Output < 0 )
      {
    return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^ANR4895E$/i )
  {
    print "The dedup audit tool is running in ANR4895E mode";
    print "\n";
    $index = 0;
    foreach $key (@ANR4895E)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^ANR1165E$/i )
  {
    print "The dedup audit tool is running in ANR1165E mode";
    print "\n";
    $index = 0;
    foreach $key (@ANR1165E)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^ANR1529I$/i )
  {
    print "The dedup audit tool is running in ANR1529I mode";
    print "\n";
    $index = 0;
    foreach $key (@ANR1529I)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^ANR1162W$/i )
  {
    print "The dedup audit tool is running in ANR1162W mode";
    print "\n";
    $index = 0;
    foreach $key (@ANR1162W)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^DAMAGED_DATA$/i )
  {
    print "The dedup audit tool is running in DAMAGED_DATA mode";
    print "\n";
    $index = 0;
    foreach $key (@DAMAGED_DATA)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^MISSING_EXTENT_ENTRIES$/i )
  {
    print "The dedup audit tool is running in MISSING_EXTENT_ENTRIES mode";
    print "\n";
    $index = 0;
    foreach $key (@MISSING_EXTENT_ENTRIES)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^MISSING_AF_ENTRIES$/i )
  {
    print "The dedup audit tool is running in MISSING_AF_ENTRIES mode";
    print "\n";
    $index = 0;
    foreach $key (@MISSING_AF_ENTRIES)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^MISSING_SEGMENT_ENTRIES$/i )
  {
    print "The dedup audit tool is running in MISSING_SEGMENT_ENTRIES mode";
    print "\n";
    $index = 0;
    foreach $key (@MISSING_SEGMENT_ENTRIES)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^ORPHANED_EXTENT$/i )
  {
    print "The audit tool is running in ORPHANED_EXTENT mode";
    print "\n";
    $index = 0;
    foreach $key (@ORPHANED_EXTENT)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "\n";
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
        if( $db2Output > 0 )
        {
          $SQLsOutput{$key} = $db2Output;
          print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
        }
        else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^MISSING_VOL_ENTRIES$/i )
  {
    print "The audit tool is running in MISSING_VOL_ENTRIES mode";
    print "\n";
    $index = 0;
    foreach $key (@MISSING_VOL_ENTRIES)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "\n";
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^INVALIDATED_LINKS$/i )
  {
    print "The audit tool is running in INVALIDATED_LINKS mode";
    print "\n";
    $index = 0;
    foreach $key (@INVALIDATED_LINKS)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "\n";
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^ZERO_LENGTH_CHUNKS$/i )
  {
    print "The audit tool is running in ZERO_LENGTH_CHUNKS mode";
    print "\n";
    $index = 0;
    foreach $key (@ZERO_LENGTH_CHUNKS)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "\n";
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^MISSING_CHUNKS$/i )
  {
    print "The audit tool is running in MISSING_CHUNKS mode";
    print "\n";
    $index = 0;
    foreach $key (@MISSING_CHUNKS)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "\n";
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  elsif( $_[0] =~ /^MISSING_CHUNKS_EXTENDED$/i )
  {
    print "The audit tool is running in MISSING_CHUNKS_EXTENDED mode";
    print "\n";
    $index = 0;
    foreach $key (@MISSING_CHUNKS_EXTENDED)
    {
      $value = $SQLstoDetector{$key};
      $index ++;
      print "\n";
      print "The audit is in processing phase $index: $key";
      print "\n";
      $db2Command = $value;
      $db2Command =~ s/<poolId>/$poolId/gi;
      $db2Command =~ s/<poolName>/$poolName/gi;
      $db2Output = &DB2Command( $db2Command );

      if( $db2Output < 0 )
      {
        return -2;
      }
      else
      {
    if( $db2Output > 0 )
    {
      $SQLsOutput{$key} = $db2Output;
      print "The audit scan has detected a potential problem in phase $index: $key!";
          print "\n";
    }
    else
        {
          print "The audit scan found nothing wrong in phase $index: $key";
          print "\n";
        }
      }
    }
    @hashList = %SQLsOutput;
    print "The result of the audit scan is: @hashList\n" if( $DEBUG );
  }
  else
  {
    print "The dedup audit tool didn't recognize the specified symptom.\n";
    print "Try using the ALL mode.\n";
    return 0;
  }

  return 0;
}

###############################################################
# ReportGenerator
# Function: Generate a report of all the analysis performed
#           against the deduplication catalog. This report can
#           be used in the future to perform cleanup, if needed
#.
# Input:
#   The result from ProblemDetector
#
# Ouput:
#   0: Success
#   -1: Fail
###############################################################
sub ReportGenerator
{
   my $fileName = 'reportdedup.out';
   my $key;
   my $value;

   if( @_ != 0 )
   {
     print "ReportGenerator function should be invoked as: ReportGenerator\n";
     return -1;
   }

   chdir $curWorkdir || die "Can't change to directory $curWorkdir $!";

   print "Input the report file name <def: $fileName>: ";
   chomp( $defLine = <STDIN> );
   $fileName=$defLine if( $defLine =~ /\S+/ );

   open ( my $REPORT_FH,'+>', $fileName ) || die "Open file $fileName error $!";
   while( ($key,$value) = each %SQLsOutput )
   {
     $SQLstoDetector{$key} =~ s/<poolId>/$poolId/gi;
     $SQLstoDetector{$key} =~ s/<poolName>/$poolName/gi;
     print $REPORT_FH "******************************************************************\n";
     print $REPORT_FH "SYMPTOM: $key\n";
     print $REPORT_FH "DB2SQL:\n";
     print $REPORT_FH "        $SQLstoDetector{$key}\n";
     print $REPORT_FH "RESULT: $value\n";
     print $REPORT_FH "STORAGE POOL NAME(POOLID): $poolName($poolId)\n";
     print $REPORT_FH "******************************************************************\n";
   }

   close $REPORT_FH || die "Close file $fileName error $!";
   print "The report file has been generated.";
   print "\n";
   print "Check $fileName for the results.";
   print "\n";
   return 0;
}


###############################################################
# ReportReader
# Function: Read the report and format the cleanups
#       to perform.
#
# Input:
#   The report file previously generated.
#
# Output:
#   0: Success
#   -1: Fail
###############################################################
sub ReportReader
{
  my $fileName="reportdedup.out";
  my $defLine;
  my @keys;
  my $pattern="";
  if( @_ != 0 )
  {
    print "ReportReader function should be invoked as: ReportReader\n";
    return -1;
  }

  chdir $curWorkdir || die "Can't change to directory $curWorkdir $!";

  print "Input the report file name < def: reportdedup.out>: ";
  chomp( $defLine = <STDIN> );
  $fileName=$defLine if( $defLine =~ /\S+/ );

  print "Read report file $fileName ...\n";

  foreach (keys %SQLstoDetector)
  {
    $pattern = sprintf("$pattern%s|", $_);
  }
  $pattern =~ s/\|$//;
  print "The pattern in ReportReader is: $pattern\n" if($DEBUG);

  open (my $REPORT_FH,'<', $fileName ) || die "Open file $fileName error $!";
  while( <$REPORT_FH> )
  {
    chomp;
    if( /$pattern/ )
    {
      print "The matched part of $_ is: $&";
      print "\n";
      if( $DEBUG ) {$SQLsOutput{$&}="NA";}
    }
  }
  close( $REPORT_FH );

  print "The report file( $fileName ) processing was successful";
  print "\n";
  return 0;
}


#################################################################################
# Below are the supporting sub routines used by the main                        #
# processing section to get the environment setup                               #
#################################################################################

###############################################################
# TSMConnect
# Input:
#   None
# Output:
#     0: Connect succeed
#    <0: Connect failed
###############################################################
sub TSMConnect
{
  my $TSMFILE;
  my @tsmOutput;
  my @tsmMatch;
  my $tsmRes = -1;
  my $defLine;

  if( @_ != 0 )
  {
    print "TSMConnect function should be invoked as: TSMConnect\n";
    return $tsmRes;
  }

  print( "Input the dsmadmc path(double quote the path) < def: $tsmPath>:" );
  chomp( $defLine = <STDIN> );
  if( $defLine =~ /\S+/ )
  {
    if( $^O =~ /win/i )
    {
      $defLine =~ s/\\/\\\\/g;
    }

    $tsmPath = $defLine;
  }

  $defLine = $tsmPath;
  $defLine =~ s/^\"//;
  $defLine =~ s/\"$//;

#  print "Change work directory to $defLine\n" if( $DEBUG );
#  chdir $defLine || die "cant change to directory $defLine: $!";

  print( "Input the admin name <def: $admin>:");
  chomp( $defLine = <STDIN> );
  $admin = $defLine if( $defLine =~ /\S+/ );

  print( "Input the password for $admin <def: $pass>:");
  chomp( $defLine = <STDIN> );
  $pass = $defLine if( $defLine =~ /\S+/ );

  print( "Input the option file(double quote the path) < def: $optionFile>:" );
  chomp( $defLine = <STDIN> );
  if( $defLine =~ /\S+/ )
  {
    if( $^O =~ /win/i )
    {
      $defLine =~ s/\\/\\\\/g;
    }

    $optionFile = $defLine;
  }

  if( $^O =~ /win/i )
  {
    $tsmCommandPre = "$tsmPath\\dsmadmc.exe -id=$admin -pass=$pass -optfile=$optionFile -dataonly=no -display=list";
  }
  else
  {
    $tsmCommandPre = "$tsmPath/dsmadmc -id=$admin -pass=$pass -optfile=$optionFile -dataonly=no -display=list";
  }

  $tsmCommand="$tsmCommandPre select server_name from status";
  print( "The executing TSM connection command: $tsmCommand\n" ) if ($DEBUG);

  open( $TSMFILE, "$tsmCommand |") or die "cant open $tsmCommand |, $!";
  while( <$TSMFILE> )
  {
    if( m/server_name/i )
    {
      @tsmOutput = split /:/, $_;
      $tsmOutput[1] =~ s/\R*$//; #Removes the ending newline for output
      $tsmOutput[1] =~ s/^\s+//; #Removes leading white space
      print( "Connection to $tsmOutput[1] succeeded.\n" );
      $tsmRes = 0;
    }
  }

  print "Connection to the tsm server failed\n" if( $tsmRes == -1 );

  close( $TSMFILE );
  return $tsmRes;
}

###############################################################
# TSMCommand
# input:
#   tsm command or "[-itemcommit] macro xxx"
# output:
#   0: Success
#   <0: Error
# Notes:
#   Add your customized tsm command here if you
#   need commands besides "w=y"/macro/show
#   dedupdeleteinfo
###############################################################
sub TSMCommand
{
  my $res = 0;
  my @output;
  my $command;
  my @result;
  my @subCommand;
  my @subResult;
  my $TSMFILE;

  # Change working directory back to TSM client location #
  $defLine = $tsmPath;
  $defLine =~ s/^\"//;
  $defLine =~ s/\"$//;

#  print "Change work directory to $defLine\n" if( $DEBUG );
#  chdir $defLine || die "cant change to directory $defLine: $!";

  if( @_ != 1 )
  {
    print "TSMCommand function should be invoked as: TSMCommand <command>\n";
    return -1;
  }
  elsif ( $_[0] =~ /w\s*=\s*y/i  )
  {
    print scalar localtime;
    print "\nThe TSM command has started";
    print "\n";
    $command = $_[0];
    $command = "$tsmCommandPre $command";
    @output = `$command`;
    @result = grep { /ANR0985I[\d\D]*completion state SUCCESS/ } @output;
    if ( @result < 1 )
    {
      @result = grep { /ANR0986I[\d\D]*completion state of SUCCESS/ } @output;
    }

    print "The output of command $command is: @output AND the grep result is @result\n" if ( $DEBUG );

    print scalar localtime;
    print "\n";
    if( @result < 1 )
    {
      print "$command aborted, pls check tsm server console!";
      print "\n";
      return -2;
    }
    else
    {
      print "The TSM command succeeded";
      print "\n";
      return 0;
    }
  }
  elsif( $_[0] =~ /macro/i )
  {
    print scalar localtime;
    print "\nThe TSM command has started";
    print "\n";
    $command = $_[0];
    $command = "$tsmCommandPre $command";

    if( $command =~ /\s*=>\s*/ )
    {
      @subCommand = split /\s*=>\s*/, $command;

      #We append the result to show invo output.
      open( $TSMFILE, "+>>$subCommand[1]" ) || die "Open $subCommand[1] failed: $!" ;
      print $TSMFILE  `$subCommand[0]`;
      $res = $? >> 8;
      close $TSMFILE;

      print scalar localtime;
      print "\n";
      if( $res != 0 )
      {
        print "The macro file that failed to be executed is: $subCommand[0] > $subCommand[1]\n" if( $DEBUG );
        print "Failed with error $res, check TSM server actlog!";
        print "\n";
        return -2;
      }
      else
      {
        print "The TSM command succeeded";
        print "\n";
        return 0;
      }
    }
    else
    {
      @output = `$command`;
      @result = grep { /ANS8002I/ } @output;

      print "The output of command $command is: @output AND the grep result is @result\n" if ( $DEBUG );

      print scalar localtime;
      print "\n";
      if( @result < 1 )
      {
        print "$command failed, check TSM server actlog!";
        print "\n";
        return -2;
      }
      else
      {
        print "The TSM command succeeded";
        print "\n";
        return 0;
      }
    }
  }
  elsif( $_[0] =~ /show\s+dedupdeleteinfo/ )
  {
    sleep(10); #sleep 10 seconds to give the deref engine time to start up

    DEDUP_IDLE: while( 1 )
    {
      print "The TSM command has started";
      print "\n";
      $command = "$tsmCommandPre $_[0]";

      @output = `$command`;
      @result = grep /Number of active worker threads/, @output;
      print "The output of command $command is: @output AND the grep result is @result\n" if ( $DEBUG );

      if( @result > 0 )
      {
        print "The TSM command succeeded";
    print "\n";
      }
      else
      {
        print "The TSM command failed, pls check tsm server console!";
    print "\n";
    return -2;
      }
      @subResult = split /:/, $result[0];
      $subResult[1] =~ s/\s*//g;
      if( $subResult[1] > 0 )
      {
    print "Dedup chunk deletions need to be quiesced and they are currently active.\n";
        print "The script will sleep 5 minutes and then check deletion status again.\n";
        print "Show Dedupdeleteinfo command can also be issued to check the deletion status.\n";
    sleep( 300 );
    redo DEDUP_IDLE;
      }
      else
      {
        print "DEREF ENGINE is idle, proceeding with recovery";
    print "\n";
    last DEDUP_IDLE;
      }
    }
  }
  else
  {
    print "Exiting since this command can only be run in the foreground";
    print "\n";
    return -1;
  }
  return 0;
}

###############################################################
# DB2Connect
# Input:
#     None
# Output:
#     0: Success
#     <0: Error
###############################################################
sub DB2Connect
{
  my $DB2FILE;
  my $db2Res=0;
  my @db2Output;
  my @db2Match;


  print( "Input the db2 database name <def: $db2Alias>:");
  chomp( $defLine = <STDIN> );
  $db2Alias = $defLine if( $defLine =~ /\S+/ );

  $db2Command = "db2 connect to $db2Alias";
  print "Calling $db2Command \n" if( $DEBUG );
  open( $DB2FILE, "$db2Command |" ) || die "Cant open db2 connect to $db2Alias, $!";

  while( <$DB2FILE> )
  {
    $db2Res=-1 if( m/^DB21061E/ )
  }
  close( $DB2FILE );


  if( $db2Res == 0 )
  {
    print( "Input the db2 schema name <def: $db2Schema>:");
    chomp( $defLine = <STDIN> );
    $db2Schema = $defLine if( $defLine =~ /\S+/ );

    $db2Command = "db2 set current schema \"$db2Schema\"";
    print "Calling $db2Command \n" if( $DEBUG );
    open( $DB2FILE , "$db2Command |" ) || die "Cant set db2 schema $db2Schema, $!";

    $db2Res = -1;
    while( <$DB2FILE> )
    {
      $db2Res = 0 if( m/^DB20000I/ )
    }
    close($DB2FILE);
  }

  if( $db2Res == 0 )
  {
    $db2Command = "db2 -x \"select count(*) from NODES\"";
    print "Calling $db2Command to test\n" if( $DEBUG );
    @db2Output = `$db2Command`;
    @db2Match =  grep /[^\d\s]/, @db2Output;

    print "The output of $db2Command is: @db2Output\n" if( $DEBUG );

    if( @db2Match > 0 )
    {
      $db2Res = -1;
      print "A connect attempt to DB2 failed!";
      print "\n";
    }
    else
    {
      print "The connection to DB2 succeeded";
      print "\n";
    }
  }
  return $db2Res;
}

###############################################################
# DB2Command
# input:
#   db2 command
# output:
#   0: success without feedback.
#   >0: the select output count OR single column
#   <0: error
# Note:
#   SQLs supported: select single column or count()
#                   update/delete/insert
#   SQLs limitation: select single column only works
#                for integer bigger than 0
###############################################################
sub DB2Command
{
  my @db2Output;
  my @db2Match;
  my @db2SubCommand;
  my $DB2FILE;

  if( @_ > 1 )
  {
    print "DB2Command function should be invoked as: DB2Command <SQL statement>\n";
    return -1;
  }

  # Change working directory back to TSM client location #
  $defLine = $tsmPath;
  $defLine =~ s/^\"//;
  $defLine =~ s/\"$//;

#  print "Change work directory to $defLine\n" if( $DEBUG );
#  chdir $defLine || die "cant change to directory $defLine: $!";

  print scalar localtime;
  print "\nThe DB2 command has started";
  print "\n";

  #format db2Command to pass on to db2
  if( ( $_[0] =~ /^\s*\"*update/i ) || ( $_[0] =~ /^\s*\"*delete/i ) || ( $_[0] =~ /^\s*\"*insert/i ) )
  {
    $db2Command = "db2 -m $_[0]";
    @db2Output = `$db2Command`;
    print "The result of $db2Command is:\n @db2Output\n" if( $DEBUG );

    @db2Match = grep {/DB20000I|SQL0100W/i} @db2Output;
    print scalar localtime;
    print "\n";
    if( @db2Match > 0 )
    {
      print "The DB2 command succeeded";
      print "\n";
      return 0;
    }
    else
    {
      print "The $db2Command failed!";
      print "\n";
      return -1;
    }
  }

  elsif( $_[0] =~ /^\s*\"*select/i )
  {
    $db2Command = "db2 -x $_[0]";

    if( $db2Command =~ /\s*=>\s*/ )
    {
      @db2SubCommand = split /\s*=>\s*/, $db2Command;

      open( $DB2FILE, "+>$db2SubCommand[1]" ) || die "Open $db2SubCommand[1] failed: $!" ;
      print $DB2FILE `$db2SubCommand[0]`;
      close $DB2FILE;

      print "Executed db2 command $db2SubCommand[0] > $db2SubCommand[1]\n" if( $DEBUG );
      print scalar localtime;
      print "\n";
      print "The DB2 command succeeded";
      print "\n";
      return 0;
    }
    else
    {
      @db2Output = `$db2Command`;
      print "The result of $db2Command is:\n @db2Output\n" if( $DEBUG );

      @db2Match =  grep /SQL\d*N/, @db2Output;
      print scalar localtime;
      print "\n";
      if( @db2Match > 0 )
      {
        print "The db2 command:$db2Command failed!";
        print "\n";
        return -1;
      }
      else
      {
        $db2Output[0] =~ s/^\s*//g;
        $db2Output[0] =~ s/\s*$//g;

        if( $db2Output[0] eq "" ) {$db2Output[0]=0;}
        print "The DB2 command succeeded";
        print "\n";
        return $db2Output[0];
      }
    }
  }

  else
  {
    print( "The DB2 statement generation failed. Re-run in DEBUG mode" );
    print "\n";
    return -1;
  }
}

###############################################################
# CommandProcess
# Function: wrap up the db2 and tsm command in
#                   a single interface.
# Input:
#       <command in the cleanup procedures>
#       <preview mode>
# Output:
#       ERROR: -1
#       SUCCESS: 0
###############################################################
sub CommandProcess
{
  my $res = 0;
  $commandItem = $_[0];
  $commandItem =~ s/<poolId>/$poolId/gi;
  $commandItem =~ s/<poolName>/$poolName/gi;
  $index ++;

  print "\nCommand #$index to perform is:\n $commandItem\n";


  return 0 if( $_[1] == 1 );

  if( $commandItem =~ /^tsm/i )
  {
    $commandItem =~ s/^tsm\s*//;
    $res = &TSMCommand( $commandItem );
    if( $res < 0)
    {
      print( "$commandItem execution failed\n" );
      return -1;
    }
  }
  elsif( $commandItem =~ /^db2/i )
  {
    $commandItem =~ s/^db2\s*//;
    $res = &DB2Command( $commandItem );
    if( $res < 0 )
    {
      print( "$commandItem execution failed\n" );
      return -1;
    }
  }
  elsif( $commandItem =~ /^sleep/i || $commandItem =~ /^print/i )
  {
    $res = $commandItem;
    if( $res < 0)
    {
      print( "$commandItem execution failed\n" );
      return -1;
    }
  }
  else
  {
    print( "The command attempted is not supported in this utility\n");
    return -1;
  }

  return 0;
}

###############################################################
# GetDedupPool
# Input:
#   None
# Output:
#   Display the deduplicated storage pools
###############################################################
sub GetDedupPool
{
  my @dedupPoolNames;
  my $dedupPool;

  print "\n";
  print "Finding all deduplicated storage pools\n";
  print ".\n";
  sleep (1);
  print ".\n";
  sleep (1);

  @dedupPoolNames = `db2 -x "select POOLNAME from SS_POOLS where DEDUPLICATE!=0"`;
  print "\n";
  print "***********************************\n";
  print "DEDUPLICATED STORAGE POOLS:\n";
  print "\n";
  foreach $dedupPool ( @dedupPoolNames )
  {
    print($dedupPool);
  }
  print "***********************************\n";
  print "\n";
}

###############################################################
# ConvertPool
# Input:
#   poolid or poolname
# Output:
#   0: succeed
#   -100000: error ( in case negative copypool id )
###############################################################
sub ConvertPool
{
  if( @_ != 1 )
  {
    print "ConvertPool function should be invoked as: ConvertPool <poolid/poolname>\n";
    return -100000;
  }

  if( $_[0] =~ /^\d+$/ )
  {
    print "Converting poolid to poolname\n" if ($DEBUG);
    $poolId = $_[0];
    $poolName = &DB2Command( "\"select POOLNAME from SS_POOLS where poolid=$poolId\"" );
    return $poolName;
  }
  else
  {
    print "Converting poolname to poolid\n" if( $DEBUG );
    $poolName = $_[0];
    $poolName =~ s/(\w+)/\U$1/ig;
    $poolId = &DB2Command( "\"select POOLID from SS_POOLS where POOLNAME=\'$poolName\'\"" );
    return $poolId;
  }
}

############################ END OF SCRIPT ###########################################################

exit 0;
