CODEWITHSUNDEEP
shellunixkshteradata
yugesh
yugesh

Reputation: 116

Get Teradata BTEQ query result in a unix variable

I am writing a shell script, where I need a result of BTEQ query in Teradata into a unix variable.

I tried,

    testabcd=$(bteq << EOF 2>&1 >> $LOG_FILE
     .LOGON ${HOST}/${USER},${PASSWORD}
     DATABASE ${SRC_DB};
     .set width 2000;
      .set titledashes off;
     SELECT COUNT(*) FROM ${SRC_DB}.${SOURCE_TABLE};
    .LOGOFF;
    .QUIT;
    .EXIT
     EOF)

      echo "The count is: " $testabcd

The query runs successfully but the variable is not assigned with the output. Any suggestions?

Upvotes: 2

Views: 18989

Answers (5)

Anton Kalicinski
Anton Kalicinski

Reputation: 1

Try this way:

testabcd(){
bteq << EOFBT |grep '^>' |sed -e "s/^>//"
.SET ERROROUT STDOUT
.SET ECHOREQ ON
.SET INDICDATA OFF
.SET QUIET OFF
.SET SIDETITLES OFF
.SET TIMEMSG QUERY
.SET TITLEDASHES OFF

.LOGON ${LOGIN_TD_DWC};

 SELECT '>'||'ANTON';

 .LOGOFF;
.QUIT;
EOFBT
}

var_test=$(testabcd)

echo $var_test

Upvotes: 0

saash
saash

Reputation: 1

Try using this:

count=$(echo "SELECT '>'||COUNT(*) FROM YOUR_DBNAME.YOUR_TABLENAME" | bteq .LOGON ${HOST}/${USER},${PASSWORD} 2>&1 |grep '^>' |sed -e "s/^>//");

Using this, we write a one line bteq query and result is stored in the variable count, so can be used.

No need for a extra file or anything.

Upvotes: -1

Juan Diego Godoy Robles
Juan Diego Godoy Robles

Reputation: 14945

No need for an extra file:

testabcd=$(bteq << EOF 2>&1  |grep '^>' |sed -e "s/^>//"
 .LOGON ${HOST}/${USER},${PASSWORD}
 DATABASE ${SRC_DB};
 .set width 2000;
  .set titledashes off;
 SELECT '>'||COUNT(*) FROM ${SRC_DB}.${SOURCE_TABLE};
.LOGOFF;
.QUIT;
.EXIT
 EOF)

'>'||COUNT(*): > to mark the output that we really want from the query.

grep '^>': to filter the marked text. Avoid bteq artifacts.

sed -e "s/^>//": to get rid of the flag >.

Upvotes: 4

yugesh
yugesh

Reputation: 116

I couldnot find any direct way to assign the query output to the variable, but instead had to write the output to the file and read it. 

      ERR_COUNT_FILE="err_count.txt"
      cat null > $ERR_COUNT_FILE
      bteq << EOF >>${LOG_FILE} 2>&1
      .LOGON ${HOST}/${USER},${PASSWORD}
         DATABASE ${SRC_DB};
         .set titledashes off;
         .set heading '';
      .EXPORT FILE=${ERR_COUNT_FILE}

      SELECT COUNT(*) from ${SRC_DB}.${SOURCE_TABLE};

      .EXPORT RESET
      EOF
      sed -i '1d;s/[[:blank:]]//g' $ERR_COUNT_FILE /* Remove header line and all blank spaces from the file*/
      count_err=$(cat ${ERR_COUNT_FILE})
      echo $count_err

I could not find an easier way.

Upvotes: -1

Josh Jolly
Josh Jolly

Reputation: 11786

The problem is this redirection:

2>&1 >> $LOG_FILE

This effectly means "send stderr and stdout to the file $LOG_FILE". Since stderr and stdout are being redirected, the command will not send any output to the shell, and so the command substitution will not capture anything. Example:

$ test=$(echo "Hello" 2>&1)
$ echo $test
Hello   // as expected
$ test2=$(echo "Hello" 2>&1 >> example.log)
$ echo $test2
        // is empty, as output has been redirected
$ cat example.log
Hello   // here is the redirected output

The output of the bteq command will be found inside $LOG_FILE - if you want to save it in a variable instead, then you need to remove the redirection:

testabcd=$(bteq << EOF 2>&1 
....

Upvotes: 4

Related Questions