How to pass parameters 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. 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

Command to run: 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:

#!/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

Command to run: 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.

#!/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

Command to run: sh -x run_BTEQ.sh InfoKey InfoData dbc dbcinfo
Output may look below:
$# 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.