Caché License Key monitoring

One of our Caché database servers stopped working last week. Because, the License key got expired. There was an oversight in checking the License key expiration date.

To avoid any such issues in future, I coded a shell script that runs everyday on Caché database servers and does the following:

       1. Compare License key expiration date with future date (current date + 1 week)

       2. Trigger an email to DBA if the License key expiration date is less than the future date

I'm sure that the Date handling (in the script) could be done in a better way. Still working on improving my scripting skills.

The script as below.

#!/bin/ksh

#coder: Bharath Nunepalli

LICKEY_EXP=/home//lickey_exp

LICKEY_EXP1=/home/lickey_exp1

LICKEY_EXP2=/home/lickey_exp2

LICKEY_EXP3=/home/lickey_exp3

LICKEY_EXP_DATE=/home/lickey_date

timezone=$(date +%Z)

NEXT_WEEK=$(TZ=$timezone-168 date +"%Y%m%d")

csession cache "^CKEY" | grep -i ExpirationDate > $LICKEY_EXP

 cat $LICKEY_EXP | read LINE

  LIC_EXP_DT=$(echo $LINE | awk '{print $3}')

echo $LIC_EXP_DT|awk -F'/' '{ print $1 }' > $LICKEY_EXP1

 cat $LICKEY_EXP1 | read LINE

  LIC_EXP_DT1=$(echo $LINE | awk '{print $1}')

   if (( $LIC_EXP_DT1 < 10 )) then

     LIC_EXP_MON=$(echo "0"$LIC_EXP_DT1)

   fi

echo $LIC_EXP_DT|awk -F'/' '{ print $2 }' > $LICKEY_EXP2

 cat $LICKEY_EXP2 | read LINE

  LIC_EXP_DT2=$(echo $LINE | awk '{print $1}')

   if (( $LIC_EXP_DT2 < 10 )) then

     LIC_EXP_DAY=$(echo "0"$LIC_EXP_DT2)

   fi

echo $LIC_EXP_DT|awk -F'/' '{ print $3 }' > $LICKEY_EXP3

 cat $LICKEY_EXP3 | read LINE

  LIC_EXP_DT3=$(echo $LINE | awk '{print $1}')

echo $LIC_EXP_DT3$LIC_EXP_MON$LIC_EXP_DAY > $LICKEY_EXP_DATE

 cat $LICKEY_EXP_DATE | read LINE

  LIC_EXP_DATE=$(echo $LINE | awk '{print $1}')

  if (( $LIC_EXP_DATE < $NEXT_WEEK )) then

    mail -s "LICENSE KEY ABOUT TO EXPIRE ON $HOSTNAME "  <email ID>

  fi

  else exit

exit

The above script can be coded as below.

#!/bin/ksh
#Coder: Bharath Nunepalli

DB_STATUS=/home/db_status
ccontrol list | grep -i running > $DB_STATUS
if [[ -s $DB_STATUS ]] then
  #collect expiration date from cache
  EXPR_DATE=$(csession cache "^CKEY"|grep -i ExpirationDate|awk '{print $3}'|awk -F'/' '{printf("%d%02d%02d\n",$3,$2,$1)}')
  #get future date
  NEXT_WEEK=$(TZ="$(date +%Z)-168" date "+%Y%m%d")
  # compare date
  if [[ $EXPR_DATE -lt $NEXT_WEEK ]] then
    echo mail -s "LICENSE KEY ABOUT TO EXPIRE ON $HOSTNAME "  <email ID>
    exit 0
   else exit 0
  fi
 else exit 0
fi

Caché Journaling Status Monitoring

In InterSystems Caché, Journals are the files that record the changes made to the Databases. 

The process are recording the Database changes is called Journaling.

Journaling Uses:

1. Record of changes made to Data

2. Supports restoring Data from backup

3. Supports Transaction processing

4. Protects Application Data

5. Prevents Data loss or inconsistent Data

6. Required for

a.      Shadowing

b.      Mirroring

c.       Shared-disk cluster configurations

In our Caché on AIX architecture, Journals are continuously shipped from Production Server (called as Primary Server) to Reporting Server (called as Secondary Server). Journal files are mounted on /journal file system.

Whenever the /journal file system becomes full, Journaling daemon tries multiple times to continue with the activity before getting Disabled. Journaling will not be Enabled automatically after additional Storage is added to /journal file system, and has to be done manually.

There is no direct approach for pro-active monitoring of the Journaling Status. I coded a simple Shell Script that’ll help in monitoring the Status. The Script is scheduled to run every 30 minutes, and email if the Journaling Status is found as Disabled.

#!/bin/ksh

DB_JRN_DET=/home/rfo7936/db_jrn_det

csession cache "Status^JOURNAL"    > $DB_JRN_DET

cat $DB_JRN_DET | grep -i enabled  > $DB_JRN_DET

if [[ -s $DB_JRN_DET ]] then RC=0

 else RC=1

fi

if (( $RC > 0 )) then

    mail -s "JOURNALING is disabled on $HOSTNAME"  <email ID>

    exit $RC

  else

    exit $RC

fi

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

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