Creating and Using Scripts - Basic Teradata Query

Creating and Using Scripts

A script is a special input file that contains BTEQ commands and Teradata SQL, and is defined as the SYSIN file for automatic execution when BTEQ is invoked.

Creating a Script

To create a BTEQ script to display the contents of table Department in the default database, create a file called SAMPFILE, and enter the following BTEQ script:

   .LOGON tdpid/userid,password 

   .SET separator '|' 

   SELECT * FROM department;

   .LOGOFF

   .EXIT 

Identifying the Script as the Standard Input File

After creating and saving a script file, it must be identified as the standard input or SYSIN file before invoking BTEQ.

BTEQ < SAMPFILE

The use of the -c and -e command line options or the “charset_id” value in the clispb.dat file is highly recommended when a Unicode® input file is used. Otherwise, BTEQ interprets the input file as Unicode® and sets the session character set accordingly, as follows. BTEQ checks the input file for a UTF-16 or UTF-8 BOM and automatically changes the session character set to the appropriate Unicode® encoding. The endianness (16-bit output) syntax of a UTF-16 BOM must match that of the machine BTEQ is running on. If a BOM-less UTF-16 input file is used, BTEQ validates the first character and automatically changes the session character set to UTF16.
Note that BTEQ does not automatically change the session character set for a BOM-less UTF-8 input file. If none of the above apply, BTEQ detects the input file is not a Unicode® file and defaults the session character set to ASCII.

Displaying Script Results

Depending on the configuration of the system SYSPRINT specification or system output settings, BTEQ either displays the results on the user terminal or writes them to the specified file:

   .Logon e/fml, password, acctid  

   *** Logon successfully completed.

   *** Total elapsed time was 3 seconds. 

   .SET SEPARATOR ' | '

   SELECT * FROM department;  

   *** Query completed. 5 rows found. 4 columns returned. 

   *** Total elapsed time was 3 seconds.

     DeptNo | DeptName      | Loc |  MgrNo

     ------   --------------  ---    -----

        500 | Engineering   | ATL |  10012

        700 | Marketing     | NYC |  10021

        300 | Exec Office   | NYC |  10018

        600 | Manufacturing | CHI |  10007

        100 | Administration| NYC |  10011

   .LOGOFF   

   *** You are now logged off from the DBC. 

   .EXIT; 

Omitting the Userid and Password from the Script File

If, for security reasons, a user does not include the logon string in the script file, it can be entered on the command line when BTEQ is invoked from a Workstation or the JCL PARM parameter in BTEQ STEP when BTEQ is invoked from Mainframe.

For example, on a Workstation, if the LOGON command is deleted from the SAMPFILE script, invoke BTEQ and run the script by entering the following command-line text:

   bteq .LOGON tdpid/userid,password < SAMPFILE

On Mainframe, JCL PARM can be used to pass the logon command.

   //BTEQ00   EXEC PGM=BTQMAIN,PARM='/.LOGON tdpid/userid,password'