Merge statement showing 0 records merged

Database
Enthusiast

Merge statement showing 0 records merged

Hi all,

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?

Note:-

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.

Thanks,

Soumik

Tags (1)
6 REPLIES
Enthusiast

Re: Merge statement showing 0 records merged

Hi,

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.

Cheers,

Raja

kw
Enthusiast

Re: Merge statement showing 0 records merged

This is the normal behaviour for the MERGE statement - by accident or deisgn I'm not sure.

Enthusiast

Re: Merge statement showing 0 records merged

Hi,

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.

Cheers,

Raja

Enthusiast

Re: Merge statement showing 0 records merged

hi Raja,

as you mentioned that it can be the behaviour of teradata sql assistant, but as per my experience, when i ran the same query in sql assistant, i could see the non zero record counts.

Running the same query on unix environment also showed non zero record count.

Regards,
Soumik
Enthusiast

Re: Merge statement showing 0 records merged

Hi Soumik,

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.

Cheers,

Raja

Enthusiast

Re: Merge statement showing 0 records merged

Soumik,

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.

Cheers.