BTEQ Introduction
01) BTEQ stands for Basic Teradata Query program.
02) BTEQ is a front end tool for submitting SQL queries.
03) BTEQ is client tool.
04) BTEQ is a Character User Interface (CUI) Tool.
05) Require TDP ID (Teradata Director Program id), Teradata Username and Teradata User Password.
06) TDP ID identifies the Teradata instance.
07) TDP ID will be configured in hosts file in the local system.
08) TDP ID is used in sending the SQL request / collecting the result from Teradata Server.
BTEQ Commands
01) Must be preceded by a period (.)
02) BTEQ commands are not case-sensitive.
Session Connection Modes
01) Transaction semantics, which allow to set the session as ANSI or Teradata (BTET) mode.
02) BTET stands for "Begin Transaction End Transaction".
03) All features of Teradata and ANSI will work in either mode.
04) Login using ANSI Mode
.SET SESSION TRANSACTION ANSI;
05) Login using BTET Mode (Default Mode)
.SET SESSION TRANSACTION BTET;
06) SELECT DATE; /* BTET Standard */
07) SELECT TIME; /* BTET Standard */
08) SELECT CURRENT_DATE; /* ANSI Standard */
09) SELECT CURRENT_TIME; /* ANSI Standard */
10) Need to establish session mode prior to logging on.
BTEQ <Enter>
.SET SESSION TRANSACTION ANSI;
.logon TDPID/Teradata_User_Name, Teradata_User_Password;
11) .SHOW CONTROL displays the BTEQ settings.
12) Can execute the SQL Statements (Individual & Batch Mode) and BTEQ scripts
BTEQ <Enter>
.logong TDPID/Teradata_User_Name, Teradata_User_Password;
.run file=BTEQ_Script_name
BTEQ Execution Details
01) Interactive and Batch utility SQL utility.
02) Can be used for IMPORT and EXPORT of data (slow).
03) Can be used as a report writer.
04) Can do simple branching (GOTO) and looping (REPEAT).
05) All BTEQ commands begin with a period (.) and do not have trailing semi-colon.
06) SQL statements in BTEQ scripts do not have beginning period (.) and have trailing semi-colon;
07) .EXPORT is to SELECT data from the Teradata database.
08) .IMPORT is to process input from a host-resident data file.
09) BTEQ does error reporting, not error capture.
10) Export
a) Field Mode
1) .EXPORT REPORT
2) .EXPORT REPORTWIDE
3) Transfers data one column at a time with numeric data converted to character.
4) Data set contains column headings and formatted data.
5) Data is truncated if exceeds 75 characters.
6) .SET WIDTH 500. By using this command can increase the length.
b) Record Mode
1) .EXPORT DATA
2) Transfers data one row at a time in host format.
3) Nulls are represented as zeros or spaces.
c) Indicator mode
1) .EXPORT INDICDATA
2) Transfers data one row at a time, sending an indicator variable for nulls.
3) Nulls are represented as zeros or spaces.
d) Data Interchange Format
1) .EXPORT DIF
2) Excel Format
11) Limit Export Parameters
.EXPORT DATA FILE= <File_Name>, LIMIT=100
12) Can export the data by executing a Macro.
13) Can execute Operating System commands using .OS inside BTEQ
14) BTEQ Parameters
a) .SET SEPARATOR “!~”
b) .SET FOLDLINE ON ALL
c) .SET SIDETITLES ON
d) .SHOW CONTROLS
e) .HELP BTEQ
f) .SHOW VERSION
g) .SET RECORDMODE ON
h) .SET INDICDATA ON
i) .SET null as '0'
j) .SET HEADING 'Report Heading'
k) .SET FOOTING '&DATE &TIME || Confidential'
15) Import
a) DATA
1) Import records contain NULL values.
b) INDICDATA
1) Import records contain NULL bits.
c) REPORT
1) Imports Teradata “report” data.
2) Data expected in BTEQ EXPORT REPORT format.
d) VARTEXT
1) Record format as variable length character fields.
2) Default delimiter is | or specify with field delimiter within single quotes.
16) Import Variables
a) .REPEAT * (Default). All the flat file rows will be considered for the import.
b) .REPEAT 100. First 100 records flat file rows will be considered for the import.
01) BTEQ stands for Basic Teradata Query program.
02) BTEQ is a front end tool for submitting SQL queries.
03) BTEQ is client tool.
04) BTEQ is a Character User Interface (CUI) Tool.
05) Require TDP ID (Teradata Director Program id), Teradata Username and Teradata User Password.
06) TDP ID identifies the Teradata instance.
07) TDP ID will be configured in hosts file in the local system.
08) TDP ID is used in sending the SQL request / collecting the result from Teradata Server.
BTEQ Commands
01) Must be preceded by a period (.)
02) BTEQ commands are not case-sensitive.
Session Connection Modes
01) Transaction semantics, which allow to set the session as ANSI or Teradata (BTET) mode.
02) BTET stands for "Begin Transaction End Transaction".
03) All features of Teradata and ANSI will work in either mode.
04) Login using ANSI Mode
.SET SESSION TRANSACTION ANSI;
05) Login using BTET Mode (Default Mode)
.SET SESSION TRANSACTION BTET;
06) SELECT DATE; /* BTET Standard */
07) SELECT TIME; /* BTET Standard */
08) SELECT CURRENT_DATE; /* ANSI Standard */
09) SELECT CURRENT_TIME; /* ANSI Standard */
10) Need to establish session mode prior to logging on.
BTEQ <Enter>
.SET SESSION TRANSACTION ANSI;
.logon TDPID/Teradata_User_Name, Teradata_User_Password;
11) .SHOW CONTROL displays the BTEQ settings.
12) Can execute the SQL Statements (Individual & Batch Mode) and BTEQ scripts
BTEQ <Enter>
.logong TDPID/Teradata_User_Name, Teradata_User_Password;
.run file=BTEQ_Script_name
BTEQ Execution Details
01) Interactive and Batch utility SQL utility.
02) Can be used for IMPORT and EXPORT of data (slow).
03) Can be used as a report writer.
04) Can do simple branching (GOTO) and looping (REPEAT).
05) All BTEQ commands begin with a period (.) and do not have trailing semi-colon.
06) SQL statements in BTEQ scripts do not have beginning period (.) and have trailing semi-colon;
07) .EXPORT is to SELECT data from the Teradata database.
08) .IMPORT is to process input from a host-resident data file.
09) BTEQ does error reporting, not error capture.
10) Export
a) Field Mode
1) .EXPORT REPORT
2) .EXPORT REPORTWIDE
3) Transfers data one column at a time with numeric data converted to character.
4) Data set contains column headings and formatted data.
5) Data is truncated if exceeds 75 characters.
6) .SET WIDTH 500. By using this command can increase the length.
b) Record Mode
1) .EXPORT DATA
2) Transfers data one row at a time in host format.
3) Nulls are represented as zeros or spaces.
c) Indicator mode
1) .EXPORT INDICDATA
2) Transfers data one row at a time, sending an indicator variable for nulls.
3) Nulls are represented as zeros or spaces.
d) Data Interchange Format
1) .EXPORT DIF
2) Excel Format
11) Limit Export Parameters
.EXPORT DATA FILE= <File_Name>, LIMIT=100
12) Can export the data by executing a Macro.
13) Can execute Operating System commands using .OS inside BTEQ
14) BTEQ Parameters
a) .SET SEPARATOR “!~”
b) .SET FOLDLINE ON ALL
c) .SET SIDETITLES ON
d) .SHOW CONTROLS
e) .HELP BTEQ
f) .SHOW VERSION
g) .SET RECORDMODE ON
h) .SET INDICDATA ON
i) .SET null as '0'
j) .SET HEADING 'Report Heading'
k) .SET FOOTING '&DATE &TIME || Confidential'
15) Import
a) DATA
1) Import records contain NULL values.
b) INDICDATA
1) Import records contain NULL bits.
c) REPORT
1) Imports Teradata “report” data.
2) Data expected in BTEQ EXPORT REPORT format.
d) VARTEXT
1) Record format as variable length character fields.
2) Default delimiter is | or specify with field delimiter within single quotes.
16) Import Variables
a) .REPEAT * (Default). All the flat file rows will be considered for the import.
b) .REPEAT 100. First 100 records flat file rows will be considered for the import.
********************
FastExport
Today here we will discuss about FastExport utility in Teradata.
Today here we will discuss about FastExport utility in Teradata.
- To export large volume of data from Teradata database to client.
- Takes advantage of multiple sessions.
- Fastexport sorts data rows before sending them through sessions.
- Can be used with INMOD/OUTMOD feature for external data sources and destinations.
- Restartable feature supported.
Example:
a).
.LOGTABLE sample_log;
.LOGON tdpid/username,password;
.BEGIN EXPORT SESSIONS 4;
.EXPORT OUTFILE out.dat;
SELECT * FROM employees
WHERE loc_name = ‘MGRD’
ORDER BY 1;
.END EXPORT;
.LOGOFF;
b).
.LOGTABLE sample_log;
.LOGON tdpid/username,password;
.BEGIN EXPORT SESSIONS 4;
.EXPORT OUTMOD out.dll;
SELECT * FROM employees
WHERE loc_name = ‘MGRD’
ORDER BY 1;
.END EXPORT;
.LOGOFF;
a).
.LOGTABLE sample_log;
.LOGON tdpid/username,password;
.BEGIN EXPORT SESSIONS 4;
.EXPORT OUTFILE out.dat;
SELECT * FROM employees
WHERE loc_name = ‘MGRD’
ORDER BY 1;
.END EXPORT;
.LOGOFF;
b).
.LOGTABLE sample_log;
.LOGON tdpid/username,password;
.BEGIN EXPORT SESSIONS 4;
.EXPORT OUTMOD out.dll;
SELECT * FROM employees
WHERE loc_name = ‘MGRD’
ORDER BY 1;
.END EXPORT;
.LOGOFF;
Following are the
features of Tpump utility in Teradata:
- Allows near real-time updates from transactional systems into the warehouse.
- Best fit for low volume data maintenance.
- Unlike Multiload, Tpump uses row hash lock allows concurrent updates on the same table.
- INSERT,UPDATE, DELETE supported.
- No restrictions applied for tables with SI, RI, triggers etc.
- No limit on number of concurrent sessions.
- Speed can be tuned dynamically.
Example:
.LOGTABLE Logtable001_ml;
.LOGON tdp3/user2,tyler;
.BEGIN LOAD SESSIONS 4 ERRORTABLE ET_Employee;
.LAYOUT Employee_Trans;
.FILLER in_Transcode 1 CHAR(3);
.FIELD in_EmpNo * SMALLINT;
.FIELD in_DeptNo * SMALLINT;
.FIELD in_Salary * DECIMAL (8,2);
.DML LABEL Payroll DO INSERT FOR MISSING UPDATE ROWS ;
UPDATE Employee SET Salary = :in_Salary
WHERE EmpNo = :in_EmpNo;
INSERT INTO Employee (EmpNo, Salary)
VALUES (:in_EmpNo, :in_Salary);
.DML LABEL Terminate ;
DELETE FROM Employee WHERE EmpNo = :in_EmpNo;
INSERT INTO Employee_History (EmpNo, DeptNo)
VALUES (:in_EmpNo, :in_DeptNo);
.IMPORT INFILE infile1
LAYOUT Employee_Trans
APPLY Payroll WHERE in_Transcode = 'PAY'
APPLY Terminate WHERE in_Transcode = 'DEL';
.END LOAD;
.LOGOFF;
.LOGTABLE Logtable001_ml;
.LOGON tdp3/user2,tyler;
.BEGIN LOAD SESSIONS 4 ERRORTABLE ET_Employee;
.LAYOUT Employee_Trans;
.FILLER in_Transcode 1 CHAR(3);
.FIELD in_EmpNo * SMALLINT;
.FIELD in_DeptNo * SMALLINT;
.FIELD in_Salary * DECIMAL (8,2);
.DML LABEL Payroll DO INSERT FOR MISSING UPDATE ROWS ;
UPDATE Employee SET Salary = :in_Salary
WHERE EmpNo = :in_EmpNo;
INSERT INTO Employee (EmpNo, Salary)
VALUES (:in_EmpNo, :in_Salary);
.DML LABEL Terminate ;
DELETE FROM Employee WHERE EmpNo = :in_EmpNo;
INSERT INTO Employee_History (EmpNo, DeptNo)
VALUES (:in_EmpNo, :in_DeptNo);
.IMPORT INFILE infile1
LAYOUT Employee_Trans
APPLY Payroll WHERE in_Transcode = 'PAY'
APPLY Terminate WHERE in_Transcode = 'DEL';
.END LOAD;
.LOGOFF;
BTEQ
DEFINITION : BTEQ - Basic
Teradata Query
- General-purpose, command-based program that allows users on a workstation to communicate with one or more Teradata Database systems.
- A set of SQL statements used to inserts updates or deletes in teradata tables.
- Imports data to teradata database from a file.
- Exports data from table and formats the results and returns them to the screen, a file, or to a designated printer.
- Do report the error occurs but will not capture it as log.
BTEQ Session:
- Logical connection between host and teradata database
- Multiple sessions are allowed to work tasks on in parallel
- In a bteq session, .show control command list out all the parameters set over that session. We can also set the parameters.
- Session for a script can be set using command,
- For e.g. .SET SESSION 4
Capabilities in BTEQ:
In a BTEQ session, we
can access a Teradata Database easily and do the following:
- Enter Teradata SQL statements to view, add, modify, and delete data.
- Enter BTEQ commands.
- Enter operating system commands.
- Create and use Teradata stored procedures
- BTEQ supports Teradata-specific SQL functions for doing complex analytical querying and data mining
- All database requests in BTEQ are expressed in Teradata SQL.
- BTEQ also supports the conditional logic (i.e., "IF.THEN...") based on activity count or error code. It is useful for batch mode export / import processing.
E.g., select ……
From ….
.if activitycount >
0 then .goto continue
.quit
.label
continue
- Error handling is applicable in BTEQ. We can assign error level for each error code and make decisions based on the level assigned.
- E.g., .set errorlevel 2168 severity 4
OPERATING MODES:
1) Interactive mode:
In
interactive mode, you start a BTEQ session by entering BTEQ logon at the
system prompt on your terminal and submit SQL commands to the database as
needed.
Format
of logon cmd: bteq .logon servername/user_name, password
2) Batch mode:
In batch mode, you prepare BTEQ scripts or macros, and then submit
them to BTEQ from a scheduler or manually for processing.
A BTEQ script is a set of SQL statements and BTEQ commands saved
in a file with the extension ".bteq".
However, it does not matter what file extension is used.
The
BTEQ script can be run using the following command (in UNIX or Windows)
Here
infile is the BTEQ script, and outfile is the output or log file.
bteq
< infle > outfile
No comments:
Post a Comment