Skip to main content

Teradata Utilities


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.

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).

Comments

  1. UnknownNovember 5, 2014 at 8:29 PM

    Thanks for Information Teradata is a Relational Database Management System (RDBMS) for the world’s largest commercial databases. Teradata can store data upto Teradata bytes in size. This makes the Teradata as a market leader in data warehousing applications. Teradata Online Training

    ReplyDelete

Post a Comment

Popular posts from this blog

BYNET

DEFINITION BYNET, acronym for "BanYan NETwork," is a folded banyan switching network built upon the capacity of the YNET. It acts as a distributed multi-fabric inter-connect to link PEs, AMPs and nodes on a Massively Parallel Processing (MPP) system. OVERVIEW Interconnect technology is important for parallel computing. The BYNET is Teradata's "system interconnect for high-speed, fault tolerant warehouse-optimized messaging between nodes." [11] As an indispensable part of the Teradata MPP system, it can be understood better with its predecessor "YNET" in the background. In 1982, the YNET interconnecting technology used on the DBC 1012 was patented for the parallelism. As a broadcast-based hardware solution, it linked all the IFPs, COPs, and AMPs together with circuit boards and cables in a dual bus architecture. Two costom-built busses operated concurrently within the interconnect framework: YNET A to connect the IFPs and COPs on one side, and YNET ...
3 comments
Read more >>

Datawarehousing Concepts

Data warehousing : Data warehousing is combining data from multiple and usually varied sources into one comprehensive and easily manipulated database. Common accessing systems of data warehousing include queries, analysis and reporting. Because data warehousing creates one database in the end, the number of sources can be anything you want it to be, provided that the system can handle the volume, of course. The final result, however, is homogeneous data , which can be more easily manipulated. Data warehousing is comprised of two primary tools: databases and hardware. In a data warehouse, there are multiple databases and data tables used to store information. These tables are related to each other through the use of common information or keys. The size of a data warehouse is limited by the storage capacity of the hardware. The hardware required for a data warehouse includes a server, hard drives, and processors. In most organizations, the data warehouse is accessible via the sh...
3 comments
Read more >>

Vprocs

DEFINITION Vproc, acronym for "Virtual PROCessor," is a self-contained instance of the processor software on a Teradata platform (SMP platform or a node). Vprocs are the basic units of parallelism that make up the Teradata database server. OVERVIEW To put it simply, a virtual processor is a simulated processor in a processing software system, or a software version of a dedicated physical processor. Each vproc uses a portion of the physical processor resource, and runs independently of other vprocs.  The vprocs co-existing within a node share a single memory pool - a collection of free memory in the node. The portion of memory allocated from the pool to one vproc will be returned to the pool after usage for use by other vprocs. Vprocs are the basic units of parallelism that make up the Teradata database server. A single SMP node is made up of multiple vprocs, and a single Teradata MPP system is made up of multiple SMP nodes.  Vprocs run as multi-threaded processes to...
Post a Comment
Read more >>