Teradata FAQ's: Teradata Learning

BTEQ:

BTEQ stands for Basic Teradata Query program.

It’s a front end utility for submitting queries.

BTEQ is a client software that resides on the network and channel attached host. Its will reside on the unix box of the mainframe system.
To invoke BTEQ on unix box we can just type 'bteq' on the command line and on mainframe for using BTEQ in batch we use the utility BTQMAIN.

After starting BTEQ,you will log on to Teradata using a TDP-id (Teradata Director Program id), your user id and password. The TDP-id identifies the instance of Teradata you are going to access.

TDP's come in two varieties - the standard TDP for channel-attached clients, and the Micro-TDP (or MTDP) for network-attached clients.

It operates under all host systems and local area networks (LANs).

Step 1:

Type bteq on command line and get a below screen:

Teradata BTEQ 13.10.00.08 for z/OS.

Enter your logon or BTEQ command:

Step 2:

Enter the logon statement as follows:

.logon 1/sukul;

'1' is the TDP id and 'sukul' is the username. And type enter . It will ask for password. Type in the correct password and hit enter.

On successful logon we will get a screen as below:

*** Logon successfully completed.

*** Teradata Database Release is 13.10.04.08

*** Teradata Database Version is 13.10.04.08

*** Transaction semantics are BTET.

*** Session Character Set Name is 'EBCDIC'.

*** Total elapsed time was 0.21 seconds.

BTEQ -- Enter your SQL request or BTEQ command:

Step 3:

ON this screen we can enter SQL commands as follows:

BTEQ -- Enter your SQL request or BTEQ command: select date;

select date;

*** Query completed. One row found. One column returned.

*** Total elapsed time was 0.00 seconds.

Date

----------

2013/02/15

BTEQ commands must be preceded by a period (.) OR must end with a semicolon OR both. Do not confuse this will SQL commands. SQL does not need to begin with a period.

BTEQ can be used for report formatting also.

Difference between BTEQ and SQL assistant:

BTEQ	SQL ASSISTANT
BTEQ resides on the unix or the mainframe system. (there is a windows based BTEQ , but its not generally used)	SQL assistant is client software that runs on windows.
BTEQ uses CLIv2 for communicating with DB	SQL assistant uses ODBC for communicating with DB.
With BTEQ we can implement transaction control using BT-ET statements	With SQL assistant we cannot use transaction control.
BTEQ allows us flow control by making use of IF-ELSE	SQL assistant does not allow use if IF-ELSE
WITH and WITH BY clause only work with BTEQ	WITH and WITHBY don’t work in SQL assistant.

Session Parameters: Transaction semantics and SQL flagger

We can set transaction semantics to either BTET(Teradata) or ANSI.

Note that all features of Teradata work in both the modes , but each of these modes have a different case sensitivity and data conversion defaults. Ex: Teradata Mode is case insensitive , but ANSI mode is case sensitive.

Setting ANSI session:

SET SESSION TRANSACTION ANSI; -> sets ANSI mode

SET SESSION TRANSACTION BTET; --> sets BTEQ mode.

Note that these session parameters need to be established before logging on.

In ANSI mode: ANSI mode is case sensitive

select 'MATCH' where 'abc'='ABC';

*** Query completed. No rows found.

*** Total elapsed time was 0.00 seconds.

BTEQ -- Enter your SQL request or BTEQ command:

In BTET mode: Teradata mode is not case sensitive

select 'MATCH' where 'abc'='ABC';

*** Query completed. One row found. One column returned.

*** Total elapsed time was 0.01 seconds.

'MATCH'

-------

MATCH

BTEQ -- Enter your SQL request or BTEQ command:

You may also activate the ANSI Flagger, which automatically flags non-ANSI compliant syntax with a warning but still returns the expected answer set. This command also needs to be entered before logon.

Also note that SET SESSION is a BTEQ command and not a SQL command.

.SET SESSION SQLFLAG ENTRY; /* Causes non-Entry level ANSI syntax to be flagged */

.SET SESSION SQLFLAG NONE /* Disables ANSI Flagger*/

Using SQLFLAG

select date;

*** Query completed. One row found. One column returned.

*** Total elapsed time was 0.03 seconds.

select date;

*** SQL Warning 5836 Token is not an entry level ANSI Identifier or Keyword.

select date;

*** SQL Warning 5821 Built-in values DATE and TIME are not ANSI.

select date;

*** SQL Warning 5804 A FROM clause is required in ANSI Query Specification.

Date

----------

2013/02/15

Note that since DATE is not a ANSI standard , it gave us warning , however it still gave us the expected result.

SHOW CONTROL:

The BTEQ .SHOW CONTROL command displays BTEQ settings. The following output shows the result of this command.

.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   1/sukul;
RUN
[SET] ECHOREQ           = ON
[SET] ERRORLEVEL        = ON
[SET] FOLDLINE          = OFF ALL
[SET] FOOTING           = NULL
[SET] FORMAT            = OFF
[SET] FORMCHAR          = OFF
[SET] FULLYEAR          = OFF
[SET] HEADING           = NULL
[SET] INDICDATA         = OFF
[SET] NOTIFY            = OFF
[SET] NULL              = ?
[SET] OMIT              = OFF ALL
[SET] PAGEBREAK         = OFF ALL
[SET] PAGELENGTH        = 55
[SET] QUIET             = OFF
[SET] RECORDMODE        = OFF
[SET] REPEATSTOP        = OFF
[SET] RETCANCEL         = OFF
[SET] RETLIMIT          = No Limit
[SET] REPEATSTOP        = OFF
[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   = NONE
[SET] SESSION TRANSACTION = BTET
[SET] SESSIONS          = 1
[SET] SIDETITLES        = OFF for the normal report.
[SET] SKIPDOUBLE        = OFF ALL
[SET] SKIPLINE          = OFF ALL
[SET] SUPPRESS          = OFF ALL
[SET] TDP               = L7544
[SET] TIMEMSG           = DEFAULT
[SET] TITLEDASHES       = ON for the normal report.
[SET] UNDERLINE         = OFF ALL
[SET] WIDTH             = 75

Teradata FAQ's

links

Blog Archive

Teradata Learning - Page 2 (BTEQ)

No comments:

Post a Comment