Submitting SQL in BTEQ’s Interactive Mode

Submitting SQL in BTEQ’s Interactive Mode

Once you logon to Teradata through BTEQ, you are ready to run your queries. Teradata knows the SQL is finished when it finds a semi-colon, so don’t forget to put one at the end of your query. Below is an example of a Teradata table to demonstrate BTEQ operations.

Employee_Table

Employee_No	Last_Name	First_Name	Salary	Dept_No
2000000 1256349 1333454 1121334	Jones Harrison Smith Strickling	Squiggy Herbert John Cletus	32800.50 54500.00 48000.00 54500.00	? 400 200 400

Figure 2-1

BTEQ execution

.LOGON cdw/sql01;	Type at command prompt: Logon with TDPID and USERNAME.
Password: XXXXX	Then enter PASSWORD at the second prompt.
Enter your BTEQ/SQL Request or BTEQ Command.	BTEQ will respond and is waiting for a command.
SELECT * FROM Employee_Table WHERE Dept_No = 400;	An SQL Statement
*** Query Completed. 2 rows found. 5 Columns returned. *** Total elapsed time was 1 second.	BTEQ displays information about the answer set.

The result set

Employee_No	Last_name	First_name	Salary	Dept_No
1256349	Harrison	Herbert	54500.00	400
1121334	Strickling	Cletus	54500.00	400

Figure 2-2

Submitting SQL in BTEQ’s Batch Mode

On network-attached systems, BTEQ can also run in batch mode under UNIX (IBM AIX, Hewlett-Packard HP-UX, NCRMP-RAS, Sun Solaris), DOS, Macintosh, Microsoft Windows and OS/2 operating systems. To submit a job in Batch mode do the following:

1. Invoke BTEQ

2. Type in the input file name

3. Type in the location and output file name.

The following example shows how to invoke BTEQ from a DOS command. In order for this to work, the directory called Program Files\NCR\Teradata Client\bin must be established in the search path.

C:/> BTEQ < BatchScript.txt > Output.txt

BTEQ is invoked and takes instructions from a file called BatchScript.txt. The output file is called Output.txt.

Figure 2-3

Notice that the BTEQ command is immediately followed by the ‘<BatchScript.txt’ to tell BTEQ which file contains the commands to execute. Then, the ‘>Output.txt’ names the file where the output messages are written. Here is an example of the contents of BatchScript.txt file.

BatchScript.txt File

.LOGON CDW/sql00,whynot	Logon statement onto Teradata. Notice the “,” before the password. This is in batch script format.
SELECT * FROM Employee_Table WHERE Dept_No = 400;	The actual SQL
.LOGOFF	Logging off of Teradata

Figure 2-4

The above illustration shows how BTEQ can be manually invoked from a command prompt and displays how to specify the name and location of the batch script file to be executed.

The previous examples show that when logging onto BTEQ in interactive mode, the user actually types in a logon string and then Teradata will prompt for a password. However, in batch mode, Teradata requires both a logon and password to be directly stored as part of the script.

Since putting this sensitive information into a script is scary for security reasons, inserting the password directly into a script that is to be processed in batch mode may not be a good idea. It is generally recommended and a common practice to store the logon and password in a separate file that that can be secured. That way, it is not in the script for anyone to see.

For example, the contents of a file called “mylogon.txt” might be:

.LOGON cdw/sql00,whynot.

Then, the script should contain the following command instead of a .LOGON, as shown below and again in the following script: .RUN FILE=mylogon.txt

This command opens and reads the file. It then executes every r