Notes

############################################################################
# File Name : mbis_lib.sh
# Description : This is the common functions used by MBIS loading
#
# YYYYMMDD Name Description
# ------------------------------------------------------------------------ #
# 20180124 Yiting Nan Created
# 20180201 Yiting Nan Update with metadata changes
# 20180214 Yiting Nan Update to Netezza
############################################################################

# common message, error message, etc
############# Wrapper for P1/p12/p14/p16 "create_etl_job_track ${DATASET} ${FILEDATE}"
# create a new ETL job if not loaded before or load with error.
# if reload is 'FALSE', check the status first.
# export ETL_JOB_TRACK_SK
create_etl_job_track()
{
DATASET=$1
FILEDATE=$2

if [ ${RELOAD} = "FALSE" ]
then
#Check if there is another active process loading MBIS files
v_etl_job_track_status_out=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
ETL_JOB_TRACK_SK varchar2(200) := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.get_etl_job_track_status('${DATASET}', to_date('${FILEDATE}', 'YYYYMMDD'), ETL_JOB_TRACK_SK);
dbms_output.put_line(ETL_JOB_TRACK_SK);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)

if [[ ${v_etl_job_track_status_out} =~ "ORA" ]] || [[ ${v_etl_job_track_status_out} == "" ]];
then
echo "ORA-Error trying to check the loading status in the MD_ETL_JOB_TRACK table for ${DATASET}! : ${v_etl_job_track_status_out} " >> ${LOG_FILE}
send_email "${DATASET} on ${FILEDATE} loading error" "ORA Load Status: ORA-Error trying to check the loading status in the MD_ETL_JOB_TRACK table for file!"
exit 1
fi

if [ ${v_etl_job_track_status_out} = "SUCCESS" ]
then
echo `date` "${DATASET} on ${FILEDATE} is loaded before. Continue on next one. " >> ${LOG_FILE}
exit 0
fi
fi

# RELOAD !=FALSE or [ ${v_etl_job_track_status_out} = "ERROR" ] || [ ${v_etl_job_track_status_out} = "NOTFOUND" ]

ETL_JOB_TRACK_SK=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
v_etl_job_track_sk integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.create_etl_job_track('${DATASET}', 'NULL', to_date('${FILEDATE}', 'YYYYMMDD'), v_etl_job_track_sk);
dbms_output.put_line(v_etl_job_track_sk);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)

if [[ ${ETL_JOB_TRACK_SK} =~ "ERROR" ]] || [[ ${ETL_JOB_TRACK_SK} =~ "ORA" ]] || [[ ${ETL_JOB_TRACK_SK} == "" ]];
then
echo "ORA-Error create a record in the MD_ETL_JOB_TRACK table for ${DATASET}!" >> ${LOG_FILE}
send_email "${DATASET} on ${FILEDATE} loading error" "ORA-Error trying to create a record in the MD_ETL_JOB_TRACK table for file: ${DATASET}!"
exit 1
fi

echo "${DATASET} on ${FILEDATE} get a new ETL_JOB_TRACK_SK: ${ETL_JOB_TRACK_SK}. " >> ${LOG_FILE}

export ETL_JOB_TRACK_SK

}


############# Wrapper for P2-10 create_etl_job_track_phase ${LOADPHASE[0]}
# PROCEDURE create_etl_job_track_phase(etl_job_track_sk_in IN integer, dataset_phase_id_in IN varchar2, etl_job_track_phase_sk_out OUT integer)
# PROCEDURE error_etl_job_track(etl_job_track_sk_in IN integer, update_count OUT integer);
# export ETL_JOB_TRACK_PHASE_SK
create_etl_job_track_phase()
{
PHASENAME=$1

ETL_JOB_TRACK_PHASE_SK=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
v_etl_job_track_phase_sk integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.create_etl_job_track_phase(${ETL_JOB_TRACK_SK}, '${PHASENAME}', v_etl_job_track_phase_sk);
dbms_output.put_line(v_etl_job_track_phase_sk);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)

if [[ ${ETL_JOB_TRACK_PHASE_SK} =~ "ERROR" ]] || [[ ${ETL_JOB_TRACK_PHASE_SK} =~ "ORA" ]] || [[ ${ETL_JOB_TRACK_PHASE_SK} == "" ]];
then
echo "ORA-Error trying to create a record in the MD_ETL_JOB_TRACK_PHASE table for ${ETL_JOB_TRACK_SK}!: " ${ETL_JOB_TRACK_PHASE_SK} >> ${LOG_FILE}
error_etl_job_track
send_email "Failed to create a record in the MD_ETL_JOB_TRACK_PHASE table for ${ETL_JOB_TRACK_SK}" "ORA MBIS Data Load Status: ORA-Error trying to update record in the MD_ETL_JOB_TRACK_PHASE table for ${ETL_JOB_TRACK_SK}!"
exit 1
fi

echo "create a record in the MD_ETL_JOB_TRACK_PHASE was successful for ${ETL_JOB_TRACK_SK}: ${ETL_JOB_TRACK_PHASE_SK}" >> ${LOG_FILE}
export ETL_JOB_TRACK_PHASE_SK

}


############# Wrapper for P3-8-10 create_etl_job_track_file ${DATASET} ${FILE_PATH} ${DATEFILENAME} ${FILEDATE}
# PROCEDURE create_etl_job_track_file (etl_job_track_sk_in IN integer,dataset_id_in IN varchar2,source_file_name_in IN varchar2,source_file_date_in IN date,source_file_os_time_in IN timestamp DEFAULT null,edgar_document_id_in IN integer DEFAULT null,etl_job_track_file_sk_out OUT integer);
# PROCEDURE error_etl_job_track_phase(etl_job_track_phase_sk_in IN integer, update_count OUT integer);
# PROCEDURE error_etl_job_track(etl_job_track_sk_in IN integer, update_count OUT integer)
# export ETL_JOB_TRACK_FILE_SK
create_etl_job_track_file()
{
dataset=$1
file_path=$2
datefilename=$3
file_date=$4

ETL_JOB_TRACK_FILE_SK=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
v_etl_job_track_file_sk integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.create_etl_job_track_file(${ETL_JOB_TRACK_SK}, '${dataset}', '${file_path}/${datefilename}.csv.gz', to_date('${file_date}', 'YYYYMMDD'), SYSTIMESTAMP, NULL, v_etl_job_track_file_sk);
dbms_output.put_line(v_etl_job_track_file_sk);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)
if [[ ${ETL_JOB_TRACK_FILE_SK} =~ "ERROR" ]] || [[ ${ETL_JOB_TRACK_FILE_SK} =~ "ORA" ]] || [[ ${ETL_JOB_TRACK_FILE_SK} == "" ]];
then
echo "ORA-Error trying to create a record in the MD_ETL_JOB_TRACK_FILE table for file: ${file_path}/${datefilename}.csv.gz!" >> ${LOG_FILE}
error_etl_job_track_phase
error_etl_job_track
echo "Failed to create a record in the MD_ETL_JOB_TRACK_FILE table for file ${file_path}/${datefilename}.csv.gz" >> ${LOG_FILE}
send_email "Failed to create a record in the MD_ETL_JOB_TRACK_FILE table for file ${file_path}/${datefilename}.csv.gz" "ORA MBIS Data Load Status: ORA-Error trying to create a record in the MD_ETL_JOB_TRACK_FILE table for file: ${file_path}/${datefilename}.csv.gz!"
exit 1
else
echo "Creating a record in the MD_ETL_JOB_TRACK_FILE was successful for file: ${file_path}/${datefilename}.csv.gz" >> ${LOG_FILE}
fi
echo "New ETL_JOB_TRACK_FILE_SK: ${ETL_JOB_TRACK_FILE_SK}" >> ${LOG_FILE}

export ETL_JOB_TRACK_FILE_SK
}


############# Wrapper for P4-6-8-10 create_etl_process_log ${status} ${msg}
# status can be "ERROR", "INFO"
# PROCEDURE create_etl_process_log(etl_job_track_sk_in IN integer, etl_job_track_phase_sk_in IN integer DEFAULT null, etl_job_track_file_sk_in IN integer DEFAULT null, message_type_in IN varchar2, message_in IN varchar2, number_records_error_in IN integer DEFAULT null, etl_process_log_sk_out OUT integer);
# PROCEDURE error_etl_job_track_file(etl_job_track_file_sk_in IN integer, update_count OUT integer);
# PROCEDURE error_etl_job_track_phase(etl_job_track_phase_sk_in IN integer, update_count OUT integer);
# PROCEDURE error_etl_job_track(etl_job_track_sk_in IN integer, update_count OUT integer)
create_etl_process_log()
{
status=$1
msg=$2

v_etl_process_log_sk=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
etl_process_log_sk integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.create_etl_process_log(${ETL_JOB_TRACK_SK}, ${ETL_JOB_TRACK_PHASE_SK}, ${ETL_JOB_TRACK_FILE_SK}, '${status}', '${msg}', NULL, etl_process_log_sk);
dbms_output.put_line(etl_process_log_sk);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)

if [[ ${v_etl_process_log_sk} =~ "ERROR" ]] || [[ ${v_etl_process_log_sk} =~ "ORA" ]];
then
echo "ORA-Error trying to create a record in the MD_ETL_PROCESS_LOG table for for file ${ETL_JOB_TRACK_FILE_SK}: ${v_etl_process_log_sk}" >> ${LOG_FILE}
error_etl_job_track_file
error_etl_job_track_phase
error_etl_job_track

send_email "failed in creating log " " JOB_ID: ${ETL_JOB_TRACK_SK} PHASE_ID: ${ETL_JOB_TRACK_PHASE_SK} FILE_ID: ${ETL_JOB_TRACK_FILE_SK}, ${status}, ${msg}. n /
ORA Data Load Status: ORA-Error trying to to create a record in the MD_ETL_PROCESS_LOG table for ETL_JOB_TRACK_FILE_SK=${ETL_JOB_TRACK_FILE_SK}!"
exit 1
else
echo "Creating a record in the MD_ETL_PROCESS_LOG was successful for ${ETL_JOB_TRACK_FILE_SK}!" >> ${LOG_FILE}
fi

}


############# Wrapper for P5-8-10 complete_etl_job_track_file
# PROCEDURE complete_etl_job_track_file(etl_job_track_file_sk_in IN integer, number_records_inserted_in IN integer, number_records_updated_in IN integer, update_count OUT integer);
# PROCEDURE error_etl_job_track_phase(etl_job_track_phase_sk_in IN integer, update_count OUT integer);
# PROCEDURE error_etl_job_track(etl_job_track_sk_in IN integer, update_count OUT integer)
complete_etl_job_track_file()
{
v_etl_job_track_file_success1=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
number_updates integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.complete_etl_job_track_file(${ETL_JOB_TRACK_FILE_SK}, 1, 0, number_updates);
dbms_output.put_line(number_updates);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)
if [[ ${v_etl_job_track_file_success1} =~ "ERROR" ]] || [[ ${v_etl_job_track_file_success1} =~ "ORA" ]];
then
echo "ORA-Error trying to update record in the MD_ETL_JOB_TRACK_FILE table for file ${ETL_JOB_TRACK_FILE_SK}!" >> ${LOG_FILE}
error_etl_job_track_phase
error_etl_job_track
send_email "Failed in complete_etl_job_track_file for FILE_ID: ${ETL_JOB_TRACK_FILE_SK} " "ORA MBIS Data Load Status: ORA-Error trying to update record in the MD_ETL_JOB_TRACK_FILE table for file ${ETL_JOB_TRACK_FILE_SK}!"
exit 1
else
echo "Updating a record in the MD_ETL_JOB_TRACK_FILE was successful for file ${ETL_JOB_TRACK_FILE_SK}" >> ${LOG_FILE}
fi
}