The listing 2 example: 1 SELECT D_TAX, D_NEXT_O_ID 2 INTO :dist_tax , :next_o_id 3 FROM OLD TABLE ( UPDATE DISTRICT 4 SET D_NEXT_O_ID = D_NEXT_O_ID + 1 5 WHERE D_W_ID = :w_id 6 AND D_ID = :d_id 7 ) AS OT
A lot of this is non-standard SQL, so I can't really tell what DB2 is doing
here. Can you explain it?
Quote from the article at:
http://www-106.ibm.com/developerworks/db2/library/techarticle/dm -0411rielau/?ca=dgr-lnxw06SQL-Speed
First, DB2 deals with the DISTRICT table. Data needs to be returned and an update needs to be performed. Conventional wisdom states that this requires 2 SQL statements, and that the UPDATE ought to be done prior to the SELECT; otherwise deadlocks may occur as concurrency increases.
DB2 however supports a new SQL feature which is in the process of being standardized. This feature allows access to what is known as transition tables in triggers. The OLD TABLE transition table holds the original state of the affected rows before they are processed by the UPDATE or DELETE statement. The NEW TABLE transition table holds the affected rows immediately after an INSERT or UPDATE was processed. That is the state prior to when AFTER triggers fire. Users with a Microsoft or Sybase background may know these tables by the names DELETED and INSERTED.
So, if I understand they use only ONE query to get the UPDATE and the SELECT of the old value.
Cordialement, Jean-Gérard Pailloncy
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
