can you please tell why mload accepts duplicates, what happens inside and why fastload not allow multiset duplicates.
Thanks in advance
FastLoad is older than MLoad, when FL was implemented there was no MultiSet table in Teradata, yet.
This has been added later due to Standard SQL.
FastLoad doesn't know if a row is duplicate because it's an actual duplicate row or because there was a restart and the same row has been sent a second time. FL stores data directly in the target table and there's no place to store that kind of information.
If this feature was added it would mean more overhead/addidional space usage/slowing down the load and then it's no longer faster than MLoad. And why implementing this when there's already a tool (MLoad) which has this feature?
Btw, FL will load duplicates into a NoPI table (because there's no sort involved).
I found out (the hard way) that it is tied to the FL protocol not to the FL utility.
JDBC using FL protocol shows the same behavior.
Yes. We always talk about the "protocol" and not the utility.
The client utilities never actually look at the data and decide what is a duplicate (or a primary index violation).
All of those types of issues are handled by the DBS,.
FastLoad (and MultiLoad) just sends the data to the database.
And the management of the error tables is performed by Teradata as well, not by the utility.