We are using Informatica PC to move data from TD Staging tables to TD Target tables leveraging Push Down Optimization via ODBC connection. Essentially, this is converted to INSERT/SELECT and UPDATE statements which are pushed down to TD via ODBC.
My question is that if we can use Bteq scripts instead of the PDO via ODBC (in Informatica) for the Insert/Selects and Updates then what advantages may it offer ? Will Insert/Selects and Updates be faster via Bteq vs ODBC ? Is it wiser to use Bteq scripts instead ?
Thanks in advance...
An INSERT/SELECT or UPDATE statement is running in the database only, so there's not much traffic through ODBC, if I understand you correctly. You can check this in DBQL to see if you have thousands of INSERT statements, but since you're using PDO, you wouldn't get any different performance with BTEQ vs. ODBC/Informatica.