Set unix variable inside bteq

Database
Enthusiast

Set unix variable inside bteq

Hi,

i have a script where i want to set unix variable inside bteq script and then use this variable

further in the bteq script too.

script1.sh

VAR_FLAG=""

bteq << EOF >>${LOG_FILE} 2>&1

SELECT BT_FLAG from db1.table1;

<set unix variable VAR_FLAG with BT_FLAG from above>

update table db1.table2 set col1='${VAR_FLAG}';

EOF

I know it seems very obvious that i could have written two simple bteqs, the result of first bteq can be saved in the unix variable.

But my actual bteq is very complex and it has several label statements and they keep calling each other until a certain condition is met (can take many hrs)

And inside the bteq i want to set the unix variable with result of one select statement so that this unix variable can be used in other sqls (without quitting the bteq) for cleaner code, this avoid using complex subqueries to achive the same result

The othe advantage being when the bteq finally exits the unix variable will be used for other purposes

Thanks,

-srinivas yelamanchili

Tags (3)
4 REPLIES
Enthusiast

Re: Set unix variable inside bteq

Hi Srinivas,

you can export your select query result into a param file and then use the same in other sql's

before startin the BTEQ in shell script jus refer to the path of the param file like

. param_file_path

inside the BTEQ give the following export command

bteq << EOF >>${LOG_FILE} 2>&1

.EXPORT FILE=param_file_path

SELECT 'VAR_FLAG=' ||BT_FLAG from db1.table1;

this will automatically export your variable and its value to the param file.later u can use this variable wherever u require as

update table db1.table2 set col1='${VAR_FLAG}';

.EXPORT RESET

Enthusiast

Re: Set unix variable inside bteq

I have seen a similar post here a couple times as to using this parameter file, yet I can't find any mention of it in the BTEQ manual, nor can I get anything like it to work.  Is this something that sounds like it should work, or does it actually work and there is a crucial part or command missing? That being said, it is time for BTEQ to easily allow parameters to be passed into .runfile files.  Oracle has been allowing that for decades in their SQL scripts.

Enthusiast

Re: Set unix variable inside bteq

I meant to mention that the ". param_file_path" results in a error of " *** Error: Unrecognized command.".  I do believe this would be a much more elegant solution if it works some how.

Re: Set unix variable inside bteq

Hi Srinivas,

There is possible use environment variables and have a "clean" script (bteq, tpt, or whatever.), perl are very useful.


First you need a library functions (_dwh_env.lib), one to do variable substitution.


#!/bin/ksh

#BF#################################################################################################
_f_substitude_vars (){
########################################################################
# Usage: _f_substitude_vars
# Description: Substitude environment variables on file.
# Creation Date: 2015-04
########################################################################
tmpFile=/tmp/tmp.$$

perl -ne 's/\$\{(.*?)\}/if ( ! exists $ENV{$1} || $ENV{$1} eq "" ){print "\nNotset=$1\n"} else {$ENV{$1}}/ge ; print;' $1 > ${tmpFile}
RC=$?

if [ $(grep -ic '^Notset=' $tmpFile) -ne 0 ]
then
grep -i '^Notset=' $tmpFile | sort -u #> ${tmpFileerr}
else
cat $tmpFile
fi

rm -f $tmpFile
}

#EF#################################################################################################

Next, the variables shell (_dwh_vars.var):

#!/bin/ksh

__start_timedate=$(date '+%Y-%m-%d 00:00:00.000000')
__end_timedate=$(date '+%Y-%m-%d %H:%M:%S.000000')

# Custom variables
export S_SERVER=S_Alias
export S_USER=up_user
export S_PASSWD=up_pass
export DataBase_=DWH_DWH_DWH
export Table_=TableName_TableName
export Condition_="col_date1 BETWEEN '2016-01-01 00:00:00.000000' AND '2016-01-05 23:59:59.570000'"
export Condition2_="col_date2 BETWEEN '$__start_timedate' AND '$__end_timedate'"

Next, a "clean" script (dummy.bteq), bteq in that case:

.logon ${S_SERVER}/${S_USER},${S_PASSWD}

SELECT col1
,col2
,col3
FROM ${DataBase_}.${Table_}
WHERE col1 ${Condition_}
AND col2 ${Condition2_}
;

Finally, a shell (exec_bteq.sh) to call: library shell, variables shell and bteq script:

#!/bin/ksh

## Library functions
. lib/_dwh_env.lib

## Custom variables
. var/_dwh_vars.var

## Substitute environment variables
_f_substitude_vars dummy.bteq | tee exec_dummy.bteq

# _f_substitude_vars dummy.bteq > exec_dummy.bteq
# bteq exec_dummy.bteq > ...
# ...

I hope you find it useful.

Regards,

Misgate.