Nowadays it has become fashion to use a timestamp column as unique key. One of the problems by using this aproach is that two inserts on the same table may be using the same timestamp. Another irritating detail is that you cannot use INSERT INTO ... SELECT * FROM ... on such tables if you want to generate new unique timestamps with CURRENT TIMESTAMP. If your installation is running DB2 version 7 or later then the function GENERATE_UNIQUE is able to help you.
First of all you can find out whether GENERATE_UNIQUE works on your DB2. It is very easily done using a:
You must remember to put () after GENERATE_UNIQUE otherwise DB2 interprets GENERATE_UNIQUE as the name of a column. If the above statement fails with anything else than a -904 then GENERATE_UNIQUE is not available at the DB2 subsystem where you were executing the SQL statement. If you are running DB2 version 7 you must ask your DB2 systems programmer to install APAR PQ70901. When he or she has done that it should work. If you receive a -904 then the GENERATE_UNIQUE function may not be properly installed or your z/OS hardware/software may not be sufficiently upgraded to make GENERATE_UNIQUE work.
When the above SQL statement works and outputs ten timestamps then please note that they are all different and in ascending order. The combination of GENERATE_UNIQUE and the TIMESTAMP function delivers a unique timestamp for each returned row within the same SQL statement. If you make an INSERT INTO ... SELECT * FROM ... then TIMESTAMP(GENERATE_UNIQUE()) will work in exactly the same way and therefore the SELECT part may be programmed to return a unique timestamp for each row.
Two INSERT statements executed concurrently against the same DB2 (or DB2 data sharing group) may still result in a -803. This problem may also be solved by using GENERATE_UNIQUE by defining a new unique key column (named for instance key_column) as a CHAR(13) FOR BIT DATA. Columns with this definition can be assigned the value returned by GENERATE_UNIQUE and will always be unique and ascending even in a data sharing group. This approach is of course most easy to implement on new tables, while it will require a lot of adjustments if used on existing tables. The good trick about using a key column defined as above is that you can perform a TIMESTAMP(key_column) and get the exact date and time for the creation of the unique value. In other words you get the same information as when using a TIMESTAMP key column and on top of that you get full uniqueness.
At last I have to mention that DB2 version 9 and DB2 version 8 in socalled New Function Mode (NFM) returns unreadable values for TIMESTAMP(GENERATE_UNIQUE()) if one of the tables in the FROM part is a UNICODE table (SYSDATABASE is such a table). The result returned is a timestamp in unicode value. This problem is solved by using a CHAR(TIMESTAMP(GENERATE_UNIQUE())). Another detail is that in the result of a SELECT statement the GENERATE_UNIQUE values are generated before any DB2 internal SORT. For instance you can try the following two SQL statements in order to see the consequences of this:
In the first SQL statement the ten returned timestamps will not be in ascending order as this SQL statement will trigger an internal DB2 sort (unless for some reason an index is defined on DBID at your installation). In the last SQL statement the ten timestamps are returned in ascending order because DB2 retrieves the rows in the order specified by an index thus avoiding an internal sort.