Bteq Sample Script

  • November 2019
  • PDF

This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA


Overview

Download & View Bteq Sample Script as PDF for free.

More details

  • Words: 597
  • Pages: 4
# #*************************************************************** #!/bin/ksh echo `date +'%m_%d_%y%h:%m:%s'` "******************begin of bteq task******************* " stage_database="$1" stage_table="$2" target_database="$3" target_table="$4" bteq_file="$5" rpt_data="$6" # check if all the parameters are passed if [ $# -eq 6 ] && [ $rpt_data = "rpt" -o $rpt_data = "rpt" ] then rpt_pass=$idw_cmd/idwpass_rpt echo `date +'%m_%d_%y%h:%m:%s'` "rpt data is present" else rpt_pass=$idw_cmd/idwpass echo `date +'%m_%d_%y%h:%m:%s'` "rpt data is not present" fi # check for the connection string file idwpass or idwpass_rpt if [ -f $rpt_pass ] then echo `date +'%m_%d_%y%h:%m:%s'` "file found: " $rpt_pass else echo `date +'%m_%d_%y%h:%m:%s'` "error idw_ins_sel_bore.ksh: $rpt_pass not found" exit 1 fi echo "get the max(dw_eff_dt) from $target_database.$target_table" max_date_file=/opt/idw/pr/data/max_date.dat rm -f $max_date_file bteq <<eofbteq `/bin/cat $rpt_pass` .export file=$max_date_file sel coalesce(max(dw_eff_dt),cast('0001-01-01'as date)) (format 'yyyymmdd') (title '') from $target_database.$target_table; .export reset .quit; eofbteq # catch the return code err1=$? if [ $err1 -ne 0 ] then echo `date +'%m_%d_%y%h:%m:%s'` "error while getting max(dw_eff_dt) from $target_database.target_table" exit 1 else echo `date +'%m_%d_%y%h:%m:%s'` "max(dw_eff_dt) exported successfully" fi #put in a variable the max date max_date=`/bin/cat $max_date_file`

echo "max_date = $max_date" if [ $max_date == '00010101' ] then echo "no max_date in $target_database.$target_table" else echo "get the 3rd business day of the month of max(dw_eff_dt)" business_day3_file=/opt/idw/pr/data/business3.dat rm -f $business_day3_file bteq <<eofbteq `/bin/cat $rpt_pass` .export file=$business_day3_file sel calendar_date (format 'yyyymmdd') (title '')from ( sel calendar_date ,month_day_number ,rank(month_day_number asc) as business_day ,month_number ,day_of_week,holiday ,business_week_end_day ,business_month_end_day from $target_database.date_dim_t where cast(year_month as char(6))= substring('$max_date' from 1 for 6) and holiday = 0 qualify business_day = 3 ) t; .export reset .quit; eofbteq # catch the return code err1=$? if [ $err1 -ne 0 ] then echo `date +'%m_%d_%y%h:%m:%s'` "error while getting 3rd business day from date_dim_t " exit 1 else echo `date +'%m_%d_%y%h:%m:%s'` "3rd busines day exported successfully" fi business_day3=`/bin/cat $business_day3_file` if [ -z $business_day3 ] then echo "error in retrieving 3rd business day from the date dimension table" exit 1 else echo "3rd business day =$business_day3" fi if [ $max_date == $business_day3 ] then echo "max date from monthly table same as the 3rd business day for the month"

else bkp=$target_table'_bk' bteq<<eofbteq `/bin/cat $rpt_pass` .set errorlevel 3108 severity 0; drop table $stage_database.$bkp; .set errorlevel 3108 severity 8; create table $stage_database.$bkp as $target_database.$target_table with no data; .if errorcode !=0 then .quit errorcode; insert into $stage_database.$bkp sel * from $target_database.$target_table where dw_eff_dt = cast('$max_date' as date format 'yyyymmdd'); .if errorcode !=0 then .quit errorcode; delete from $target_database.$target_table where dw_eff_dt = cast('$max_date' as date format 'yyyymmdd'); .if errorcode !=0 then .quit errorcode; .quit; eofbteq # catch the return code err1=$? if [ $err1 -ne 0 ] then echo `date +'%m_%d_%y%h:%m:%s'` "error while deleting records of date:$max_date from $target_database.$target_table " exit 1 else echo `date +'%m_%d_%y%h:%m:%s'` "records of date: $max_date deleted from $target_database.$target_table" fi fi #end of test condition max=bus3 fi # end of loop which test if max_date is not null #load records from staging to target table temp_bteq=$idw_logs/reorg_recon/stg_idw_ins_sel_detl_tmp.bteq comb_bteq=$temp_bteq"_pass" rm -f $temp_bteq rm -f $comb_bteq # replace the database variable names echo `date +'%m_%d_%y%h:%m:%s'` "replacing varibales in bteq script" sed -e "s/\$stage_database/$stage_database/g" -e "s/\$target_database/$target_database/g" $bteq_file >$temp_bteq

# concatenate passwd file and temp bteq file to a combined temp file cat $rpt_pass $temp_bteq > $comb_bteq

# call bteq to load target table echo "start of loading target table $target_table" bteq <$comb_bteq # catch the return code err1=$? #remove the temp files before exiting rm -f $temp_bteq rm -f $comb_bteq if [ $err1 -ne 0 ] then echo `date +'%m_%d_%y%h:%m:%s'` "error while loading records from staging table to target table" exit 1 else echo `date +'%m_%d_%y%h:%m:%s'` "records loaded from stage table to target table $target_database.$target_table" fi echo `date +'%m_%d_%y%h:%m:%s'` "****************end of bteq task *************** "

Related Documents

Bteq Sample Script
November 2019 2
Sample Script
May 2020 1
Script
October 2019 23
Script
April 2020 14