Timestamp issue under heavy load

[Chris Burnley]

We're seeing very strange behaviour with db2 under heavy load.

We're running our system in GMT and the timezone never changes for
British Summer Time.

Basically the thing we're doing is (using the udb jdbc driver):

"values current timestamp"

get that value and supply it "?" in the next insert statement :

insert into x,y,ts values (x,y,?);

Most of the time ( 99.999...%) this works fine, but what we have found
(due to debug statements etc.) that under heavy system load, the
timestamp inserted via the second statement, is actually 1 hour behind
what it should be.

Any thoughts ?

[Mark A]

"Chris Burnley" <ch***********@gmail.com> wrote in message
news:11*********************@y41g2000cwy.googlegro ups.com...

We're seeing very strange behaviour with db2 under heavy load.

We're running our system in GMT and the timezone never changes for
British Summer Time.

Basically the thing we're doing is (using the udb jdbc driver):

"values current timestamp"

get that value and supply it "?" in the next insert statement :

insert into x,y,ts values (x,y,?);

Most of the time ( 99.999...%) this works fine, but what we have found
(due to debug statements etc.) that under heavy system load, the
timestamp inserted via the second statement, is actually 1 hour behind
what it should be.

Any thoughts ?

Is there some reason why you don't use this:

insert into x,y,ts values (x, y, current_timestamp);

[Chris Burnley]

Yes there is. Basically we need to use the timestamp in another
statement later on. It is part of a key to the table we are insert into
to.
Mark A wrote:

"Chris Burnley" <ch***********@gmail.com> wrote in message
news:11*********************@y41g2000cwy.googlegro ups.com...

We're seeing very strange behaviour with db2 under heavy load.

We're running our system in GMT and the timezone never changes for
British Summer Time.

Basically the thing we're doing is (using the udb jdbc driver):

"values current timestamp"

get that value and supply it "?" in the next insert statement :

insert into x,y,ts values (x,y,?);

Most of the time ( 99.999...%) this works fine, but what we have found
(due to debug statements etc.) that under heavy system load, the
timestamp inserted via the second statement, is actually 1 hour behind
what it should be.

Any thoughts ?

Is there some reason why you don't use this:

insert into x,y,ts values (x, y, current_timestamp);

[Mark A]

"Chris Burnley" <ch***********@gmail.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...

Yes there is. Basically we need to use the timestamp in another
statement later on. It is part of a key to the table we are insert into
to.

I would try this to see if it helps:

select current_timestamp from sysibm.sysdummy1;

[Paul Peters]

what is the problem with :

select ts from new table (insert into table (x,y,ts) values (x,y,current
timestamp) ?

"Chris Burnley" <ch***********@gmail.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...

Yes there is. Basically we need to use the timestamp in another
statement later on. It is part of a key to the table we are insert into
to.
Mark A wrote:

"Chris Burnley" <ch***********@gmail.com> wrote in message
news:11*********************@y41g2000cwy.googlegro ups.com...

> We're seeing very strange behaviour with db2 under heavy load.
>
> We're running our system in GMT and the timezone never changes for
> British Summer Time.
>
> Basically the thing we're doing is (using the udb jdbc driver):
>
> "values current timestamp"
>
> get that value and supply it "?" in the next insert statement :
>
> insert into x,y,ts values (x,y,?);
>
> Most of the time ( 99.999...%) this works fine, but what we have found
> (due to debug statements etc.) that under heavy system load, the
> timestamp inserted via the second statement, is actually 1 hour behind
> what it should be.
>
> Any thoughts ?
>

Is there some reason why you don't use this:

insert into x,y,ts values (x, y, current_timestamp);

[Chris Burnley]

You're both missing the point. It works 99.99999% of the time,
occasionaly it doesn't work. Usually under heavy load. This to me,
smells like a race condition of some sort in the db2 driver.
Paul Peters wrote:

what is the problem with :

select ts from new table (insert into table (x,y,ts) values (x,y,current
timestamp) ?

"Chris Burnley" <ch***********@gmail.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...

Yes there is. Basically we need to use the timestamp in another
statement later on. It is part of a key to the table we are insert into
to.
Mark A wrote:

"Chris Burnley" <ch***********@gmail.com> wrote in message
news:11*********************@y41g2000cwy.googlegro ups.com...
> We're seeing very strange behaviour with db2 under heavy load.
>
> We're running our system in GMT and the timezone never changes for
> British Summer Time.
>
> Basically the thing we're doing is (using the udb jdbc driver):
>
> "values current timestamp"
>
> get that value and supply it "?" in the next insert statement :
>
> insert into x,y,ts values (x,y,?);
>
> Most of the time ( 99.999...%) this works fine, but what we have found
> (due to debug statements etc.) that under heavy system load, the
> timestamp inserted via the second statement, is actually 1 hour behind
> what it should be.
>
> Any thoughts ?
>

Is there some reason why you don't use this:

insert into x,y,ts values (x, y, current_timestamp);

[Serge Rielau]

Chris Burnley wrote:

You're both missing the point. It works 99.99999% of the time,
occasionaly it doesn't work. Usually under heavy load. This to me,
smells like a race condition of some sort in the db2 driver.

If you think it's a DB2 bug please open a PMR.
Try this alternative:
TIMESTAMP(GENERATE_UNIQUE())

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/