BTEQ IN TERADATA

Share via:
BTEQ IN TERADATA

In this article, we will see the BTEQ IN TERADATA

BTEQ was the first utility and query tool for Teradata. BTEQ can be used as a Query tool, to load data a row at a time into Teradata and to export data off of Teradata a row at a time.

The features of BTEQ

  • BTEQ can be used to submit SQL in either a batch or interactive environment.
  • BTEQ gives the outputs in a report format, where Queryman outputs data in a format more like a spreadsheet.
  • As said Bteq is an excellent tool for importing and exporting data.

There are mainly 4 types of BTEQ Exports

Export DATA

This is set by .EXPORT DATA.

Generally, users will export data to a flat file format.This is called Record Mode or DATA mode.

If the data has no headers or white space between the data contained in each column and the data is written to the file in a normal format.

Export INDICDATA

This is set by .EXPORT INDICDATA.

This mode is used to export data with extra indicator bytes to indicate NULLs in column for a row.

Export REPORT

This is set by .EXPORT REPORT

In this mode the output of BTEQ export would return the column headers for the fields, white space, expanded packed or binary data.

Its just looks like a report with column headers and data.

Export DIF

This called as Data Interchange Format, which allows users to export data from Teradata to be directly utilized for spreadsheet applications like Excel, FoxPro and Lotus.

BTEQ IMPORT

BTEQ can also import the the data from the source and incorporate the data into SQL to modify the content of one or more tables. But before the we need to provide the name of the file and the format. These will be defined within the scripts file-

Format of the IMPORT command

Below is the example of BTEQ IMPORT Script.We are taking data form a flat file from C:\TEMP\EMPDATA.txt and importing records into Empdb.Emp_Table.

Below is a review of those commands for the above example

Commands for the above example

QUIET ON limits BTEQ output to reporting only errors and request processing statistics.

REPEAT * causes BTEQ to read a specified number of records or until EOF. The default is one record. Using REPEAT 100 would perform the loop 100 times.

The USING defines the input data fields and their associated data types coming from the host.

BTEQ Operation in Network Attached System

Teradata Transaction Modes

ANSI mode

  • If any system error occurs and all the statement in the request are not completed successfully, then the system:
  • Abort only the specific request.
  • Restores back any changes made by the resuest.
  • Does not release locks held by the request.
  • Character comparison is CASE sensitive.
  • Create table are default to MULTISET tables.
  • Does not support BEGIN TRANSACTION/END TRANSATION statements.

Teradata mode-

  • Teradata mode transactions can be either implicit or explicit.
  • An explicit transaction is a single set of BEGIN TRANSACTION/END TRANSACTION statements surrounding one or more requests.

Consider the below example

If any error occurs during DELETE or UPDATE statement, the system rolls back and restores both the Employee and STUDENT table to the previous state.

  • Character comparison is NOT CASE sensitive.
  • Create table are default to SET tables.
  • Auto commit, the COMMIT request is not valid.

You need to define transition mode using .SET SESSION TRANSACTION before logging into the database in BTEQ.

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

 

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment