Extracting data using sql queries

I will just list down the commands that achieve this. The prerequisites for running these are to be managed by the users/admin.

For Db2:

Do a sanity check

db3 "connect to $DB_SVR user $db2user using $db2passwd"
connectionStatus=$?
 if [ $connectionStatus != 0 ]
 then
            #sendmail or raise an alert
        fi

Run the command:

fetchDataSql=" export to ${filePath}_data of del modified by coldel| ${query} "
 
 log "Extracting data using:: ${fetchDataSql}"
 db2 "${fetchDataSql}"
 extractionStatus=$?
 if [ $extractionStatus -eq 0 ] || [ $extractionStatus -eq 2 ]
 then
  log "Preparing the output file"
  echo ${title} > ${filePath}
  cat ${filePath}_data >> ${filePath}
  rm -f ${filePath}_data 
  log "########################################################################################"
  log "##  Data Extraction process completed successfully."
  log "########################################################################################"     
 else
  log "########################################################################################"
  log "##   Alert!!Problems extracting data from ${tableName}, return status::$extractionStatus "
  log "########################################################################################"
  send_mail "Alert!!Problems extracting data from ${tableName}, return status::$extractionStatus" ${FAILURE}
  terminate_db
  exit 4   
 fi

The terminate function just closes the db connection

function terminate_db {
 log "Close database connection"
 db2 "terminate"
}

For Oracle:

Load the oracle client variables into the environment. Ask you admin for details.

#Loading the oracle client
export ORACLE_BASE="/xyz/oracle/sqlnet/11.1.0/product/11.1.0/client_1"
export ORACLE_HOME="$ORACLE_BASE"
export PATH="$ORACLE_BASE/bin":"$ORACLE_BASE/perl/bin":"$ORACLE_BASE/sqldeveloper":$PATH
export LD_LIBRARY_PATH="$ORACLE_BASE/lib":$LD_LIBRARY_PATH
export PERL5LIB="$ORACLE_BASE/perl/lib/5.8.3":"$ORACLE_BASE/perl/lib/site_perl/5.8.3":$PERL5LIB

Query for results

values=`sqlplus  -s ${oracleuser}/${oraclepasswd}@${ORCL_SVR} << EOF
   SET LINESIZE 1000
   SET HEAD OFF
   SET FEEDBACK OFF
   ${query};
   exit;
   EOF`
extractionStatus=$?
if [ $extractionStatus -eq 0 ] || [ $extractionStatus -eq 2 ]
 then
  log "########################################################################################"
  log "##   Preparing the output file::${filePath}"
  log "########################################################################################"
  echo ${title} > ${filePath}
  echo ${values} > ${filePath}_data.txt
  sed 's/VDB_SEP/\n/g' < ${filePath}_data.txt >>${filePath}
  rm -f ${filePath}_data.txt
                log "########################################################################################"
  log "##   Data Extraction process completed successfully."
  log "########################################################################################"     
 else
  log "########################################################################################"
  log "##   Alert!!Problems extracting data from ${tableName}, return status::$extractionStatus "
  log "########################################################################################"
  exit 4   
fi