In most of the Teradata environment I have seen the preferred medium to call BTEQ is shell script. It means that Teradata developer will write a shell script which will invoke BTEQ utility to run SQL commands on Teradata. In this post, we will see how to write a sample BTEQ script using Shell and run it on Teradata. To start with, I would like to discuss why we shall pick Shell script to call BTEQ and why not directly the BTEQ script. BTEQ supports plain ANSI SQL with few TD extension to SQL. This means that PL/SQL constructs like CURSOR , conditional statements, logical statements are not supported or minimal support is only available. Another reason is most of the times, you will prefer having parameters in the SQL. BTEQ does not support dynamic queries as such. So with Shell script, you can pass runtime parameters to BTEQ script.
The first thing you require is connection string to connect to BTEQ. For security purposes, it is recommend to have it in separate file and not write directly inside BTEQ script. So first create a file with login statement in it.
Filename: logonFile.txt . Change “localhost” to Teradata IP or TDPID.
Now let’s create Shell Script which will invoke BTEQ utility to run SQL commands. Add your queries between “label_bteq”. Filename: BTEQ_sample.sh
Command to run the shell script. Remember we have to pass 3 parameters to shell [$1,$2,$3] used in BTEQ query.
sh -x BTEQ_SAMPLE.sh "'2019-06-01'" "'2019-07-01'" DB_DEFAULT_NAME
So in this post we saw following:
- How to write a shell script which calls BTEQ utility.
- How to create a separate file with login credentials and call it in shell script.
- How to pass shell variables into BTEQ queries.
Hope this helps. In next post, we will discuss couple of options to make this BTEQ script better by adding some failure checks and we will see how we can add basic conditional statements in BTEQ queries.