Multiload in ETL


Multiload in ETL

Hi All,

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.

Thank you

Re: Multiload in ETL

You may want to start your analysis by checking how skewed your data is, if you have too many NUPI duplicates etc ....

Re: Multiload in ETL

As well check any non unique primary index on the target table.

Re: Multiload in ETL


I have had many good experiences using Multiload.

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 hope this helps.

Re: Multiload in ETL

Hi Guys,

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.

Thanks once again.