Does DB2 CURRENT TIMESTAMP on z/OS return unique values?

Question

We have a DB2 running on z/OS and some tables use a timestamp as a Primary Key.

My opinion is, that it might be possible that two transactions calling CURRENT TIMESTAMP in the same nanosecond can have exactly the same Timestamp returned.

My colleague thinks that the CURRENT TIMESTAMP function on the same database is always unique.

The DB2 documentation here is not very clear.

Is there an offical statement from IBM, which proofs the one or the other thesis? I found only a statement for UNIX DB2, which is maybe not applicable for z/OS.

Thank you.

Answer 1

There are instances when it won't be unique. They are:

1. Datetime special registers are stored in an internal format. When two or more of these registers are implicitly or explicitly specified in a single SQL statement, they represent the same point in time.
2. If the SQL statement in which a datetime special register is used is in a user-defined function or stored procedure that is within the scope of a trigger, DB2 uses the timestamp for the triggering SQL statement to determine the special register value.

Source: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/xfbb68.htm#xfbb68

You should use GENERATE_UNIQUE() if you want a unique timestamp. Good example here: http://www.mainframesupport.dk/tips/tip0925.html