FW: sample scripts

#!/bin/ksh
##################################################################
# ETL Name: mol_reo_auto_valutn_cfcnt_hst.shl
#
# Description: Shell to Load CDW table mol_reo_auto_valutn_cfcnt_hst
# Date: 05/30/2007
# Author: Nitin Gulati (c14900)
# Change:
##################################################################

CDWInfo()
{
echo "#INFO:`${CURR_TS}`:${1}"
}

CDWWarn()
{
echo "#WARNING:`${CURR_TS}`:${1}"
}

CDWErr()
{
echo "#ERROR:`${CURR_TS}`:${1}"
}

#-----------------------------------------------------------------
# LOCAL VARIABLE SECTION
#-----------------------------------------------------------------
SET_VAR()
{
CDWInfo "SET_VAR() Started ........"

export DB2INSTANCE=${DB2PROF}

export CDWDIR=/fmac/${ENV}/mis/cdw
export DATADIR=/fmacdata/${ENV}/mis/cdw
export CNTLDIR=${CDWDIR}/cntl
export BINDIR=${CDWDIR}/util/bin
export SHLDIR=${CDWDIR}/shl
export SRCDIR=${CDWDIR}/src
export DATA_IN=${DATADIR}/datain
export DATA_OUT=${DATADIR}/dataout
export LOGDIR=${DATADIR}/logs
export TRGR_DIR=${DATADIR}/trigger
export CNTL_RPT_FILE=${DATA_OUT}/${TABLE_NAME}_cntl_report.csv
export SRC_FILE_NAME=cl_autovl_coeff_hst
export DATA_FILE=${DATA_IN}/${SRC _FILE_NAME}.csv
export MAIL_CDW_SUPPORT=${CNTLDIR}/CDW_support_mail_list.txt
export MAIL_RPT_FILE=${CNTLDIR}/${SRC_FILE_NAME}_support_mail_list.txt

CDWInfo "SET_VAR() Ended ........"
}

#------------------------------------------------------------------------------
# This Function Set the LOG file for that day and append if exists
#------------------------------------------------------------------------------
SET_LOG_FILE()
{
CDWInfo "SET_LOG_FILE() Started ........"

export LOGFILE=${LOGDIR}/${PROG}_${EXT}.log
exec 3>> $LOGFILE

if [ $? -ne 0 ]
then
CDWErr "CANNOT write/create log file:${LOGFILE}"
CDWErr "SET_LOG_FILE() Terminated ........"
exit 9
else
CDWInfo "$PROG Started ..... " >&3 2>&3
CDWInfo "Setting Environment to:${ENV} and Data Base to:${DB2DBDFT}" >&3 2>&3
fi

CDWInfo "SET_LOG_FILE() Ended ........"
CDWInfo "SET_LOG_FILE() Ended ........" >&3 2>&3
}

#------------------------------------------------------------------------------
# This Function Send e-mail in this program fail
#----------------------------------------------------------- -------------------

SEND_MAIL()
{
FILE_TO_MAIL=$1
MAIL_FILE_NAME=$2

if [ -f ${MAIL_FILE_NAME} ]
then
MAIL_LIST=`cat ${MAIL_FILE_NAME} `
else
MAIL_LIST="nitin_gulati@freddiemac.com,apurva_patel@freddiemac.com"
fi

cat ${FILE_TO_MAIL} |mail -s "${MAIL_MSG}" ${MAIL_LIST}
}

#------------------------------------------------------------------------------
# This Function will clean 10 days or older log and outfile for LOB & DATA_TYPE
#------------------------------------------------------------------------------
CLEAN_UP_LOG()
{
MOVE_AND_COMPRERSS ${DATA_FILE}
MOVE_AND_COMPRERSS ${DATA_FILE}.txt

CDWInfo "CLEAN_UP_LOG Started ..." >&3 2>&3

## Clean-up of old log file -main programe
for FILE in `find ${LOGDIR} -name ${PROG}.*.log -mtime +30 `
do
rm -f ${FILE} && printf "removed: %s for being more than 10 days(s) old.\n" $FILE | tee -a ${LOGFILE}
done

## Clean-up of Old Source/CDW date file
for FILE in `find ${DATA_IN} -name ${SRC_FILE_NAME}*.*.*.Z -mtime +160 `
do
rm -f ${FILE} && printf "removed: %s for being more than 160 days(s) old.\n" $FILE | tee -a ${LOGFILE}
done

## Clean-up of Old Control Report file
for FILE in `find ${DATA_OUT} -name ${CNTL_RPT_FILE}_*.csv -mtime +60 `
do
rm -f ${FILE} && printf "removed: %s for being more than 40 days(s) old.\n" $FILE | tee -a ${LOGFILE}
done

CDWInfo "CLEAN_UP_LOG Ended ..." >&3 2>&3
}

#------------------------------------------------------------------------------
# This Function will move and compress PS and CDW Data Files
#------------------------------------------------------------------------------
MOVE_AND_COMPRERSS()
{
export FILE_NAME=$1

CDWInfo "Move and compress started for ${FILE_NAME}" >&3 2>&3

if [ -f ${FILE_NAME} ]
then
chmod 644 ${FILE_NAME}

mv -f ${FILE_NAME} ${FILE_NAME}.${EXT}

if [ $? -eq 0 ]
then
compress -f ${FILE_NAME}.${EXT}
else
CDWWarn "${PROG}:compress Failed for: ${FILE_NAME}" >&3 2>&3
fi
else
CDWWarn "File: ${FILE_NAME} NOT FOUND " >&3 2>&3
fi

CDWInfo "Move and compress ended for ${FILE_NAME}" >&3 2>&3
}

#------------------------------------------------------------------------------
# This Function prepare list of Available files
#------------------------------------------------------------------------------
PREP_LOAD_RDY_FILE()
{
integer in_rec_cnt

if [ -f ${DATA_FILE} ]
then
in_rec_cnt=`cat ${DATA_FILE} | wc -l`
else
CDWWarn "Cannot find input file:${DATA_FILE}" >&3 2>&3
exit 0
fi

if [ ${in_rec_cnt} -le 0 ]
then
CDWInfo "No Detail record found in input data file:${DATA_FILE}" >&3 2>&3
exit 0
else
cat ${DATA_FILE} | grep -c '^FTR' | read NO_OF_FTR_REC

if [ ${NO_OF_FTR_REC} -ne 1 ]
then
CDWErr "Footer record is missing or More then one footer record " >&3 2>&3
MAIL_MSG="${ENV}:Footer record is missing or More then one footer record "
SEND_MAIL ${LOGFILE} ${MAIL_CDW_SUPPORT}
exit 9
else
IFS=,
cat ${DATA_FILE} | grep '^FTR'| sed -e 's/ //g' | cut -d, -f 2-5 | read DT_AUTO_VALUTN_CFCNT_CREATN DT_AUTO_VALUTN_CFCNT_APPRL TOTAL_OF_HASH_COEFF NO_OF_DATA_ROWS

unset IFS

echo "DT_AUTO_VALUTN_CFCNT_CREATN:${DT_AUTO_VALUTN_CFCNT_CREATN} "
echo "DT_AUTO_VALUTN_CFCNT_APPRL:${DT_AUTO_VALUTN_CFCNT_APPRL} "
echo "TOTAL_OF_HASH_COEFF:${TOTAL_OF_HASH_COEFF} "
echo "NO_OF_DATA_ROWS:${NO_OF_DATA_ROWS} "
fi

fi

grep -v '^FTR' ${DATA_FILE} > ${DATA_FILE}.txt

if [ $? -ne 0 ]
then
CDWErr "Failed to Create File:${DATA_FILE}.txt from ${DATA_FILE}.txt" >&3 2>&3
MAIL_MSG="${ENV}:Failed to Create File:${DATA_FILE}.txt from ${DATA_FILE}.txt"
SEND_MAIL ${LOGFILE} ${MAIL_CDW_SUPPORT}
exit 9
else
integer CDW_REC_CNT=`cat ${DATA_FILE}.txt | wc -l `

IFS=,
integer NO_OF_DT_EFF=`cat ${DATA_FILE}.txt | cut -d, -f 1 | sort -u | wc -l`
unset IFS

if [ ${NO_OF_DT_EFF} -eq 1 ]
then
IFS=,
head -1 ${DATA_FILE}.txt | cut -d, -f 1 | read DT_AUTO_VALUTN_CFCNT_EFF
unset IFS
else
CDWErr "${ENV}: More then one DT_AUTO_VALUTN_CFCNT_EFF Found from ${DATA_FILE}" >&3 2>&3
MAIL_MSG="${ENV}:More then one DT_AUTO_VALUTN_CFCNT_EFF Found from ${DATA_FILE}"
SEND_MAIL ${LOGFILE} ${MAIL_CDW_SUPPORT}
exit 9
fi
fi

if [ ${CDW_REC_CNT} -ne ${NO_OF_DATA_ROWS} ]
then
CDWErr "Record Count Between File:${DATA_FILE}.txt(${CDW _REC_CNT}) and Footer(${NO_OF_REC_FTR}) didnot match" >&3 2>&3
MAIL_MSG="${ENV}:Record Count Between File:${DATA_FILE}.txt(${CDW_REC_CNT}) and Footer(${NO_OF_REC_FTR}) didnot match"
SEND_MAIL ${LOGFILE} ${MAIL_CDW_SUPPORT}
exit 9
else
RUN_DS_JOB
GEN_CNTL_RPT
fi
}

#------------------------------------------------------------------------------
# This Function Control Report
#-----------------------------------------------------------------------------
CNTL_REPT_AMT()
{
export NET_COEFF_AMT
NET_COEFF_AMT=`echo "${TOTAL_OF_HASH_COEFF}-(${TOTAL_OF_COEFF})" | bc `
echo "Total Sum of Coeff:,${TOTAL_OF_HASH_COEFF},${TOTAL_OF_COEFF},${NET_COEFF_AMT}\c" >> ${CNTL_RPT_FILE}
if [ ${NET_COEFF_AMT} == 0 ]
then
echo "${RECON}" >> ${CNTL_RPT_FILE}
echo " Sum of Hash Coeff calculated from table ${TABLE_NAME} matches with the Footer Coeff total " >&3 2>&3
else
echo "${NOT_RECON}" >> ${CNTL_RPT_FILE}
echo " There is a mismatch between Sum of HasH Coeff calculated from table ${TABLE_NAME} and the Footer Coeff Total" >&3 2>&3
fi
}

#-----------------------------------------------------------------------------
# Control Report for Record Count
#----------------------------------------------------------------------------
CNTL_REPT_CNT()
{
export NET_REC_COUNT
NET_REC_COUNT=`expr ${NO_OF_DATA_ROWS} - ${RowCount}`
echo "Number of Record Count:,${NO_OF_DATA_ROWS},${RowCount},${NET_REC_COUNT}\c" >> ${CNTL_RPT_FILE}
if [ ${NET_REC_COUNT} == 0 ]
then
echo "${RECON}" >> ${CNTL_RPT_FILE}
echo " Total number of rows from table ${TABLE_NAME} matches with the Footer total count " >&3 2>&3
else
echo "${NOT_RECON}" >> ${CNTL_RPT_FILE}
echo " There is a mismatch between number of rows in table ${TABLE_NAME} and the Footer total count" >&3 2>&3
fi
}
#---------------------------------------------------------------------------
# Send Control Report
#--------------------------------------------------------------------------
SEND_REPT()
{
if ( [ ${NET_COEFF_AMT} -eq 0 ] && [ ${NET_REC_COUNT} -eq 0 ] )
then
export STATUS=PASS

else
export STATUS=FAILED
fi

export MAIL_MSG="REO Valution Coeff Control Report --> ${STATUS}"

SEND_MAIL ${CNTL_RPT_FILE} ${MAIL_RPT_FILE}
}
#------------------------------------------------------------------------------
# This Function Run Data Stage Job
#------------------------------------------------------------------------------
RUN_DS_JOB()
{
CDWInfo "Running DataStage Job:${DS_JOB_NAME} on DS Project:${DS_INST}" >&3 2>&3

export DS_JOB_NAME=pxLdMolReoValCfcntHst

dsjob -param DATA_IN=${DATA_FILE}.txt -param DT_AUTO_VALUTN_CFCNT_APPRL=${DT_AUTO_VALUTN_CFCNT_APPRL} -param DT_AUTO_VALUTN_CFCNT_CREATN=${DT_AUTO_VALUTN_CFCNT_CREATN} ${DS_INST} ${DS_JOB_NAME} > /dev/null

Ret_Code=$?

if ( [ ${Ret_Code} -eq 0 ] || [ ${Ret_Code} -eq 4 ] )
then
CDWInfo "Load Job:${DS_JOB_NAME} completed for Data File:${DATA_FILE}.txt with Return Code:${Ret_Code} on DS_PROJ:${DS_INST}" >&3 2>&3
else
CDWWarn "${DS_JOB_NAME} FAILED for Data File:${DATA_FILE}.txt with Return Code:${Ret_Code} on DS_PROJ:${DS_INST}" >&3 2>&3
exit 9
fi
}

GEN_CNTL_RPT()
{
CDWInfo "Setting Control Report File to:${CNTL_RPT_FILE}" >&3 2>&3

if [ -f ${CNTL_RPT_FILE} ]
then
rm -f ${CNTL_RPT_FILE}
fi

echo "REO Valutions Coeff Control Report,,,Run Date: `date`" > ${CNTL_RPT_FILE}

if [ $? -ne 0 ]
then
CDWErr "Failed to create Control Report File:${CNTL_RPT_FILE} " >&3 2>&3
MAIL_MSG="${ENV}:Failed to create Control Report File:${CNTL_RPT_FILE}"
SEND_MAIL ${LOGFILE} ${MAIL_CDW_SUPPORT}
exit 9
fi

echo "REO Valutions Coeff Control Summary Report" >> ${CNTL_RPT_FILE}
echo >> ${CNTL_RPT_FILE}
echo "Control Item:,CLAS,CDW,Difference,Comments" >> ${CNTL_RPT_FILE}

${DB2_BIN}/db2 -x "select sum(val_auto_valutn_cfcnt), count(*) from udbadm.${TABLE_NAME} where DT_AUTO_VALUTN_CFCNT_EFF = '${DT_AUTO_VALUTN_CFCNT_EFF}' and date(dt_srce_end) = '9999-01-01-00.00.00.000000' and flag_del = 'N' " | read TOTAL_OF_COEFF RowCount

if [ $? -ne 0 ]
then
CDWErr "Failed to retrive data from udbadm.${TABLE_NAME}" >&3 2>&3
MAIL_MSG="${ENV}:Failed to retrive data from udbadm.${TABLE_NAME}"
SEND_MAIL ${LOGFILE} ${MAIL_CDW_SUPPORT}
exit 9
else
CNTL_REPT_AMT
CNTL_REPT_CNT
SEND_REPT
fi
}

#########################################################################################
#
# MAIN
#
#########################################################################################
export NOT_RECON=",NOT-Reconciled"
export RECON=",Reconciled"

export PROG=`basename $0`
export CURR_TS="date +"%Y-%m-%d-%H.%M.%S""
export EXT=`date +"%Y%m%d"`
export EXT_LONG=`date +"%Y-%m-%d-%H.%M.%S"`
echo "#BEGIN:`$CURR_TS` :$PROG Started .... "

. /fmac/users/cdwmgr/.profile

. /fmac/users/cdwmgr/.setServer

if [ $? -ne 0 ]
then
CDWErr "/fmac/users/cdwmgr/.setServer Failed ........"
exit 9
else
. /fmac/users/cdwmgr/.setPXEnvironment

if [ $? -ne 0 ]
then
CDWErr "/fmac/users/cdwmgr/.setPXEnvironment Failed ........"
exit 9
fi
fi

. /fmac/${ENV}/mis/cdw/CDWprofile
. /udb/home/$DB2PROF/sqllib/db2profile
export DB2_BIN=/udb/home/${DB2INSTANCE}/sqllib/bin

trap 'trapfunc' INT QUIT TERM

function usageerr
{
echo "\n\nUSAGE: ${PROG} [ -d {CDWD/CDWU/CDWP/CDWPBCP} ] [ -p {CDW_DEV/CDW_PROD} "
echo
echo "Example ${PROG} -d CDWD -p CDW_DEV \n\n"
exit 2
}

function trapfunc
{
CDWErr "${PROG}: Signal caught. Exiting with code: $RC" | tee -a $LOGFILE
exit 255
}

while getopts :d:p:h arguments
do
case ${arguments} in
d) TRGT_DB=${OPTARG};;
p) DS_INST=${OPTARG};;
h) usageerr;;
:) print "$PROG: $OPTARG requires a value."
usageerr;;
?) print "$PROG: Invalid option: $OPTARG"
usageerr;;
esac
done

shift OPTIND-1

export TABLE_NAME=mol_reo_auto_valutn_cfcnt_hst

SET_VAR
SET_LOG_FILE

if [ ${TRGT_DB} ]
then
export DB2DBDFT=`echo ${TRGT_DB} | tr "[a-z]" "[A-Z]"`
fi

if [ ${DS_INST} ]
then
CDWInfo "Setting Data Stage Project to:${DS_INST}" >&3 2>&3
else
if [ ${ENV} = "dev" ]
then
DS_INST=CDW_DEV
else
DS_INST=CDW_PROD
fi

CDWInfo "Setting Default Data Stage Project to:${DS_INST}" >&3 2>&3
fi

export DB2INSTANCE=${DB2PROF}
PREP_LOAD_RDY_FILE

 

 

-----Original Message-----
From: Yadlapati Srikanth
Sent: Tuesday, December 11, 2007 11:28 AM
To: Vanamamalai Murali
Subject: FW: sample scripts

 

 

 

-----Original Message-----
From: Yadlapati Srikanth
Sent: Wednesday, December 05, 2007 3:54 PM
To: 'jasingh2@in.ibm.com'
Subject: sample scripts

 

Sample datastage script

**********************************************************************

CONFIDENTIALITY: This communication, including attachments, is for the exclusive use of the

addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended

recipient, any use, copying, disclosure, or distribution or the taking of any action in reliance upon this

information is strictly prohibited. If you are not the intended recipient, please notify the sender

immediately and delete this communication and destroy all copies.

**********************************************************************