How to run the BTEQ script in Teradata using Unix shell script
Contents
BTEQ(Batch TEradata Query) is the utility used in Teradata and It helps to execute the queries either batch or interactive mode.BTEQ is the excellent tool for import and export the data in Teradata.
If the Teradata query needs to run in the batch mode,We can prepare the BTEQ along with the query. Then the shell script is used to run the BTEQ in the batch mode.We can schedule this shell script in the scheduler(example: Control-M,UC4) to run daily or weekly.
Example query
INSERT INTO Employee VALUES(573,’Logan’,’Manager’,’2015-05-26′);
BTEQ Script file with the query:
sample file saved in the name of test_bteq.sql
.LOGON tdpid/userid, password
INSERT INTO Employee VALUES(573,’Logan’,’Manager’,’2015-05-26′);
.LOGOFF;
.EXIT;
Explanation of the BTEQ:
.LOGON – It is command, which is used to log on to a Teradata Database from BTEQ.
tdpid – The Teradata Director Program ID of the Teradata server that the user is logging on to.
user id – A valid user identifier
password – the password for the valid user id.
.LOGOFF – Ends the current Teradata Database sessions without exiting BTEQ.
.EXIT – Ends the current Terdata Database sessions and exits BTEQ.
Shell script to execute BTEQ in Batch mode
The sample shell script file saved in the name of insert_rec.sh
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
#!/bin/sh SCRIPT_PATH="/home/revisit/scripts/test_bteq.sql" LOG_DIR="/home/revisit/scripts/log" LOG_FILEPATH="${LOG_DIR}/test_bteq.log" touch $LOG_FILE bteq < ${SCRIPT_PATH} > ${LOG_FILEPATH} ReturnCode=$? if[[ ${ReturnCode} -eq 0 ]]; then echo "BTEQ script completed successfully" exit 0 else echo "BTEQ script failled" exit 1 fi |
Execute the shell script in the unix
$sh insert_rec.sh
Explanation of Shell script
Here the BTEQ file named as test_bteq.sql and it is placed in the path /home/revisit/scripts.For every run of this script,the log will created and it will be placed in the path /home/revisit/scripts/log.The log file named as test_bteq.log.
The touch command is used to create the touch file before run the bteq script.Then the bteq execute the given BTEQ file and written the success or failure message in the log file.
The symbol $? is used to validate the return code of bteq command and prints the success or failure message in the output screen.
Recommended Articles
