Its important to note that sqlldr only ever passes back a successful return code. Whether the load is successful or not. I suggest you call a script that calls sqlldr and then greps the log file
for errors.
The script can then pass back an appropriate return code.
Try something like:
#!/bin/sh
# Author: Altis Consulting
# Date Written: April, 2001
# Description:
# - execute sqlldr
# - check for successfull execution
# - check log file for any errors as sqlldr returns success
# even on a failed load.
# - return 0 if no errors, else 1
#
# Modification Log
# ===================================================================
#
#------------------------------------------------------------------------------
# define USAGE for script
USAGE="Usage:nt$0 nn"
#------------------------------------------------------------------------------
#
#------------------------------------------------------------------------------
# check parameter count for correct usage
#------------------------------------------------------------------------------
case "$#" in
4) ;;
*) echo $USAGE
exit 1
esac
#------------------------------------------------------------------------------
# set parameters (and do any further checks required)
#------------------------------------------------------------------------------
ROOT_DIR=/taurusftp
DATABASE="$1"
TABLE="$2"
CONTROL_FILE="$ROOT_DIR/ctl/$TABLE.ctl"
DATA_FILE="$ROOT_DIR/data/$TABLE.dat"
USER="$3"
PASS="$4"
#------------------------------------------------------------------------------
# set constants (and do any further checks required)
#------------------------------------------------------------------------------
JOB_LOG=${ROOT_DIR}/log/AQsqlldr.log
# OPTIONS="log=$ROOT_DIR/log/$TABLE.log bad=$ROOT_DIR/bad/$TABLE.bad direct=true errors=0 rows=50000 silent=all" OPTIONS="log=$ROOT_DIR/log/$TABLE.log bad=$ROOT_DIR/bad/$TABLE.bad direct=true errors=0 rows=50000"
PATH=/taurusftp/script:$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/ccs/bin:/usr/ucb:/opt/bin:/opt/local/bin:usr/local/gnu/bin:.
ORACLE_HOME=/db00/app/oracle/product/8.1.6
PATH=$PATH:$ORACLE_HOME/bin
ORACLE_SID=$DATABASE
export ORACLE_SID
ORAENV_ASK=NO
export ORAENV_ASK
. oraenv
TEMP=/tmp
export TEMP
#------------------------------------------------------------------------------
# check for the existence of the root directory and the control file
#------------------------------------------------------------------------------
cd "${ROOT_DIR}"
if [ $? -ne 0 ] ; then
echo "ERROR: Could not cd to "${ROOT_DIR}""
exit 1
fi
if [ ! -f "$CONTROL_FILE" ] ; then
echo "ERROR: control file "$CONTROL_FILE" not found"
exit 1
fi
#------------------------------------------------------------------------------
# execute sqlldr
#------------------------------------------------------------------------------
echo `date + %Y/%m/%d %H:%M:%S` $TABLE Start >> $JOB_LOG
sqlldr $USER/$PASS control=$CONTROL_FILE data=$DATA_FILE $OPTIONS if [ $? -ne 0 ] ; then
echo "ERROR: Execution of sqlldr failed."
echo `date + %Y/%m/%d %H:%M:%S` $TABLE ERROR >> $JOB_LOG
exit 1
fi
#------------------------------------------------------------------------------
# check log file
#------------------------------------------------------------------------------
LOG_FILE="$ROOT_DIR/log/$TABLE.log"
grep ORA- "$LOG_FILE"
if [ $? -eq 0 ] ; then
echo "ERROR: Errors found in sqlldr log file, "$LOG_FILE""
echo `date + %Y/%m/%d %H:%M:%S` $TABLE ERROR >> $JOB_LOG
exit 1
fi
grep "Rows successfully loaded" "$LOG_FILE"
#------------------------------------------------------------------------------
# indicate successfull completion
#------------------------------------------------------------------------------
echo "SUCCESSc"
echo `date + %Y/%m/%d %H:%M:%S` $TABLE Loaded >> $JOB_LOG exit 0
sqlldr with DSExecute
Archive of postings to DataStageUsers@Oliver.com. This forum intended only as a reference and cannot be posted to.
Return to “Archive of DataStage Users@Oliver.com”
Jump to
- Moderators' Choice
- ↳ Editor's BLOG Corner
- ↳ Ask the Experts! - Dads and Grads
- ↳ DSXchange Testimonials
- ↳ Cognos (IBM BI)
- FAQs
- ↳ FAQs
- ↳ FAQ Discussion
- DataStage
- ↳ General
- ↳ IBM<sup>®</sup> Infosphere DataStage Server Edition
- ↳ IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)
- ↳ Archive of DataStage Users@Oliver.com
- IBM<sup>®</sup>Infosphere Products<sup></sup>
- ↳ Business Glossary
- Suggestions
- ↳ Site/Forum
- ↳ Enhancement Wish List
- Consulting
- ↳ Talent
- ↳ Looking for Talent
- Support
- ↳ Parameter Manager
- ↳ Compile All Plus
- Usergroup Forums
- ↳ Usergroup Central Forum
- ↳ Heartland Usergroup Forum
- The Written Word
- ↳ Articles, White Papers and Tips and Tricks
- ↳ Product Documentation
- Third Party Applications
- ↳ Third Party Applications
- Product Derivatives
- ↳ Functions
- ↳ Routines
- ↳ Jobs
- ↳ Logs
- Tools
- ↳ Tools Forum
- Category
- ↳ Infosphere Master Data Management
- ↳ Data Quality Best Practices
- ↳ IBM QualityStage
- ↳ Information Analyzer (formerly ProfileStage)
- ↳ IBM<sup>®</sup> SOA Editions (Formerly RTI Services)
- ↳ IBM<sup>®</sup> DataStage TX
- ↳ BI
- ↳ Data Integration