I have a quick task to setup a job to load data directly into database from flat files. I was going to use SQL Loader.
Attached are the two sample data files. 1st priority is with the log.par file. I need to set up a process where both of these get loaded into the database on a continuous basis (also included the two Unix scripts which currently generate these log files). Need to somehow feed the data directly into the database (without modifying the application binaries), that would also be helpful.
Challenge:
+ New records are appended at end every hour
+ Need to load incremental updates to the database without re-entering already existing/duplicate data
Note: There are currently no tables in the database to field data so I must create the table.
### Exist Script #1
#!/bin/ksh
# PATH and LIBRARY variables
LD_LIBRARY_PATH=/usr/lib:/usr/ucblib:
export LD_LIBRARY_PATH
PATH=/sbin:/usr/5bin:/usr/sbin:/usr/ucb:/usr/ccs/bin:/usr/local/bin
export PATH
BINPATH="$HOME/base"
PROCPATH="$HOME/process"
DATAPATH="$HOME/data"
ETMSFILT="etmsfilter"
PATH=$PATH:$BINPATH
export PATH
cd $PROCPATH
#######################
# temp set
BACK_DATE=`/usr/bin/date +m%d%H%M%S`
SDATE=$1
ACCESSNAME=$2
RAWPATH=$3
if [ -z "$1" ] ; then
echo usage $0 date_yyyymmddhh name_list orig_data_path
exit 1
fi
ACCESSNAME=${ACCESSNAME:=$HOME/process/name}
NAME=$ACCESSNAME
RAWPATH=${RAWPATH:=$HOME/orig}
sleep 2
ps -ef | grep $ETMSFILT | grep -v grep > /dev/null
if [ $? -eq 0 ] ; then
ps -ef | grep $ETMSFILT | read f1 pid other
kill $pid
fi
ps -ef | grep $ETMSFILT | grep -v grep > /dev/null
if [ $? -eq 1 ] ; then
$BINPATH/$ETMSFILT -Fdata.pipe -T$SDATE -N9000 -D$DATAPATH -Llog.count >>$NAME 2>log.filterr &
fi
zcat $RAWPATH/orig.${SDATE}* > $PROCPATH/data.pipe
sleep 10
ps -ef | grep $ETMSFILT | grep -v grep > /dev/null
if [ $? -eq 0 ] ; then
ps -ef | grep $ETMSFILT | grep -v grep | read f1 pid other
kill $pid
fi
#### Exist Script #2
pswd=$1
BIN5="$HOME/base"
DATA_PATH="$HOME/process"
ORACLE_SID="etmsb1"
export BIN5 DATA_PATH ORACLE_SID
ORACLE_HOME=/opt/oracle/product/10.2.0
LD_LIBRARY_PATH=/usr/openwin/lib:/opt/SUNWmotif/lib:/usr/lib:/usr/ucblib:$ORACLE_HOME/lib
PATH=/sbin:/usr/5bin:/usr/sbin:/usr/ucb:/usr/ccs/bin:/usr/local/bin:/$ORACLE_HOME/bin
export LD_LIBRARY_PATH ORACLE_HOME PATH
CURRENT=${DATA_PATH}/.current_date
PARSER=etmspar
export CURRENT PARSER
#set -x
cd $DATA_PATH
if [ -f $CURRENT ] ; then
cat $CURRENT
fi
## PAR ##
ps -ef | grep $PARSER | grep -v grep > /dev/null
if [ $? -eq 1 ] ; then
${BIN5}/${PARSER} ${DATA_PATH}/name dummy $ORACLE_SID $pswd $CURRENT 150 >>${DATA_PATH}/log.par 2>${DATA_PATH}/log.parerr &
fi
#set +x
Attached are the two sample data files. 1st priority is with the log.par file. I need to set up a process where both of these get loaded into the database on a continuous basis (also included the two Unix scripts which currently generate these log files). Need to somehow feed the data directly into the database (without modifying the application binaries), that would also be helpful.
Challenge:
+ New records are appended at end every hour
+ Need to load incremental updates to the database without re-entering already existing/duplicate data
Note: There are currently no tables in the database to field data so I must create the table.
### Exist Script #1
#!/bin/ksh
# PATH and LIBRARY variables
LD_LIBRARY_PATH=/usr/lib:/usr/ucblib:
export LD_LIBRARY_PATH
PATH=/sbin:/usr/5bin:/usr/sbin:/usr/ucb:/usr/ccs/bin:/usr/local/bin
export PATH
BINPATH="$HOME/base"
PROCPATH="$HOME/process"
DATAPATH="$HOME/data"
ETMSFILT="etmsfilter"
PATH=$PATH:$BINPATH
export PATH
cd $PROCPATH
#######################
# temp set
BACK_DATE=`/usr/bin/date +m%d%H%M%S`
SDATE=$1
ACCESSNAME=$2
RAWPATH=$3
if [ -z "$1" ] ; then
echo usage $0 date_yyyymmddhh name_list orig_data_path
exit 1
fi
ACCESSNAME=${ACCESSNAME:=$HOME/process/name}
NAME=$ACCESSNAME
RAWPATH=${RAWPATH:=$HOME/orig}
sleep 2
ps -ef | grep $ETMSFILT | grep -v grep > /dev/null
if [ $? -eq 0 ] ; then
ps -ef | grep $ETMSFILT | read f1 pid other
kill $pid
fi
ps -ef | grep $ETMSFILT | grep -v grep > /dev/null
if [ $? -eq 1 ] ; then
$BINPATH/$ETMSFILT -Fdata.pipe -T$SDATE -N9000 -D$DATAPATH -Llog.count >>$NAME 2>log.filterr &
fi
zcat $RAWPATH/orig.${SDATE}* > $PROCPATH/data.pipe
sleep 10
ps -ef | grep $ETMSFILT | grep -v grep > /dev/null
if [ $? -eq 0 ] ; then
ps -ef | grep $ETMSFILT | grep -v grep | read f1 pid other
kill $pid
fi
#### Exist Script #2
pswd=$1
BIN5="$HOME/base"
DATA_PATH="$HOME/process"
ORACLE_SID="etmsb1"
export BIN5 DATA_PATH ORACLE_SID
ORACLE_HOME=/opt/oracle/product/10.2.0
LD_LIBRARY_PATH=/usr/openwin/lib:/opt/SUNWmotif/lib:/usr/lib:/usr/ucblib:$ORACLE_HOME/lib
PATH=/sbin:/usr/5bin:/usr/sbin:/usr/ucb:/usr/ccs/bin:/usr/local/bin:/$ORACLE_HOME/bin
export LD_LIBRARY_PATH ORACLE_HOME PATH
CURRENT=${DATA_PATH}/.current_date
PARSER=etmspar
export CURRENT PARSER
#set -x
cd $DATA_PATH
if [ -f $CURRENT ] ; then
cat $CURRENT
fi
## PAR ##
ps -ef | grep $PARSER | grep -v grep > /dev/null
if [ $? -eq 1 ] ; then
${BIN5}/${PARSER} ${DATA_PATH}/name dummy $ORACLE_SID $pswd $CURRENT 150 >>${DATA_PATH}/log.par 2>${DATA_PATH}/log.parerr &
fi
#set +x