#!/bin/ksh

# v3.4, Michael Wang, 2003-06-24, <xw73@columbia.edu>.
# * Correct a typo in create table sql in man page.
# v3.3, Michael Wang, 2002-04-25, <xw73@columbia.edu>.
# "Loaded zero rows" is treated as INFO instead of ERROR.
# v3.2, Michael Wang, 2002-02-14, <xw73@columbia.edu>.
# * Change table name from LOAD_LOG to JOBS_LOG. The new table
#   has different definition from the old one.
# v3.1, Michael Wang, 2002-02-04, <xw73@columbia.edu>.
# * Fixed type "NOT_EXITS" => "NOT_EXISTS".
# * [[ $INCOMING_FILE == */* ]] => [[ $INCOMING_FILE == /* ]]
#   $INCOMING_BASE = true basename or relative pathname (a/b).
#   $INCOMING_DIR  = true dirname  or top level pathname.
#   $INCOMING_FILE = absolute path.
# * Fix in backup code:
#   use resolve_path() to find $THE_INCOMING_FILE.
#   $(dirname $THE_INCOMING_FILE) instead of $INCOMING_DIR.
# * Updated man page to include table creation SQL script.
# * Made load_log_table optional.
# * Generate unique log file names.
# * Made the length of keeping the data and log files adjustable.
# * Use both the symlink and the resolved name in log and notification
#   if they are different.
#
# v3.0, Michael Wang, 2002-02-02 - 2002-02-03, <xw73@columbia.edu>.
# * Simplied, user friendly argument specification (new style).
#   Eliminated derivable arguments. Backward compatible with old style.
# * Pushed application specific info in job configuration file,
#   becomes a generally useful utility for the first time.
# * Added "skip=" option, useful for rerun job failed in the middle.
# * Allow each loader login to have its own passwd, default to the
#   common passwd.
# * Hide passwd inside here document for security.
# * Added help=y man page.
#
# v2.2, Michael Wang, 2001-08-30, <xw73@columbia.edu>.
# * Corrected sed regexp below:
#   OLD: sed -n "s/Total logical records rejected:.*\([0-9]\{1,\}\)/\1/p"
#   NEW: sed -n "s/Total logical records rejected:[^0-9]*\([0-9]\{1,\}\)/\1/p"
#
# v2.1, Michael Wang, 2001-08-17, <xw73@columbia.edu>.
# * Changed logfile format from *.out.$THE_DATE to *.$THE_DATE.out
#   so that they ordered by date.
#
# v2.0, Michael Wang, 2001-06-11, <xw73@columbia.edu>.
# * Allow incoming feeds in any directory.
# * Eliminated all hardcoded path except that used by autosys.
# * Consolidated all files under a common dir.
# * Combined process_feed, check_feed, load_feed, check_loadcount.
# * Substantially reduced the number of paramters sourced.
# * Eliminated source from multilple programs.
# * No password shows in ps.
# * Allow control/program names to have or not to have .ctl/.ksh extention.
# * Changed FILE_RCVD_DATE to DATE type, and loaded with meaningful data.
# * Changed various load statistics to NUMBER type.
# * Eliminated autosys stdout/stderr redirect.
#
# v1.2, Chetan Sheth, 2000-11-17.
# * Passed MAX_ERRORS argument to load_feed.ksh.
#
# v1.1, Carol Sheng, 1999-05-06 - 1999-05-19, 1999-05-24.
# * Added load_log_table() to log the program_name (the main script)
#   the module_name (the subset script) and load_status to load_log
#   table for debug and report purpose
#
# v1.0, Sheck Cho, 1999-05-05.
# * IPO.

set +x

function my_getopts {
# version 3.141592, 2002-02-02, Michael Wang <xw73@columbia.edu>.
  typeset PATH= SEP= i
  typeset -u _I
  for i; do
    _I=$i
    eval ${_I%%?(:)=*}= unset ${_I%%?(:)=*}
    case $_I in
      *[!:]=*) eval $(IFS=$SEP; print ${_I%%=*}=\"${_I#*=}\")   ;;
      *:=*)    eval $(IFS=$SEP; print ${_I%%:=*}=\"${i#*:=}\")  ;;
    esac
  done
}

ARG_STYLE= unset ARG_STYLE
ARG_STYLE=NEW
for i; do
  [[ $i == *=* ]] || { ARG_STYLE=OLD; break; }
done

if [[ $ARG_STYLE == NEW ]]; then
  my_getopts FILE:=           \
             HEADER= TRAILER= \
             CONTROL:=        \
             LOGIN=           \
             OWNER=           \
             TABLE=           \
             DISCARD=         \
             ERROR=           \
             MYNAME:=         \
             MOVE=Y           \
             SKIP=0           \
             LOG_TABLE=       \
             HELP=            \
             "$@"
  INCOMING_FILE=$FILE
  HDR_TRL_FLAG=${HEADER:-$TRAILER}
  CONTROL_FILE_NAME=$CONTROL
  LOADER_LOGON=$LOGIN
  SCHEMA_NAME=$OWNER
  TABLE_NAME=$TABLE
  MAX_DISCARDS=$DISCARD
  MAX_ERRORS=$ERROR
else
  (( $# == 9 )) || {
    print "ERROR: Incorrect Number of arguments passed to $0 ($# != 9)."
    exit $ARG_MISMATCH
  }
  INCOMING_FILE=$1
  HDR_TRL_FLAG=$2
  REL_PATH=$3
  CONTROL_FILE_NAME=$4
  LOADER_LOGON=$5
  SCHEMA_NAME=$6
  TABLE_NAME=$7
  MAX_DISCARDS=$8
  MAX_ERRORS=$9
  MYNAME=${0%%/jobs*}/jobs/$REL_PATH/${CONTROL_FILE_NAME%.ctl}.ksh
fi

[[ $HELP == Y || $HELP == PDF ]] && {
  i= t= unset i t
  i=$(basename $0 .ksh)
  t=/tmp/$b.$$.$RANDOM
  mkdir $t && trap "rm -rf $t" EXIT INT
  sed -n "s:^## \{0,1\}::p" $0 > $t/$i.pod
  [[ $HELP == Y   ]] && perldoc $t/$i.pod
  [[ $HELP == PDF ]] && pod2man $t/$i.pod | groff -man | ps2pdf - > $i.pdf
  exit 0
}

BINDIR=${0%/*}
ETCDIR=$BINDIR/../etc
. $ETCDIR/.jobs_env

typeset -u LOADER_LOGON  # needed for LOADER_PASS
: ${HDR_TRL_FLAG:=Y}
: ${MAX_DISCARDS:=0}
: ${MAX_ERRORS:=0}
FILE_MOVE=${MOVE:-Y}
[[ -n $LOG_TABLE ]] && LOAD_LOG_TABLE=$LOG_TABLE
: ${LOAD_LOG_TABLE:=Y}

[[ -n $MYNAME ]] || {
  print "ERROR: caller program (myname) is not specified."
  print "Type \"$0 help=y\" for more info."
  exit $ARG_MISMATCH
}

PROGRAM_NAME=$(basename $MYNAME)
PROGRAM_DIR=$(cd $(dirname $MYNAME) >/dev/null && pwd -P)
PROGRAM_PATH=$PROGRAM_DIR/$PROGRAM_NAME

: ${SCHEMA_NAME:=${PROGRAM_DIR##*/}}
: ${LOADER_LOGON:=$SCHEMA_NAME}
: ${CONTROL_FILE_NAME:=${PROGRAM_PATH%.ksh}.ctl}

i= unset i
for i in $CONTROL_FILE_NAME                    \
         $PROGRAM_DIR/$CONTROL_FILE_NAME       \
         $PROGRAM_DIR/${CONTROL_FILE_NAME}.ctl
do
  [[ -r $i ]] && { CTL_FILE=$i; break; }
done

[[ -r $CTL_FILE ]] || {
  print "ERROR: Can not find control file ($CTL_FILE)."
  print "Type \"$0 help=y\" for more info."
  exit $ARG_MISMATCH
}

CTL_BASE=$(basename $CTL_FILE .ctl)

: ${INCOMING_FILE:=$(
  perl -ne "next if /-- \s* INFILE/ix;
            if (/INFILE \s+ ' \s* (\S+) \s* '/ix) {
              print \$1;
              last;
            }" < $CTL_FILE)}

if [[ $INCOMING_FILE == /* ]]; then
  INCOMING_DIR=$(dirname $INCOMING_FILE)
  INCOMING_BASE=$(basename $INCOMING_FILE)
elif [[ -n "$INCOMING_FILE" ]]; then
  INCOMING_BASE=$INCOMING_FILE
  INCOMING_FILE=$INCOMING_DIR/$INCOMING_FILE
fi

[[ -r $INCOMING_FILE ]] || {
  print "ERROR: Feed file ($INCOMING_FILE) not found."
  print "Type \"$0 help=y\" for more info."
  exit $ARG_MISMATCH
}

function resolve_path {  # stripped down version of resolve_path().
  typeset i=$1 j d f s="set"

  while [[ -n "$s" ]]; do
    d=$(cd ${i%/*} >/dev/null && pwd -P)
    f=${i##*/}
    s=$(perl -e "print(readlink(\"$i\"));")
    [[ -n "$s" ]] && i=$d/$s || { i=$d/$f && print "$i"; }
  done
}

THE_INCOMING_FILE=$(resolve_path $INCOMING_FILE)

: ${TABLE_NAME:=$(
  perl -ne "next if /-- \s* INTO \s+ TABLE/ix;
            if (/INTO \s+ TABLE \s+ (\S+)/ix) {
              print \$1;
              last;
            }" < $CTL_FILE)}

[[ -z $LOADER_LOGON || -z $SCHEMA_NAME || -z $TABLE_NAME ]] && {
  print "One of mandatory arguments not specified:"
  print "LOADER_LOGON ($LOADER_LOGON), SCHEMA_NAME ($SCHEMA_NAME), or TABLE_NAME ($TABLE_NAME)."
  exit $ARG_MISMATCH
}

LOGDIR=$PROGRAM_DIR/log/$CTL_BASE
[[ -d $LOGDIR ]] || mkdir -p $LOGDIR
: ${LOG_KEEP:=7}
(( LOG_KEEP >= 0 )) && find $LOGDIR -type f -mtime +$LOG_KEEP -exec rm {} \;

UNX_FILE= unset UNX_FILE
while [[ -z $UNX_FILE || -f $UNX_FILE ]]
do
  [[ -z $UNX_FILE ]] || sleep $(( RANDOM%10 ))
  THE_DATE=$(date +%m-%d.%H:%M)
  UNX_FILE=$LOGDIR/$CTL_BASE.$THE_DATE.out
done

LOG_FILE= DSC_FILE= BAD_FILE= unset LOG_FILE DSC_FILE BAD_FILE
LOG_FILE=$LOGDIR/$CTL_BASE.$THE_DATE.log
DSC_FILE=$LOGDIR/$CTL_BASE.$THE_DATE.dsc
BAD_FILE=$LOGDIR/$CTL_BASE.$THE_DATE.bad

print "Please check the log file: $UNX_FILE"
exec > $UNX_FILE 2>&1

print "process_feed.ksh called with the following arguments:"
print "... TIME STARTED         = $(date +%Y-%m-%d_%H:%M:%S)"
i= unset i
[[ $THE_INCOMING_FILE == $INCOMING_FILE ]] && i=$INCOMING_FILE
print "... INCOMING_FILE        = ${i:-$INCOMING_FILE ($THE_INCOMING_FILE)}"
print "... HDR_TRL_FLAG         = $HDR_TRL_FLAG"
print "... CONTROL_FILE_NAME    = $CTL_FILE"
print "... LOADER_LOGON         = $LOADER_LOGON"
print "... SCHEMA_NAME          = $SCHEMA_NAME"
print "... TABLE_NAME           = $TABLE_NAME"
print "... MAX_DISCARDS         = $MAX_DISCARDS"
print "... MAX_ERRORS           = $MAX_ERRORS"
print "... PROGRAM_NAME         = $PROGRAM_NAME"
print "... FILE_MOVE            = $FILE_MOVE"

load_log_table() {
  sqlplus << EOF
    $idlog/$pwdlog
    INSERT INTO JOBS_LOG (
      PROGRAM_NAME,
      FEED_NAME,
      HDR_LABEL,
      TRL_LABEL,
      TRLR_RECS_RCVD,
      FILE_RECS_COUNT,
      FILE_RCVD_DATE,
      MODULE_NAME,
      LOAD_STATUS,
      RECS_LOADED,
      RECS_REJ,
      RECS_DSC,
      PROCESS_DATE
    ) VALUES (
     '$PROGRAM_NAME',
     '$INCOMING_BASE',
     '$HDR_LABEL',
     '$TRL_LABEL',
      TO_NUMBER('$TRLR_COUNT'),
      TO_NUMBER('$ACTUAL_COUNT'),
      TO_DATE('$FILE_RCVD_DATE', 'YYYY-MM-DD_HH24:MI:SS'),
     '$MODULE_NAME',
      TO_NUMBER('$JOB_STATUS'),
      TO_NUMBER('$ROWS_LOADED'),
      TO_NUMBER('$ROWS_REJECTED'),
      TO_NUMBER('$ROWS_DISCARDED'),
      SYSDATE);
     EXIT
EOF
}

function notify_contact {
  { print $ORACLE_SID
    date
    i= unset i
    [[ $THE_INCOMING_FILE == $INCOMING_FILE ]] && i=$INCOMING_FILE
    print "ERROR IN loading ${i:-$INCOMING_FILE ($THE_INCOMING_FILE)}"
    print "Module $MODULE_NAME of Program $PROGRAM_NAME"
    print "Exit Code=$JOB_STATUS"
  } | if (( JOB_STATUS == EXCEED_MAX_ERRORS )); then
        [[ -n $JOB_EMAIL ]] && mailx -s "ERROR: $ORACLE_SID $PROGRAM_NAME" $JOB_EMAIL
      else
        [[ -n $JOB_PAGER ]] && mailx -s "ERROR: $ORACLE_SID $PROGRAM_NAME" $JOB_PAGER
      fi
}

# check_feed

(( JOB_STATUS = SUCCESS ))
(( ACTUAL_COUNT = $(wc -l < "$INCOMING_FILE") ))
print "INFO: ACTUAL_COUNT = $ACTUAL_COUNT"

set -- $(perl -we "print join(\" \", (localtime((stat(\"$THE_INCOMING_FILE\"))[9]))[5,4,3,2,1,0])")
typeset -Z4 YYYY=$(( $1 + 1900 ))
typeset -Z2 MM=$(( $2 + 1 ))
typeset -Z2 DD=$3
typeset -Z2 HH24=$4
typeset -Z2 MI=$5
typeset -Z2 SS=$6
FILE_RCVD_DATE=$YYYY-$MM-${DD}_$HH24:$MM:$SS

[[ "$HDR_TRL_FLAG" == "Y" ]] && {
  set -- $(head -1 $INCOMING_FILE | grep "UHDR")
  if [[ -n "$1" ]]; then
    shift
    HDR_LABEL="$@"
    set -- $(tail -1 $INCOMING_FILE | grep "UTRL")
    if [[ -n "$1" ]]; then
      shift
      (( TRLR_COUNT = $1 ))
      TRL_LABEL="$@"
      print "INFO: TRLR_COUNT = $TRLR_COUNT"
      (( TRLR_COUNT == ACTUAL_COUNT )) || {
        print "ERROR: Trailer count and actual count mismatch."
        (( JOB_STATUS = FEED_COUNT_MISMATCH ))
      }
    else
      print "ERROR: Can not find UTRL in the trailer."
      (( JOB_STATUS = FEED_TRL_NOT_EXISTS ))
    fi
  else
    print "ERROR: Can not find UHDR in the header."
    (( JOB_STATUS = FEED_HDR_NOT_EXISTS ))
  fi
}

(( JOB_STATUS == SUCCESS )) || {
  print "ERROR: check_feed failed ($JOB_STATUS)."
  MODULE_NAME="check_feed"
  [[ $LOAD_LOG_TABLE == Y ]] && load_log_table
  notify_contact
  exit $JOB_STATUS
}
print "INFO: check_feed is successful."

# load_feed

cd $INCOMING_DIR

[[ -d $INCOMING_DIR ]] || exit $DIRECTORY_NOT_EXISTS

eval LOADER_PASS=\$${LOADER_LOGON}PASS
: ${LOADER_PASS:=$pwd}

sqlldr \
  control=$CTL_FILE \
      log=$LOG_FILE \
  discard=$DSC_FILE \
      bad=$BAD_FILE \
     skip=$SKIP     \
   errors=$MAX_ERRORS << EOF
$LOADER_LOGON/$LOADER_PASS
EOF

(( JOB_STATUS = $? ))
(( JOB_STATUS == 1 || JOB_STATUS >= 3 )) && {    # JOB_STATUS = 2 is taken care of by check_loadcount
  print "ERROR: load_feed failed ($JOB_STATUS)."
  MODULE_NAME="load_feed"
  [[ $LOAD_LOG_TABLE == Y ]] && load_log_table
  notify_contact
  exit $SQLLOAD_EXECUTION_ERROR
}
print "INFO: load_feed is successful."

# check_loadcount

(( JOB_STATUS = SUCCESS ))

[[ -r $LOG_FILE ]] || {
  print "ERROR: Can not read the log file ($LOG_FILE)."
  exit 1
}

(( ROWS_LOADED    = $(sed -n "s:\([0-9]\{1,\}\) Rows\{0,1\} successfully loaded\.:\1:p" $LOG_FILE) ))
(( ROWS_REJECTED  = $(sed -n "s/Total logical records rejected:[^0-9]*\([0-9]\{1,\}\)/\1/p" $LOG_FILE) ))
(( ROWS_DISCARDED = $(sed -n "s/Total logical records discarded:[^0-9]*\([0-9]\{1,\}\)/\1/p" $LOG_FILE) ))
(( ROWS_SKIPPED   = $(sed -n "s/Total logical records skipped:[^0-9]*\([0-9]\{1,\}\)/\1/p" $LOG_FILE) ))

(( ROWS_LOADED == 0 )) && {
# (( JOB_STATUS = LOAD_COUNT_ZERO ))
  print "INFO: Loaded zero rows."
}

(( ROWS_REJECTED > MAX_ERRORS )) && {
  (( JOB_STATUS = EXCEED_MAX_ERRORS ))
  print "ERROR: Exceeded max allowable errors."
}

(( TOTAL_REC = ROWS_LOADED + ROWS_REJECTED + ROWS_DISCARDED + ROWS_SKIPPED ))
print "INFO: Total records in log = $TOTAL_REC, actual count = $ACTUAL_COUNT."

(( TOTAL_REC == ACTUAL_COUNT )) || {
  (( JOB_STATUS = LOAD_COUNT_MISMATCH ))
  print "ERROR: Total records in log and actual count mismatch."
}

(( ROWS_DISCARDED >= 1 )) && {
  if [[ $HDR_TRL_FLAG == "Y" ]] && (( ROWS_SKIPPED == 0 )); then
    (( ROWS_DISCARDED_1 = ROWS_DISCARDED - 2 ))
  elif [[ $HDR_TRL_FLAG == "Y" ]] && (( ROWS_SKIPPED >= 1 )); then
    (( ROWS_DISCARDED_1 = ROWS_DISCARDED - 1 ))
  else
    (( ROWS_DISCARDED_1 = ROWS_DISCARDED - 0 ))
  fi

  (( ROWS_DISCARDED_1 > MAX_DISCARDS )) && {
    (( JOB_STATUS = EXCEED_MAX_DISCARDS ))
    print "ERROR: Rows discarded ($ROWS_DISCARDED_1) is greater than max allowed ($MAX_DISCARDS)."
  }
}

if (( JOB_STATUS == SUCCESS )); then
  [[ $FILE_MOVE == "Y" ]] && {
    D= unset D
    D=${BACKUP_DIR}$(dirname $THE_INCOMING_FILE)
    [[ -d $D ]] || mkdir -p $D
    : ${DATA_KEEP:=3}
    (( DATA_KEEP >= 0 )) && find $BACKUP_DIR -mtime +$DATA_KEEP -type f -exec rm {} \;
    compress -f $THE_INCOMING_FILE
    mv $THE_INCOMING_FILE.Z $THE_INCOMING_FILE.Z.$THE_DATE  # mv /path/file.Z /path/file.Z.<date>: rename
    mv $THE_INCOMING_FILE.Z.$THE_DATE $D                    # mv /path/file.Z.<date> /backup/path: move
  }
else
  print "ERROR: check_loadcount failed ($JOB_STATUS)."
  MODULE_NAME="check_loadcount"
  [[ $LOAD_LOG_TABLE == Y ]] && load_log_table
  notify_contact
  exit $JOB_STATUS
fi
print "check_loadcount.ksh successfully executed"

# summary

print "process_feed.ksh finished with following log information:"
print "... ROWS_LOADED         = $ROWS_LOADED "
print "... LOAD_STATUS         = $JOB_STATUS "
print "... ROWS_DISCARDED      = $ROWS_DISCARDED"
print "... ROWS_REJECTED       = $ROWS_REJECTED"
i= unset i
[[ $THE_INCOMING_FILE == $INCOMING_FILE ]] && i=$INCOMING_FILE
print "... FEED_NAME           = ${i:-$INCOMING_FILE ($THE_INCOMING_FILE)}"
print "... HDR_LABEL           = $HDR_LABEL"
print "... TRL_LABEL           = $TRL_LABEL"
print "... FILE_RCVD_DATE      = $FILE_RCVD_DATE"
print "... TRLR_COUNT          = $TRLR_COUNT"
print "... TIME FINISHED       = $(date +%Y-%m-%d_%H:%M:%S)"
MODULE_NAME="summary"
[[ $LOAD_LOG_TABLE == Y ]] && load_log_table
exit $JOB_STATUS

## =head1 NAME
##
## process_feed - load feed to Oracle database.
##
## =head1 SYNOPSIS
## 
## B<process_feed.ksh>
##   myname:=I<caller_full_pathname>
##   [owner=I<schema_owner>]
##   [table=I<table_name>]
##   [login=I<loader_login>]
##   [control:=I<control_file>]
##   [file:=I<feed_file>]
##   [header=Y|N] | [trailer=Y|N]
##   [discard=I<max_discars>]
##   [error=I<max_error>]
##   [skip=I<num_records>]
##   [move=Y|N]
##   [log_table=Y|N]
##   [help=Y]
##
## B<process_feed.ksh>
##   P1: Name of incoming data file to be loaded
##   P2: Y/N flag to determine if header/trailer check should be performed
##   P3: Relative path to jobs directory, eg trade/for_trade
##   P4: Name of SQL*Loader control file
##   P5: Name of Oracle user to perform load
##   P6: Oracle schema to be loaded
##   P7: Oracle table name to be loaded
##   P8: Maximum allowable discards by SQL*Loader, exclusive of header/trailer
##   P9: Maximum allowable errors by SQL*Loader
##
## =head1 DESCRIPTION
##
## B<process_feed.ksh> drives the loading of data into Oracle. This script
## checks the accuracy of the incoming ascii file (compare the trailer count
## and actual count); runs SQL*Loader; checks
## the SQL*Loader log to ensure that the file loaded successfully; updates
## status table in the database with the job's completion status and relevant
## load information.
##
## The new key=val argument style requires only one mandatory argment,
## myname, which is the program name that calls B<process_feed.ksh>
## (the rest can be either derived or have default values). However,
## the caller name is available as "$0" in the caller's program, this is
## not user replaceable part. If we can rely on the default, and derivable
## values, then the caller program is maintenance free. For different jobs,
## the content of the caller program is identical, only the names differ.
##
## And if you do need to supply additional parameters, it can be in any
## order. ":=" is used for case sensitive options, myname and file, and
## "=" is used for other options, which are case insensitive.
##
## The old argument style requires exactly 9 arguments, in the order shown
## above.
##
## The new B<process_feed.ksh> is backward compatible, ie, it accepts both
## new and old argument style. I hope you find the new style is easier to
## use and maintain.
##
## =head1 COMMAND LINE OPTIONS
##
## The feed file name is optional in new argument style.  If not
## specified, it is derived from control file. A line containing "infile
## 'I<incoming_file>'", not preceeded with "--" in the control file is
## assumed, and the word "infile" can be in mixed case and there can be
## any number of white spaces between the two words. The control file
## can be either full path or basename. In the latter case, the directory
## $INCOMING_DIR defined in job configuration file is assumed.
##
## The "header trailer flag" is optional in new argument style.
## The default is Y.
## Either header=Y or trailer=Y turn on the "header trailer flag".
##
## Relative path to jobs directory is depreciated, which is only required when
## using the old argument style.
##
## control file name is optional in new argument style. If not specified,
## it is assumed to be the same as calling program name with .ctl extention, in the
## same directory as the program itself. For example, if the program is
## bc_load_pending_trades.ksh, then the control file is assumed to be
## bc_load_pending_trades.ctl.
##
## The loader login name is optional in new argument style. If not specified,
## it is assumed to be the same as the schema owner.
##
## The schema owner is optional in new argument style. If not specified,
## it is assumed to be the same as immediate directory containing the
## caller program, as the caller programs are organized by schemas.
##
## The table name is optional in new argument style. If not specified,
## it is derived from control file. A line containing "into table I<table_name>"
## not preceeded with "--" in the control file is assumed. Mixed case
## and any number of white spaces are accepted.
##
## Maximum allowable discards (besides header and trailer if present), and maximum
## allowable errors are optional. They are assume to be 0 by default.
##
## "move=y|n" option specifies whether the feed file should be moved (archived).
## The default value Y works for most cases. However, if the feed is used by
## multiple load programs, it should not moved until all the load programs
## finish.
##
## "skip=I<num_records>" skips the first I<num_records>. It is useful for reload
## a job which is failed in the middle. Supposed I<n> records are already loaded,
## then you want to skip I<n> records if there is no header, and I<n+1> records
## if there is header.
##
## "log_table=y|n" specifies whether to load the log table. The info is collected
## regardless. The use of log table allows to generate a nice looking report.
## The precedence is the command line option, job configuration file, and the
## default which is Y.
##
## If you do use log_table, the creation script is
##
##   create table jobs_log (
##     PROGRAM_NAME     VARCHAR2(200)  NOT NULL,
##     FEED_NAME        VARCHAR2(200),
##     HDR_LABEL        VARCHAR2(200),
##     TRL_LABEL        VARCHAR2(200),
##     TRLR_RECS_RCVD   NUMBER,
##     FILE_RECS_COUNT  NUMBER,
##     FILE_RCVD_DATE   DATE,
##     MODULE_NAME      VARCHAR2(200),
##     LOAD_STATUS      NUMBER,
##     RECS_LOADED      NUMBER,
##     RECS_REJ         NUMBER,
##     RECS_DSC         NUMBER,
##     LOGFILE          VARCHAR2(200),
##     PROCESS_DATE     DATE           NOT NULL
##   )
##
## "help=y" prints out the man page you are reading.
##
## =head1 CONFIGURATION FILE
##
## A sample configuration file is shown below.
##
##   # BASIC ENVIRONMENT VARIABLES FOR ORACLE
##   
##   typeset -x ORACLE_SID=BCCS1
##   typeset -x ORACLE_HOME=/bcc-bccs1-s/ora01/app/oracle/product/8.1.7
##   typeset -x PATH=$(/bin/getconf PATH):/usr/local/bin:$ORACLE_HOME/bin
##   typeset -x TNS_ADMIN=/var/opt/oracle
##   typeset -x LD_LIBRARY_PATH=
##   
##   # USERS AND PASSWORDS
##   
##   idlog=<login>           # user name and
##   pwdlog=<passwd>         # passwd for "INSERT INTO JOBS_LOG"
##   <SCHEMA>PASS=<passwd>   # <SCHEMA> passwd for process feed and sqlfile
##   pwd=<passwd)            # common passwd in the absense of <SCHEMA>PASS
##   
##   # EXIT STATUS CODES
##   
##   (( SUCCESS                 =  0 ))
##   (( FEED_NOT_EXISTS         =  1 ))
##   (( FEED_HDR_NOT_EXISTS     =  2 ))
##   (( FEED_TRL_NOT_EXISTS     =  3 ))
##   (( FEED_COUNT_MISMATCH     =  4 ))
##   (( LOAD_COUNT_ZERO         =  5 ))
##   (( LOAD_COUNT_MISMATCH     =  6 ))
##   #
##   (( SQL_PROCESSING_ERROR    =  9 ))
##   (( ARG_MISMATCH            = 10 ))
##   (( DIRECTORY_NOT_EXISTS    = 11 ))
##   (( SQLLOAD_EXECUTION_ERROR = 12 ))
##   (( EXCEED_MAX_ERRORS       = 13 ))
##   (( EXCEED_MAX_DISCARDS     = 14 ))
##   
##   # FEED
##   
##   INCOMING_DIR=~ftpstars
##   BACKUP_DIR=~ftpstars/backup
##   (( DATA_KEEP = -1 ))  # days to keep data file (negative number = forever)
##   ((  LOG_KEEP = -1 ))  # days to keep log file  (negative number = forever)
##   
##   # NOTIFICATION
##   
##   JOB_EMAIL="foo@my.com,bar@my.com"
##   JOB_PAGER="1234567@pager.com,7654321@pager.com"
##
## =head1 DIRECTORY STRUCTURE
##
## I described below a preferred directory structure. No abosolute path is hard coded
## in this setup, which makes the program very portable.
##
## First, you setup the top level job directory, I<whatever_dir>/jobs, which we
## will refer as I<TOP_DIR>.
##
## Secondly, you setup bin directory under I<TOP_DIR>, that is where this program
## B<process_feed.ksh> should reside. And its configuration file should be in
## I<TOP_DIR>/etc and shuld be named ".jobs_env". It should be be read/write by owner
## only.
##
## Thirdly, the caller programs should be organized by schema. For example,
##
##   bc_load_pending_trades.ctl
##   bc_load_pending_trades.ksh
##
## are placed under I<TOP_DIR>/trade. B<process_feed.ksh> will put the log files
## under
##
##   I<TOP_DIR>/trade/bc_load_pending_trades:
##
##   log/bc_load_pending_trades/bc_load_pending_trades.02-03_00:48.out
##   log/bc_load_pending_trades/bc_load_pending_trades.02-03_00:48.log
##   log/bc_load_pending_trades/bc_load_pending_trades.02-03_00:48.dsc
##   log/bc_load_pending_trades/bc_load_pending_trades.02-03_00:48.bad
##
## The *.out is the unix stdout and stderr, and the *.log is the Sql*Loader
## log file, and *.dsc, *.bad are Sql*Loader discard and bad files respectively
## if we have them.
##
## The autosys command line would be:
##
##   command: I<TOP_DIR>/trade/bc_load_pending_trades.ksh
##
## =head1 SECURITY
##
## The user program does not handle passwd, I<process_feed.ksh> retrieve the
## passwd for the loader login. The passwd file should be read only by
## the program, and the program disables trace, and is only modifiable
## by the owner.
##
## The passwd is not part of sqlldr command arguments and therefore
## does not show up in ps output.
##
## =head1 CVS REPOSITORY
##
## By design, all programs, control files, sql files are under the same
## top level directory, it is easier to put them under version control system
## CVS. This avoids the clutter we had on the production system.
##
## Here is an example to commit a change to CVS:
##
##   $ cvs ci -m "ver 3.0 tested"  process_feed.ksh 
##   Checking in process_feed.ksh;
##   /bcc-bccs1-s/ora01/home/oracle/jobs-repo/jobs/bin/process_feed.ksh,v
##   +  <--  process_feed.ksh
##   new revision: 1.6; previous revision: 1.5
##   done
##
## =head1 EXAMPLES
##
## New style:
##
##   $ cat bc_load_pending_trades.ksh  
##
##   #!/bin/ksh
##   [[ $0 == /* ]] || exec $(pwd -P)/$0 "$@"
##   ${0%%/jobs*}/jobs/bin/process_feed.ksh myname:=$0 move=n skip=3
##
## Old style:
##
##   $ cat bc_load_pending_trades1.ksh 
##
##   #!/bin/ksh
##   [[ $0 == /* ]] || exec $(pwd -P)/$0 "$@"
##   ${0%%/jobs*}/jobs/bin/process_feed.ksh \
##     TR0152TC.FTPTRADE      \
##     Y                      \
##     trade                  \
##     bc_load_pending_trades \
##     trade                  \
##     trade                  \
##     pending_trades         \
##     0                      \
##     0
##
## Please note is there is no hard coded path and other info in new argument style.
##
## =head1 SEE ALSO
##
## process_sqlfile.ksh help=y.
##
## =head1 AUTHORS
##
## Michael Wang, 2001-2002, <xw73@columbia.edu>.
##
## Chetan Sheth, 2000-11-17.
##
## Carol Sheng, 1999-06-06 - 1999-05-19, 1999-05-24.
##
## Sheck Cho, 05/05/1999.