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: 348
        
/Oracle/OBI
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