Returning inserted rows (and updated and deleted)

Motivation

The Problem

Taking an order

CREATE TABLE order(order_id INTEGER      NOT NULL PRIMARY KEY,
                   stamp    TIMESTAMP    DEFAULT CURRENT TIMESTAMP, 
                   name     VARCHAR(20),
                   price    INTEGER,
                   agent_id INTEGER);

CREATE OR REPLACE SEQUENCE order_seq;

SELECT order_id, stamp 
  FROM NEW TABLE(INSERT INTO order(order_id, name, price) 
                   VALUES(NEXT VALUE FOR order_seq, 'John', 1000));
   ORDER_ID STAMP                     
----------- --------------------------
          1 2012-05-09 22:05:29.156000

SELECT order_id, stamp
  FROM NEW TABLE(INSERT INTO order(order_id, name, price) 
                   VALUES(NEXT VALUE FOR order_seq, 'John', 1000),
                         (NEXT VALUE FOR order_seq, 'Beth',  500),
                         (NEXT VALUE FOR order_seq, 'John', 1200));
   ORDER_ID STAMP                     
----------- --------------------------
          2 2012-05-09 22:06:36.515000
          3 2012-05-09 22:06:36.515000
          4 2012-05-09 22:06:36.515000

SELECT order_id, stamp, name, sum(price) over(order by order_id) as total_price
  FROM NEW TABLE(INSERT INTO order(order_id, name, price) 
                   VALUES(NEXT VALUE FOR order_seq, 'John', 1000),
                         (NEXT VALUE FOR order_seq, 'Beth',  500),
                         (NEXT VALUE FOR order_seq, 'John', 1200),
                         (NEXT VALUE FOR order_seq, 'Beth',  700),
                         (NEXT VALUE FOR order_seq, 'Jack', 1100));
   ORDER_ID STAMP                      NAME                 TOTAL_PRICE
----------- -------------------------- -------------------- -----------
          5 2012-05-09 22:07:26.062000 John                        1000
          6 2012-05-09 22:07:26.062000 Beth                        1500
          7 2012-05-09 22:07:26.062000 John                        2700
          8 2012-05-09 22:07:26.062000 Beth                        3400
          9 2012-05-09 22:07:26.062000 Jack                        4500

                Rows 
               RETURN
               (   1)
                Cost 
                 I/O 
                 |
                  5 
               TBSCAN
               (   2)
               6.7822 
                  1 
                 |
                  5 
               SORT  
               (   3)
               6.78117 
                  1 
                 |
                  5 
               TBSCAN
               (   4)
               6.77634 
                  1 
                 |
                  5 
               SORT  
               (   5)
               6.7753 
                  1 
                 |
                  5 
               INSERT
               (   6)
               6.77286 
                  1 
          /------+------\
         5                 0 
      TBSCAN     TABLE: ADMINISTRATOR
      (   7)             ORDER
   8.88007e-005           Q5
         0 
        |
         5 
 TABFNC: SYSIBM  
      GENROW
        Q1

	3) SORT  : (Sort)
...
		SORTKEY : (Sort Key column)
			NONE

Dispatching an order

CREATE OR REPLACE VARIABLE my_agent_id INTEGER;
SET my_agent_id = 1;

SELECT order_id, stamp, name, price
  FROM OLD TABLE(UPDATE (SELECT * FROM order 
                          WHERE agent_id IS NULL 
                          ORDER BY order_id FETCH FIRST ROW ONLY)
                    SET agent_id = my_agent_id);
   ORDER_ID STAMP                      NAME                       PRICE
----------- -------------------------- -------------------- -----------
          1 2012-05-09 22:05:29.156000 John                        1000

                        Rows 
                       RETURN
                       (   1)
                        Cost 
                         I/O 
                         |
                          1 
                       TBSCAN
                       (   2)
                       13.5567 
                          2 
                         |
                          1 
                       SORT  
                       (   3)
                       13.5561 
                          2 
                         |
                          1 
                       UPDATE
                       (   4)
                       13.5549 
                          2 
                   /-----+-----\
                  1               9 
               FETCH    TABLE: ADMINISTRATOR
               (   5)           ORDER
               6.79517           Q1
                  1 
           /-----+------\
          9                9 
       IXSCAN    TABLE: ADMINISTRATOR
       (   6)            ORDER
      0.0205156           Q2
          0 
         |
          9 
   INDEX: SYSIBM  
 SQL120509220520090
         Q2

Deleting an order

WITH del AS (SELECT * FROM OLD TABLE(DELETE FROM order WHERE order_id = 1))
SELECT fulfilled 
  FROM NEW TABLE(INSERT INTO order_archive
                   SELECT del.*, CURRENT TIMESTAMP FROM del);
FULFILLED                 
--------------------------
2012-05-09 22:32:28.078000

                                Rows 
                               RETURN
                               (   1)
                                Cost 
                                 I/O 
                                 |
                                  1 
                               TBSCAN
                               (   2)
                               20.3274 
                                  3 
                                 |
                                  1 
                               SORT  
                               (   3)
                               20.3268 
                                  3 
                                 |
                                  1 
                               INSERT
                               (   4)
                               20.3256 
                                  3 
                           /-----+-----\
                          1               1 
                       DELETE   TABLE: ADMINISTRATOR
                       (   5)       ORDER_ARCHIVE
                       13.5558           Q6
                          2 
                   /-----+-----\
                  1               9 
               FETCH    TABLE: ADMINISTRATOR
               (   6)           ORDER
               6.78603           Q1
                  1 
           /-----+-----\
          1               9 
       IXSCAN   TABLE: ADMINISTRATOR
       (   7)           ORDER
      0.015546           Q2
          0 
         |
          9 
   INDEX: SYSIBM  
 SQL120509220520090
         Q2