I have been recently facing an issue while asking TD DBAs to execute a MERGE statement.
Everytime a merge statement is executed by them, it shows 0 records MERGED, whereas while cross checking it later by select query using INNER join of source and target table, it shows some figure, also the same MERGE statement when runs under its daily scheduled batch job, it shows records that have MERGED.
This issue is actually misleading our team by showing 0 records merged everytime any MERGE query is fired by Teradata DBAs.
Could you please let me know if there is some mode that is being applied by the DBAs which is showing 0 records merged everytime, although the reocrds have already merged in reality?
1. The MERGE statements are run in TD SQL assistant by TD DBAs.
2. The other statements like INSERT or UPDATE is showing non-zero records count that is getting inserted or updated. This issue is only with the MERGE statements.
Kindly let me know if further information is needed.
Few things that come to my mind maybe, just to narrow down the search:
It was executed in a different environment, DBs etc, with data being available in one and not available in another.
Maybe you can compare word by word and line by line with the resolved values of DBs and Tablenames by DBA and team.
What you can do is take both the codes of DBA and your code and execute in bteq in unix/linux and see the results and compare if they match. I always trust Unix/linux more.
Second, then you run both in sql assistant and see. It may be behaviour of SQL assistant.
Why don't you paste the code here in all four cases with data so that we can have a look.
Of course, I dont have login to unix box, but others who can test , can give an opinion.
MERGE statements running from SQL Assistant does not work properly because it is a BTT transaction mode.
Try the same query in BTEQ with ANSI transaction mode and let me know.