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

Comments

Post a Comment

Popular posts from this blog

Informatica Powercenter Partitioning

Informatica PowerCenter Partitioning Option increases the performance of PowerCenter through parallel data processing. This option provides a thread-based architecture and automatic data partitioning that optimizes parallel processing on multiprocessor and grid-based hardware environments. Introduction: With the Partitioning Option, you can execute optimal parallel sessions by dividing data processing into subsets that are run in parallel and spread among available CPUs in a multiprocessor system. When different processors share the computational load,large data volumes can be processed faster. When sourcing and targeting relational databases, the Partitioning Option enables PowerCenter to automatically align its partitions with database table partitions to improve performance. Unlike approaches that require manual data partitioning, data integrity is automatically guaranteed because the parallel engine of PowerCenter dynamically realigns data partitions for set-oriented trans...
6 comments
Read more

Data virtualization

Data virtualization is a process of offering a data access interface that hides the technical aspects of stored data, such as location, storage structure, API, access language, and storage technology. Analogous to concept of views in databases Data virtualization tools come with capabilities of  data integration, data federation, and data modeling Requires more memory caching Can integrate several data marts or data warehouses through a  single data virtualization layer This concept and software is a subset of data integration and is commonly used within business intelligence, service-oriented architecture data services, cloud computing, enterprise search, and master data management. Composite, Denodo, and Informatica are the largest players in the area of data virtualization References for definition: http://www.b-eye-network.com/view/14815
7 comments
Read more

Find Changed Data by computing Checksum using MD5 function in Informatica

Introduction: Capturing and preserving the state of data across time is one of the core functions of a data warehouse, but CDC can be utilized in any database or data integration tool. There are many methodologies such as Timestamp, Versioning, Status indicators, Triggers and Transaction logs exists but MD5 function outlines on working with Checksum. Overview: MD5 stands for Message Digest 5 algorithm.It calculates the checksum of the input value using a cryptographic Message-Digest algorithm 5 and returns a128-bit 32 character string of hexadecimal digits (0 - F). Advantage of using MD5 function is that, it will reduce overall ETL run time and also reduces cache memory usage by caching only required fields which are utmost necessary. Implementation Steps : Identify the ports from the source which are subjected to change. Concatenate all the ports and pass them as parameter to MD5 function in   expression transformation Map the MD5 function output to a checksum outp...
1 comment
Read more