• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

BTEQ Features in Teradata

BTEQ Features

It is utility, which important and export data from multiple tables to multiple files

It does not runs in phases, it runs according to the system process

It is not automatic Restart table, it supports arithmetic, mathematical etc expression

It supports conditional programming branching arid looping

It performs error handily but not error capturing

It supports select, insert, delete etc all operations[DDL,DML DCL etc]

It load duplicate record also even the table is having SI,RI etc

It does not bother about the connotations ,indexes, and triggers etc, on the tables,

It supports formatting and reporting commands etc

Working with BTEQ Interactive Mode

Screenshot_87

Minimize the window

Right click on the window properties

Screenshot_88

Save properties for future windows with same title

LOGON 127.0.0.1/Vinaya[Enter key press]

Password: Vinayaka[Press on enter key]

Select * From Dept;

O S<OPERATING SYSTEM COMMAND>

  • Ex: CLEARING THE SCREEN
  • WINDOWS2.OS CLS
  • UNIX2.OS Clear

Remark ’Vinay Here’[Remark is like ’c’ language primt]

SHOW Control2To see current settings

Setting some properties.

A).SET STDETITLES ON

  • Select * FROM PARTY:
  • Pid  1   pnm   vinay
  • Pid  2   pnm   madhu
  • .SET SIDETIZES OFF

B).SET FOLDLINE ON

  • SELECT*FROM PARTY;
  • Pid  Pname
  • 1    vinay
  • 2   madhu
  • SET FOLDLINE OFF

C).SET WINDTH 300;

D).SET HEADING’ This is tera data class’

E).SET FOOTING data ware housing

  • – Technologies’
  • (one line continuation to another line is-)
Inclined to build a profession as Teradata Developer? Then here is the blog post on 
Teradata Training Program.

F).SET PAGE LENGTH 25;

G).SET page break ON;

Changing the mode from BTET TO ANSI

  • .LOGOFF
  • .SET SESSION TRANSACTION ANSI
  • .LOGON 127.0.0.1/ VINAYAKA
  • Password: vinay

SHOW. Versions2To see the current version of various components in teradata

Working with ANSI

A) Select *From dept;

COMMIT;

B)INSERT INTO DEPT VALUS(100,’marketing’) ;

COMMIT;

C)BEGIN TRANSACTION[press enter key]

[IT Should fail, because ANSI Doesn’t support]

Working with Transactions in BTET

  1. Change To BTET
  2. Begin Transaction OR BT

Select*From Dept;

—5 Rows

Insert into dept values (100,’marke’);

Select* From Dept;

–6 Rows

Delete From kkk;

–Failed Due To NO Table name kkk

Select* From Dept;

–5Rows

[Because the last statement failed, Transaction failed and the] previous statement are Roll back)

Note

2MAX Select able Rows are one million

2MAX Select able Columns are 2048

2MAX Select able session are 200

 

 

BTEQ COMMAND

BTEQ Command Classified in the below 4 types:

a)Session control commands

ABORT,EXTI, Halt  EXECUTION, LOGON, LOGOFF, QUIT, Session, charset, session transaction, show versions etc.

b)File formatting commands

ERROR OUT,EXPORT, HALT EXECUTION,IMPORT,INDIA DATA, OS, QUIET, Repeat, run etc.

c)Sequence control commands

Abort, error level, Exit, GO TO, quite, IF Then, Label etc

d)Format control commands

Side titles, fold line, Footing, Heading etc.

Note

Refer to the material for all those command  explanation

BTEQ IMPORTANT STRUCTURE And export structure

Exporting

.Export Data file=”<Filename>”, limit =n DD=<DD Name>

INDICDATA

REPORT

DIFF

LARGEDATA

RESET

IMPORTING

.Importing DATA FILE =”< FILE NAME>” SKIP=N

DD=<DD Name>

INDICDATA

REPORT

VARTEXT’D’

D2Delimiter or separator

Export Reset

It Reverses the effect of previous export command and closes the out put on the console/*system apply read lock while loading the data, it releases the locks on target table*/

Ex – Exporting the data in data node[First example in the Browser]

Screenshot_89

.LOGON 127.0.0.1/Vinayaka, vinay;

.Export data file=”c:\Data-Log\party –Data.TXT”

Select party id as party – id, party name as party- income, jdate, organization As party – loc, party income as party –income,

From party

.Export reset

.LOGOFF

Start2RUNàCMDàBTEQ<C:\BTEQ\Export.Data.Txt

C:\Data.Log\Exportlog.txt

2Export the data in variable text format[Common delimiter]

2IN The above script Do the below statement changes

Replace the mode data to report

Replace the above statement to the below statement select

CAST(party id As Varchar(10)//’,’//

CAST(party name As Varchar(30)//’,’//

CAST(party Loc As Varchar(30)//’,’//

From party

EXPORTING THE DATA IN FIXED TEXT FORMAT

IN the above script do the below changes

a)Change data to report mode

b)Change the SQL Query to below way

Export Report file =”c:\Data-log\party-Repari . txt”, limit=2; Select

CAST(party id As Char(10)//

CAST(party name As Char(30)//

CAST(party Loc As Char(30)//

From party;

Screenshot_90

EXPORTING THE DATA WITH ETL LOGIL[SECOND SCRIPT IN THE PROJECT]

.LOGON 127.0.0//DBC,DBC;

.SET WIDTH 400;

Database vinayaka;

.Export report file=”c:\Data- log\party- Report.Txt” Select

Party Id As party id

CAST(Party name//’-’//party Loc As varchar(17))As Address,

Coast sce(Party income,99999)As income,

CASE

When party income<=30000 then ‘poor’>

WHEN PARTY INCOME<=50000 And party income>30000 Then ‘Avg’ else ’Good’

End AS RATING,

CAST (JNATS As Date)(Format’dd-mm-yyyy’)

AS JDate

CAST(CURRENT- Date AS Varchar(10)As Business- Date,

Sub STR(Organization,I,3)As org-short From party;

.Export Reset /* system apply the Read lock while loading the data, it realize

.LOGOFF

Learn more about Teradata Interview Questions in this blog post.

IMPORTING THE DATA IN THE DATA MODE

3rd Script in the project

RUN File c:\btEQ\Conn.txt;

Database Vinayaka;

.Import Data File=”C:\Data log\party-Data.txt”;

.QUIET ON;/*we want reduce Lock size, only error and processing information

.Repeat*;/*iteration one my one like for loop*/

Using

I-PID(Integer),

I-PNM(Varchar(30)),

I-Ploc(Varchar (30)),

I-PINS(Integer),

I- JDate(Date),

I-org(Varchar(30))

INSERT INTO party (party id, party name, party Loc, party income, JDate, orgam zation)Vlues(:I-PNM:I-PLOC,:I-PINC,I-Jdate:I-org);

.IF error code<>o Then .Quit:

.LOGOFF

Conn.txt

.Logon 127.0.0.1/DBC, DBC;

Coon.txt:

It is a configuration file, that is user define and  configuration settings[user id, password, variables, data base name etc..]

Note

2In the above script conn.txt is a configuration file

2 This file is useful when we are moving script from development to testing, testing to production etc…

2 The i/p file should be in data mode[we can not create data file manually]

Importing the data with ETL LOGIC

4TH SCRIPT IN THE PROJECT

/*.RUN File c:\BTEQ\CONN.TXT;*/

.Logon 127.0.0.1/DBC,DBC;

Database vinayaka;

.Import vartext’,’File= “c:\BTEQ\ Data-Vartext.TXT”,sktp=1;

.Quiet on;

.Repeat *;

Using

I- org(Varchar(30),)

I- NUM(Varchar(30),)

I- EName(Varchar(30),)

I- LName(Varchar(30),)

I- MName(Varchar(30),)

I- PLOC(Varchar(30),)

I- PCD(Varchar(30),)

I-JDate (Varchar(30),)

I- PINC(Varchar(30),)

Insert into party2(party id, partyname, email, party code, jdate, party income, party income-INC, Source- system-ID)

Values(:I-org//’-’//: I-NUM,:I-FNAME//:I –WAME//:I-M Name,

‘Tera data vinay @yahoo. co. in’,/*:I-F Name//’gmail.com*/

:I-PCD’

CAST(:I-J data is data)(format ’yy-mm-dd’.)

:I-PINC,

:I-PINC*12/100,

41000/* TO Identify which source system loading the data ware house*/

Screenshot_91

.IF ERROR CODE<>0 THEN SELECT error encountered

.Quiet 16;

Select ‘No error’;

.Logoff

WORKING WITH CONDITIONAL PROGRAMMING[SIMPLE BRANCHING AND LOADING]

.Logon 127.0.0.1/DBC,DBC;

Database vinayaka;

Select*From party;

.IF Error code=o THEN .GOTO Vinay LBL

.Remark ‘No table existed’,

.Label vinay LBL

.IF Activity count>5 Then. Remark

‘Table existed and contained more than 5 Rows’,

.IF Activity count<=5 Then. Remark’ No of rows are less than or equal to five’;

.Logoff

2Only BTEQ We do this conditional programming

For Indepth knowledge on Teradata click on:

Summary
Review Date
Reviewed Item
BTEQ Features in Teradata
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

1 Responses on BTEQ Features in Teradata"

  1. atul says:

    its really helpful notes….!

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.