OBIA on Teradata - Part 1 Loader and Monitoring

Posted by Mohan Ramanuja on Oracle Blogs See other posts from Oracle Blogs or by Mohan Ramanuja
Published on Tue, 4 Sep 2012 19:06:56 +0000 Indexed on 2012/09/04 21:45 UTC
Read the original article Hit count: 252

Filed under:

The out-of-the-box (OOB) OBIA Informatica mappings come with TPump loader. 

 TPUMP  FASTLOAD
TPump does not lock the table.
FastLoad applies exclusive lock on the table.
The table that TPump is loading can have data.
The table that FastLoad is loading needs to be empty.
TPump is not efficient with lookups.
FastLoad is more efficient in the absence of lookups.
The out-of the box Informatica mappings come with TPump loader.
There is chance for bottleneck in writer thread

The out-of the box tables in Teradata supplied with OBAW features all Dimension and Fact tables using ROW_WID as the key for primary index. Also, all staging tables use integration_id as the key for primary index. This reduces skewing of data across Teradata AMPs.

You can use an SQL statement similar to the following to determine if data for a given table is distributed evenly across all AMP vprocs. The SQL statement displays the AMP with the most used through the AMP with the least-used space, investigating data distribution in the Message table in database RST.
SELECT vproc,CurrentPerm
FROM DBC.TableSize
WHERE Databasename = ‘PRJ_CRM_STGC’
AND Tablename = ‘w_party_per_d’
ORDER BY 2 desc

If you suspect distribution problems (skewing) among AMPS, the following is a sample of what you might enter for a three-column PI:
SELECT HASHAMP (HASHBUCKET (HASHROW (col_x, col_y, col_z))), count (*)
FROM hash15
GROUP BY 1
ORDER BY 2 desc;

ETL Error Monitoring

Error Table – These are tables that start with ET. Location and name can be specified in Informatica session as well as the loader connection.
Loader Log – Loader log is available in the Informatica server under the session log folder. These give feedback on the loader parameters such as Packing Factor to use. These however need to be monitored in the production environment. The recommendations made in one environment may not be used in another environment.
Log Table – These are tables that start with TL. These are sparse on information.
Bad File – This is the Informatica file generated in case there is data quality issues


© Oracle Blogs or respective owner

Related posts about /Oracle/OBI