Bteq Sample Script

# #*************************************************************** #!/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 *************** "