The easiest method to pass parameters to BTEQ script is to call it within a shell script. You can very easily pass shell variables to BTEQ script. Very often when working with BTEQ script you will face requirement to use dynamic variables or parameters. This is not possible in plain SQL without using PL/SQL construct in Teradata. However if you are calling BTEQ code in shell script you can easily use shell variables in BTEQ SQL queries. In this post we will see different methods in which you can pass parameters to BTEQ script. Let's quickly see an example to understand this. I have below BTEQ script:
Filename: abc.bteq
.logon 192.168.100.100/dbc,dbc select InfoKey,InfoData from dbc.dbcinfo; .quit
How to run bteq file
bteq < abc.bteq
Now you can see I have hardcoded SQL query in the BTEQ. What if I want to populate column name , database name or tablename as the parameter. So I can create a shell script and pass the values as parameter. Let's look at the example below:
Filename: run_BTEQ.sh
#!/bin/sh col1=InfoKey col2=InfoData dbname=dbc tblname=dbcinfo bteq << lbl_btq .logon 192.168.100.100/dbc,dbc select $col1,$col2 from $dbname.$tblname; .quit lbl_btq
How to run bteq using shell script
sh -x run_BTEQ.sh
In the above script I have created 4 variables with column names, database name, tablename and using it directly in the BTEQ query. If you want to pass the values as command line argument then you can use below shell script.
Pass parameters to BTEQ from command line
#!/bin/sh col1=$1 col2=$2 dbname=$3 tblname=$4 bteq << lbl_btq .logon 192.168.100.100/dbc,dbc select $col1,$col2 from $dbname.$tblname; .quit lbl_btq
How to run bteq with command line parameters
sh -x run_BTEQ.sh InfoKey InfoData dbc dbcinfo
Output
$# sh -x run_BTEQ.sh InfoKey InfoData dbc dbcinfo + col1=InfoKey + col2=InfoData + dbname=dbc + tblname=dbcinfo + bteq BTEQ 15.00.00.00 Thu May 30 13:01:05 2019 PID: 26852 +---------+---------+---------+---------+---------+---------+---------+---- .logon 192.168.100.100/dbc, *** Logon successfully completed. *** Teradata Database Release is 15.00.00.08 *** Teradata Database Version is 15.00.00.07 *** Transaction Semantics are BTET. *** Session Character Set Name is 'ASCII'. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---- select InfoKey,InfoData from dbc.dbcinfo; *** Query completed. 3 rows found. 2 columns returned. *** Total elapsed time was 1 second. InfoKey InfoData ------------------------------ -------------------------------------------- LANGUAGE SUPPORT MODE Standard RELEASE 15.00.00.08 VERSION 15.00.00.07 +---------+---------+---------+---------+---------+---------+---------+---- .quit *** You are now logged off from the DBC. *** Exiting BTEQ... *** RC (return code) = 0
Let me know if you need more inputs.