I'm trying to extract 400,000 records from flat file,perfom lookup with a teradata table and finally load into existing teradata production table using multiload stage with load type as upsert.Everything is fine till extraction but loading into table dramatically falls down like 300rows/sec,..,199r/s,198.... . Approximately it takes 6 hrs to finish loading.I set sessions max as 5.
Can you please let me know where is the problem and how efficiently can we use multiload to fasten this.
It is a very fast load tool, except.... when you have a problem with your layout or field consistency, like datatype mismatch, where the "ET_" or "UV_" error tables start to be feed. The ideal world is when only your "WT_" table grows, and the "ET_" and "UV_" tables remain stable, not growing. If you have this picture, your process should run really fast. When the tables "ET_" and/or "UV_" start to grow, the whole process becomes really slow. If you notice the previous "slow" picture, you could abort the loading process, analyze the logs, make the fixes and restart the loading.
I thank all of you for your replies. I figured out the problem and It's working fine now,Mload just taking 5mts. The problem was - the operation performed by upsert was on 4columns whereas primary index existed on only one column. I recreated the table with primary index on all the 4columns.