Why can't we MLoad utility for all the ETL scripts instead of a BTEQ ? Ofcourse, there is a Secondary Index limitations when using Mload, but apart from it, wouldn't it beneficial for us to use Mload / TPT for all the ETL scripts within the organization ? Please advice.
Yes, Mload or TPT would be ideal. I am not aware of any ETL tool that insists on using Bteq; most if not all of them use the TPT API. Bteq can do multi-statement requests, but that is not as fast as MultiLoad (TPT Update).
Thanks for your response. When i said ETL, I didnt mean ETL tools like Informatica, but i meant Teradata Utilities for ETL purposes. Predominantly I observe that BTEQ is used for loading / manipulation purposes, whereas Multiload or TPT is used only for specific scenarios (when the data volume is too huge). Is it resources that the other Utility takes due to which BTEQ is the preferred for ETL purposes and not the other utitilies like Mload ?
If the amount of data one is loading or exporting is small enough (the definition of "small enough" varies depending on the number and type of nodes), Bteq or a user-written ODBC/JDBC/CLIv2 program is preferable because by default it only has to connect one session, so it starts up and shuts down faster. If the primary task is simply to manipulate data within the database, which is what most users are doing with DML, then the utilities are not really applicable. The TPT Load, Update and Export operators use special CLIv2 interfaces that tell the database management system to use parallel operations for acquiring external data or extracting data from the database. If that is not what you are doing, then these utilities are not really relevant. One example is the classic E-L-T (as opposed to ETL) process: a data set extracted from somewhere else is loaded in raw form to a staging table using TPT Load, and from there it is merged into the target (production) table via SQL, which could be executed from a Bteq script.