I have got a question regarding usage of FastLoad and MultiLoad.
As per my understanding and Teradata Manuals, FastLoad can only be used while loading a table for first time (Initial load) and MultiLoad is being used in case of incremental load.
However same MultiLoad can also be used to load empty table or for initial load. So why do we need to use Fastload tool if Multiload is capable of doing the same task of Fastload (incremental Load)?
I am aware that Fastload needs only 2 phases for dataload while Multiload takes 5 phases. Is this the only reason for which usage of Fastload is highly recommended?
the functionality of FastLoad is a subset of MLoad, every FastLoad job could be replaced by an MLoad.
But FL is always faster than ML, because it inserts directly into the empty target table while ML loads in a staging table (worktable) and then inserts into the target. And ML needs additional perm space for this worktable.
The 2 or 5 phases are just a definition. The acquisition phase (phase 3) of ML is just like FastLoading the source data into MLoad's worktable, you could split that into two phases, too :-)
In our site, we are using a Stream operater in TPT, which is like very slow becuase it uses ODBC connection.
The reason why development team are using it becuase, they want to do both insert and update in 2 instance of the same job.
Now my questions is, can we use update operater in TPT, which will use Multiload for this purpose.
The Stream operator does NOT use ODBC.
The Update operator is part of TPT, so the answer to your first question is yes.