For many years one of the most irritating details about DB2 has been the lacking ability to perform an INSERT INTO mytable SELECT * FROM mytable WHERE ... . That was changed in DB2 version 7. Unfortunately very few people has been aware of this so that is why I am writing this tip.

As soon as you solve one problem new problems arise. This is also the case for copying rows within the same DB2 table. If there is one or more unique indexes on the table you have to change the key values. Imagine table mytable consisting of columns a, b, c and d with a unique index on columns a and b. Then you may make a new copy of one or more rows like this:

INSERT INTO mytable (a, b, c, d)
SELECT 'NEWVALUE', b, c, d
FROM mytable
WHERE a = 'OLDVALUE'

This is quite simple and a lot of systems may take advantage of this trick especially when creating new rows in history tables. If a table has a unique index on a column declared as TIMESTAMP it might seem straight forward to use CURRENT TIMESTAMP as the new value for such a column. If the SELECT returns more than one row the approach will fail miserably by returning a sqlcode -803, because CURRENT TIMESTAMP has the same value during the execution of a SQL statement. That hurts. Well obviously someone complained and IBM invented the GENERATE_UNIQUE function. Imagine that column b in the previous example is a TIMESTAMP column:

INSERT INTO mytable (a, b, c, d)
SELECT 'NEWVALUE', timestamp(generate_unique()), c, d
FROM mytable
WHERE a = 'OLDVALUE'

The generate_unique function returns a unique value for each reference to it in each row. The value returned by generate_unique may be converted into a timestamp using the timestamp function. This creates a unique timestamp for each column and row in the DB2 table and makes the above insert work.

By the way it is a bit thought-provoking that CURRENT TIMESTAMP has the same value during the execution of one single SQL statement. Think of an update SQL statement updating millions of rows and assigns the CURRENT TIMESTAMP value to a "last updated" column. Such a statement may execute for a very long time and assign the same timestamp to all updated rows even though there may be hours between the update of the first and last row.