With DB2 version 8 a lot of new functionality was introduced. One of the new options which is not easy to discover is the possibility to return the value of built-in functions used in an INSERT statement. Previously you had to issue SQL SET statements before the INSERT to retrieve these values, but this is now history. You were also cut off from the possibility of retrieving the values assigned to columns WITH DEFAULT or ROWID. The same applied to identity columns.

Many installations uses a timestamp column as a unique key on their tables. In order to reuse the assigned timestamp value as a foreign key on other tables you most likely perform an EXEC SQL SET :keyColValue = CURRENT TIMESTAMP statement before an INSERT, but now you can do it all in one statement:

SELECT keyCol, col3
INTO :keyColValue, :col3value
FROM FINAL TABLE
( INSERT INTO myTable (keyCol, col1, col2, col3)
  VALUES(CURRENT TIMESTAMP, :col1, :col2, DEFAULT)
)

I think this is easy and elegant. Imagine COL3 in the above example is created as WITH DEFAULT 'XXX'. In this case the hostvariable COL3VALUE will contain the value 'XXX' after executing the statement. By specifying DEFAULT in the VALUES list you are able to retrieve values for columns that you normally would not specify in your VALUES list, like identity columns and ROWID columns. Now you probably wonder what happens if the INSERT fails because current timestamp was not unique (another parallel INSERT was assigned the same value which happens surprisingly often nowadays), well you will receive a SQLCODE -803 despite the fact that it is a SELECT statement which is executed.

Now you probably want to know if it will have any measurable effect to replace your SET followed by INSERT with a SELECT with INSERT. Yes, it has although it is limited. You save the cost of executing the SET statement, but on the other hand the INSERT becomes a little more expensive, because it has to return values to your program, but all in all it is less expensive to use SELECT FROM FINAL TABLE instead.