Unique Timestamp

Steve Reznikov

Unique Timestamp

Hi all,

I know that this was discussed before, but I couldn't find the answer, so I decided to post this question again to see if someone has a solution.

Our application is inserting a lot of data and they have been using Timestamp as a unique row identifier for a long time. Some time ago they received several duplicates and converted their code to " SELECT ((TIMESTAMP(GENERATE_UNIQUE()))". This has worked for several years, but last week they noticed duplicates again. I understand that the current approach doesn't guarantee uniqueness. Can someone suggest a better way of generating a Unique Timestamp? The application team doesn't want to change their code and they insist on using Timestamp for uniqueness.

Steve.

Daniel Luksetich

Unique Timestamp
(in response to Steve Reznikov)
Nope, they are not unique, period. The application will have to change or retry.

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Steve Reznikov [mailto:[login to unmask email]
Sent: Monday, July 17, 2017 9:50 AM
To: [login to unmask email]
Subject: [DB2-L] - Unique Timestamp



Hi all,

I know that this was discussed before, but I couldn't find the answer, so I decided to post this question again to see if someone has a solution.

Our application is inserting a lot of data and they have been using Timestamp as a unique row identifier for a long time. Some time ago they received several duplicates and converted their code to " SELECT ((TIMESTAMP(GENERATE_UNIQUE()))". This has worked for several years, but last week they noticed duplicates again. I understand that the current approach doesn't guarantee uniqueness. Can someone suggest a better way of generating a Unique Timestamp? The application team doesn't want to change their code and they insist on using Timestamp for uniqueness.

Steve.



-----End Original Message-----

Robert Krall

RE: Unique Timestamp
(in response to Steve Reznikov)

Why not let DB2 generate the timestamp for you?  In the table DDL you just define it as a timestamp with default.  

Here is a snip-it of code it from a table I created:

BEGIN_DATE TIMESTAMP NOT NULL with default

 

 

Russell Peters

RE: Unique Timestamp
(in response to Steve Reznikov)

As of DB2 10 a longer timestamp is available. Maybe that would help.

Isaac Yassin

Unique Timestamp
(in response to Russell Peters)
TIMESTAMP is not guarantied to be unique. Never



*Isaac Yassin IBM Gold Consultant*
*IBM Champion for Analytics*
IBM Certified Solution Expert
IBM Certified Database Administrator - DB2 for z/OS 9,10,11
IBM Certified System Administrator - DB2 10, 11 for z/OS
IBM Certified Database Administrator - DB2 LUW 10.1
IBM Certified Specialist - PureData System for Analytics v7.1
IDUG Israel RUG co-Chair
IDUG GMC

Tel: +972 54 4452793


On Tue, Jul 18, 2017 at 4:45 PM, Russell Peters <[login to unmask email]>
wrote:

> As of DB2 10 a longer timestamp is available. Maybe that would help.
>
> -----End Original Message-----
>

Robert Krall

RE: Unique Timestamp
(in response to Steve Reznikov)

One way to ensure you have unique row is to use a identity column.

Here is an example of that.

ROW_ID INTEGER NOT NULL GENERATED ALWAYS

      AS IDENTITY (START WITH 100000, INCREMENT BY 1, CACHE 20,

       NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER)

 

But If I were you would I create a Primary Key unique index on a few different columns that make a row unique that would unsure the you never get duplicate rows.

Steve Reznikov

RE: Unique Timestamp
(in response to Robert Krall)

Thanks, but the application folks want to keep Timestamp as data type.

It sounds like the only way around this is to retry as Dan suggested.

Steve.

Daniel Luksetich

Unique Timestamp
(in response to Russell Peters)
Longer timestamp is not the solution. It is actually NOT totally time that is stored in the extra precision.



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Russell Peters [mailto:[login to unmask email]
Sent: Tuesday, July 18, 2017 8:46 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unique Timestamp



As of DB2 10 a longer timestamp is available. Maybe that would help.



-----End Original Message-----

Daniel Luksetich

Unique Timestamp
(in response to Steve Reznikov)
In your case that is the only solution. As machines get faster and insert processing more efficient you may find yourself retrying more than inserting. Wasting cycles of course……and it will be Db2’s fault, not the application. Make sure they understand that!



I’ve seen as much as 6 timestamps per microsecond. Db2 11 for z/OS.



Cheers,

Dan



Jeez, I hate the little b



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Steve Reznikov [mailto:[login to unmask email]
Sent: Tuesday, July 18, 2017 9:57 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unique Timestamp



Thanks, but the application folks want to keep Timestamp as data type.

It sounds like the only way around this is to retry as Dan suggested.

Steve.



-----End Original Message-----

Steve Reznikov

RE: Unique Timestamp
(in response to Daniel Luksetich)

Thanks Dan.

I agree with you that that is the only solution at this time.

Steve.

Russell Peters

RE: Unique Timestamp
(in response to Steve Reznikov)

ok, I agree a timestamp is never guaranteed to be unique, but the longer timestamp was added to assist in resolving issues where duplicate timestamps are encountered. It would require a programming change but might work and would be a minor change. But the identity column would be the best way to assure unique records if you can get programming to make a change.