Get Teradata BTEQ query result in a unix variable

Question

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?

Solution 1

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 
....

Solution 2

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 >.

Comments

[yugesh]

Thanks!! I tried by removing the >>$LOG_FILE path, but the problem was, BTEQ would send all status messages for the query as the output, and it would require a lot of processing to extract the only count I want, but I found EXPORT output to File option in BTEQ which solved the problem.

[lightweight]

can you explain what grep and sed are doing?