Problems with GENERATE_UNIQUE... :-\
Dmitry Bond.
Faced strange thing in DB2 9.5.
I just noticed that TIMESTAMP(GENERATE_UNIQUE()) returns timestamps in
April 24 while current time on computer is April 27 !
It is virtual machine under VmWare with Windows Server 2003 SP2 and
IBM DB2 9.5.
Do you have any ideas - why TIMESTAMP(GENERATE_UNIQUE()) returning
timestamps in a day 3 days ago?!
Well, I suspect it could be because of VM was suspended for weekend
and then resumed today's morning. But current time on VM screen is
correct (windows show that now there is April 27, 15:12), so I do not
understand - why DB2 still think it is in April 24, 21:22 ?!
Regards,
Dmitry.
Frederik
Dmitry,
I'm not sure you can use TIMESTAMP(GENERATE_UNIQUE()) to get the
current timestamp. What is the output of
VALUES(CURRENT TIMESTAMP)
?
--
Frederik
Dmitry Bond.
> On Apr 27, 3:12 pm, "Dmitry Bond." <dima_...@ukr.net> wrote:
> Dmitry,
>
> I'm not sure you can use TIMESTAMP(GENERATE_UNIQUE()) to get the
> current timestamp. What is the output of
>
> VALUES(CURRENT TIMESTAMP)
> ?
We cannot use "CURRENT TIMESTAMP"
because in DB2 9.5 on Windows it has truncated time fraction(!) - last
3 digits in fraction always zeros so using "CURRENT TIMESTAMP" is not
an option at all!
Exactly because of this __ABSOLUTELY STUPID LIMITATION FROM IBM__ we
have to use custom function returning _correct_ timestamp value.
One of variants I tried was the
TIMESTAMP(GENERATE_UNIQUE()) + CURRENT TIMEZONE
It was perfectly works in all cases except this one with virtual
machine - after it was suspended and then resumed few days later.
And seems there are no direct statements in DB2 documentation
disabling to use TIMESTAMP(GENERATE_UNIQUE()) in such form (with +
timezone).
Of course I have my own implementation of function to replace TIMESTAMP
(GENERATE_UNIQUE()) but I prefer to use "TIMESTAMP(GENERATE_UNIQUE())
+ CURRENT TIMEZONE" to be sure all keys in database have unique
values. Because on fast computers there are chances to got DUP_KEY
error.
Btw, after I reboot VM it starts to return correct values = current
time.
Perhaps it will also start to work after db2stop + db2start but I'm
wondering why DB2 not validating this case.
VM was started at morning (about 10:00) and at 15:10 it still not
recognized that current time not match values returned by
GENERATE_UNIQUE()...
Lennart
> have to use custom function returning _correct_ timestamp value.
Are you sure that this is an IBM limitation? I don't know since I
don't use that os, but my first guess would be that there is a
limitation in some api for that specific os. Can anyone from IBM
clarify this?
/Lennart
[...]
Mark A
> news:7881f4d7-709d-49d7...@y10g2000prc.googlegroups.com...
> We cannot use "CURRENT TIMESTAMP"
> because in DB2 9.5 on Windows it has truncated time fraction(!) - last
> 3 digits in fraction always zeros so using "CURRENT TIMESTAMP" is not
> an option at all!
I don't think DB2 is truncating it. I think that is the percision of Windows
timestamp.
Serge Rielau
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 serves up last and if the
OS time hasn't progressed then it adds 1 microsecond to the last
generated value.
No, back to the original problem. It really appears as if the VM has two
separate time-api's one that is used by DB2 and another that is going
after the Windows clock.
I'm no expert in VM but perhaps that can be controls. Or it could be a
VM ware bug.
Have you considered opening a PMR with IBM?
Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
Serge Rielau
> Since running my guest VMs on a Linux Host and with VMware Workstation
> 6.X I have not seen this problem anymore !
To make a long story short we have been struggling with this anomaly,
but it was/is not a DB2 thing and it may have been by VMWare.
Dmitry Bond.
[...]
Ok. Thanx for answer.
Nice to know that is it "not DB2 thing"... unbelivable but seems no
options as only to belive you.
FYI. From our experience.
On some production sites physical computers were converted to virtual
machines. Somewhere it is VmWare, somwhere other VM engine (hyper-V,
etc).
So, this problem seems going to be actual more and more. In our case
we still not faced this on production because many sites still running
DB2 7.x and 8.x.
And I'm a bit nervous when thinking about possible future migration to
DB2 9.x :-(((
Mainly because of 2 reasons:
First, 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!
Second, after we implemented workaround - use TIMESTAMP(GENERATE_UNIQUE
()) - it was discovered that we can face next piece of problems with
it because a wave of virtualization (when our customers optimizing
infrastructure by converting some phisical computers to virtual
machines).
Thus, guys, please(!) - or make "current timestamp" working again
(returning 6 digit fraction, which is preferable).
Or fix DB2 to make TIMESTAMP(GENERATE_UNIQUE()) working on VMs.
Note: I really do not care any "techinical fary tales" you can tell me
about "higher timer resolution does not supported by Windows platform"
and so on.
Because one of the most important thing in IT world - THE
COMPATIBILTY!
So, if you "decided" to make DB2 9.x incompatible with previous
versions... it is VERY VERY BAD.
Despite on "super-puper-logical" reasons and "techinical fary tales"
explaining why it should be changed in new version of DB2.
Thanks.
Dmitry Bond.
> Comment from our VMware god:
> > Since running my guest VMs on a Linux Host and with VMware Workstation
> > 6.X I have not seen this problem anymore !
>
> To make a long story short we have been struggling with this anomaly,
> but it was/is not a DB2 thing and it may have been by VMWare.
>
> Cheers
> Serge
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab
Seems google-groups did "eat" my first answer on this... ok. I'll
write again...
First of all - thanks to all for answers.
Second...
1) One of the current tendency in the IT world is - the
virtualization. Optimization of IT infrastructure by converting some
physical computers to virtual machines.
So, the mentioned problem became more and more actual. Thus, please
pay attention to this problem with DB2+VM.
I think you can make option like "VM_RUNNING" or something like this
to check current time on computer using typical WinAPI (ex.:
GetSystemTime/GetLocalTime function).
Note: some of our customers already converted production servers to
VM. The only reason why we still not faced this problem there - they
running DB2 7.x and 8.x!
Older DB2 version do not have such problem with "current timestamp".
But thinking about future possible migration to DB2 9.x I'm bacaming
nervous!... :-\
2) And here I want to remember you one of the most important thing in
IT world - THE COMPATIBILITY.
Unfortunately DB2 9.x is _incompatible_ with DB2 8.x (and lower), in
particular - "current timestamp" not simulating full 6 digits fraction
any more. :-(((
Which is VERY BAD!
Also bad that - after we implemented a workaround with a wrapper-
function which simulate full 6 digits fraction we faced another
problem - DB2 having problems working on VM. :-\
Thus, my conclusions are:
a) of course it is very good to have a lot of new features provided by
DB2 9.x but the most important - TO KEEP ALL OLDER STUFF WORKING FINE
on newer version of DB2.
b) sorry, I do not belive in "technical fairy tales" about "low timer
resolution on Windows" and so on.
If you _were_able_ to simulate full 6 digits time fraction on DB2 8.x
and lower - WHY YOU DECIDED TO CANCEL IT?!
I see following options:
* implement option returning back 6 digits time fraction simulation;
* implement possibility to replace standard "current timestamp"
function with custom implementation - just any user can think of to
have full 6 digits time fraction.
Now about this problem with VM.
I think we can try to implement some VM scripts and put them on VM
resuming. So, it will stop some applications, restart DB2 and start
applications back - seems it should help.
Which of course is not nice... but works.
Mark A
> First, 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!
Nope, worked the same way in V8. It's a Windows problem.
Serge Rielau
With all due respect. It is VMware's responsibility to properly emulate
the environment. Not DB2's.
I may have been inclined to use my connections to get our VMware expert
to talk to his buddies over at VMWare. But calling me a liar ("fairy
tales") is not the way to my heart.
Have you confirmed that DB2 behaves the same on other VM platforms? That
would be useful information.
Have yu considered asking VMWare?
Dmitry Bond.
> > "Dmitry Bond." <dima_...@ukr.net> wrote in message
It is not true!
Becuase on the SAME COMPUTER DB2 V8 and V9 returns DIFFERENT values
for "current timestamp"!
So, it is not Windows problem. It is problem of DB2 V9.x!
Do you agree that - if version 2 of component X works differently than
version 1.
And this is on the same hardware and on the same OS and computer it
means that there is a bug in component X?
Thus, V9.x works differently on the same OS/computer it means -
PROBLEM IN DB2 V9.x!
Dmitry Bond.
> Dmitry,
>
> With all due respect. It is VMware's responsibility to properly emulate
> the environment. Not DB2's.
> I may have been inclined to use my connections to get our VMware expert
> to talk to his buddies over at VMWare. But calling me a liar ("fairy
> tales") is not the way to my heart.
>
> Have you confirmed that DB2 behaves the same on other VM platforms? That
> would be useful information.
> Have yu considered asking VMWare?
>
> Cheers
> Serge
>
Hi Serge.
Sorry, if you think I called you "liar". I did not mean that.
But I explain what I meant - it is a well known characteristic feature
of humans - fall to hasty conclusions.
So, exactly this human feature I meant when told that "I have to
belive you because of lack of options" - I do not see facts confirming
your position and have to told that.
I think this position - the "it is Windows problem, not Db2 problem"
is not technically confirmed because -
if version 2 of component X works differently on the same OS/computer
it means - bug in component X version 2!
Do you agree?
And if after that you (or IBM) still telling that "it is Windows
problem, not Db2 problem" it means one of:
a) internal political games in IBM;
b) somebody do not want to confirm that made mistake in code/release
so trying to hide that.
I'm appologize for such words, but(!) it is the only conclusion I can
made analyzing existing facts.
If you can see other interpretation of facts please put it here so we
can look and see what is true.
Of course I can be mistaken and could make hasty conclustions but it
is feature of any human so - you too.
That is why I suggest to put your facts here.
I did put my facts. And the main one - "version 9.x of Db2 works
differently than V8.x and lower on the same OS/computer". There is
clear test case confirming that.
So I'm wating for your facts. Please. :)
Tonkuma
> > > platform _did_return_6_digits_fraction_!
> > > So, problems with "current timestamp" _begins_exactly_ with DB2 9.x!
>
> > Nope, worked the same way in V8. It's a Windows problem.
>
> It is not true!
> Becuase on the SAME COMPUTER DB2 V8 and V9 returns DIFFERENT values
> for "current timestamp"!
> So, it is not Windows problem. It is problem of DB2 V9.x!
timestamp" on DB2 V8 and V9 on same Windows OS on same VMware on your
machine?
Possible source of differences are:
- Hardware(this would be same according to your posts).
- VMware on the machine.
- Windows OS on the VMware.
- DB2 on the Windows OS.
Dmitry Bond.
[...]
> Would you please show me the exact results of "VALUES current
> timestamp" on DB2 V8 and V9 on same Windows OS on same VMware on your
> machine?
> Possible source of differences are:
> - Hardware(this would be same according to your posts).
> - VMware on the machine.
> - Windows OS on the VMware.
> - DB2 on the Windows OS.
First of all I want to clarify one thing - here we have 2 threads.
First - problems with DB2 time on VM.
(Wondering - why it could be "too difficult" for IBM developer to call
the GetLocalTime (or GetSystemTime) WinAPI function to obtain current
time?...)
This is the root problem of discussions here.
Second - problem with "current timestamp" - working differetly on DB2
V8.x and V9.x.
This is quite old problem - it begins with DB2 V9.1 - so it is not new
issue. Unfortunately it is still an issue.
But I mentioned it here only because it was annoying reason of extra
changes in our product which finally lead us to this problem with
wrong time on VM.
So, "current timestamp" on VM works fine (seems IBM guys were able to
call right WinAPI function here) but the TIMESTAMP(GENERATE_UNIQUE())
does not work on VM (seem other part of IBM developers do not know how
to use WinAPI).
So, if you ask me about test case how to reproduce the second one -
the problem with too short time fraction. Here is test case:
1) create table: CREATE TABLE tab1 ( tsk TIMESTAMP NOT NULL DEFAULT
current timestamp, txt CHAR(32) NOT NULL DEFAULT '', tsv TIMESTAMP NOT
NULL , PRIMARY KEY (tsk) )
2) create simple C/C++ program with static SQL. Here I put only piece
of code just to show the idea:
int i, counter=100;
for (i=0; i<counter; i++)
{
EXEC SQL INSERT INTO =tab1 (txt, tsv) VALUES (:dbSzTxt,
current);
if (sqlcode != 0) { LogError(ERR_SERVER_INTERNAL, IntToStr(i),
IntToStr(sqlcode), LOG_END); }
}
On Db2 V8 and lower it works fine - inserts any number of records, no
errors appears.
On Db2 V9.x (tested on 9.1, 9.5.2) - it inserts only 30-40% of records
(sometimes less, sometimes more, seems depending on system workload) -
the rest inserts fail with -803/SQL0803N error (DUP_KEY). In my tests
there are ~125 errors per 200 inserts (it is on VM!). If it will be
normal physical server (not VM) we can expect 180-190 errors per 200
inserts. :-\
If we look into Tab1 table on Db2 V8 we can see:
TSK
------------
2009-04-30:12:00:23.216000
2009-04-30:12:00:23.216001
2009-04-30:12:00:23.216002
2009-04-30:12:00:23.216003
2009-04-30:12:00:23.216004
2009-04-30:12:00:23.216005
[...]
As you can see on DB2 V8 and lower versions it DO EMULATE full 6
digits time fraction!
If we look into Tab1 table on Db2 V9 we can see:
TSK
----------
2009-04-30:12:03:49.327000
2009-04-30:12:03:49.593000
2009-04-30:12:03:49.343000
2009-04-30:12:03:50.030000
2009-04-30:12:03:49.358000
2009-04-30:12:03:49.608000
2009-04-30:12:03:49.796000
As you can see - last 3 digits always zero!
And between these timestamp values there are lot of DUP_KEY errors.
Yes, TIMESTAMP(GENERATE_UNIQUE()) is a good workaround - solving the
problem but unfortunately if has problems on VM.
I really do not care about "VALUES current timestamp" because it is
not essential factor here.
Try the code with static SQL and you will see the problem.
So, ONCE AGAIN - IBM cancelled emulation of full 6 digis time fraction
in DB2 9.x! And make V9.x _INCOMPATIBLE_ with V8.x - created extra
problems for software vendors using DB2.
And seems there are no any hints about that in release notes for DB2
9.x!!! Which of course is also QUITE BAD!
I belive it must be in Release Notes.
Summary:
1) "current timestamp" does not support full 6 digits time fraction
anymore (I call this - SERIOUS INCOMPATIBILITY).
2) TIMESTAMP(GENERATE_UNIQUE()) having problems on VMs.
Do you see the problems?
Serge Rielau
It has been fixed in VMware ESX server.
It has NOT been fixed in the free version (vmware's choice).
I do know that that there was a change in api's used for CURRENT
TIMESTAMP, but that change was within what's documented behavior. Trying
to track exactly what was changed.
Serge Rielau
I'm too dumb for political games....
Official truth from Microsoft:
http://msdn.microsoft.com/en-us/library/ms725496.aspx
The change you're seeing in DB2 version to version is explained here:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0005886.htm
Trying to find an official admission by VMWare that it's their problem
that they serve teh wrong time
Serge Rielau
> The change you're seeing in DB2 version to version is explained here:
> http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0005886.htm
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.rn.doc/doc/c0024687.htm
Dmitry Bond.
> Serge Rielau wrote:
> > The change you're seeing in DB2 version to version is explained here:
>
> Posted the wrong link, here is what i meant to post:http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2....
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab
Ok.
Thank you.
Especially for url to part about "CURRENT TIMESTAMP"... sad news.
But good that at least it was specified in documentation.
VmWare... We using GSX Server 3.2.0. Seems it was commercial version
(not freeware).
Thanks to all.
Assume issue closed because:
a) "CURRENT TIMESTAMP" is changed and seems not going to be changed
back, so continue using GENERATE_UNIQUE();
b) looks like our customers using newer VM software than we are, so
problem isolated only on our environment;
c) to solve local problem with GENERATE_UNIQUE() seems we need to put
some scripts to hande "On VM Resume" event.
Serge Rielau
http://www.vmware.com/pdf/vmware_timekeeping.pdf
Some interpretation from "backstage":
ESX is a bare metal hypervisor, meaning it has complete access to the
system clock and doesn't need to depend on another OS as do the Server
and Workstation products (which we don't support for production). In
addition, our best practice is to install VMware Tools on the guest OS
which further helps keep the clocks in sync as the guests are in regular
communication with the hypervisor. The whitepaper I believe gives tips
if you are on a hosted product like Server on how to tune the host OS to
minimize skew but ESX will always be better in this regard.
Having said that, timekeeping isn't perfect in these environments (this
includes other hypervisors like xen ,etc) and subject to some skew at
some points in time.
---------