if ((@ARGV < 1) || (@ARGV > 2))
{
  showUsage();
}

$platform = $^O;  

$scopespecifier = shift(@ARGV);   # either -all or -nodelist
$inputfile = shift(@ARGV);        # name of input file (valid only when -nodelist is specified as first argument)

@nodefsArray = ();      # array to hold initial information about nodes and filespace obtained from user input file, or select statement
                        # for all nodes, as the case may be

@nodeidfsidArray = ();  # as nodes/filespaces are processed in stage 1, this array is populated with records
                        # pertaining to nodes/filespaces which will be subsequently processed in stage 2; these
                        # records include more information about the nodes/filespaces, namely, node id and
                        # filespace id, which will be needed in the sql queries for affected objects in stage 2

$scriptLog = "queriesIT11994.log";
$foundaffectedobject = 0;                 # set to 1 if at least one affected object is found
$currenttime = localtime();

# log the time script execution started

logentry("\n*****************************************************************\n");
logentry("Script execution began at $currenttime\n");
logentry("*****************************************************************\n");

if (($scopespecifier ne "-all") && ($scopespecifier ne "-nodelist"))  # should be either "-all" or "-nodelist"
{
  showUsage();
}
elsif (($scopespecifier eq "-all") && ($inputfile ne ""))  # if -all is specified, there should not be an input file specified
{
  showUsage();
}
elsif ($scopespecifier eq "-all")
{
  getallnodes();
}
elsif (($scopespecifier eq "-nodelist") && ($inputfile eq "")) # if -nodelist is specified, there should be an input file specified
{
  showUsage();
}
elsif (($scopespecifier eq "-nodelist") && (! -f $inputfile ))  # input file specified but file does not exist
{
  die "Input file $inputfile not found\n";
}
elsif ($scopespecifier eq "-nodelist")
{
  parseinputfile();
}

#
#  Having determined the list of nodes or nodes/filespaces from the input file, or select statement as the
#  case may be, we now, in stage 1, go through the list and filter out the API filespaces, and nodes without
#  any filespaces
# 

logentry("\n*****************************************************************\n");
print "\nSTAGE 1: Determine which nodes/filespaces from the input file need to be queried\n\n";
logentry("\nSTAGE 1: Determine which nodes/filespaces from the input file need to be queried\n");
logentry("\n*****************************************************************\n");

foreach $ndinfo (@nodefsArray)
{
  my $currnode = $ndinfo->{nodename};
  my $currfsname = $ndinfo->{fsname};
  my $db2cmdfile = "getnodeidfsidcmdfile";
  my $currnode_uc = uc($currnode);

  if ($currfsname ne "")
  {
    open (CMDH, ">$db2cmdfile") or die "Unable to open $db2cmdfile\n";
    print CMDH "connect to tsmdb1;\n";
    print CMDH "select adnr.nodeid,imfs.fsid,imfs.fstype,cast(imfs.fsname as varchar(255) for sbcs data) from tsmdb1.nodes adnr, tsmdb1.filespaces imfs where adnr.nodeid=imfs.nodeid and adnr.nodename like \'${currnode_uc}\' and imfs.fsname like \'${currfsname}\';\n";
    print CMDH "terminate;\n";
    close CMDH;
  }
  else
  {
    open (CMDH, ">$db2cmdfile") or die "Unable to open $db2cmdfile\n";
    print CMDH "connect to tsmdb1;\n";
    print CMDH "select adnr.nodeid,imfs.fsid,imfs.fstype,cast(imfs.fsname as varchar(255) for sbcs data) from tsmdb1.nodes adnr, tsmdb1.filespaces imfs where adnr.nodeid=imfs.nodeid and adnr.nodename like \'${currnode_uc}\';\n";
    print CMDH "terminate;\n";
    close CMDH;
  }
   
  my $db2cmd = "db2 -tf $db2cmdfile";

  if ($platform eq "MSWin32")
  {
    $db2cmd = "db2cmd /c /w /i " . "$db2cmd";
  }

  my @db2out = `$db2cmd`;
  my $outputstarted = 0;
  my $numrecordsfound = 0;
 
  foreach $db2outln (@db2out)
  {
    if ($db2outln =~ m/----+/)
    {
      $outputstarted = 1;
    }
    elsif ($db2outln =~ m/record\(s\)\s+selected/)
    {
      $outputstarted = 0;
    }
    elsif (($outputstarted == 1) && ($db2outln =~ m/^\s*(\d+)\s+(\d+)\s+(\S+)\s+(\S[\S|\s]*)$/))
    {
      my $nid = $1;
      my $fsid = $2;
      my $fstype = $3;
      my $fsname = $4;

      $fsname =~ s/\s+$//;

      if ($fstype !~ m/^API:/)
      {
        my $nodeidfsidinfo = {};
        $nodeidfsidinfo->{nodeid} = $nid;
        $nodeidfsidinfo->{fsid} = $fsid;
        $nodeidfsidinfo->{nodename} = $currnode;
        $nodeidfsidinfo->{fsname} = $fsname;

        push(@nodeidfsidArray, $nodeidfsidinfo);
      }
      else
      {
        print "\nNode $currnode, filespace $fsname is an API filespace, not affected by this APAR\n";
        logentry("\nNode $currnode, filespace $fsname is an API filespace, not affected by this APAR\n");
      }
      $numrecordsfound++;
    }
  }

  if ($numrecordsfound == 0)
  {
    if ($currfsname ne "")
    {
      print "\nNodename $currnode, filespace $currfsname not found\n";
      logentry("\nNodename $currnode, filespace $currfsname not found\n");
    }
    else
    {
      print "\nNo filespaces found under nodename $currnode\n";
      logentry("\nNo filespaces found under nodename $currnode\n");
    }
  }
  unlink($db2cmdfile);
}     

$numndfstoquery = @nodeidfsidArray;

if ($numndfstoquery > 0)    # proceed into this block only if nodes/filespaces still remaining after stage 1
{

#
#  In stage 2, run the two sql queries for all node/filespace still remaining after stage 1,
#  to determine if there are affected objects; one query is for backup objects, the other for 
#  archive objects
#

logentry("\n*****************************************************************\n");
print "\nSTAGE 2: Run sql queries for nodes/filespaces determined as needing to be queried in stage 1\n\n";
logentry("\nSTAGE 2: Run sql queries for nodes/filespaces determined as needing to be queried in stage 1\n");
logentry("\n*****************************************************************\n");
 

foreach $nodeidwithfsid (@nodeidfsidArray)
{
  my $nodeidofinterest = $nodeidwithfsid->{nodeid};
  my $fsidofinterest = $nodeidwithfsid->{fsid};
  my $nodeofinterest = $nodeidwithfsid->{nodename};
  my $fsofinterest = $nodeidwithfsid->{fsname};
  my $resultsfile = "queryresults_${nodeidofinterest}_${fsidofinterest}";

  my $numberaffectedobj;

  print "\nRunning sql queries for node $nodeofinterest and filespace $fsofinterest . . .\n";
  logentry("\nRunning sql queries for node $nodeofinterest and filespace $fsofinterest . . .\n");

  $numberaffectedobj = runselectstatements($nodeidofinterest, $fsidofinterest, $resultsfile);
  
  if ($numberaffectedobj == 0)
  {
    print "\nNo affected objects found under node $nodeidwithfsid->{nodename}, filespace $nodeidwithfsid->{fsname}\n";
    logentry("\nNo affected objects found under node $nodeidwithfsid->{nodename}, filespace $nodeidwithfsid->{fsname}\n");
  }
  else
  {
    print "\n$numberaffectedobj affected objects found under node $nodeidwithfsid->{nodename}, filespace $nodeidwithfsid->{fsname}. See file $resultsfile\n"; 
    logentry("\n$numberaffectedobj affected objects found under node $nodeidwithfsid->{nodename}, filespace $nodeidwithfsid->{fsname}. See file $resultsfile\n");
  }
}

}

##############  Print suitable message to screen and point user to script log for details ####################

print "\n\n******************************************************************************\n\n";
if ($foundaffectedobject == 1)
{
  print "\n  At least one affected object was found;\n  please see log $scriptLog for details\n";
}
else
{
  print "\n  No affected objects were found;\n  please see log $scriptLog for details\n";
}
print "\n******************************************************************************\n";

# log the time script execution completed

$currenttime = localtime();
logentry("\n*****************************************************************\n");
logentry("Script execution ended at $currenttime\n");
logentry("*****************************************************************\n");

############################################################
#      sub: runselectstatements
#
#     desc: Invokes the actual sql queries to determine if the
#           specified node/filespace has affected objects; one
#           query for backup objects, and the second for archive
#           objects
# 
#   params: 1. the node id to be queried
#           2. the filespace id of the filespace under that
#              node id to be queried
#           3. the name of file to write affected object ids,
#              if any
#
#  returns: number of affected objects found
#
############################################################

sub runselectstatements
{
  my $nodeid = shift(@_);
  my $fsid = shift(@_);
  my $results = shift(@_);

  my $db2cmdfile = "selectstatement_${nodeid}_${fsid}";
  
  my $numaffectedobj = 0;

  # query for backup objects
  
  open (CMDH, ">$db2cmdfile") or die "Unable to open $db2cmdfile\n";
  print CMDH "connect to tsmdb1;\n";
  print CMDH "select \'show invo \' || cast (imbk.objid as char(24)) from tsmdb1.backup_objects imbk where nodeid=${nodeid} and fsid=${fsid} and BITAND( imbk.flags, CAST(8 AS SMALLINT) ) > 1 and imbk.bfsize + 50000 < (select sum( bfbe.size ) from tsmdb1.bf_bitfile_extents as bfbe, tsmdb1.bf_aggregated_bitfiles as bfbf where (bfbf.srvid=0) and (bfbf.owner=imbk.objid) and (bfbe.bfid=bfbf.bfid) and (bfbe.superbfid=bfbf.superbfid) and (bfbe.poolid>0) with ur) for read only with ur;\n";
  print CMDH "terminate;\n";
  close CMDH;
  
  my $db2cmd = "db2 -tf $db2cmdfile";

  if ($platform eq "MSWin32")
  {
    $db2cmd = "db2cmd /c /w /i " . "$db2cmd";
  }

  my @db2out = `$db2cmd`;
  my $db2outln;

  foreach $db2outln (@db2out)
  {
    if ($db2outln =~ m/show invo/)
    {
      open(RESULTH, ">>$results") or die "Unable to open $results\n";
      print RESULTH "$db2outln";
      close RESULTH;
      $foundaffectedobject = 1;
      $numaffectedobj++;
    }
  }
  
  # query for archive objects

  open (CMDH, ">$db2cmdfile") or die "Unable to open $db2cmdfile\n";
  print CMDH "connect to tsmdb1;\n";
  print CMDH "select \'show invo \' || cast (imbk.objid as char(24)) from tsmdb1.archive_objects imbk where nodeid=${nodeid} and fsid=${fsid} and BITAND( imbk.flags, CAST(8 AS SMALLINT) ) > 1 and imbk.bfsize + 50000 < (select sum( bfbe.size ) from tsmdb1.bf_bitfile_extents as bfbe, tsmdb1.bf_aggregated_bitfiles as bfbf where (bfbf.srvid=0) and (bfbf.owner=imbk.objid) and (bfbe.bfid=bfbf.bfid) and (bfbe.superbfid=bfbf.superbfid) and (bfbe.poolid>0) with ur) for read only with ur;\n";
  print CMDH "terminate;\n";
  close CMDH;
  
  $db2cmd = "db2 -tf $db2cmdfile";

  if ($platform eq "MSWin32")
  {
    $db2cmd = "db2cmd /c /w /i " . "$db2cmd";
  }

  @db2out = `$db2cmd`;
  
  foreach $db2outln (@db2out)
  {
    if ($db2outln =~ m/show invo/)
    {
      open(RESULTH, ">>$results") or die "Unable to open $results\n";
      print RESULTH "$db2outln";
      close RESULTH;
      $foundaffectedobject = 1;
      $numaffectedobj++;
    }
  }
  unlink($db2cmdfile);

  return $numaffectedobj;
}

############################################################
#      sub: parseinputfile
#
#     desc: Parses the user input file, creating a suitable
#           record for each node, or node/filespace, from the
#           input file, and adding the record to the array
#           @nodefsArray 
# 
#   params: none
#
#  returns: none
#
############################################################

sub parseinputfile
{
  open(INH, "<$inputfile") or die "Unable to open $inputfile\n";
  while (<INH>)
  {
    if ($_ =~ m/^\s*(\S+)\s+(\S[\S|\s]*)$/)
    {
      my $nodename = $1;
      my $fsname = $2;

      $fsname =~ s/\s+$//;  # remove any trailing whitespace
     
      my $nodefsnameinfo = {};
      $nodefsnameinfo->{nodename} = $nodename;

      if ($fsname eq "*")
      {
        $nodefsnameinfo->{fsname} = "";
      }
      else
      {
        $nodefsnameinfo->{fsname} = $fsname;
      }
      push(@nodefsArray, $nodefsnameinfo);
    }
    elsif ($_ =~ m/^\s*(\S+)\s*$/)
    {
      my $nodename = $1;
     
      my $fsname = "*";  # if no fsname field is present in a line of the input file, it is assumed to be "*"
     
      my $nodefsnameinfo = {};
      $nodefsnameinfo->{nodename} = $nodename;

      $nodefsnameinfo->{fsname} = "";
      
      push(@nodefsArray, $nodefsnameinfo);
    }
  }
  close INH;
}
 
############################################################
#      sub: getallnodes
#
#     desc: Invokes db2 select statement for list of all
#           the nodes, parsing the output from that, creating 
#           a suitable record for each node, and adding the  
#           record to the array @nodefsArray 
# 
#   params: none
#
#  returns: none
#
############################################################

sub getallnodes
{
  my $db2cmdfile = "getallnodescmdfile";

  open (CMDH, ">$db2cmdfile") or die "Unable to open $db2cmdfile\n";
  print CMDH "connect to tsmdb1;\n";
  print CMDH "select NODENAME from tsmdb1.NODES;\n";
  print CMDH "terminate;\n";
  close CMDH;
  
  my $db2cmd = "db2 -tf $db2cmdfile";

  if ($platform eq "MSWin32")
  {
    $db2cmd = "db2cmd /c /w /i " . "$db2cmd";
  }

  my @db2out = `$db2cmd`;
  my $outputstarted = 0;
 
  foreach $db2outln (@db2out)
  {
    if ($db2outln =~ m/----+/)
    {
      $outputstarted = 1;
    }
    elsif ($db2outln =~ m/record\(s\)\s+selected/)
    {
      $outputstarted = 0;
    }
    elsif (($outputstarted == 1) && ($db2outln =~ m/^(\S+)/))
    {
      my $nodename = $1;
     
      my $fsname = "*";  # if no fsname field is present in a line of the input file, it is assumed to be "*"
     
      my $nodefsnameinfo = {};
      $nodefsnameinfo->{nodename} = $nodename;

      $nodefsnameinfo->{fsname} = "";
      
      push(@nodefsArray, $nodefsnameinfo);
    }
  }
  unlink($db2cmdfile);
}

############################################################
#      sub: logentry
#
#     desc: Logs message to the script log
#
#   params: 1. the message to be logged
#
#  returns: none
#
############################################################

sub logentry
{
  $info = shift(@_);

  open(LOGH, ">>$scriptLog") or die "Unable to open $scriptLog\n";

  print LOGH "$info";

  close LOGH;
}

############################################################
#      sub: showUsage
#
#     desc: Displays the usage syntax for this script
#
#   params: none
#
#  returns: none
#
############################################################

sub showUsage
{
  print "USAGE: perl runqueriesIT11994.pl -all                                            \n";
  print "   or: perl runqueriesIT11994.pl -nodelist <input file name>                     \n";
  print "       where the flag \"-all\" is specified if intending to run the queries on   \n";
  print "       all relevant nodes/filespaces, or alternatively, specify                  \n";
  print "       -nodelist, followed by <input file name>, which is a file each line of    \n";
  print "       which is in one of the following formats:                                 \n";
  print "                        <node name>  <filespace name>                            \n";
  print "         or                                                                      \n";
  print "                        <node name>         *                                    \n";
  print "         or, simply                                                              \n";
  print "                        <node name>                                              \n";
  print "       to restrict the queries to the nodes/filespaces in that <input file name> \n\n";
  die;
}