Bharath's Tech Blog

Caché Database Size Monitoring

We use a vendor supplied application called Artiva for maintaining Medical bills collection details, hosted on AIX servers. Artiva uses InterSystems Caché as the DBMS.

There is AIX File systems (on which Caché Databases are mounted) size monitoring alerts mechanism, but no Caché Databases size monitoring alerts mechanism in place when I started as DBA. InterSystems and the Artiva’s vendor didn’t provide any straight forward approach for Databases size monitoring.

All Caché Databases have the following Size details.

  1. Current size of the Database

  2. Expansion size                          – how much MB can the Database expand further

  3. Maximum size of the Database – with expansion, Database can grow till this size

I coded a Shell script that will do the following.

  1. Search for the Artiva-Caché Databases

  2. Calculate the Total Free Storage percentage, available for the Database to grow

  3. Send an email if any Database with Total Free Storage percentage is less than 10%

This Script is scheduled to be executed every 15 minutes, every day.

The Script is as follows.

 #!/bin/ksh

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

DB_FREE_SPC=/cache/cachedba/scripts/db_free_spc

DB_FREE_SPC_MOD=/cache/cachedba/scripts/db_free_spc_mod

DB_FREE_REPORT=/cache/cachedba/scripts/db_free_report

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

RC=0

#####Getting Database Size Details from Caché######

csession cache "ALL^%ZUTFREE" > $DB_FREE_SPC

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

cat $DB_FREE_SPC | grep -i / | grep -v Auto     > $DB_FREE_SPC

cat $DB_FREE_SPC | sed -n 's/%//p' > $DB_FREE_SPC_MOD

echo "--------------------------------------------------------------------------------------------------------------" > $DB_FREE_REPORT

echo " DB_NAME \t\t  CUR_DB_SIZE \t\t FREE_SPC_PCT_IN_DB         MAX_DB_SIZE \t      TOT_FREE_SPC_PCT \t      FREE_SPC_IN_FS" >> $DB_FREE_REPORT

echo "--------------------------------------------------------------------------------------------------------------"  >> $DB_FREE_REPORT

cat $DB_FREE_SPC_MOD |

while read LINE

do

  DB_NM=$(echo $LINE | awk '{print $2}' )

  DB_CUR_SIZ=$(echo $LINE | awk '{print $5}')

  DB_FREE_PCT=$(echo $LINE | awk '{print $4}')

  DB_MAX_SIZ=$(echo $LINE | awk '{print $6}')

  FS_FREE_SIZ=$(echo $LINE | awk '{print $8}')

  DB_CUR_SIZ=$(echo ${DB_CUR_SIZ%?})

  DB_MAX_SIZ=$(echo ${DB_MAX_SIZ%?})

  ((DB_TOT_FREE_PCT = 100 - DB_CUR_SIZ / DB_MAX_SIZ *100))

 if (( $DB_TOT_FREE_PCT < 10 )) then

      (( RC += 1 ))

      echo " $DB_NM  \t\t\t $DB_CUR_SIZ   \t\t\t $DB_FREE_PCT  \t\t\t $DB_MAX_SIZ  \t\t\t $DB_TOT_FREE_PCT  \t\t\t $FS_FREE_SIZ"  >> $DB_FREE_REPORT

 fi

done

#####Email step#####

if (( $RC > 0 )) then

    mail -s "DATABASE SIZE ALERT ON $HOSTNAME"  <email ID> < $DB_FREE_REPORT

    exit $RC
  else exit $RC

fi

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