Teradata - run a file/script at Unix Linux command prompt

AKS picture AKS · May 8, 2013 · Viewed 28.5k times · Source

I have the client/TTU installed on Unix box for Teradata.

If I do the following, it works. Where "..." is Teradata BTEQ normal output and once the following is done, I'm back at the prompt.

$ bteq
...
....

. logon dbname/dbuser,dbpassword
SELECT DATE, TIME;
.LOGOFF;
.QUIT;

..
...
$

Now, lets say I put the following lines in a file called "testtd.bteq"

. logon dbname/dbuser,$dbpassword
SELECT DATE, TIME;
.LOGOFF;
.QUIT;

What I want now is ... how can I run this script (.bteq) at Unix $ prompt ???

I tried the following methods so far, but they didn't work, may be Im missing anything:
1. bteq < /path/to/testtd.bteq > testtd.log

2. bteq < .run /path/to/testtd.bteq
HereDocEndsHere

Any ideas? DO I HAVE to provide ". logon dbname/dbuser,dbpassword" FIRST, if I'm using the HereDocument way?

Running bteq command on $ prompt doesn't even give me any HELP/options that I can use, like other commands.

i.e. cmd -u user -p password -f file etc...

Answer

grisaitis picture grisaitis · Sep 23, 2014

The best practice I'm aware of is

  1. store your teradata credentials in a ~/.tdlogon file
  2. create a script that contains your bteq call with all the stuff it needs.

E.g., create a file bteqScript.sh with

/* define helper variables, e.g.... */
export ARCHIVEDIR=~/data
export DATAFILE=dataOutput1.txt
bteq <<EOF
.run file=$HOME/.tdlogon
.export data file=${ARCHIVEDIR}|${DATAFILE} 
/* sql code on next line(s) */
select 
    'foo' as "bar"
; 
.export reset
EOF

Note that .run file=... executes the .logon command with your credentials, stored elsewhere.

Kudos to Alex Hasha for the bteq script.