FAST EXPORT UTILITY IN TERADATA
FastExport is a command-driven utility that uses multiple sessions to quickly transfer large amounts of data from tables and views of Teradata Database to a client-based application.
Data can be exported from any table or view where the SELECT privilege has been granted. The destination for the exported data can be:
- A file on a mainframe-attached or network-attached client system
- An Output Modification (OUTMOD) routine written to select, validate, and preprocess the exported data
What it does :
When Teradata FastExport is invoked, the utility executes the FastExport commands and Teradata SQL statements in the FastExport job script. These direct FastExport to:
- Log on to Teradata Database for a specified number of sessions, using username, password, and tdpid/acctid information.
- Retrieve the specified data from Teradata Database, in accordance with format and selection specifications.
- Export the data to the specified file or OUTMOD routine on a client system.
- Log off of Teradata Database
How it Works :
Teradata FastExport processes a series of FastExport commands and Teradata SQL statements enter, usually as a batch mode job script.
The FastExport commands provide the session control and data handling specifications for the data transfer operations. The Teradata SQL statements perform the actual data export functions on Teradata Database tables and views.
Modes of FastExport :
FastExport works on two modes: RECORD and INDICATOR. By default INDICATOR mode is used. The difference between the two is that INDICATOR mode will set the indicator bits to 1 for the NULL values. If you are working on mainframes then use RECORD mode.
Why it is called FAST Export :
We have Teradata BTEQ in place for exporting the data then why should one use FastExport? The main reason for using FastExport is that it takes full advantage of multiple session which influence Teradata parallelism.
Also, BTEQ exports data row by row but FastExport does it in 64K blocks. Thus the export is lightning fast.
FastExport only supports SELECT statement. It can support multiple SELECT statements in one run. this should only be used if you want to export more than half million records.
COMPARISON BETWEEN BTEQ AND FASTEXPORT:-
Once the BTEQ is invoked it immediately starts sending the rows to the file.
If the output data is sorted, Bteq will still continue to send the data row by row, no redistribution is required.
If BTEQ terminates abruptly, all the rows are discarded from the spool. You will have to rerun the BTEQ from scratch.
Once the FastExport is invoked it puts the rows into spool and then starts building the data blocks.
Redistribution of data is required to build the blocks into correct sequence which thus takes a little longer time.
If FastExport terminates abruptly, all the rows are in worktables and it can continue sending them where it left off.
FastExport Commands :
Task Commands :
Begin Export : Begins the export task and sets the specifications for the number of sessions with Teradata.
End Export : Ends the export task and initiates processing by Teradata.
Export : Provides two things which are:
The client destination and file format specifications for the export data retrieved from Teradata
A generated MultiLoad script file that can be used later to reload the export data back into Teradata
Field : Constitutes a field in the input record section that provides data values for the SELECT statement.
Filler : Specifies a field in the input record that will not be sent to Teradata for processing. It is part of the input record to provide data values for the SELECT statement.
Import : Defines the file that provides the USING data values for the SELECT.
Layout : Specifies the data layout for a file. It contains a sequence of FIELD and FILLER commands. This is used to describe the import file that can optionally provide data values for the SELECT.
SQL Commands :
Alter table : Change a column or table options of a table.
checkpoint : Add a checkpoint entry in the journal table.
collect statistics : Collect statistics for one or more columns or indexes in atable
comment : Store or retrieve a comment string for a particular object.
create database : Creates a new database.
create table : Creates a new table.
create macro : Creates a new macro.
delete : Delete rows from a table.
drop database : Drops a database
database : Specify a default database for the session.
give : Transfer ownership of a database or user to another user.
delete database : Removes all tables, views, macros, and stored procedures from a database.
grant : Grant access privileges to an object.
update : Change a column value of an existing row or rows in a table.
FastExport Sample Script :
.LOGTABLE MYDB.STUDENT; /* define restart log */
.LOGON ip_address/username,password; /* DBC logon string*/
.BEGIN EXPORT SESSIONS 10; /* begin export with number of sessions to be used */
.EXPORT OUTFILE filepath/output.txt mode record format text; /* identify the destination */
)(TITLE '') FROM MYDB.STUDENT;
.END EXPORT ; /* terminate the export */
.LOGOFF ; /* disconnect from the DBS */
How to invoke FastExport :
fastexport < fastexportscriptfile
Concurrent Load Utility Tasks
>> The maximum number of concurrent FastExport tasks that can run is variable; the limit can be controlled by the system administrator. MaxLoadTasks may be overridden if TASM is active.
Exponential operators and Concatenation of data files are not allowed.
>> Are evaluated from left to right, using the Teradata Database order of preference, but can be overridden by parentheses
>> FastExport does not accept and will not display object names specified in internal Teradata Database hexadecimal form.
> NUMBER datatype is not supported in the nullif and apply-where clause. SET and ACCEPT commands cannot assign and accept NUMBER datatype.