/* Rexx */
/*
################################################################################
#
# SAMPLE NAME          : analyzeReorg
#
# USAGE                : analyzeReorg
#
# ENVIRONMENT VARIABLES: TC_FAMILY
#
# DESCRIPTION          : This sample shell script is used to analyze the 
#                        statistics of the DB2 database and if needed,
#                        then a Reorganization of the statistics is done 
#                        in order to update the indexes and rebind the packages.
#
################################################################################
#
#           NOTICE TO USERS OF THE SOURCE CODE EXAMPLES
#
# INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THE SOURCE CODE
# EXAMPLES, BOTH INDIVIDUALLY AND AS ONE OR MORE GROUPS, "AS IS" WITHOUT
# WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT
# LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
# PARTICULAR PURPOSE.  THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE
# OF THE SOURCE CODE EXAMPLES, BOTH INDIVIDUALLY AND AS ONE OR MORE GROUPS,
# IS WITH YOU.  SHOULD ANY PART OF THE SOURCE CODE EXAMPLES PROVE
# DEFECTIVE, YOU (AND NOT IBM OR AN AUTHORIZED DEALER) ASSUME THE ENTIRE
# COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.
#
             
################################################################################
*/

 parse arg inFile outFile opts

 if inFile = '' & lines( inFile) = 0 then
   do /* no file name passed and nothing in stdin */
     say 'either specify filename of reorgchk output, or put in stdin'
     exit 99
   end
 if wordpos( '-RUN', translate(opts)) > 0 then run = 1
 else run = 0

 toBeReorged.   = 0 /* collects tables for reorganization */
 runStats.      = 0 /* collects tables for runstats */

 call initKeyIndexes /* sets stem keyIndex. ignoreIndex. */
 call loadRexxFunctions

 do until left( inRec, 40) = copies('-',40) /* pass over header information */
   if \ lines( inFile)  then leave
   inRec = lineIn( inFile)
 end

 rs = 0 /* counter for runstats. */
 ro = 0 /* counter for toBeReorged. */
 inTableSection = 1 
 do qq = 1 while lines( inFile) & inTableSection
   inRec = lineIn( inFile)
   if inRec = '' then iterate qq
   if left( inRec, 40) <> copies('-',40) then
     do /* not past last table */
       parse var inRec creator tName stats
       reOrgInd = subword( stats, words(stats)) 
       card = word( stats, 1)
       if dataType( card, 'N')  then
         if card > 0 then
           do /* positive integer value */
             rs = rs + 1
             runStats.rs = creator'.'tName
           end
       if pos( '*', reOrgInd) > 0  then 
         do /* flag table as reorg candidate */
           ro = ro + 1
           toBeReorged.ro = creator'.'tName
           toBeReorged.tName = 1
         end
     end 
   else inTableSection = 0
 end qq /* do while inTableSection */
 runStats.0 = rs /* count of all of the non-zero cardinality tables */

 do until left( inRec, 40) = copies('-',40) /* pass over header information for indexes */
   if \ lines( inFile)  then leave
   inRec = lineIn( inFile)
 end

 inIndexSection = 1
 do qq = 1 while lines( inFile) & inIndexSection
   inRec = lineIn( inFile)
   if inRec = '' then iterate qq /* skip blank lines */
   if left( inRec, 40) <> copies('-',40) then
     do /* not past last index */
       if word( inRec, 1) = 'Table:' then parse upper value word( inRec, 2) with creator '.' tName
       else 
         do
           parse upper var inRec xCreator xName stats
           if (translate( left( xName, 2)) = 'PK' & , /* PK = primary key and */
               keyIndex.tName = '') | ,               /* key index not already set or */
              translate( xName) = keyIndex.tName then /* this is the alternate index for reorg key */
             keyIndex.tName = xCreator'.'xName        /* save index creator.name */
           reOrgInd = subword( stats, words(stats))   /* last blank delimited string */
           if pos( '*', reOrgInd) > 0  & ,            /* index not optimally organized */
              ignoreIndex.tName.xName = 0 & ,         /* and not counter to proper organization */
              toBeReorged.tName = 0 then              /* table not already flagged */
             do                                       /* flag table as reorg candidate */
               ro = ro + 1
               toBeReorged.ro = creator'.'tName
               toBeReorged.tName = 1
             end
         end
     end 
   else inIndexSection = 0
 end qq /* do while inIndexSection */
 junkRC = lineOut( inFile) /* close the file */
 toBeReorged.0 = ro

 if toBeReorged.0 > 0 | runStats.0 > 0 then
   do
     if outFile = '' then outFile = sysTempFileName( 'reOrg???.cmd')
     j = 0
     j=j+1; line.j = '/* TeamConnection recommended re-org/runstats' date() time() '*/'
     j=j+1; line.j = ' parse arg familyName .'
     j=j+1; line.j = " if familyName = '' then familyName = 'tc'"
     j=j+1; line.j = ' "db2 connect to" familyName'
     if run then "db2 connect to tc"
     if toBeReorged.0 = 0 then
       say 'no tables flagged for reorganization.'
     else
       do i = 1 to toBeReorged.0
         parse var toBeReorged.i creator '.' tName
         db2Cmd = 'db2 reorg table' toBeReorged.i 'index' keyIndex.tName
         j=j+1; line.j = ' "'db2Cmd '"'
         if run then db2Cmd
       end
     do i = 1 to runStats.0
       tName = runStats.i
       db2Cmd = 'db2 runstats on table' tName 'with distribution and detailed indexes all'
       j=j+1; line.j = ' "'db2Cmd '"'
       if run then db2Cmd
     end
     if run then call rebindPackages
     db2Cmd = 'db2 connect reset'
     j=j+1; line.j = ' "'db2Cmd '"'
     if run then db2Cmd
     else /* not run */
       do
         j=j+1; line.j = 'Exit'
        
         outRC = lineOut( outFile, line.1, 1)
         if outRC = 0 then
           do
             say 'The following has been written to' outFile':' 
             say line.1
           end
         do i = 2 to j while outRC = 0
           outRC = lineOut( outFile, line.i)
           if outRC = 0 then say line.i
         end i
         if outRC = 0 then
           say 'The preceding was written to' outFile'.' 
       end
   end
 else say 'There was no indication found in' inFile 'that any tables are populated at this time!' 
 parse arg args
 parse source . . execName
 say execName args
 say 'completed at' time()
Exit

initKeyIndexes: 
   keyIndex. = ''
   keyIndex.FHCAUTHORITY    = 'FHCAUTHACTNAME'     /* not PK */
   keyIndex.FHCCONFIG       = 'FHCCNFGTYPENAMEDEP' /* not PK */
   keyIndex.FHCDEFECT       = 'FHCDEFECTNAME'      /* not PK */
   keyIndex.FHCUSER         = 'FHCUSERLOGIN'       /* not PK */

   ignoreIndex. = 0
   ignoreIndex.FHCAUTHORITY.FHCAUTHORITYNAME = 1
   ignoreIndex.FHCINTEREST.FHCINTERESTACTION = 1

   ignoreIndex.FHCCONFIG.PKFHCCONFIG = 1

   ignoreIndex.FHCDEFECT.FHCDEFECTDUPLICATE = 1 
   ignoreIndex.FHCDEFECT.FHCDEFECTOSRLCMPID = 1 
   ignoreIndex.FHCDEFECT.FHCDEFECTREFERENCE = 1 
   ignoreIndex.FHCDEFECT.FHCDEFECTSRLORGNID = 1 
   ignoreIndex.FHCDEFECT.FHCDEFECTSRLOWNRID = 1 
   ignoreIndex.FHCDEFECT.PKFHCDEFECT        = 1 

   ignoreIndex.FHCUSER.PKFHCUSER = 1 
 return

loadRexxFunctions: procedure
   parse source opSys how file .
   if rxFuncQuery('SysLoadFuncs') Then
     do;
       call rxFuncAdd 'SysLoadFuncs', 'REXXUTIL', 'SysLoadFuncs';
       rc = sysLoadFuncs();
     end;
   else rc = sysLoadFuncs();
 return

rebindPackages:
'db2 rebind package tcbuild.adlink   '
'db2 rebind package tcbuild.admerge  '
'db2 rebind package tcbuild.adobject '
'db2 rebind package tcbuild.adsqlsvr '
'db2 rebind package tcbuild.adview   '
'db2 rebind package tcbuild.advroot  '
'db2 rebind package tcbuild.bredidl  '
'db2 rebind package tcbuild.dvnt     '
'db2 rebind package tcbuild.fhcaccss '
'db2 rebind package tcbuild.fhccntxt '
'db2 rebind package tcbuild.fhcconfg '
'db2 rebind package tcbuild.fhcfgfld '
'db2 rebind package tcbuild.fhcpath  '
'db2 rebind package tcbuild.fhcuser  '
'db2 rebind package tcbuild.fhcversn '
'db2 rebind package tcbuild.fixup    '
'db2 rebind package tcbuild.session  '
'db2 rebind package tcbuild.tcbase   '
'db2 rebind package tcbuild.tcbuild  '
'db2 rebind package tcbuild.tccollc  '
'db2 rebind package tcbuild.tcenvy   '
'db2 rebind package tcbuild.tcmod    '
'db2 rebind package tcbuild.tctrans  '
'db2 rebind package tcbuild.xml      '
return 0

