Get rows affected by the update

Question

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

It is very useful to see which lines have just been updated, even more during the tests.

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

I have also seen this method , but I cannot use it because my update request is too complex.

An additional feature may be: getting the previous version of these lines as well , so you can compare it with the actual version (after updating) and see the changes.

EDIT: my DB2 versions - for IBM I have V5R3 and V6R1

Answer 1

You won’t tell which version of i5 / OS you are using, but if you are using V6R1 or later, you can use the links to the data change tables to see the rows that have been changed by the update statement. For example:

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

FINAL TABLE will give you the lines after running all / all triggers. Note that there are other links to the data change table, NEW TABLE — which will display the rows before they are affected by any triggers.

You can read the links to the data change tables in the i5 / OS documentation.

Answer 2

 GET DIAGNOSTICS updated_rows = ROW_COUNT; 

Take a look here: GET DIAGNOSTICS statement

Your “extra features” sound like you need a trigger.

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