Utilities Description

Utilities Description

Teradata Utilities

BTEQ 

• Batch mode utility for submitting SQL requests to    the Teradata Database
• Runs on every supported platform – laptop to   mainframe
• Flexible and easy to use report writer
• Exports data to a client system from the Teradata   Database
• Reads input data and imports it to the Teradata   Database
• Limited ability to branch forward to a LABEL
•BTEQ has the ability to branch forward in a script based on a test of either an error code or an activity count
•This is not a true loop function yet it can be used to avoid unnecessary, time-consuming steps
•We can do Error handling buut not Error capturing with this Bteq.

FastLoad 

•  Fast batch mode utility for loading new tables into  the Teradata Database
•  Can reload previously emptied tables
•  Full restart capability
•  Error limits and error tables, accessible using SQL
•  Restartable INMOD routine capability
•  Ability to load data in several stages
•  Can apply Checkpoint
•  Two Phases 1. Acquisition 2. Application 
•  Two Error tables 1. ET . UV

Purpose:

Load large amounts of data into an empty table at high speed.

Concepts:

            Load into an empty table with no secondary indexes.

            Creates two error tables.

            Checkpoints can be taken for restarts.

Restrictions

•If an AMP goes down, FastLoad cannot be restarted until it is back online
•The Teradata Database will accommodate up to 15 FL/ML/FE runs at one time
•Tables defined with referential integrity or secondary  indexes cannot be loaded with FastLoad
•Duplicate rows cannot be loaded into multi-set table with FastLoad

Phase 1

•FastLoad uses one SQL session to define AMP steps

•The PE sends a block to each AMP

•AMPs hash each record and redistribute them to the AMP responsible for the hash value

•Records are written to the target table in unsorted blocks

Phase 2

•When loading completes, each AMP sorts the target table, puts the rows into blocks, and writes the blocks to the disk

•Fall back rows are then generated if required

MultiLoad 

MultiLoad is a batch mode utility in Teradata which is used to insert, update and delete data to/from a populated table. It has following features:

    * MultiLoad supports upto 5 populated tables per script.

    * MultiLoad has an ability to perform Inserts, Updates and Deletes.

    * Target tables may contain pre-existing data but shouldn’t have USIs (Unique Secondary Indexes) defined on a table.

    * Target tables shouldn’t have Refrential Integrity.

5 phases in a MultiLoad Utility

    * Preliminary Phase – Basic Setup

    * DML Phase – Get DML steps down on AMPs

    * Acquisition Phase – Send the input data to the AMPs  and sort it

    * Application Phase – Apply the input data to the appropriate Target Tables

    * End Pahse – Basic Cleanup

Limitations of Teradata Multiload Utility: 

MultiLoad is a very powerful utility; it has following limitations:

    * MultiLoad Utility doesn’t support SELECT statement.

    * Concatenation of multiple input data files is not allowed.

    * MultiLoad doesn’t support Arithmetic functions i.e. ABS, LOG etc. in Mload Script.

    * MultiLoad doesn’t support Exponentiation and Aggregator Operators i.e. AVG, SUM etc. in Mload Script.

    * MultiLoad doesn’t support USIs (Unique Secondary Indexes), Refrential Integrity, Join Indexes, Hash Indexes and Triggers.

    * Import task require use of PI (Primary Index).
•No data retrieval capability
•Arithmetic functions, exponentiation or aggregates  are not processed
•Cannot process tables with Secondary Indexes or Referential Integrity defined

TPUMP (Teradata Parallel Data Pump) 

    * TPUMP allows near real time updates from Transactional Systems into the Data Warehouse.

    * It can perform Insert, Update and Delete operations or a combination from the same source.

    * It can be used as an alternative to MLOAD for low volume batch maintenance of large databases.

    * TPUMP allows target tables to have Secondary Indexes, Join Indexes, Hash Indexes, Referential Integrity, Populated or Empty Table, Multiset or Set Table or Triggers defined on the Tables.

    * TPUMP can have many sessions as it doesn’t have session limit.

    * TPUMP uses row hash locks thus allowing concurrent updates on the same table.

Limitations of Teradata TPUMP Utility:

    * Use of SELECT statement is not allowed.

    * Concatenation of Data Files is not supported.

    * Exponential & Aggregate Operators are not allowed.

    * Arithmatic functions are not supported.

    * There is a limit of four IMPORT commands within a single TPump “load” task

TPump / MultiLoad

•MultiLoad performance improves as the volume of changes increases.

•TPump does better on relatively low volumes of changes

•TPump uses macros to modify tables rather than actual DML commands

•MultiLoad uses the DML statements.

•TPump uses  row hash locking to allow for concurrent read and write access to target tables.

•MultiLoad locks tables for write access (Phase 2) until it completes