BTEQ stands for Batch Teradata Query. It is one of the utilities in Teradata to perform DDL and DML queries, creating macros, etc. However, the BTEQ export feature is exclusively for exporting the result of a query to a file.
BTEQ modes
There are two modes of running BTEQ. They are interactive mode and Batch mode respectively. Out of these two modes, the most common mode is the Batch mode. In batch mode, the given SQL can be executed by calling the utility in a shell script.
BteqExecutor.sh
export logfile="Full path of log file"
export resultfile="Full path of the result file"
echo "We are executing this script on $(date +%Y-%m-%d)"
bteq <<! >> $logfile 2>&1
.LOGON TeradataServer/username,password
.os rm -f $result_file;
.EXPORT report file = $result_file;
.MAXERROR 1
.SET SEPARATOR '|'
SELECT userid,username,location FROM databasename.tablename
ORDER BY userid;
.EXPORT reset;
.IF ERRORCODE > 0 THEN .EXIT 2;
.IF ACTIVITYCOUNT > 0 then .EXIT 0;
.LOGOFF
!
returncode=$?
if [[ $returncode -eq 0 ]]
then
echo "BTEQ executed successfully"
else
echo "BTEQ failed with errors. Check the log file $logfile for more details"
fi
Let us see the purpose of each of the BTEQ commands in the above script.
bteq <> $logfile 2>&1 – This will invoke the BTEQ utility and redirects the logs to the specified log file. After that, the program will start executing the next commands under this user.
.LOGON TeradataServer/username,password – Log into Teradata server
.EXPORT report file = $result_file; – This command is to set the result file to a specific file of our choice and kick start the export.
.SET SEPARATOR ‘|’ – This is the delimiter in the result file between the columns. Similarly, we can also use other kinds of delimiters provided the delimiter should not present in any of the column values.
SELECT userid,username,location from databasename.tablename order by userid; – This is the select statement that will fetch the table data from Teradata. Likewise, we can also use other conditions using a where condition to filter the records.
.LOGOFF – Log off from the Teradata session.
.IF ACTIVITYCOUNT > 0 then .EXIT 0; – The activity count will give the number of records that got affected by the query. However, it cannot be used to count the number of records within the query.
returncode=$?
if [[ $returncode -eq 0 ]]
then
echo "BTEQ executed successfully"
else
echo "BTEQ failed with errors. Check the log file $logfile for more details"
fi
Inside the BTEQ we have used the exit codes based on the result. Firstly, this will give the benefit of reading the return code and knowing what has happened inside the BTEQ. Secondly, this will give us the added benefit of logging in, sending email notifications, etc.
To explore more about BTEQs, check “Error handling with ACTIVITYCOUNT and ERRORCODE in BTEQ”, “If else conditional flow in BTEQ“, “BTEQ export without column header“