script monitoring record count 1

[watergrinder]

In continuation to thread822-811945, I have come up with this script in order to check if the counts are steadily increasing. It is working.

I also have the following questions

1) A txt file is being used for each table. i.e these txt files store 5 count(*) values. Ex: 10 20 30 40 50 etc .. and they reside
in the directory where scripts are. Can I use txt files like this? Ghodmode originally used txt files in his solution ...
2) How to create txt files from the script? Ex: I want to create table1.txt dynamically from the script. Idea is that
whenever a new table is added to dat, a new txt file will automatically be created in order to store the counts.
3) Please comment on the quality of script

Thanks a lot in advance!

#!/usr/bin/sh
. sqlplus.sh

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

check_tab_status ()
{  
  set -A checkcounts $* 
  x=${#checkcounts[*]}
  eval mailfile=\$$(($# -1))
  eval tabname=\$$(($#))
  if [ ${checkcounts[$x-3]} -ne 0 ]
  then
       i=0
       j=0
       while [ $i -lt $x-3 ]
       do
          if [ ${checkcounts[$i+1]} -gt ${checkcounts[$i]} ]
          then
               j=`expr $j + 1`
          fi 
          i=`expr $i + 1`
       done
  fi # End if for NE check
  if [ $j -eq $x-3 ]
  then
       echo "TABLE $tabname Steadily Increasing" >> $mailfile
  fi
}

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


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

calc_tab_counts ()
{
# Temporary files. 
COUNTFILE="/tmp/$2.txt"  # tab count(*) data is stored here
SQLTEMP="/tmp/montabs.out"

set -A allcounts $(<$COUNTFILE)  # Initialize array allcounts from countfile

# Count the number of elements in allcounts. If it is less than five then ..... ???
x=${#allcounts[*]}
if [ $x -lt 5 ]
then
     set -A allcounts 0 0 0 0 0
fi 

connect_oracle_sqlplus aaa bbb ccc $SQLTEMP "select count(*) from $2"  # Connect to oracle.

# Find the current d/b count(*). 
CURRENT_DB_COUNT=`cat $SQLTEMP | tr -d ' ' | sed /^$/d`

# Add the current d/b count, drop the oldest count.
i=0
x=${#allcounts[*]}
while [ $i -lt $x ]
do
   allcounts[$i]=${allcounts[$i+1]}
   i=`expr $i + 1`
done
allcounts[$x]=$CURRENT_DB_COUNT

# If current d/b count is zero, exit the script. No further processing is needed.
if [ $CURRENT_DB_COUNT -eq 55 ]
then
     exit 0
fi


check_tab_status ${allcounts[*]} $1 $2 # Pass the count(*), mailfile and tab name 
#rm $SQLTEMP # Remove tmp file

echo ${allcounts[*]} > $COUNTFILE  # Update countfile
}

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


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

monitor_tab_counts ()
{
cat montabs.dat | awk '{ print $1 }' | while read TABLE
do
calc_tab_counts $1 "$TABLE"  # Pass mailfile, table name as parameters
done
}

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

 

[watergrinder]

I just noticed this was in a wrong place:

echo ${allcounts[*]} > $COUNTFILE # Update countfile

Any comments or suggestions would be helpful. Thanks again.


Updated function:

calc_mq_counts ()
{
# Temporary files. 
COUNTFILE="/tmp/$2.txt"  # MQ count(*) data is stored here
SQLTEMP="/tmp/monmqs.out"

set -A allcounts $(<$COUNTFILE)  # Initialize array allcounts from countfile

# Count the number of elements in allcounts. If it is less than five then ..... ???
x=${#allcounts[*]}
if [ $x -lt 5 ]
then
     set -A allcounts 0 0 0 0 0
fi 

connect_oracle_sqlplus sop zoo FHSOP $SQLTEMP "select count(*) from lec"  # Connect to oracle.

# Find the new count(*). 
CURRENT_DB_COUNT=`cat $SQLTEMP | tr -d ' ' | sed /^$/d`

# Add current d/b count, drop the oldest count.
i=0
x=${#allcounts[*]}
while [ $i -lt $x ]
do
   allcounts[$i]=${allcounts[$i+1]}
   i=`expr $i + 1`
done
allcounts[$x]=$CURRENT_DB_COUNT

echo ${allcounts[*]} > $COUNTFILE  # Update countfile

# If current d/b count is zero, exit the script. No further processing is needed.
if [ $CURRENT_DB_COUNT -eq 55 ]
then
     exit 0
fi

check_mq_status ${allcounts[*]} $1 $2 # Pass the count(*), mailfile and mq name 

#rm $SQLTEMP # Remove tmp file

}

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

 

[watergrinder]

Could anybody throw some light on this please?

 

[aigles]

Try and adapt the following (non tested) version of your script (arrays not used).
Files asscociated with new tables are created by the script.

# check_tab_status
# Args: $1  = Mail file
#       $2  = Table
#       $3- = Last record counts
# Output: Message add to file $2 if counts steadily increasing

check_tab_status ()
{  
  # Get args.
  local MailFile=$1
  local Table=$2
  shift 2 # $1- = Last counts

  # Test if counts are steadily increasing
  i=1
  while [ $i -lt $# ]
  do    
     (( j += 1 ))
     eval c1=\$$i
     eval c2=\$$j
     if [ $c1 -eq 0 -o $c2 -eq 0 ]
        then delta=0
        else (( delta = c2 - c1 ))
     fi
     [ $delta -le 0 ] && break
     (( i += 1 ))
   done

   # If delta is greater than 0, counts steadily increasing
   if [ $delta -gt 0 ]; then
        echo "TABLE $tabname Steadily Increasing" >> $mailfile
   fi
}

# calc_tab_counts
# Args:   $1 = Mailfile
#         $2 = Table
# Input:  /tmp/$2.txt = Last record counts for table
# Output: /tmp/$2.txt = Updated

calc_tab_counts ()
{
   local MailFile Table
   local CountFile SqlTemlp RecCount

   # Get args
   MailFile=$1
   Table=$2

   # Temporary files. 
   CountFile="/tmp/$2.txt"  # tab count(*) data is stored here
   SqlTemp="/tmp/montabs.out"


   # Connect to Oracle and get the record count for the table
   connect_oracle_sqlplus aaa bbb ccc $SQLTEMP "select count(*) from $Table" 
   RecCount=`awk 'NF==1'`

   # Build and memorize the 5 last record count
   # Get last counts ($1, $2, ...)
   set -- 0 0 0 0 0 $(cat $CountFile 2>/dev/null) $RecCount
   shift $(( $# - 5 ))
   echo $* > $CountFile

   # If current d/b count is not zero,i check for count evolution
   [ $RecCount -ne 0 ] && check_tab_status $MailFile $Table $*

}

# monitor_tab_counts
# Args: $1 = Mailfile
# Input: montabs.dat = List of tables to monitor (one per line)
monitor_tab_counts ()
{
   while read TABLE filler
   do
      calc_tab_counts $1 "$TABLE" 
   done < montabs.dat 
}

Jean Pierre.

 

[watergrinder]

Thank you, can you tell what these separate statements are doing?

1) check_tab_status ()
shift 2 # $1- = Last counts

2) calc_tab_counts ()
RecCount=`awk 'NF==1'`

 

[aigles]

[tt]shift 2[/tt]

Shift left parameters. $3 becomes $1, $4 becomes $2 ....

[tt]RecCount=`awk 'NF==1' $SQLTEMP`[/tt]

Sorry i forgot the input file...
The awk output only lines with only one field.


Jean Pierre.