Code:
#!/bin/ksh
#set -x
#ksh shell_to_sql.ksh -d db1 -f 2015094 -o tera1 -l use_act -r run -i use_act_tbl -k unique_key_col -g 0
##----------------------------------------------------##
# Constants and Variables
##----------------------------------------------------##
export SCRIPT_NAME_FULL=$(basename $0)
export SCRIPT_NAME=${SCRIPT_NAME_FULL%.*}
export USAGE_STRING=`cat <<-EOF
Usage: ${SCRIPT_NAME}
Required:
-o TD_ODBC_DSN <Teradata DSN>
-d TD1_DATABASE <Teradata Database>
-i INPUT_TBL <Input Derivation Table>
-f F_KEY <yyyyddd date>
-r RUN_TYPE <Run type>
-k KEY_COL <Key Column>
-l user_act_NM <user act Name>
-g DEBUG_FLG <Debug Flag>
EOF`
#Set Defaults for all variables
export USER=$(whoami)
export PWD=$HOME
export DELIMITER='|'
# Get the options from the command line
while getopts d:f:o:l:r:i:k:g OPTION ; do
case ${OPTION} in
d) export TD1_DATABASE=${OPTARG};;
f) export F_KEY=${OPTARG};;
o) export TD_ODBC_DSN=${OPTARG};;
l) export user_act_nm=${OPTARG};;
r) export RUN_TYPE=${OPTARG};;
i) export INPUT_TBL=${OPTARG};;
k) export KEY_COL=${OPTARG};;
g) export DEBUG_FLG=1;;
esac
done
# Load the TD ODBC entry
DSN_EXPORT_STATEMENTS=`$HOME/odbc_dsn.ksh ${TD_ODBC_DSN} tera1`
${DSN_EXPORT_STATEMENTS}
#Extracting user_act_nms
echo $user_act_nm>in_usecase_NMs.out
# Set temp files used for fixed and delim determination
export FILE_FIXDEL="$PWD/file.log"
export FILE_FIXDEL_OUT3="$PWD/var_check.out"
export FILE_FIXDEL_OUT4="$PWD/user_act_nmdata.out"
export FILE_FIXDEL_OUT11="$PWD/column_type_check.out"
export FILE_FIXDEL_OUT9="$PWD/vt_varnm_lst.out"
export FILE_FIXDEL_OUT14="$PWD/act_cd.out"
export FILE_FIXDEL_OUT01="$PWD/session1.out"
#Execute SQL to determine run_type availability in t_act_cd table.
echo ".export DATA file = "${FILE_FIXDEL_OUT3}";
.SET RECORDMODE OFF;
SELECT 1 FROM mpt_tbl WHERE user_act_nm = '"$user_act_nm"' AND var_nm<>'XXXXXXXX' AND var_nm IS NOT NULL AND TRIM(var_nm)<>'' AND end_dt='9999-12-31' AND var_nm NOT IN (SELECT columnname FROM dbc.COLUMNS WHERE tablename='"$INPUT_TBL"' AND databasename=(SELECT DATABASE));
.export reset;
.export DATA file = "${FILE_FIXDEL_OUT4}";
.SET RECORDMODE OFF;
SELECT usecase_child,usecase_parent,user_act_nm,musecase_id,musecase_nm,var_nm,cma_rule_cond,release_flg,end_flg,start_dt,end_dt FROM mpt_tbl WHERE user_act_nm = '"$user_act_nm"';
.export reset;
.export DATA file = "${FILE_FIXDEL_OUT11}";
.SET RECORDMODE OFF;
select type ("$INPUT_TBL"."$KEY_COL");
.export reset;
.export DATA file = "${FILE_FIXDEL_OUT14}";
.SET RECORDMODE OFF;
select act_cd from t_act_cd where act_desc='"$run_typ_1"';
.export reset;">>mpt_tbl.out
bteq >> ${FILE_FIXDEL} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/mpt_tbl.out;
.export reset;
.export DATA file = '${FILE_FIXDEL_OUT01}';
.SET RECORDMODE OFF;
select session;
.export reset;
.quit;
EOF
if [ "x"$li_error_cd = "x" ]; then #outer error check
li_mdl_cnt_alloth=0
#awk -F '|' ' { print $1 }' usecase_master.out>usecasenm_list.out
#awk '{a[$1]++}a[$1]<2' usecasenm_list.out>usecasenm_list1.out
#sed 's/ //g' user_act_nmdata.out >user_act_nmdata11.out
awk -F '|' '$12 = "9999-12-31" {print $3}' user_act_nmdata.out>user_act_nmdata1.out
#awk -F '|' ' { print $1 }' user_act_nmdata1.out>user_act_nmdata2.out
#sort IP_usecase_NMs.out> IP_usecase_NMss.sorted
sort user_act_nmdata1.out> user_act_nmdata1.sorted
comm -23 in_usecase_NMs.out user_act_nmdata1.sorted >usecase_check.out
if [ $(wc -l < usecase_check.out) -ge 1 ]; then
echo "usecaseATION EXISTANCE CHECK PASSED"
#variable existence check
if [ $(wc -l < var_check.out) -ge 1 ]; then
export li_error_cd=59;
if [ $li_debug_flg = "1" ]; then
echo ""
echo "Error !! Column not found in dbc.columns.."
fi
fi
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 3 : Variable list preparation.."
fi
if [ "x"$li_error_cd = "x" ]; then
export FILE_FIXDEL9="$PWD/file9.log"
export FILE_FIXDEL_OUT9="$PWD/vt_varnm_lst.out"
echo ".export DATA file = "${FILE_FIXDEL_OUT9}";
.SET RECORDMODE OFF;
SELECT var_nm FROM mpt_tbl WHERE user_act_nm = '"$user_act_nm"' AND var_nm<>'XXXXXXXX' AND var_nm IS NOT NULL AND end_dt='9999-12-31' AND TRIM(var_nm)<>'' GROUP BY var_nm;
.export reset;">>vt_varnm_lstt.out
bteq >> ${FILE_FIXDEL9} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.export DATA file = '${FILE_FIXDEL_OUT9}';
.RUN FILE = $HOME/vt_varnm_lstt.out;
.export reset;
.quit;
EOF
paste -d, -s vt_varnm_lst.out >lv_var_qry.out
lv_var_qry=$(<lv_var_qry.out)
fi
echo "create table vt_ip_usecase_tbl as (select "$KEY_COL","$lv_var_qry" from "$INPUT_TBL" where feed_key="$F_KEY") with data;">>vt_ip_usecase_tbl.out
tr '[:upper:]' '[:lower:]' < in_usecase_NMs.out > IP_usecase_NMs1.out
if [ "x"$li_error_cd = "x" ]; then #outer error code check
if [ "x"$li_error_cd = "x" ]; then
export FILE_FIXDEL10="$PWD/file10.log"
#export FILE_FIXDEL_OUT15="$PWD/dec_ds1.out"
export FILE_FIXDEL_OUT10="$PWD/tmp_msm.out"
bteq >> ${FILE_FIXDEL10} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
DEL FROM tmp_msm WHERE user_act_nm = '$user_act_nm';
DEL FROM tmp_msm WHERE session_id in (select session);
.export DATA file = '${FILE_FIXDEL_OUT10}';
.SET RECORDMODE OFF;
INSERT INTO tmp_msm(usecase_child, usecase_parent, user_act_nm, musecase_nm, var_nm,cma_rule_cond, end_flg) SELECT usecase_child, usecase_parent, user_act_nm, musecase_nm, var_nm, LOWER(cma_rule_cond), end_flg FROM mpt_tbl WHERE end_dt='9999-12-31' AND user_act_nm = '$user_act_nm';
update tmp_msm set session_id=(sel session) WHERE user_act_nm = '$user_act_nm';
select session_id,usecase_child, usecase_parent, user_act_nm, musecase_nm, var_nm,cma_rule_cond, end_flg from tmp_msm where user_act_nm='$user_act_nm';
.export reset;
.quit;
EOF
awk -F '|' ' { print $1 }' tmp_msm.out>session.out
export session=`awk '{print }' session.out | sort | uniq`
if [ $(wc -l < tmp_msm.out) -ge 1 ]; then
echo ""
else
li_error_cd=51
fi
if [ "x"$li_error_cd = "x" ]; then #inner error code check
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 4 : creating table vt__usecase_tbl.."
fi
export CHAR_CHECK=`cat $FILE_FIXDEL_OUT11|grep CHAR|wc -l|sed -e 's/^ *//'|sed -e 's/ *$//'`
if [ $CHAR_CHECK -eq 1 ]; then
export lv_key_col='0$KEY_COL'
export lv_col_nm='key_col_chr'
# export lv_alt_col=' '
export lv_alt_col1='key_col'
# export lv_key_col_col1='key_col,'
# export lv_dkey_d_id='d_id,'
echo "create multiset volatile table vt__usecase_tbl("$KEY_COL" varchar(100), user_act_nm varchar(100), usecase varchar(100)) primary index("$KEY_COL") on commit preserve rows;">>vt__usecase_tbl.out
else
export lv_key_col='1$KEY_COL'
export lv_col_nm='key_col'
export lv_alt_col='0'
export lv_alt_col1='key_col_chr'
# export lv_key_col_col1=' '
# export lv_dkey_d_id=' '
echo "create multiset volatile table vt__usecase_tbl("$KEY_COL" bigint, user_act_nm varchar(100), usecase varchar(100)) primary index("$KEY_COL") on commit preserve rows;">>vt__usecase_tbl.out
fi
export FILE_FIXDEL11="$PWD/file11.log"
export FILE_FIXDEL_OUT12="$PWD/tmp_msm12.out"
bteq >> ${FILE_FIXDEL11} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/vt__usecase_tbl.out;
.export reset;
.export DATA file = '${FILE_FIXDEL_OUT12}';
.SET RECORDMODE OFF;
SEL 1 FROM tmp_msm WHERE session_id=$session AND user_act_nm='$user_act_nm' AND cma_rule_cond NOT LIKE '%all%other%' AND cma_rule_cond NOT LIKE '%rule%' AND cma_rule_cond IS NOT NULL AND TRIM(cma_rule_cond)<>' ';
.export reset;
.quit;
EOF
if [ $(wc -l < tmp_msm12.out) -ge 1 ]; then
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 5 : Binary tree logic.."
fi
touch tmp_msm1.out
while read line
do
session_id1=`echo "$line"|cut -f1 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
var_nm=`echo "$line"|cut -f6 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
cma_rule_cond=`echo "$line"|cut -f7 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
end_flg=`echo "$line"|cut -f8 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
if [ $session_id1 = $session -a "$end_flg" = "Z" -a "$cma_rule_cond" != "?" -a "$var_nm" != "xxxxxxxx" ]; then
echo "$line">>tmp_msm1.out
fi
done <tmp_msm.out
echo "CREATE TABLE vt_var_0 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm AS var_nm," >>_$user_act_nm.out
echo "CAST( ' OR ' AS VARCHAR(4)) || var_nm ||CAST( ' is null ) ' AS VARCHAR(15) ) AS newcol,
CAST( CASE WHEN cma_rule_cond NOT LIKE '% missing%' AND INDEX(cma_rule_cond,' to ') > 0 THEN OREPLACE(cma_rule_cond,'to','and_logic')
WHEN cma_rule_cond LIKE '% missing%' AND INDEX(cma_rule_cond,' to ') > 0 THEN OREPLACE(SUBSTR(cma_rule_cond,1,INDEX(cma_rule_cond,' or ')),'to','and1_logic')
WHEN INDEX(cma_rule_cond,'>') > 0 THEN cma_rule_cond
WHEN INDEX(cma_rule_cond,'<') > 0 THEN cma_rule_cond
WHEN INDEX(cma_rule_cond,'<>') > 0 THEN cma_rule_cond
WHEN INDEX(cma_rule_cond,'!=') > 0 THEN cma_rule_cond
WHEN INDEX(cma_rule_cond,',') > 0 THEN ' in (' || cma_rule_cond || ')'
WHEN INDEX(cma_rule_cond,'missing') > 0 THEN OREPLACE(cma_rule_cond,'missing', ' is null ')
ELSE ' = ' ||(CASE WHEN IDN_IsNumeric(cma_rule_cond)=0 THEN ''''||cma_rule_cond||'''' ELSE cma_rule_cond END)
END AS VARCHAR(8000)) cond">>_$user_act_nm.out
echo "FROM tmp_msm WHERE session_id="$session" AND end_flg = 'Z' AND cma_rule_cond IS NOT NULL AND var_nm <> 'xxxxxxxx') with data;">>_$user_act_nm.out
#echo "CREATE TABLE vt_var_1 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, cond|| ' ' ||newcol as cond from vt_var_0 WHERE cond LIKE '%and1_logic%') with data;">>_$user_act_nm.out
#echo "CREATE TABLE vt_var_2 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, OREPLACE(cond,'and1_logic','and_logic') as cond from vt_var_1) with data;">>_$user_act_nm.out
#echo "CREATE TABLE vt_var_3 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol,'between ' ||cond as cond from vt_var_2 WHERE cond LIKE '%and_logic%') with data;">>_$user_act_nm.out
#echo "CREATE TABLE vt_var_4 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, OREPLACE(cond,'and_logic','and') as cond from vt_var_3) with data;">>_$user_act_nm.out
echo "CREATE TABLE vt_var_1 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, case when cond LIKE '%and1_logic%' then cond|| ' ' ||newcol else cond end as cond from vt_var_0) with data;">>_$user_act_nm.out
echo "CREATE TABLE vt_var_2 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, OREPLACE(cond,'and1_logic','and_logic') as cond from vt_var_1) with data;">>_$user_act_nm.out
echo "CREATE TABLE vt_var_3 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol,case when cond LIKE '%and_logic%' then 'between ' ||cond else cond end as cond from vt_var_2 WHERE cond LIKE '%and_logic%') with data;">>_$user_act_nm.out
echo "CREATE TABLE vt_var_4 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, OREPLACE(cond,'and_logic','and') as cond from vt_var_3) with data;">>_$user_act_nm.out
echo " sel trim(usecase_child),trim(usecase_parent),trim(musecase_nm),trim(var_nm),trim(newcol),trim(cond) from vt_var_4;">>_$user_act_nm.out
echo " sel trim(usecase_child),trim(usecase_parent),trim(musecase_nm),trim(var_nm),trim(newcol),trim(cond) from vt_var_4;">>_$user_act_nm.out
export FILE_FIXDEL12="$PWD/file12.log"
#export FILE_FIXDEL_OUT13="$PWD/vt_var.out"
bteq >> ${FILE_FIXDEL12} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/_$user_act_nm.out;
.quit;
EOF
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 6 : All other condition check for given usecaseation tree.."
fi
#awk -F '|' ' { print $6 }' vt_var.out>cond.out
# export cond1=`cat vt_var.out|grep other|wc -l|sed -e 's/^ *//'|sed -e 's/ *$//'`
cond1=0
#all other
if [ $cond1 -ge 1 ]; then
cat vt_var.out|grep other|sed -e 's/^ *//'|sed -e 's/ *$//'>cond2.out
awk -F '|' ' { print $4 }' cond2.out>cond3.out
awk '{print }' cond3.out | sort | uniq >vt_var1.out
#loop3
while read line
do
var_nm=`echo $line|cut -f1 -d"|"`
awk -F '|' ' { print $1, $2, $3, $6 }' cond2.out>cond4.out
sort -k3 -n cond4.out>vt_var2.out
awk -F '|' ' { print $2 }' vt_var2.out>cond5.out
awk '{print }' cond5.out | sort | uniq >vt_var3.out
#loop4
while read line
do
var_nm=`echo $line|cut -f1 -d"|"`
comm -23 vt_var2.out vt_var3.out >vt_var4.out
export li_cma_idx=`cat vt_var4.out | cut -f1 -d " " | sort -nr | tail -1`
cat vt_var4.out|grep other|sed -e 's/^ *//'|sed -e 's/ *$//'>cond6.out
export li_max_cma_idx=`cat cond6.out | cut -f1 -d " " | sort -nr | head -1`
export lv_cma_rule_cond_stmt="null"
#loop5
if [ $li_cma_idx > $li_max_cma_idx ]; then
comm -2 vt_var4.out cond6.out >cond7.out
while read line
do
usecase_child=`echo "$line"|cut -f1 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
#var_nm=`echo "$line"|cut -f6 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
#cma_rule_cond=`echo "$line"|cut -f7 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
#end_flg=`echo "$line"|cut -f8 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
if [ $usecase_child = $li_cma_idx -a $usecase_child < $li_max_cma_idx -a ]; then
export lv_cma_rule_cond=`awk -F '|' ' { print $4 }' cond7.out`
fi
done <cond7.out
cat $lv_cma_rule_cond|grep =|sed -e 's/^ *//'|sed -e 's/ *$//'>cond8.out
cat $lv_cma_rule_cond|grep in|sed -e 's/^ *//'|sed -e 's/ *$//'>cond9.out
if [ $(wc -l < cond8.out) -ge 1 ]; then
export lv_cma_rule_cond=`echo $lv_cma_rule_cond | cut -c1,3`
elif [ $(wc -l < cond9.out) -ge 1 ]; then
export cond10=`echo $lv_cma_rule_cond | wc -l`
cond10=`expr $cond10 - 6`
export lv_cma_rule_cond=`awk ' { print substr($lv_cma_rule_cond,6,$cond10) }'`
fi
if [$lv_cma_rule_cond_stmt = "null"]; then
export lv_cma_rule_cond_stmt='not in('$lv_cma_rule_cond
elif [ $li_cma_idx = $li_max_cma_idx ]; then
export lv_cma_rule_cond_stmt=$lv_cma_rule_cond_stmt')'
else
export lv_cma_rule_cond_stmt=$lv_cma_rule_cond_stmt', '$lv_cma_rule_cond
fi
export li_cma_idx=`expr $li_cma_idx + 1`
fi #loop5 end
#UPDATE a
# FROM vt_var a, vt_var4 b
# SET cond = lv_cma_rule_cond_stmt
# WHERE a.var_nm=lv_var_nm1
# AND a.var_nm=b.var_nm
# AND a.usecase_parent=b.usecase_parent
# AND a.usecase_child=b.usecase_child
# AND a.usecase_child=li_max_cma_idx
# AND cond LIKE '%all%other%';
# DROP TABLE vt_var4;
# SET li_usecase_idx = li_usecase_idx + 1;
# IF li_usecase_idx > li_max_usecase_idx THEN
# LEAVE lb4;
done <vt_var3.out
done < vt_var1.out
fi #all other
while read line
do
session_id=`echo "$line"|cut -f1 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
#var_nm=`echo "$line"|cut -f6 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
#cma_rule_cond=`echo "$line"|cut -f7 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
end_flg=`echo "$line"|cut -f8 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
if [ $session_id = $session -a "$end_flg" = "D" ]; then
awk -F '|' ' { print $4 }' tmp_msm.out>>cond11.out
fi
done <tmp_msm.out
export lv_def_mod=`awk '{print }' cond11.out | sort | uniq `
echo "lv_def_mod"$lv_def_mod
export FILE_FIXDEL17="$PWD/file17.log"
#export FILE_FIXDEL_OUT18="$PWD/vt_varr.out"
bteq >> ${FILE_FIXDEL17} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
create table vt_var_5 as (select xx.usecase_child, yy.usecase_parent,xx.musecase_nm,xx.var_nm,xx.newcol,xx.cond FROM vt_var_4 xx,tmp_msm yy WHERE session_id=$session AND xx.usecase_parent = yy.usecase_child AND yy.end_flg = 'N' AND NULLIF(TRIM(yy.var_nm),'') IS NOT NULL) with data;
.quit;
EOF
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 7 : creating vt_final table.."
fi
echo "CREATE TABLE vt_mname1 as (SELECT usecase_child, usecase_parent, musecase_nm FROM tmp_msm WHERE session_id = "$session" AND end_flg='Y') with data;
CREATE TABLE vt_final_0 as (SELECT a.*, b.usecase_parent AS newp_id, CASE WHEN cond LIKE '%is null ) ' THEN ' when ('||b.var_nm||' '||cond ELSE ' when '||b.var_nm||' '||cond END AS exp1 FROM vt_mname1 a, vt_var_5 b WHERE a.usecase_parent = b.usecase_child) with data;">vt_final.out
export FILE_FIXDEL13="$PWD/file13.log"
bteq >> ${FILE_FIXDEL13} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.SET RECORDMODE OFF;
.RUN FILE = $HOME/vt_final.out;
.quit;
EOF
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 8 : Entering first loop in preparing the sql query.."
fi
i=0
k=1
while [ $i -eq 0 ]
do
export FILE_FIXDEL21="$PWD/file21.log"
export FILE_FIXDEL_OUT23="$PWD/activity_count.out"
m=`expr $k + 1`
l=`expr $k - 1`
bteq >> ${FILE_FIXDEL21} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
create table vt_final_$k as (select xx.usecase_child,xx.usecase_parent,xx.musecase_nm,xx.newp_id,CASE WHEN yy.cond LIKE '%is null ) ' THEN xx.exp1 || ' and (' || yy.var_nm ELSE xx.exp1 || ' and ' || yy.var_nm END as exp1 FROM vt_final_$l xx,vt_var_5 yy WHERE xx.newp_id = yy.usecase_child) with data;
create table vt_final_$m as (select xx.usecase_child,xx.usecase_parent,xx.musecase_nm,yy.usecase_parent as newp_id,CASE WHEN yy.cond LIKE '%is null ) ' THEN xx.exp1 || ' and (' || yy.var_nm ELSE xx.exp1 || ' and ' || yy.var_nm END as exp1 FROM vt_final_$k xx,vt_var_5 yy WHERE xx.newp_id = yy.usecase_child) with data;
drop table vt_final_$k;
drop table vt_final_$l;
.export DATA file = '${FILE_FIXDEL_OUT23}';
.SET RECORDMODE OFF;
sel count(1) from vt_final_$m;
.export reset;
.quit;
EOF
export activity_count=`tail -1 $FILE_FIXDEL_OUT23|sed -e 's/^ *//'|sed -e 's/ *$//'`
echo "activity_count "$activity_count
if [ $activity_count -eq 0 ]; then
i=1
else
rm $FILE_FIXDEL_OUT23
fi
k=`expr $k + 2`
done
#vt_final_latest= vt_final_$m
echo "latest vt_final" vt_final_$m
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 9 : Done with first loop.."
fi
echo "CREATE TABLE vt_statement1_0 as (SELECT usecase_child, CAST('insert into vt__usecase_tbl select unique_key_col,use_act as user_act_nm,cast((case '||exp1||' then '''||musecase_nm||'''' AS VARCHAR(22000)) AS stmt FROM vt_final_4
WHERE usecase_child = (SELECT MIN(usecase_child) FROM vt_final_4)) with data;">>vt_statement1.out
export FILE_FIXDEL14="$PWD/file14.log"
bteq >> ${FILE_FIXDEL14} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/vt_statement1.out;
.quit;
EOF
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 10 : Entering second loop in preparing sql query.."
fi
j=0
n=1
while [ $j -eq 0 ]
do
o=`expr $n - 1`
#UPDATE xx
#FROM vt_statement1 xx, vt_final yy SET stmt = xx.stmt || ' ' ||yy.exp1 || ' then '''||musecase_nm||'''', usecase_child = yy.usecase_child WHERE yy.usecase_child = (SELECT MIN(usecase_child) FROM vt_final WHERE usecase_child > xx.usecase_child);
export FILE_FIXDEL23="$PWD/file23.log"
export FILE_FIXDEL_OUT24="$PWD/activity_count1.out"
bteq >> ${FILE_FIXDEL23} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
create table vt_statement1_$n as (select yy.usecase_child as usecase_child, xx.stmt || ' ' ||yy.exp1 || ' then '''||musecase_nm||'''' as stmt FROM vt_statement1_$o xx, vt_final_$m yy WHERE yy.usecase_child = (SELECT MIN(usecase_child) FROM vt_final_$m WHERE usecase_child > xx.usecase_child)) with data;
drop table vt_statement1_$o;
.export DATA file = '${FILE_FIXDEL_OUT24}';
.SET RECORDMODE OFF;
sel count(1) from vt_statement1_$n;
.export reset;
.quit;
EOF
export activity_count1=`tail -1 $FILE_FIXDEL_OUT24|sed -e 's/^ *//'|sed -e 's/ *$//'`
echo "activity_count1 "$activity_count1
if [ $activity_count1 -eq 0 ]; then
q=`expr $n + 1`
# export FILE_FIXDEL35="$PWD/file35.log"
touch file35.log
#export FILE_FIXDEL_OUT08="$PWD/vt_statement1111.out"
bteq >> file35.log <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
create table vt_statement1_$q as (select usecase_child,stmt ||' else '||'''$lv_def_mod'''||' end) as varchar(100)) as usecase from vt_ip_usecase_tbl)' as stmt from vt_statement1_$n) with data;
.quit;
EOF
j=1
else
rm $FILE_FIXDEL_OUT24
fi
n=`expr $n + 1`
done
export FILE_FIXDEL36="$PWD/file36.log"
export FILE_FIXDEL_OUT37="$PWD/sql_stmt_prep.out"
bteq >> ${FILE_FIXDEL23} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.export DATA file = '${FILE_FIXDEL_OUT37}';
.SET RECORDMODE OFF;
sel * from vt_statement1_$q;
.export reset;
.quit;
EOF
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 11 : Done with second loop.."
fi
awk -F '|' ' { print $2 }' sql_stmt_prep.out>>cond12.out
export lv_sql_stmt=`awk '{print }' cond12.out | sort | uniq `
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 12 : Execute the prepared sql query to get the usecase details.."
fi
export FILE_FIXDEL15="$PWD/file15.log"
#export FILE_FIXDEL_OUT07="$PWD/session7.out"
bteq >> ${FILE_FIXDEL13} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
CALL dbc.SysExecSQL($lv_sql_stmt);
CALL dbc.SysExecSQL('collect stats on vt__usecase_tbl index("$KEY_COL")');
DEL FROM tmp_msm WHERE session_id = $session;
DEL FROM tmp_msm WHERE user_act_nm = '$user_act_nm';
.export reset;
.quit;
EOF
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 13 : write data to _musecase_decision table.."
fi
li_act_cd=$(<act_cd.out)
if [ $li_act_cd -eq 2 ]; then
# if [ $CHAR_CHECK -eq 1 ]; then
# export lv_alt_col="'"$lv_d_id"'||trim(row_number() over (order by "$KEY_COL"))"
# fi
# if [ $(wc -l <dec_ds1.out) -ge 1 ]; then
#
# echo "insert into _usecase_dec_aud_ds(feed_key,key_col, run_dt, user_act_nm,usecase) select feed_key, key_col, run_dt, user_act_nm,usecase from _usecase_dec_ds where feed_key = "$F_KEY" and user_act_nm ='"$user_act_nm"');
# delete from _usecase_dec_ds where feed_key ="$F_KEY" and user_act_nm ='"$user_act_nm"';">>usecase_dec_aud_ds.out
#
# fi
echo "insert into _usecase_dec_ds (feed_key, "$lv_col_nm ", run_dt, user_act_nm, usecase) select "$F_KEY" as feed_key,"$KEY_COL" as key_col,CURRENT_TIMESTAMP as run_dt,'"$user_act_nm"' as user_act_nm,"li_var" from p_input_tbl_nm ;">>usecase_dec_aud_ds.out
else
echo "insert into _usecase_dec_aud_ds (feed_key, "$lv_col_nm ",run_dt, user_act_nm, usecase) select "$F_KEY" as feed_key,"$KEY_COL" as key_col,CURRENT_TIMESTAMP as run_dt,'"$user_act_nm"' as user_act_nm,"li_var" from p_input_tbl_nm ;">>usecase_dec_aud_ds.out
fi
export FILE_FIXDEL15="$PWD/file15.log"
bteq >> ${FILE_FIXDEL15} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/usecase_dec_aud_ds.out;
.export reset;
.quit;
EOF
else
while read line
do
session_id=`echo "$line"|cut -f1 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
user_act_nm=`echo "$line"|cut -f3 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
musecase_nm=`echo "$line"|cut -f4 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
#cma_rule_cond=`echo "$line"|cut -f7 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
#end_flg=`echo "$line"|cut -f8 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
if [ $session_id = $session -a $user_act_nm = $user_act_nm -a "$musecase_nm" != "?" -a "$musecase_nm" != '']; then
awk -F '|' ' { print $4 }' tmp_msm.out>>li_mdl_cnt_alloth1.out
fi
done <tmp_msm.out
awk '{print }' li_mdl_cnt_alloth1.out | sort | uniq >li_mdl_cnt_alloth.out
export $li_mdl_cnt_alloth = wc -l < li_mdl_cnt_alloth.txt
if [ $li_mdl_cnt_alloth -eq 1 ]; then
lv_musecase_alloth=$(<li_mdl_cnt_alloth1.out)
echo "insert into vt__usecase_tbl select "$KEY_COL",'"$user_act_nm"','"$lv_musecase_alloth" from vt_ip_usecase_tbl";
else
li_error_cd=11
# Log
if [ $li_debug_flg = "1" ]; then
echo "****`date` - Step 14 : binary tree logic is wrong for usecaseation "$user_act_nm".."
fi
fi
if [ "x"$li_error_cd = "x" ]; then
if [ $li_act_cd -eq 2 ]; then
# if [ $CHAR_CHECK -eq 1 ]; then
# export lv_alt_col="'"$lv_d_id"'||trim(row_number() over (order by "$KEY_COL"))"
# fi
if [ $(wc -l <dec_ds1.out) -ge 1 ]; then
echo "insert into _usecase_dec_aud_ds(feed_key,key_col,key_col_chr, dec_dt, run_dt, user_act_nm,usecase) select feed_key, key_col,key_col_chr, dec_dt, run_dt, user_act_nm,usecase from _usecase_dec_ds where feed_key = "$F_KEY" and user_act_nm ='"$user_act_nm"');
delete from _usecase_dec_ds where feed_key ="$F_KEY" and user_act_nm ='"$user_act_nm"';">>usecase_dec_ds.out
fi
echo "insert into _usecase_dec_ds (feed_key, "$lv_col_nm ",dec_dt, run_dt, user_act_nm, usecase) select "$F_KEY" as feed_key,"$KEY_COL" as key_col,CURRENT_TIMESTAMP as run_dt,'"$user_act_nm"' as user_act_nm,usecase from p_input_tbl_nm ;">>usecase_dec_ds.out
else
echo "insert into _usecase_dec_aud_ds (feed_key, "$lv_col_nm ",dec_dt, run_dt, user_act_nm, usecase) select "$F_KEY" as feed_key,"$KEY_COL" as key_col,CURRENT_TIMESTAMP as run_dt,'"$user_act_nm"' as user_act_nm,usecase from p_input_tbl_nm ;">>usecase_dec_ds.out
fi
export FILE_FIXDEL26="$PWD/file26.log"
bteq >> ${FILE_FIXDEL26} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/usecase_dec_ds.out;
.export reset;
.quit;
EOF
fi
fi #tmp_msm12
fi #inner error code check
fi #outer error code check
# done <IP_usecase_NMs1.out
fi #outer error code check
else
li_error_cd=13
echo "usecaseATION EXISTANCE CHECK FAILED"
fi
fi #outer error check
echo " "
echo "Script completed Successfully . . :)"
