Deprecated entry

This entry is deprecated.


See my next blog entry for a much simpler way to extract (CSV) data from Teradata via bteq script wrapped in a Unix shell script.


This first attempt was built in frustration to eliminate the first two lines of output - i.e. the column heading and the minuses underlining the heading (very mainframe-ish) ... A colleague Arturo told me to replace the ".export report file ..." row with ".export data file ..." but this caused the output to have hidden control characters in it, like these marked in red below ... I assume for Teradata-to-Teradata data transfers.

^P^@^N^@-1||Null Value
^W^@^U^@125|BUSINESS|Business
^[^@^Y^@149|INDIVIDUAL|Individual


This example does show the FIFO technique which can be a useful, if elaborate, way to solve problems. 

#!/bin/ksh -x
# Program: td_bteq_extract.ksh
# Description: To extract data from TD via bteq without any headings or "report formatting"
# Version: 1.0 gml 15-Jul-2011 Initial version with help from A. Gonzalez
# -----------------------------------------------------------------------------------------

LOGON_STR="prodtd/username,passwd;"   # Teradata logon acct
TMP=${HOME}/tmp                         # extract directory
FILENAME=test                           # output will go in ${FILENAME}.dat via the FIFO
SEP='|'                                 # pipe separator
FIFO=${TMP}/${FILENAME}.fifo
DATAFILE=${TMP}/${FILENAME}.dat         # extract file

> $DATAFILE                             # Otherwise if run more than once, it will append records to $DATAFILE

mkfifo $FIFO                            # create FIFO
cat $FIFO | egrep -v '^ZZZZZZZZZZ|^---------' > $DATAFILE &
bteq >/dev/null 2>&1 <<EOF
.logon $LOGON_STR
.export report file = $FIFO             -- .export report file = $DATAFILE will result in column header and header underlining - 2 unwanted rows
.set recordmode off                     -- not sure what this does - leaving it out seems to make no difference

-- an arbitrary example SQL query
SELECT TRIM(COALESCE(Acct_Type_Id,-99)) || '${SEP}' ||   -- Without the TRIM, the numerics will be right justified and have leading blanks
       TRIM(COALESCE(Acct_Type_Cd,'??')) || '${SEP}' ||
       TRIM(COALESCE(Acct_Type_Name,'??')) AS "ZZZZZZZZZZ"
FROM nuc_user_view.acct_type
ORDER BY Acct_Type_id;
.export reset
.logoff
.quit
EOF

# cleanup 
rm $FIFO