How to pass parameters to BTEQ script

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.

Leave a Comment

Your email address will not be published.