Rebuild Indexes on Sybase

Shell Script  to re-build indexes

Note You may need to chnage some params as per your env

#!/bin/ksh

##set -x

#

################################################################################

#

# This script takes sql scripts as input to run clustered index rebuilds on various tables. Note, it # DOES NOT reverse-engineer tables, it's hardcoded for the clustered indexes as they exist 
# today, with the assumption that they  will not change. It also does not necessarily do all
# indexes. It is driven by the sql scripts it takes as input, so they can have as many or as few    # tables/indexes as desired. The ultimate goal was to provide flexibility in scheduling, to          # spread the work over several days and/or weeks as needed.

#

################################################################################

#

#

######################################

## Set the Server and Database Name ##

######################################

ENV_DIR="/sybase_sa"

SERVER=`echo $1 | dd conv=ucase 2>/dev/null`

DATABASE=$2

INFILE=$3

ENVFILE="$ENV_DIR/.$SERVER.env"

##

## Check for correct number of parameters

##

if [ $# -lt 3 ]

then

    echo "Usage: $0 <ASE Server Name> <Database Name> <Input script name>"

    exit 1

fi

##

## Check for existance of ASE file

##

if [ ! -s $ENVFILE ]

then

        echo "$ENVFILE.env does not exist ..."

        echo "Script exiting ..."

        exit 1

fi

##

## Source environment (ASE) file

##

. $ENVFILE

#  SET ENVIRONMENT VARIABLES

#

VERSION="Version 1.0"

#SENDMESSAGE=1 #1=on 0=off

#SQLSWITCH=1 #1=on 0=off

#DELHIST=1 #1=on 0=off

SA_PW=`$GET_PW $SERVER`

LNEW=`date +%m""%d""%y"-"%I":"%M%p`

BINDIR="${SYB_SA}/index/bin"

OUTFILE="${SYB_SA}/index/logs/rebuild_clustered.${SERVER}.${DATABASE}.${INFILE}.out"

LOGFILE="${SYB_SA}/index/logs/rebuild_clustered.${SERVER}.${DATABASE}.${INFILE}.log"

echo " "> ${LOGFILE}

#

## MAIN PROCESSING

#

# run the index rebuild sql input file

$ISQL_CMD -Usa -S$SERVER -w132 -D${DATABASE} -i${BINDIR}/${INFILE} -o${OUTFILE}  -P${SA_PW}

#

## Check for Errors and notify if necessary

#

if [ ! -s ${OUTFILE} ]

then

  echo "${OUTFILE} does not exist" >> $LOGFILE

else

  ERR=`egrep "Error|Msg|FAILED CREATING|CT-LIBRARY" $OUTFILE | wc -l`

fi

echo "Check log file '${OUTFILE}' for more details." >>${LOGFILE}

egrep 'Error|Msg|FAILED CREATING|CT-LIBRARY' ${OUTFILE}  >>${LOGFILE}

if [ $ERR -ge 1  ]

   then

      MSGERR="CRITICAL ISSUE - Index rebuild failed for $SERVER, database $DATABASE , Check email or '${OUTFILE}' for more details"

      $SYB_BIN/send_messages $SERVER "$OUTFILE" "$MSGERR" BOTH ALL

    else

      MSGERR="Successful - Index rebuild for $SERVER, database $DATABASE, Check email or '${OUTFILE}' for more details"

      $SYB_BIN/send_messages $SERVER "$OUTFILE" "$MSGERR" MAIL ALL

fi

#

## Move output file to timestamped output file

#

mv ${OUTFILE} ${OUTFILE}.$LNEW

#remove old logs files that are older than 30 days

#

RUTHERE=`find $SYB_SA/index/logs -mtime +30 -name "${OUTFILE}*" | wc -l`

if [ ${RUTHERE} -ge 1 ]

then

  find $SYB_SA/index/logs -mtime +30 -name "${OUTFILE}*" -exec /bin/rm -fr {} \;

fi

exit