3

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

Community
  • 1
  • 1
bluish
  • 26,356
  • 27
  • 122
  • 180
  • Complicated is not the only problem with the suggestion behind "this technique". You have a race condition that the between counting the rows and altering them the row count could change. This is not reliable information. – 0xCAFEBABE Jan 18 '11 at 15:22

2 Answers2

5

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.

Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
  • Thanks Ian! But looking into the documentation I realized that on DB2 for IBM i this command isn't available yet :( for any version.. The only available feature is selecting inserted values (http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/sqlp/rbafysfins.htm), starting from V6R1. – bluish Jan 19 '11 at 08:36
  • Take care that "select from insert" expression may have a big performance overhead because of the creation of temporary tables http://iprodeveloper.com/[primary-term]/select-insert-new-sql-function-system-i-v6r1... at least with v6r1, which in fact remove any benefits of reducing the number of roundtrips. So it may be only worth with large set of inserted/updated rows. – Yves Martin Sep 10 '13 at 17:12
5
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.

bluish
  • 26,356
  • 27
  • 122
  • 180
Lynette Duffy
  • 404
  • 2
  • 5
  • @Lynette Thanks! Can I use this statement also in a batch (not in a procedure)? I.e. `UPDATE MYTABLE SET MYFIELD = 'foo' WHERE COLOR='red'; GET DIAGNOSTICS ROW_COUNT;` or something like this. Can you show me how. I saw only examples used inside a procedure. – bluish Jan 26 '11 at 15:00
  • @Lynette Anyway it seems it can't return the modified rows, but only how many they are.. – bluish Jan 26 '11 at 15:02
  • @bluish From the article: "This statement can only be embedded in an application program, SQL function, SQL procedure, or trigger. It cannot be issued interactively. It is an executable statement that cannot be dynamically prepared." Batch = YES – Lynette Duffy Jan 31 '11 at 20:47
  • @bluish It most certainly captures modified rows, I use it all the time. I believe I've answered your question unless you'd like to edit it and be more specific? What do you mean by "in batch (not a procedure)"? You can certainly execute a procedure in BATCH or INTERACTIVELY. :) – Lynette Duffy Jan 31 '11 at 20:55
  • @Lynette I say "batch" according to [this definition](http://stackoverflow.com/questions/4735856/difference-between-a-statement-and-a-query-in-sql/4735902#4735902), that is a series of statement semi-colon-separated. So, according to the doc you reported, I fear this instruction can't be executed in a batch. – bluish Feb 01 '11 at 07:42
  • @bluish OK, you're talking about a "batch" of SQL statements; I thought you were talking about a "batch" job as defined by the IBM i operating system. By what method are you running your SQL batch statements? From a script using the RUNSQLSTM command? ER. Should we take this discussion offline? :) – Lynette Duffy Feb 03 '11 at 13:05
  • @Lynette I'm running it through a simple Java program I wrote to execute statements (in batch mode, in the night) in a controlled way. But now I figured out I can't obtain a record set with the modified rows (as a log)... I must wait for an upgrade. Thanks for all Lynette!! – bluish Feb 04 '11 at 08:40