I have 2 tables coming from the same datastore, one as source the other as the target, then I made a sample job with no transformation, just straight SOURCE>QUERY>TARGET.
Now when I take a look at the optimized SQL, it doesn’t perform an INSERT SELECT.
Source/Target: Teradata 13
Btw, this only happened when we upgraded to 4.2.
The test that I made is working on DS 3.2 and 4.1.
I also saw this youtube video that shows it’s a bug(?)
My colleague told me that yes this issue is a bug. SAP has acknowledge it, i just don’t have the SAP note. They said that the fix might come out on the next patch.
The youtube video that I posted was my colleagues discovery on how to enable pushdown on Teradata 13.
Here are the specific steps:
Assuming your datastore connects to a Teradata 13.x database.
Export your Teradata 13.x datastore as .xml (not .atl!)
Open the .xml file and change the “Teradata 13” to “Teradata 14”
Import the .xml file
Create a job and turn the Auto correct load and allow merge option to on/off. (follow the youtube video to see)
The optimized query is working as “insert…” in 3.2 but the same Dataflow shows “select …” in 4.2
I have NVL used in some of the columns, once I remove NVL it is working fine and optimized SQL shows “insert …”
Basically what I understand is BODS4.2 is degrading the performance of the existing job, the job used to complete in 30 minutes in 3.2 but now it takes more than an hour in 4.2.