Scribd
Upload
172 views12 pages

Teradata BTEQ Basics for SQL Users

This document provides an overview of using BTEQ (Basic Teradata Query) to submit SQL queries to a Teradata database. It describes how BTEQ can be used interactively or through batch scripts, and covers session parameters, transaction modes, syntax error handling, and examples of BTEQ scripts. It also includes optional labs for hands-on practice with BTEQ.

Uploaded by

jvijaya_kumar
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
172 views12 pages

Teradata BTEQ Basics for SQL Users

This document provides an overview of using BTEQ (Basic Teradata Query) to submit SQL queries to a Teradata database. It describes how BTEQ can be used interactively or through batch scripts, and covers session parameters, transaction modes, syntax error handling, and examples of BTEQ scripts. It also includes optional labs for hands-on practice with BTEQ.

Uploaded by

jvijaya_kumar
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 12

Module 2

Simple BTEQ
After completing this module, you will be able to: Submit SQL to the Teradata RBDMS through BTEQ. Set session parameters to enable:
ANSI or Teradata transaction semantics SQL Flagger

BTEQ: Basic Teradata Query

BTEQ: Is a front-end utility for submitting SQL. Operates under all host systems and local area networks (LANs). May use mainframe terminals, PCs, or workstations. In batch mode, it obtains input from a host file (a "BTEQ script"). Can be used in interactive mode* Has import/export features* Has report writing features*

* For more information, see Appendix F and G in this course.

BTEQ BTEQRequest RequestFlow Flow

Request CLIENT BTEQ Input Script


.LOGON _______________; SELECT_______________ ______________________ ______________________; .QUIT;

Response

BTEQ

TDP

TERADATA DATABASE SYSTEM

PARSING ENGINE

GENERATED ANSWER SET

AMP

AMP

AMP

AMP

DATA

DATA

DATA

DATA

Session Parameters

Session parameters include:


Transaction semantics: .SET SESSION TRANSACTION [ANSI | BTET] ANSI flagger: .SET SESSION SQLFLAG [NONE | ENTRY]

Session parameters must be established prior to logon. To view current parameters for transaction semantics and ANSI flagger, use the BTEQ command:
.SHOW CONTROL

There is no way to disable the Teradata RDBMS extensions, you can only flag them.

SESSION TRANSACTION Modes


.SET SESSION TRANSACTION [ANSI | BTET]
All features of Teradata and ANSI work in either mode. Your choice of mode determines transaction protocols (covered later). Each mode activates different case-sensitivity and data conversion defaults. The same query might return different results in each mode.
Teradata BTEQ 04.00.01.00 for UNIX5. Enter your logon or BTEQ command: .SET SESSION TRANSACTION ANSI Teradata BTEQ 04.00.01.00 for UNIX5. Enter your logon or BTEQ command: .LOGON l5442/mj1 Password: *** Logon successfully completed. *** Transaction Semantics are ANSI. *** Character Set Name is 'ASCII'. *** Total elapsed time was 2 seconds. BTEQ -- Enter your DBC/SQL request or BTEQ command: .logoff *** You are now logged off from the DBC. Teradata BTEQ 04.00.01.00 for UNIX5. Enter your logon or BTEQ command: .SET SESSION TRANSACTION BTET Teradata BTEQ 04.00.01.00 for UNIX5. Enter your logon or BTEQ command: .LOGON l5442/mj1 Password:

*** Logon successfully completed. *** Transaction Semantics are BTET. *** Character Set Name is 'ASCII'. *** Total elapsed time was 2 seconds.

SQLFLAG Example
.set session sqlflag entry; .logon xyz,xyz; sel date; *** Query completed. One row found. One column returned *** Total elapsed time was 1 second. sel date; $ *** SQL Warning 5836 Token is not an entry level ANSI Identifier or Keyword. sel date; $ *** SQL Warning 5818 Synonyms for Operators or Keywords are not ANSI. sel date; $ *** SQL Warning 5821 Built-in values DATE and TIME are not ANSI. sel date; $ *** SQL Warning 5804 A FROM clause is required in ANSI Query Specification. Date ----------97/01/20 .logoff .set session sqlflag none; .logon xyz,xyz; sel date; *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Date ------97/01/20

SHOW CONTROL
.SHOW CONTROL;
Default Maximum Byte Count = 4096 Default Multiple Maximum Byte Count = 2048 Current Response Byte Count = 4096 Maximum number of sessions = 20 Maximum number of the request size = 32000 . . . EXPORT RESET IMPORT FIELD LOGON l5442/mj1 RUN [SET] ECHOREQ = ON [SET] ERRORLEVEL = ON [SET] FOLDLINE = OFF ALL [SET] FOOTING = NULL . . . [SET] RETCANCEL = OFF [SET] RETLIMIT = No Limit [SET] RETRY = ON [SET] RTITLE = NULL [SET] SECURITY = NONE [SET] SEPARATOR = two blanks [SET] SESSION CHARSET = ASCII [SET] SESSION SQLFLAG = ENTRY [SET] SESSION TRANSACTION = ANSI [SET] SESSIONS =1 [SET] SIDETITLES = OFF for the normal report. And, it is ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9. [SET] SKIPDOUBLE = OFF ALL [SET] SKIPLINE = OFF ALL [SET] SUPPRESS = OFF ALL [SET] TDP = l5442 [SET] TITLEDASHES = ON for the normal report. And, it is ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9. [SET] UNDERLINE = OFF ALL [SET] WIDTH = 75

Partial output only

BTEQ Script Example

UNIX file: mod2exer.scr


.SET SESSION TRANSACTION ANSI .LOGON l5422 / ped , <password> ;

-- Obtain a list of the department numbers and names from the -- department table. SELECT FROM .QUIT department_number ,department_name department;

ANSI compliant comments: Delimited by -- (2 dashes) and the end-of-line Cannot span multiple lines Teradata extension comments: Comments delimited by /* and */ May span multiple lines To execute the script: .run file=mod2exer.scr Results are shown on the next overhead

Using BTEQ Interactively


Script started on Sat Feb 15 10:48:19 1997 # bteq Teradata BTEQ 04.00.01.00 for UNIX5. Enter your logon or BTEQ command: .run file=mod2exer.scr - this is the only user input; rest of the commands came from file mod2exer.scr .run file=mod2exer.scr Teradata BTEQ 04.00.01.00 for UNIX5. Enter your logon or BTEQ command: .SET SESSION TRANSACTION ANSI Teradata BTEQ 04.00.01.00 for UNIX5. Enter your logon or BTEQ command: .LOGON l5442/ped, *** *** *** *** Logon successfully completed. Transaction Semantics are ANSI. Character Set Name is 'ASCII'. Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command: -- Obtain a list of the department numbers and names from the -- department table. SELECT department_number ,department_name FROM department; *** Query completed. 9 rows found. 2 columns returned. *** Total elapsed time was 1 second. department_number department_name --------------------------- -----------------------------401 customer support <rest of result rows are not shown in this screen capture> BTEQ -- Enter your DBC/SQL request or BTEQ command: .QUIT *** You are now logged off from the DBC. *** Exiting BTEQ... *** RC (return code) = 0 # exit script done on Sat Feb 15 10:48:45 1997

Identifying Syntax Errors


Script started on Thu Oct 24 16:21:21 1996 # bteq Teradata BTEQ 04.00.00.00 for UNIX5. Enter your logon or BTEQ command: .logon l5442/ped, *** Logon successfully completed. *** Transaction Semantics are ANSI. *** Character Set Name is 'ASCII'. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command: SELECT department_number ,DISTINCT department_name FROM department; ,DISTINCT department_name $ *** Failure 3708 Syntax error, 'DISTINCT' that follows the ',' should be deleted. Statement# 1, Info =36 *** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command: .quit *** You are now logged off from the DBC. *** Exiting BTEQ... *** RC (return code) = 8 # exit script done on Thu Oct 24 16:21:39 1996

Lab 2

See Lab 1_1, Lab 1_2, and Lab 1_3 pencil and paper workshops. Create these same reports on-line, using BTEQ scripts. Name these labs Lab 2_1, Lab 2_2, and Lab 2_3. You should have three separate reports when finished.

Note: Save the BTEQ script for Lab 2_1 as you will need it for Lab 3_4.

Lab 2Optional

Lab 2_4 Use BTEQ to submit your two reports from Lab 1_4. Observe the number of rows returned in your output, in each case. Which rows were eliminated due to the DISTINCT option?

Lab 2_5 Set the ANSI Flagger ON, then resubmit Lab2_1. What differences do you observe? Why?

Lab 2_6 a) Determine the default system session transaction mode. What command did you use? __________________________________ b) Set the session transaction mode to the opposite setting (ex., set it to ANSI if the system default is BTET), then verify the new setting.

You might also like