MERGE and GET DIAGNOSTICS ROW_COUNT

[bughunter]

row_count - is it total inserted,update d or deleted by merge statement?
Any way get different counts?

Andy

[Serge Rielau]

bughunter@ru wrote:

row_count - is it total inserted,update d or deleted by merge statement?
Any way get different counts?

Andy

Good one... we pondered this question quite a bit.
http://publib.boulder.ibm.com/infoce...6c%63%61%22%20
"After INSERT, UPDATE, DELETE, or MERGE, contains the actual number of
rows that qualified for the operation."
The rows "qualifying for MERGE" are all the rows which don't hit the
"ELSE IGNORE" section. That is, it is the sum of all rows processed by
each WHEN [NOT] MATCHED branch.

The owrds were chosen thinking about INSTEAD OF trigger where rowcount
could be any value, yet the insert trigger just dumped the rows to
nirwana or did all sorts of complex stuff.

Eventually I'd like to see this:
SELECT COUNT(*) cnt, mode FROM
NEW TABLE(MERGE INTO T INCLUDE (mode VARCHAR(10)
USING S ON T.pk = S.pk
WHEN MATCHED THEN UPDATE SET c1 = S.c1,
mode = 'UPDATE'
WHEN NOT MATCHED THEN INSERT VALUES (pk, c1, 'INSERT'))
GROUP BY mode;

*Thinking of my happy-place with infinite resources*
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab