?
Solved

How to make DB2 unique timestamp sync with real current time in system?!

Posted on 2010-01-13
21
Medium Priority
?
3,016 Views
Last Modified: 2012-06-22
Hi.

Platform information:
* Windows 2003 R2 Sp2.
* IBM DB2 v9.5.4
* ProLiant DL360 G5/Intel Xeon E5440/4Gb RAM

To generate unique timestamps in DB2 v9.5.x I'm using following DB2 stored function:

CREATE FUNCTION GetSysTimeFraction() RETURNS timestamp
    LANGUAGE SQL
    NOT DETERMINISTIC
BEGIN ATOMIC
    RETURN TIMESTAMP(GENERATE_UNIQUE()) + CURRENT TIMEZONE;
END@

But the problem is - timestamp values returned by this function are in-past for 10-20 seconds comparing to the current time in system. Which create some problems when analyzing system logs and db records.

So, question is - how to make that timestamp values sync with the current time in system?

Are there any special parameters in DB2 like TIME_DELTA or whatever so I can add it to timestamp value returned from mentioned function?

Actually, answers like "is not possible" are not interesting to me. So, if you do not know the solution please do not disturb!

Thanks in advance.
CREATE FUNCTION GetSysTimeFraction() RETURNS timestamp
    LANGUAGE SQL
    NOT DETERMINISTIC
BEGIN ATOMIC
    RETURN TIMESTAMP(GENERATE_UNIQUE()) + CURRENT TIMEZONE;
END@

Open in new window

0
Comment
Question by:Dmitry_Bond
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 2
  • +1
21 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26303008
Hi Dmitry,

DB2 gets its timestamp from the system.  There's nothing to sync.

It's important to know that when a query starts, the system registers CURRENT_TIMESTAMP, CURRENT_DATE, and CURRENT_TIME are set to the system date/time.  This ensures that the same value for any of the current date/time values is consistent for the entire query.

What you may be seeing is a query that runs for 10 or 20 seconds (or more) that displays the current date/time but results in a late value being written to the log file.  There's nothing that you can do about that as the log file date/time is the actual date/time when it's posted, not the start date/time of the query.


Good Luck,
Kent
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 26303307
i have actually made some tests with current timestamp, maybe you will find the information useful
http://blog.badvised.com/2009/10/when-exactly-is-current-timestamp.html

but, what kent says is correct
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26303369
Hi momi,

Was that your blog?  It's an interesting read.


Kent
0
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

Author Comment

by:Dmitry_Bond
ID: 26303401
Ok.
As I see some important details are missing in my question.
So, here are more details:

For example this query (very simple and fast, executed in less than 1 second):

  SELECT GetSysTimeFraction() From DummyTable

returns the "2010-01-13-12.15.01.045115" value but the current time in system is "2010-01-13-12.15.12" - difference is 11 seconds!

For sure - it cannot be a query execution time.

So, same question AGAIN - why difference is so HUGE?!
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26303651
Hi Dmitry,

I suspect that the GENERATE_UNIQUE() function is the source of the difference.  IBM doesn't claim that the function returns the current timestamp +- a unique qualifier.  It does claim that the returned value is based on the current timestamp.

Try repeating your query returning the current timestamp instead of the result of generat_unique.


kent
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 26303692
Hi Kent,

that is my blog
glad you liked it
0
 

Author Comment

by:Dmitry_Bond
ID: 26303815
Ok...
One more important detail.

the "current timestamp" DOES NOT WORK PROPERLY on Windows platform.
Timer for "current timestamp" has very low resolution.

So, I need unique timestamp values. The only way to obtain them is the some kind of wrapping GENERATE_UNIQUE() function.

Also I want to explain the situation with this issue - OR IBM MAKE GENERATE_UNIQUE() OR current timestamp WORKING PROPERLY! OR WE HAVE TO INITIATE MOVING TO ORACLE. Which of course is bad but seems we have no options. :-\

Thus, I really do not care what claims "tricky guys" in IBM. I care - HOW TO IMPLEMENT the functionality WE REQUIRED!
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26304017
Windows keeps its internal clock to the millisecond.  Since DB2 gets its timestamp from the system you can expect the resolution on Windows to be to the millisecond.  (Other systems keep the clock to the microsecond.  On these systems the DB2 timestamp resolution is to the microsecond.)

So CURRENT TIMESTAMP works fine in Windows.  It returns exactly what Windows reports.  (You'll probably find that Oracle has the same limitation as it will get its time from Windows, too.)  

Here's an IBM page that seems to have an example of exactly what you're trying to do.

  http://www.ibm.com/developerworks/data/library/techarticle/0205pilaka/0205pilaka2.html

It uses the GENERATE_UNIQUE() function a bit differently than you do.  Perhaps this will help?


Kent
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26306558
Hi Dmitry,

Will it serve your needs to put an IDENTITY column on the table an just use CURRENT_TIMESTAMP as in the row data?  The IDENTITY value will ensure a unique key and any collisions in the timpstamp order can be resolved via the IDENTITY value.


Kent
0
 

Author Comment

by:Dmitry_Bond
ID: 26307055
Answer to Kdo on "It returns exactly what Windows reports". That is exactly the problem. IBM guys think that that is enough. But for sure they are mistaken!
Because the idea leading to the success is following - providing possiblities is the good thing! Creating restrictions and limitations is bad thing.
Unfortunately IBM guys created restriction with "current timestamp".
Yes, it is very good that they still kept the possibility to use TIMESTAMP(generate_unique()) but it seems is unfinished because of mentioned problem with the time delta.

Idea with IDENTITY column looks interesting. It maybe a workaround... I may try to use it in GetSysTimeFraction function as one more possible way to generate correct, unique timestamps.

One more detail on the question.
I cannot redevelop all system we have and change all implemented approaches and methodics. That is why I need exactly the TIMESTAMP(generate_unique()) working correctly!
But not the abstract, banal and unuseful declarations like "It returns exactly what Windows reports".
0
 

Author Comment

by:Dmitry_Bond
ID: 26313058
Additional question:

Is it possible from stored function (like the "GetSysTimeFraction()" function specified above) call some ActiveX or Automation object? Or make a WinAPI call?

I think - if DB2 does not provide required service then maybe I can implement it by myself and then wrap as a stored function. Is it possible?
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 26313597
Hi Dmitry,

DB2 allows you to create functions and stored procedures in several languages other than SQL.  Java and/or C are probably the most likely for your purpose.  Here's a link to the CREATE FUNCTION declaration that shows how to write a non-SQL based function.  Example 3 is a C function.

  http://publib.boulder.ibm.com/infocenter/db2luw/v9//topic/com.ibm.db2.udb.admin.doc/doc/r0004461.htm


Here's a link to a pretty good article on high-performance timing.  It speaks of the weaknesses in the Windows timers and a good way to get timing down close to the microsecond level.  I haven't tried it, but the solution makes a lot of sense and may well do what you need.

  http://www.devsource.com/c/a/Techniques/High-Performance-Timing-under-Windows/1/


Good Luck,
Kent
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 26325300
try this:
db2 values current timestamp, timestamp(generate_unique())+current timezone
Is this what you want?
0
 

Author Comment

by:Dmitry_Bond
ID: 26325409
Mr. ghp7000, as you can see in the original question the "timestamp(generate_unique())+current timezone" is the approach that is currently used! So your advice is not suitable!

Also - timestamp(generate_unique())+current timezone returns value which is different than the current time in system. As you can see in a question I mentioned that difference is about 10-20 seconds. So question was - how to remove that difference?
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 26325609
the answer is already posted, you cannot, because db2 timestamp is accurate to 6 position microsecond, windows can generate 3 positions. The generate unique timezone function is only if you absolutely need all 6 positions, it does not guarantee accuracy and IBM never guarnateed the accuracy even in previous versions (like v8).
If you could explain what you want to accomplish, I mean, the business rulem then perhaps other options could be explored..
0
 

Author Comment

by:Dmitry_Bond
ID: 26325761
In version 8 it did emulated full fraction 6 timestamp!
And I believe it must be compatible! So, you'd better to make IBM guys keeping their software compatible rather than telling me what it does not support. :-\

Ok...
I need some DB function replacing DB2 "current timestamp" but generating unique values and using full 6 digits fraction. I have a some implementation in C. So, plug the C code to DB2 is also ok but only if I can build and distribute binary (not the source code)! Because customers will kill us if we tell that need to install C/C++ compiler on their server to compiler DB2 functions. :-\

Actually, at Kdo already provided some useful url, so I only need to get a time to check it. Hope to look on it next week. Now I'm quite busy with some urgent stuff...
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26326081
Hi Dmitry,

About how often (and how many) records do you insert into the table?

Kent
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26326191

I'm thinking that the easiest solution might be to create a trigger that fills in the sub-millisecond values of the timestamp with the three-digit value from a running sequence.  If two values are stored with the same timestamp value (to the millisecond) the sequence value would be the tie-breaker that psuedo-resolves to the microsecond.  You'll have millisecond accuracy of the insert time and a guaranteed unique timestamp.

The only question/issue is resetting (or rolling over) the sequence.  You'll want to reset it periodically so that it doesn't roll over (extend past 999).  If two inserts were to occur during the same millisecond while the sequence was at 999, the first would appear to be stored with a microsecond counter value of 999 and the second with 000, making them appear out of order.


Kent
0
 

Author Comment

by:Dmitry_Bond
ID: 26326271
I already have tried trigger with sequence filling last 3 digits - it does not work for me. Modern computers are too fast to keep unique values with only 3 digits. So, reseting sequence is not a problem - could be any. But keeping values unique is serious problem.

But maybe I can think of replacing all 6 digits of time fraction with sequence value from 0 to 999999. Also - save last time value and if seconds changed - reset sequence to 0. That maybe a working idea... Anyway system and application logs keep timestamp to seconds (not to fraction of second). So, it may work.

Thanks for hint. :)
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 2000 total points
ID: 26326412
Inserting 3 or 6 digits under the seconds field should work the same, but your idea of replacing all 6 digits is a great one.  The counter will roll over every million rows so unless you're doing an awful lot inserts, you can address the rollover at your liesure.

The code below should work fine, though I'd pick a shorter name for the sequence.  (ms_seq)


Kent

CREATE SEQUENCE insert_microsends
      START WITH 1
      INCREMENT BY 1
      MAXVALUE 999999
      CACHE 24;

select 
  substr (current_timestamp, 1, 20) || right ('000000' || digits (NEXT VALUE for insert_millisecond), 6) 
from sysibm.sysdummy1

Open in new window

0
 

Author Closing Comment

by:Dmitry_Bond
ID: 31676620
Seems we cannot enforce IBM to fix DB2. But workaround we figured out looks not bad. I mean - sequence in range 0..999999.
0

Featured Post

A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question