Hepl please - Product stop to work on DB2 v9.1!

[Dmitry Bond.]

Hello.

Our product works fine on all 7.x and 8.x DB2 versions. But stops to
work on DB2 v9.1.

The main problem is - duplicate primary key (sqlcode=-803) happens when
inserting records in QUEUE table.
The primary key of QUEUE table is - 3 fields - QID CHAR(4), PRI INT
and QTIME TIMESTAMP.
When inserting records into QTIME table we are using the "CURRENT
TIMESTAMP" value for QTIME field.
And this is the root of problem. In DB2 v9.1 "CURRENT TIMESTAMP"
returns a lot of duplicated values(!) in contrast to previous versions
of DB2.

To solve DUP_KEY error we have added autoinremented field to the
primary key of QUEUE table and can see that all things starts to work
but there are too many duplicated TIMESTAMP values in QTIME field.
Looks like in DB2 v9.1 only first 3 digits are used in microseconds
fraction of timestamp.
Why this happens in new version of DB2? We see that in older DB2
versions all 6 digits were used in microseconds fraction in TIMESTAMP.

Is it a bug? Or "feature" of new DB2?
Should we wait for fixpack or think about workarounds?...

Could you please help?

WBR,
Dmitry.

[Larry]

Have you opened a PMR with IBM support to find out whether it is a bug
or not?

Larry Edelstein

Dmitry Bond. wrote:

Hello.

Our product works fine on all 7.x and 8.x DB2 versions. But stops to
work on DB2 v9.1.

The main problem is - duplicate primary key (sqlcode=-803) happens when
inserting records in QUEUE table.
The primary key of QUEUE table is - 3 fields - QID CHAR(4), PRI INT
and QTIME TIMESTAMP.
When inserting records into QTIME table we are using the "CURRENT
TIMESTAMP" value for QTIME field.
And this is the root of problem. In DB2 v9.1 "CURRENT TIMESTAMP"
returns a lot of duplicated values(!) in contrast to previous versions
of DB2.

To solve DUP_KEY error we have added autoinremented field to the
primary key of QUEUE table and can see that all things starts to work
but there are too many duplicated TIMESTAMP values in QTIME field.
Looks like in DB2 v9.1 only first 3 digits are used in microseconds
fraction of timestamp.
Why this happens in new version of DB2? We see that in older DB2
versions all 6 digits were used in microseconds fraction in TIMESTAMP.

Is it a bug? Or "feature" of new DB2?
Should we wait for fixpack or think about workarounds?...

Could you please help?

WBR,
Dmitry.

[Serge Rielau]

Dmitry Bond. wrote:

Hello.

Our product works fine on all 7.x and 8.x DB2 versions. But stops to
work on DB2 v9.1.

The main problem is - duplicate primary key (sqlcode=-803) happens when
inserting records in QUEUE table.
The primary key of QUEUE table is - 3 fields - QID CHAR(4), PRI INT
and QTIME TIMESTAMP.
When inserting records into QTIME table we are using the "CURRENT
TIMESTAMP" value for QTIME field.
And this is the root of problem. In DB2 v9.1 "CURRENT TIMESTAMP"
returns a lot of duplicated values(!) in contrast to previous versions
of DB2.

To solve DUP_KEY error we have added autoinremented field to the
primary key of QUEUE table and can see that all things starts to work
but there are too many duplicated TIMESTAMP values in QTIME field.
Looks like in DB2 v9.1 only first 3 digits are used in microseconds
fraction of timestamp.
Why this happens in new version of DB2? We see that in older DB2
versions all 6 digits were used in microseconds fraction in TIMESTAMP.

Is it a bug? Or "feature" of new DB2?
Should we wait for fixpack or think about workarounds?...

The granularity of CURRENT TIMESTAMP is bounded by the system clock.
So if you only get 1/1000 of seconds that's where it's coming from.
There is no rule whatsoever that defined CURRENT TIMESTAMP to produce
unique values.
However pre-DB2 9 code internaly made different invocations of CURRENT
TIMESTAMP unique (which is where you saw the extra digits from.
As concurrency and CPU speeds increase this is causing serious latching
problems, hence the change.
So much for the background. Now for the solution:
TIMESTAMP(GENERATE_UNQIQUE()) will give a guaranteed unique timestamp
for every invocation even within a single SQL statements (good for mass
inserts)

So, no bug, it's working as designed.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Mark A]

Serge Rielau wrote:

The granularity of CURRENT TIMESTAMP is bounded by the system clock.
So if you only get 1/1000 of seconds that's where it's coming from.
There is no rule whatsoever that defined CURRENT TIMESTAMP to produce
unique values.
However pre-DB2 9 code internaly made different invocations of CURRENT
TIMESTAMP unique (which is where you saw the extra digits from.
As concurrency and CPU speeds increase this is causing serious latching
problems, hence the change.
So much for the background. Now for the solution:
TIMESTAMP(GENERATE_UNQIQUE()) will give a guaranteed unique timestamp
for every invocation even within a single SQL statements (good for mass
inserts)

So, no bug, it's working as designed.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

For those of you (like me) who like to copy and paste, there is a
slight typo in the above syntax. It should be:

TIMESTAMP(GENERATE_UNIQUE())

[Bernard Dhooghe]

We have:

db2 'describe values (timestamp(GENERATE_UNIQUE()))'

SQLDA Information

sqldaid : SQLDA sqldabc: 896 sqln: 20 sqld: 1

Column Information

sqltype sqllen sqlname.data
sqlname.length
-------------------- ------ ------------------------------
--------------
392 TIMESTAMP 26 1
1
So instead of:

create table t1 (c1 timestamp not null default current timestamp)

we will (now) try

create table t1 (c1 timestamp not null default values
(timestamp(GENERATE_UNIQUE()))

Fails (8.2 tested, manual of 9 does not differ in this).

So would it be possible to have a values construct in the default
clause? (understanding and accepting the non uniqueness of current
timestamp)

Bernard Dhooghe
Mark A wrote:

Serge Rielau wrote:

The granularity of CURRENT TIMESTAMP is bounded by the system clock.
So if you only get 1/1000 of seconds that's where it's coming from.
There is no rule whatsoever that defined CURRENT TIMESTAMP to produce
unique values.
However pre-DB2 9 code internaly made different invocations of CURRENT
TIMESTAMP unique (which is where you saw the extra digits from.
As concurrency and CPU speeds increase this is causing serious latching
problems, hence the change.
So much for the background. Now for the solution:
TIMESTAMP(GENERATE_UNQIQUE()) will give a guaranteed unique timestamp
for every invocation even within a single SQL statements (good for mass
inserts)

So, no bug, it's working as designed.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

For those of you (like me) who like to copy and paste, there is a
slight typo in the above syntax. It should be:

TIMESTAMP(GENERATE_UNIQUE())

[Serge Rielau]

If this were supported, which it isn't, it would be without the VALUES.
Use a BEFORE TRIGGER.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html