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.
There is no guarantee that CURRENT TIMESTAMP will return a unique value.
I have seen many examples of DB/2 SQL INSERT statements in a z/os environment failing on duplicate key when CURRENT TIMESTAMP was used to populate a column defined as unique.
Once upon a time CURRENT TIMESTAMP had a fine enough "granularity" that the probability of a collision was extremely small. This lead to quite a few applications treating them as unique identifiers. Processors are faster and parallelism has increased tremendously over the years. Any process that expects unique values from CURRENT TIMESTAMP today is likely to crash and burn on a very regular basis.
Your colleague is running a bit behind the times (on a couple of levels).