Get rows affected by an update

Question

How can you get the exact rows affected by an SQL UPDATE statement in DB2 on AS400?

It's very usefull to see which are the rows just updated, even more during tests.

I saw this question: is the same, but specific for MySQL. Is there a standard SQL way or DB2 specific way for doing this?

I saw also this technique, but I can't use it because my update query is too much complicated.

An additional feature could be: getting also previous version of these rows, so you can compare it with the actual version (after the update) and see the modifications.

EDIT: my DB2 versions are the ones for IBM i V5R3 and V6R1

Solution 1

You don't say what version of i5/OS you are running, but if you are running V6R1 or later, you can use "data change table references" to see the rows that are modified by the update statement. For example:

select * from FINAL TABLE ( update yourtable set c1 = x where ... )

FINAL TABLE will give you the rows after any/all triggers are fired. Please note there are also another data change table references, NEW TABLE - which will show the rows before they are affected by any triggers.

You can read about data change table references in the i5/OS documentation.

Solution 2

GET DIAGNOSTICS updated_rows = ROW_COUNT;

Check it out here: GET DIAGNOSTICS statement

Your "additional features" sounds like you want a trigger.

Interactively, using STRSQL, response messages like this are displayed to the screen after the statement is completed.