Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Problem in shell script db2 query for fetching and comparing records

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problem in shell script db2 query for fetching and comparing records
# 1  
Old 10-27-2013
Code Problem in shell script db2 query for fetching and comparing records
hi..

my requirement is following:

i want to have a shell script that queries a table i.e. fcm_auth in database fcmcore (I use db2) to get no of rows present for a given combination eg org and logo for past 5-10 mins interval. You can take some values of org(numerical values) and corresponding logo values
Now I need to verify no of rows for each org and logo combination separately with a predefined value in a file.
and also send an email when its less than predefine value in file.
subject line of email has to be "issue or status"
I also need to print " start and end timestamp" used in query as first line of body of email.
now I need to vary predefined threshold limit based on time,so when building file I may take 3-4 ranges at org level.
eg
predefined threshold-1: any numerical value (6 am -6 pm)
predefined threshold-2: any numerical value (6 pm -9 pm)
predefined threshold-3: any numerical value (9 pm -12 am)
predefined threshold-4: any numerical value (12 am -6 am)

Kindly suggest a cope snippet for the above ASAP.
# 2  
Old 10-27-2013
Hi Nishant,

Kindly share the script written by you so that we can fine tune it as per your requirement.

Thanks & Regards,
Pradeep Agarwal
# 3  
Old 10-28-2013
Code MY script
Code:
#!/usr/bin/sh
#
# PEFXM1000.sh  
#
# C h a n g e   L o g
# -------------------------------
# 10/24/2013 RP Initial setup
JobNumber=1000
#Runlog=/sasvol01/hsbc/scheduling/logs/`date +%Y%m%d`"-"run.log
Runlog=/home/fcmusr/Rahul/new/`date +%Y%m%d`"-"run.log
MaintFile=/sasvol01/hsbc/scheduling/MaintFile/raptor.maint
CountFile=/home/fcmusr/Rahul/new/count
# echo `date +%Y%m%d-%H%M%S`": "Terminated the script before the execution of SAS command for job:$JobNumber >> $Runlog
# exit 0
#check if maintenance file exists
while true
do
        if [ -e $MaintFile ]
        then
                echo `date +%Y%m%d-%H%M%S`": "PEFXM$JobNumber on Hold - System is in maintenance mode >> $runlog
                chmod 777 $Runlog
                sleep 3600                      
        else
                break                   
        fi
done
#Check if previous instance of the script is running
ps -ef | grep PEFXM1000 | grep -v grep | grep '/usr/bin/sh' | wc -l | read count_1000
if [ $count_1000 -gt 1 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "PEFXM$JobNumber.sh is already executing, Terminating current run >> $Runlog
        chmod 777 $Runlog       
        exit 0
fi
 
maillist="Nishant.Jha@xxx"
 
db2 "connect to fcmcore" > /dev/null
if [ $? -ne 0 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - DB connection failure >> $Runlog
        chmod 777 $Runlog
        exit 12
fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='226' "|read count_226
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 226 >> $Runlog
        chmod 777 $Runlog
        db2 "terminate"
        exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='004' "|read count_004
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 004 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='300' "|read count_300
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 300 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='601' and RQO_LOGO in( '501', '502', '504', '601', '602', '
603', '604', '605', '606','608', '611', '612', '614', '618', '619', '624', '631', '634', '641', '645', '684', '691', '692', '
693') "|read count_601
        if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 601 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
Threshold_226=`head -1 $CountFile |awk -F= '{ print $2 }'`
Threshold_300=`head -2 $CountFile |tail -1 |awk -F= '{ print $2 }'`
Threshold_004=`head -3 $CountFile |tail -1 |awk -F= '{ print $2 }'`
Threshold_601=`head -4 $CountFile |tail -1 |awk -F= '{ print $2 }'`
 
       if [ $count_226 -lt $Threshold_226 ] ; then
               echo " PEFXM1000 Count for RQO_ORG value 226 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 226 is less than Threshold " >> $Runlog
               let error_flag="1"
       else 
               echo " PEFXM1000 Count for RQO_ORG value 226 is $count_226 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 226 is $count_226 " >> $Runlog
               let error_flag="1" 
 
       fi
 
       if [ $count_004 -lt $Threshold_004 ] ; then
 
               echo " PEFXM1000 Count for RQO_ORG value 004 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 004 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 004 is $count_004  " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 004 is $count_004 " >> $Runlog
               let error_flag="1"
 
       fi
       if [ $count_300 -lt $Threshold_300 ] ; then
               echo " PEFXM1000 Count for RQO_ORG value 300 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 300 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 300 is $count_300 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 300 is $count_300 " >> $Runlog
               let error_flag="1"
 
       fi
       if [ $count_601 -lt $Threshold_601 ] ; then
 
               echo " PEFXM1000 Count for RQO_ORG value 601 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 601 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 601 is $count_601 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 601 is $count_601 " >> $Runlog
               let error_flag="1"
 
       fi
       echo "Mail Sent">>$Runlog
       mailsub="Test mail send by Rahul Purwar Issue"
       if [ $error_flag -eq 1 ] ; then
          outputFile="PEFXM1000.out"
                (
                        echo "To: $maillist"
                        echo "MIME-Version: 1.0"
                        echo "Subject: $mailsub"
                        echo "Content-Type: text/html"   
                        echo "<HTML><BODY><PRE>"
                        cat $outputFile 
                        echo "</PRE></BODY></HTML>"
                ) | sendmail -t
       fi
       echo "******************************************************************************" >>$Runlog
       rm -f PEFXM1000.out  
db2 "terminate" > /dev/null
if [ $? -ne 0 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - DB termination failure >> $Runlog
        chmod 777 $Runlog
        exit 12
fi
 
exit 0

---------- Post updated at 01:20 PM ---------- Previous update was at 11:19 AM ----------

Quote:
Originally Posted by nishantrefound
Code:
#!/usr/bin/sh
#
# PEFXM1000.sh  
#
# C h a n g e   L o g
# -------------------------------
# 10/24/2013 RP Initial setup
JobNumber=1000
#Runlog=/sasvol01/hsbc/scheduling/logs/`date +%Y%m%d`"-"run.log
Runlog=/home/fcmusr/Rahul/new/`date +%Y%m%d`"-"run.log
MaintFile=/sasvol01/hsbc/scheduling/MaintFile/raptor.maint
CountFile=/home/fcmusr/Rahul/new/count
# echo `date +%Y%m%d-%H%M%S`": "Terminated the script before the execution of SAS command for job:$JobNumber >> $Runlog
# exit 0
#check if maintenance file exists
while true
do
        if [ -e $MaintFile ]
        then
                echo `date +%Y%m%d-%H%M%S`": "PEFXM$JobNumber on Hold - System is in maintenance mode >> $runlog
                chmod 777 $Runlog
                sleep 3600                      
        else
                break                   
        fi
done
#Check if previous instance of the script is running
ps -ef | grep PEFXM1000 | grep -v grep | grep '/usr/bin/sh' | wc -l | read count_1000
if [ $count_1000 -gt 1 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "PEFXM$JobNumber.sh is already executing, Terminating current run >> $Runlog
        chmod 777 $Runlog       
        exit 0
fi
 
maillist="Nishant.Jha@xxx"
 
db2 "connect to fcmcore" > /dev/null
if [ $? -ne 0 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - DB connection failure >> $Runlog
        chmod 777 $Runlog
        exit 12
fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='226' "|read count_226
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 226 >> $Runlog
        chmod 777 $Runlog
        db2 "terminate"
        exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='004' "|read count_004
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 004 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='300' "|read count_300
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 300 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='601' and RQO_LOGO in( '501', '502', '504', '601', '602', '
603', '604', '605', '606','608', '611', '612', '614', '618', '619', '624', '631', '634', '641', '645', '684', '691', '692', '
693') "|read count_601
        if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 601 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
Threshold_226=`head -1 $CountFile |awk -F= '{ print $2 }'`
Threshold_300=`head -2 $CountFile |tail -1 |awk -F= '{ print $2 }'`
Threshold_004=`head -3 $CountFile |tail -1 |awk -F= '{ print $2 }'`
Threshold_601=`head -4 $CountFile |tail -1 |awk -F= '{ print $2 }'`
 
       if [ $count_226 -lt $Threshold_226 ] ; then
               echo " PEFXM1000 Count for RQO_ORG value 226 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 226 is less than Threshold " >> $Runlog
               let error_flag="1"
       else 
               echo " PEFXM1000 Count for RQO_ORG value 226 is $count_226 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 226 is $count_226 " >> $Runlog
               let error_flag="1" 
 
       fi
 
       if [ $count_004 -lt $Threshold_004 ] ; then
 
               echo " PEFXM1000 Count for RQO_ORG value 004 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 004 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 004 is $count_004  " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 004 is $count_004 " >> $Runlog
               let error_flag="1"
 
       fi
       if [ $count_300 -lt $Threshold_300 ] ; then
               echo " PEFXM1000 Count for RQO_ORG value 300 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 300 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 300 is $count_300 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 300 is $count_300 " >> $Runlog
               let error_flag="1"
 
       fi
       if [ $count_601 -lt $Threshold_601 ] ; then
 
               echo " PEFXM1000 Count for RQO_ORG value 601 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 601 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 601 is $count_601 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 601 is $count_601 " >> $Runlog
               let error_flag="1"
 
       fi
       echo "Mail Sent">>$Runlog
       mailsub="Test mail send by Rahul Purwar Issue"
       if [ $error_flag -eq 1 ] ; then
          outputFile="PEFXM1000.out"
                (
                        echo "To: $maillist"
                        echo "MIME-Version: 1.0"
                        echo "Subject: $mailsub"
                        echo "Content-Type: text/html"   
                        echo "<HTML><BODY><PRE>"
                        cat $outputFile 
                        echo "</PRE></BODY></HTML>"
                ) | sendmail -t
       fi
       echo "******************************************************************************" >>$Runlog
       rm -f PEFXM1000.out  
db2 "terminate" > /dev/null
if [ $? -ne 0 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - DB termination failure >> $Runlog
        chmod 777 $Runlog
        exit 12
fi
 
exit 0


Also,
need to fetch and compare records based on CST time but our VDI i.e Virtual machine has inbuilt PST time.Devise a method to do that as well.

---------- Post updated at 05:06 PM ---------- Previous update was at 01:20 PM ----------

Quote:
Originally Posted by nishantrefound
Code:
#!/usr/bin/sh
#
# PEFXM1000.sh  
#
# C h a n g e   L o g
# -------------------------------
# 10/24/2013 RP Initial setup
JobNumber=1000
#Runlog=/sasvol01/hsbc/scheduling/logs/`date +%Y%m%d`"-"run.log
Runlog=/home/fcmusr/Rahul/new/`date +%Y%m%d`"-"run.log
MaintFile=/sasvol01/hsbc/scheduling/MaintFile/raptor.maint
CountFile=/home/fcmusr/Rahul/new/count
# echo `date +%Y%m%d-%H%M%S`": "Terminated the script before the execution of SAS command for job:$JobNumber >> $Runlog
# exit 0
#check if maintenance file exists
while true
do
        if [ -e $MaintFile ]
        then
                echo `date +%Y%m%d-%H%M%S`": "PEFXM$JobNumber on Hold - System is in maintenance mode >> $runlog
                chmod 777 $Runlog
                sleep 3600                      
        else
                break                   
        fi
done
#Check if previous instance of the script is running
ps -ef | grep PEFXM1000 | grep -v grep | grep '/usr/bin/sh' | wc -l | read count_1000
if [ $count_1000 -gt 1 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "PEFXM$JobNumber.sh is already executing, Terminating current run >> $Runlog
        chmod 777 $Runlog       
        exit 0
fi
 
maillist="Nishant.Jha@xxx"
 
db2 "connect to fcmcore" > /dev/null
if [ $? -ne 0 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - DB connection failure >> $Runlog
        chmod 777 $Runlog
        exit 12
fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='226' "|read count_226
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 226 >> $Runlog
        chmod 777 $Runlog
        db2 "terminate"
        exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='004' "|read count_004
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 004 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='300' "|read count_300
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 300 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='601' and RQO_LOGO in( '501', '502', '504', '601', '602', '
603', '604', '605', '606','608', '611', '612', '614', '618', '619', '624', '631', '634', '641', '645', '684', '691', '692', '
693') "|read count_601
        if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 601 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
Threshold_226=`head -1 $CountFile |awk -F= '{ print $2 }'`
Threshold_300=`head -2 $CountFile |tail -1 |awk -F= '{ print $2 }'`
Threshold_004=`head -3 $CountFile |tail -1 |awk -F= '{ print $2 }'`
Threshold_601=`head -4 $CountFile |tail -1 |awk -F= '{ print $2 }'`
 
       if [ $count_226 -lt $Threshold_226 ] ; then
               echo " PEFXM1000 Count for RQO_ORG value 226 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 226 is less than Threshold " >> $Runlog
               let error_flag="1"
       else 
               echo " PEFXM1000 Count for RQO_ORG value 226 is $count_226 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 226 is $count_226 " >> $Runlog
               let error_flag="1" 
 
       fi
 
       if [ $count_004 -lt $Threshold_004 ] ; then
 
               echo " PEFXM1000 Count for RQO_ORG value 004 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 004 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 004 is $count_004  " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 004 is $count_004 " >> $Runlog
               let error_flag="1"
 
       fi
       if [ $count_300 -lt $Threshold_300 ] ; then
               echo " PEFXM1000 Count for RQO_ORG value 300 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 300 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 300 is $count_300 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 300 is $count_300 " >> $Runlog
               let error_flag="1"
 
       fi
       if [ $count_601 -lt $Threshold_601 ] ; then
 
               echo " PEFXM1000 Count for RQO_ORG value 601 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 601 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 601 is $count_601 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 601 is $count_601 " >> $Runlog
               let error_flag="1"
 
       fi
       echo "Mail Sent">>$Runlog
       mailsub="Test mail send by Rahul Purwar Issue"
       if [ $error_flag -eq 1 ] ; then
          outputFile="PEFXM1000.out"
                (
                        echo "To: $maillist"
                        echo "MIME-Version: 1.0"
                        echo "Subject: $mailsub"
                        echo "Content-Type: text/html"   
                        echo "<HTML><BODY><PRE>"
                        cat $outputFile 
                        echo "</PRE></BODY></HTML>"
                ) | sendmail -t
       fi
       echo "******************************************************************************" >>$Runlog
       rm -f PEFXM1000.out  
db2 "terminate" > /dev/null
if [ $? -ne 0 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - DB termination failure >> $Runlog
        chmod 777 $Runlog
        exit 12
fi
 
exit 0

---------- Post updated at 01:20 PM ---------- Previous update was at 11:19 AM ----------




Also,
need to fetch and compare records based on CST time but our VDI i.e Virtual machine has inbuilt PST time.Devise a method to do that as well.
Code:
#!/usr/bin/sh
#
# PEFXM1000.sh  
#
# C h a n g e   L o g
# -------------------------------
# 10/24/2013 RP Initial setup
JobNumber=1000
#Runlog=/sasvol01/hsbc/scheduling/logs/`date +%Y%m%d`"-"run.log
Runlog=/home/fcmusr/Rahul/new/`date +%Y%m%d`"-"run.log
MaintFile=/sasvol01/hsbc/scheduling/MaintFile/raptor.maint
CountFile=/home/fcmusr/Rahul/new/count
# echo `date +%Y%m%d-%H%M%S`": "Terminated the script before the execution of SAS command for job:$JobNumber >> $Runlog
# exit 0
#check if maintenance file exists
while true
do
        if [ -e $MaintFile ]
        then
                echo `date +%Y%m%d-%H%M%S`": "PEFXM$JobNumber on Hold - System is in maintenance mode >> $runlog
                chmod 777 $Runlog
                sleep 3600                      
        else
                break                   
        fi
done
#Check if previous instance of the script is running
ps -ef | grep PEFXM1000 | grep -v grep | grep '/usr/bin/sh' | wc -l | read count_1000
if [ $count_1000 -gt 1 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "PEFXM$JobNumber.sh is already executing, Terminating current run >> $Runlog
        chmod 777 $Runlog       
        exit 0
fi
 
maillist="Rahul.Purwar@xx"
 
db2 "connect to fcmcore" > /dev/null
if [ $? -ne 0 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - DB connection failure >> $Runlog
        chmod 777 $Runlog
        exit 12
fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='226' and  CMX_CREATE_TIMESTAMP 
BETWEEN (current timestamp  - 5 MINUTES ) and current timestamp"|read count_226
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 226 >> $Runlog
        chmod 777 $Runlog
        db2 "terminate"
        exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='004' and CMX_CREATE_TIMESTAMP 
BETWEEN (current timestamp  - 5 MINUTES ) and current timestamp "|read count_004
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 004 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='300' and CMX_CREATE_TIMESTAMP 
BETWEEN (current timestamp  - 5 MINUTES ) and current timestamp" |read count_300
       if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 300 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
 
db2 -x "select count(RQO_ORG) from fcmcore.FCM_AUTH where RQO_ORG='601' and RQO_LOGO in( '501', '502', '504', '601', '602', '
603', '604', '605', '606','608', '611', '612', '614', '618', '619', '624', '631', '634', '641', '645', '684', '691', '692', '
693') and CMX_CREATE_TIMESTAMP BETWEEN (current timestamp  - 5 MINUTES ) and current timestamp"|read count_601 
        if [ $? -ne 0 ] ; then
               echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - while fetching count for RQO_ORG 601 >> $Runlog
               chmod 777 $Runlog
               db2 "terminate"
               exit 12
        fi
db2 "select ' ', current date,current time -5 MINUTES,'********************',current date,current time FROM sysibm.sysdummy1"
|head -4|tail -1>> PEFXM1000.out
echo "\n"  >> PEFXM1000.out
date '+%H%M'|read CurrTime
echo $CurrTime
 
Threshold_226=`head -1 $CountFile |awk -F= '{ print $2 }'`
Threshold_300=`head -2 $CountFile |tail -1 |awk -F= '{ print $2 }'`
Threshold_004=`head -3 $CountFile |tail -1 |awk -F= '{ print $2 }'`
Threshold_601=`head -4 $CountFile |tail -1 |awk -F= '{ print $2 }'`
 
       if [ $count_226 -lt $Threshold_226 ] ; then
               echo " PEFXM1000 Count for RQO_ORG value 226 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 226 is less than Threshold " >> $Runlog
               let error_flag="1"
       else 
               echo " PEFXM1000 Count for RQO_ORG value 226 is $count_226 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 226 is $count_226 " >> $Runlog
               let error_flag="1" 
 
       fi
 
       if [ $count_004 -lt $Threshold_004 ] ; then
 
               echo " PEFXM1000 Count for RQO_ORG value 004 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 004 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 004 is $count_004  " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 004 is $count_004 " >> $Runlog
               let error_flag="1"
 
       fi
       if [ $count_300 -lt $Threshold_300 ] ; then
               echo " PEFXM1000 Count for RQO_ORG value 300 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 300 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 300 is $count_300 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 300 is $count_300 " >> $Runlog
               let error_flag="1"
 
       fi
       if [ $count_601 -lt $Threshold_601 ] ; then
 
               echo " PEFXM1000 Count for RQO_ORG value 601 is less than Threshold " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 601 is less than Threshold " >> $Runlog
               let error_flag="1"
       else
               echo " PEFXM1000 Count for RQO_ORG value 601 is $count_601 " >> PEFXM1000.out
               echo " PEFXM1000 Count for RQO_ORG value 601 is $count_601 " >> $Runlog
               let error_flag="1"
 
       fi
       echo "Mail Sent">>$Runlog
       mailsub="Test mail send by Rahul Purwar Issue"
       if [ $error_flag -eq 1 ] ; then
          outputFile="PEFXM1000.out"
                (
                        echo "To: $maillist"
                        echo "MIME-Version: 1.0"
                        echo "Subject: $mailsub"
                        echo "Content-Type: text/html"   
                        echo "<HTML><BODY><PRE>"
                        cat $outputFile 
                        echo "</PRE></BODY></HTML>"
                ) | sendmail -t
       fi
       echo "******************************************************************************" >>$Runlog
       rm -f PEFXM1000.out 
 
db2 "terminate" > /dev/null
if [ $? -ne 0 ] ; then
        echo `date +%Y%m%d-%H%M%S`": "Errors found in job:$JobNumber - DB termination failure >> $Runlog
        chmod 777 $Runlog
        exit 12
fi
 
exit 0

made above changes to code and now its like that.
My problem is It fetches record for only one scenario and I need to do it for 4 scenarios for which I dont have the clue.well i explain it properly:
Its like threshold for each org/logo combination varies with time :
time 1: value 1
time 2:value 2
time 3:value 3
similarly for other 3 org/logo combination.

Kindly suggest a way to achieve that
Last edited by nishantrefound; 10-29-2013 at 06:37 AM.. Reason: Please use code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Query on fetching a value of variable in script

I have below 3 files in a directory : oktest.txt okcode.txt okfun.txt I was writing a small shell script to just print the names of three files where I took a variable a and assigned it a value ok. a=ok echo "Three files are : $atest.txt $acode.txt $afun.txt" But when I run... (3 Replies)
Discussion started by: simpltyansh
3 Replies

2. Shell Programming and Scripting

Return db2 query value to shell script

Hi, Im new to DB2. I need to connect to DB2 from shell script and return the query output back to shell script variable. this is my code #!/bin/ksh db_name=db db_user=usr db_pwd=pwd db2 <<EOSQL connect to $db_name user $db_user using "$db_pwd" select count(1) from table quit EOSQL ... (3 Replies)
Discussion started by: sup
3 Replies

3. Shell Programming and Scripting

Problem in formatting output of SQL query in excel sheet in shell script

Hi Guys.. Need your help to format the output of my shell script. I am using spool command to take out put in csv file. below is my code. (for example) col USERNAME for a15 col EMAIL for a30 col FULL_NAME for a20 col LAST_LOGIN for a40 col DATE_CREATED for a40 SPOOL 120.csv... (3 Replies)
Discussion started by: Agupte
3 Replies

4. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

5. Shell Programming and Scripting

connect to db2 using shell script

Guys, I am trying to write a shell script that connect to wcsprod database and read the query #!/bin/ksh sqlplus -s < connect to wcsprod user wcsadm using pwd > select * from catentry fetch first 1 row only with ur; databse: wcsprod user: wcsadm pwd: pwd thanks (1 Reply)
Discussion started by: skatpally
1 Replies

6. Shell Programming and Scripting

Fetching CLOB value from oracle into shell script

Hi, Can anybody let me know how i can achieve the below output. I have a select query which selects two columns. I need to spool the value into a dat file for each row that is returned from the query with the coulumn1 as the name of the dat file . ex: column1: location_id column2:... (1 Reply)
Discussion started by: justchill
1 Replies

7. Shell Programming and Scripting

How to put db2 query result into an array in shell script?

Hello, Can someone please advise me how to put the db2 query reult into an array? For example, the query reults are: string A string B string C Then how do I put them into array=string A array=string B ... (2 Replies)
Discussion started by: hanul
2 Replies

8. Shell Programming and Scripting

access db2 from shell script

How to connect to db2 through shell script using cygwin? (0 Replies)
Discussion started by: supriyat
0 Replies

9. Shell Programming and Scripting

Db2 query with script

Hi All, I want to connect two tables in DB2 using shell script and then compare the contents of two tables field by field.and i should return on the screen the un matched records .. Could any one please help me in connecting database tables using Unix and retriving data from the same. (1 Reply)
Discussion started by: kanakaraju
1 Replies

10. Shell Programming and Scripting

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Discussion started by: balaeswari
6 Replies
Login or Register to Ask a Question