Database Connection Retries

Any piece of code that establishes a database connection must check the database status before processing database objects or queries. There is no point in processing the remaining code if the database is offline. The script below checks for database connection and tries 3 times before it ends the connection.

#!/bin/ksh
## Example: db connection using kornshell
##
## DB Credential
DB_USER='Scott'
DB_PASSWD='*****'
DB='dbarepublic'
DB_CONNECTION='SUCCESS'
a=0
while [[ $a -le 3 ]]
do
        DB_CONNECTION=`sqlplus -s $DB_USER/$DB_PASSWD@$DB <<EOF
     set pagesize 0
                    set feedback off
                    set verify off
                    set heading off
                    set echo off
                    SELECT 'SUCCESS'
                    FROM dual;
                    exit;
        EOF`
        echo "Try Number: $a"
        if [[ $DB_CONNECTION = "SUCCESS" ]]
        then
                echo "$DB Database Status: ONLINE"
                break
        fi
        ((a++))
        echo "$DB Database Status: OFFLINE"
        sleep 5
done
if [[ $DB_CONNECTION != "SUCCESS" ]]
then
        echo "$DB Database Down, program terminating, try back later"
        exit
fi

##
### continue with your program here .....
##

##
### end of program
##

exit

This is a good piece of code to have on every script that makes a database connection. Checking the database status before running a query is always a best practice. If the database is down, there is no point in trying to connect and run the queries. How do you add this piece of code to script that already  exist? you can copy & paste it right before a database connection is established. It is that simple!

Ok, the smart way is to use a global function and call the function from any script that does database connection. What are the benefit of using a Global function instead of copy-paste? There is nothing wrong in copy-paste but converting it to a global function is the best because it is easy to maintain, and less coding to deal with. What does a function do? Function mainly does two things: It breaks down the overall functionality of a big code into smaller and logical piece. And the  next is to perform repetitive task which is a great way to reuse the code. A local function is only accessible within a script and the global function can be called from any other scripts.

Rainer Raab has done a cool tutorial on how to create a global function. If you do not know already, I highly recommend going over  his tutorial here. I hope you can now convert it to global function and shared it with the team.

If you’ve got a question for me, you can email me at pbaniya04@gmail.com. If you liked this article and want to read more tips, articles, and tutorial related database, please subscribe to my blog and share this with your buddies.