Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3153
  • Last Modified:

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

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
Dmitry_Bond
Asked:
Dmitry_Bond
  • 9
  • 8
  • 2
  • +1
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
momi_sabagCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi momi,

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


Kent
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dmitry_BondAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
momi_sabagCommented:
Hi Kent,

that is my blog
glad you liked it
0
 
Dmitry_BondAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Dmitry_BondAuthor Commented:
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
 
Dmitry_BondAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
ghp7000Commented:
try this:
db2 values current timestamp, timestamp(generate_unique())+current timezone
Is this what you want?
0
 
Dmitry_BondAuthor Commented:
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
 
ghp7000Commented:
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
 
Dmitry_BondAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Dmitry,

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

Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
Dmitry_BondAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Dmitry_BondAuthor Commented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 9
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now