How much transaction log is left ?

What if a rouge transaction gets on your system and start using my all your transaction logs, don’t you want to be altered ?
Once in a while I get certain ETL or applications that goes into a update, insert loop that just keeps running without committing causing the transaction logs to get full . The worst part of this is, genuine transactions and application gets affected as well and  the database goes through a rollback.  But sometimes there might be multiple mass update or delete application running and if the batch size is not configured properly, it might also fill up the log.  In  such cases I do not want to hunt down the developer and don’t want to him to take him to “Skull Island” for a “picnic” .   So I wrote this script to alert me when the Percentage of transaction log usage goes above 79%, so either I can track down the application that is doing this and take appropriate actions  :

1. Disconnect the rogue application and book a one way ticket to skull island  or
2.  Increase the secondary log and notify the developer to resize the batch size.

Here is the script to download  :log_usage.sh

Below is the script for a quick review , but I suggest you download the script from the above link

export PATH=$PATH:${HOME}/sqllib/bin/ 
. ${HOME}/sqllib/db2profile 
Datetime="`date +%Y-%m-%d-%H:%M:%S`" 
OutputDir=`echo "/home/db2inst3/report"` 
dbas="dbas@tecshatwork.com" 
echo "select 'MYDB log is greater than '||rtrim(char(int((float(total_log_used) /
 float(total_log_used+total_log_available))*100)))||'%' 
from table(snapshot_database('',-1)) as sntable where int((float(total_log_used) /
 float(total_log_used+total_log_available))*100) >79;" > $OutputDir/logspace_usage.sql

db2 connect to MYDB; 
if [ "$?" = "0" ] 
          then 
          db2 -txf $OutputDir/logspace_usage.sql >> $OutputDir/logspace.tmp 2> /dev/null 
else 
           echo "Error connecting to database -> MYDB at $Datetime" >> $OutputDir/getlogspace.err 
fi 
if [ $(cat $OutputDir/logspace.tmp|wc -l) -gt 1 ] 
 then 
   for emailaddr in $dbas 
     do 
        echo "alert has been send for - > MYDB" 
          mailx -s "Space issue in MYDB - MYSRV" $emailaddr < $OutputDir/logspace.tmp 
     done 
else 
     echo "Yippeee its good for now !" 
     rm $OutputDir/logspace.tmp 
fi