/* REXX exec to analyze the results of db2 reorgchk to determine which tables to reorg */

 parse arg inFile .

 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

 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
     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 toBeReorged.0 = 0 then
       say 'no tables flagged for reorganization.'
     else
       do i = 1 to toBeReorged.0
         parse var toBeReorged.i creator '.' tName
         j=j+1; line.j = ' "db2 reorg table' toBeReorged.i 'index' keyIndex.tName '"'
       end
     do i = 1 to runStats.0
       tName = runStats.i
       j=j+1; line.j = ' "db2 runstats on table' tName 'with distribution and detailed indexes all "'
     end
     j=j+1; line.j = ' "db2 connect reset"'
     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
 else say 'There was no indication found in' inFile 'that any tables are populated at this time!' 
Exit

initKeyIndexes: 
   keyIndex. = ''
   keyIndex.FHCDEFECT       = 'FHCDEFECTNAME' /* not PK     */

   keyIndex.FHCUSER         = 'FHCUSERLOGIN' /* not PK      */

   ignoreIndex. = 0
   ignoreIndex.FHCAUTHORITY.FHCAUTHORITYACTION = 1
   ignoreIndex.FHCINTEREST.FHCINTERESTACTION = 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
