Comments (7)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 Kovica commented Permalink

Nice post. Didn't know about the GENERATE_UNIQUE(), but we did experience the occurrence of the same timestamp in a statement.
For the record:
A while ago when we started with our application we wanted to use UUIDs as primary keys. That would be great also for our replication application, but we found out that it's too slow, to much stuff to write in SQL statements, so we ended up using BIGINTs for primary keys. :)

2 arzvi commented Permalink

I tried int(generate_unique) - no joy. Rand(10) doesn't give me a unique value to be used across my SPs. Is there a better way to generate random numbers?

3 SergeRielau commented Permalink

Arzvi,

 
GENERATE_UNIQUE() (note the brackets) generates a unique binary string.
With 13 digits that's too long to make even a BIGINT (8 bytes) through a forced cast.
 
The best way to generate unique numbers across stored procedures would be a SEQUENCE.
DB2 supports SEQUENCE as well as IDENTITY.
 
Here is a quick example:
CREATE SEQUENCE seq;
 
VALUES NEXT VALUE FOR seq;
1
-----------
1
 
VALUES NEXT VALUE FOR seq;
1
-----------
2
 
VALUES PREVIOUS VALUE FOR seq;
1
-----------
2
 
Since I see lot of searches for IDENTITY and UUID perhaps I should blog about sequences next.

4 ErwinHaTsoF commented Permalink

Thanks Serge for the great tip and explanation !
Just to check / double check , for our DB2 9.7.5 AESE on Windows2008-R2 :

 
select timestamp(generate_unique()) from sysibm.sysdummy1
2012-03-01-07.05.20.102862
 
6 digit fractions , looks OK unique to me :-)
 
 
There is/was some huge discussion on HOW UNIQUE is GENERATE_UNIQUE() ?
Especially using it on Windows (and 'some' DB2 versions)
 
I would say, this link and discussion is OLD information :
http://www.database-answers.com/microsoft/DB2/35694399/problems-with-generateunique-.aspx
“….. The granularity of Windows time is 7ms. ...... Do some pings or trace routs. You will find that it never serves up any value between 1 and 6ms ....... GENERATE_UNIQUE() keeps track of what it has served up last and if the OS time has not progressed then it adds 1 microsecond to the last generated value. …..”
“….. in DB2 9.x IBM guys killed full time fraction simulation. Because in lower versions of DB2 the "current timestamp" on Windows platform _did_return_6_digits_fraction_! ..... So, problems with "current timestamp" _begins_exactly_ with DB2 9.x! …..”
 
regards,
Erwin

5 SergeRielau commented Permalink

Erwin,

 
Seems like Tokuma from IBM Japan and I were both engaged in that discussion.
Luckily it appears we didn't contradict each other *phew*.
Aside from the VM ware issue, the core of that thread was the removal of enforced uniqueness between SQL statements.
 
Let me elaborate on the reasoning of this change.
To make CURRENT TIMESTAMP unique across different SQL statements a "last issued" value must be kept and synchronized. Within a single session that is no big deal. But when you now add a few thousand concurrent sessions each doing CURRENT TIMESTAMP this synchronization point is becoming very hot.
It will become so hot that it starts to throttle concurrency.
To add insult to injury CURRENT TIMESTAMP is used internally as well e.g. in monitoring.
So there are a lot more invocations to this API than those obvious to the application developer.
 
Cheers
Serge

6 dW1 commented Permalink

Probably a typo:
On Windows two clock ticks are MICROseconds apart.
Right?

7 SergeRielau commented Permalink

DW1,

 
No typo. I'll gladly be proven false. But when you take a look my little test:
 
SELECT * FROM event ORDER BY stamp;
STAMP VAL
-------------------------- -----------
2012-02-28 16:04:54.296000 1
2012-02-28 16:04:54.312000 640
 
I managed to get 640 iterations of the same timestamp before it jumped and then it did so by 14millisec.
 
Cheers
Serge