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
########################
