I have a scenario at hand:-
Source: 9 Binary Flat Files (From Mainframe Source Systems)
Target: 1 Teradata Table
ETL Operations: Insert / Update / Delete using Informatica Workflows – Teradata MLOAD INSERT / UPDATE Connection String & Teradata MLOAD DELETE Connections String
Current Load Strategy: Load one file at a time – Dependency created at file level to make sure that there is only one MLOAD Process running against the target table at a time.
Requirement: Run the File -> table load concurrently or achieve parallelism. 9 Load processes should be made independent to each other – so that when a file arrives in the Source Files directory – the load kicks off and will not wait for its predecessor load to complete.
Issue: MLOAD puts a Table level lock which prevents another process to act on the same table. What are the other options that can be explored so that these 9 processes do not wait for each other – and they run in independently? Can T-Pump be used as a solution? Or is there any other possible solutions?
Please try using the following:
1. Create views on top of the base table and start using view layer to load the tables.
2. you have Tenacity and sleep options in informatica and can set these parameters appropriately.
Thanks krishaneesh - so T-Pump is not a solution?
did you mentioned - to create individual views on the underlying tables to load each file?
TPUMP can be a solution as long as your data size is not too big. The processing would be slow as TPUMP would be a row based operation.
You can define the view as replace view dbname.viewname locking row for access select * from dbname.tablename;