How to avoid timestamp problems while going from winter to summer time in a Db2 for z/OS system ?
Is the CHAR or Timestamp use, the safest timestamp procedure ?
How do you fix it?
This year, as every year, the moment arrives for most of us when the clocks go forward and then in autumn back again. I really hate this, as I still have a bunch of clocks that do not automatically do it for me. My PC, phone, laptop, TV etc. all do it for me but the rest… anyway what has this got to do with Db2 I hear you all wonder? Well it really is quite a horrible little story coming up…
Same procedure as every year
At precisely 02:00 on the 25th of March a SET CLOCK console command was issued to change the UTC Offset to +2 thus leaping from 02:00 to 03:00 in the blink of an eye.
How long?
Now “how long” is the blink of an eye? For Db2 these days – too long!
Day of reckoning
At 2018-03-25-02.00.00.006999 a transaction was logged in the Audit system, in fact *lots* of transactions were in-flight at this time. Normally it is not a problem and, in fact, nothing happened until nearly three months later when someone found that there was possibly some data missing.
Alarm!
Alarm bells are ringing as these inventory checks cannot have missing data. The code is nowadays all JAVA and the developer in charge of the problem found out that the data was indeed missing!
Oh no it isn’t!
The DBA group were then involved, as it could be data corruption, and they looked and found the data – but it was not the same data as the developers had… then the penny dropped!
Clever old JAVA
In fact, the data the developer had was *exactly* one hour later than the data found by the DBA group. I mentioned earlier that the 25th March was the switch to summer time and, perhaps, the JAVA Driver is “helping” us, a bit too much help if you ask me!
Date Check
Here is a bit of SQL for you to recreate the problem and gaze in wonder at how cool/horrible (delete what is not applicable) JAVA really is.
CREATE TABLE BOXWELL.DAY_LIGHT (COL1 SMALLINT NOT NULL ,COL2 TIMESTAMP NOT NULL) ; INSERT INTO BOXWELL.DAY_LIGHT VALUES (1 , '2018-03-25-01.59.59.999999'); INSERT INTO BOXWELL.DAY_LIGHT VALUES (2 , '2018-03-25-02.00.00.006999'); INSERT INTO BOXWELL.DAY_LIGHT VALUES (3 , '2018-03-25-03.00.00.000099'); COMMIT ; SELECT * FROM BOXWELL.DAY_LIGHT ORDER BY 1 ; ---------+---------+---------+---------+---------+-------- COL1 COL2 ---------+---------+---------+---------+---------+-------- 1 2018-03-25-01.59.59.999999 2 2018-03-25-02.00.00.006999 3 2018-03-25-03.00.00.000099 DSNE610I NUMBER OF ROWS DISPLAYED IS 3
The output of SPUFI looks great! Timestamps are correct and all is fine.
It is a GUI world
Now do the select using a JAVA driver of your choice, here I am using DataStudio:
And then running it gives:
Spot the difference!
Isn’t that
great/terrifying (delete what is not applicable)
as the JAVA driver is “looking” at the timestamp data and seeing “oh oh! That timestamp is impossible! I know – I will add one hour to correct it!”
This scares me a little…actually quite a lot!
Docu – What Docu?
The only place I could find anything about this was in a chapter about not using 24 as midnight and the problem of using timestamps between October 5th 1582 and October 14th 1582:
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/java/src/tpc/imjcc_r0053436.html
If you read it you can find this one sentence:
If a string representation of a date, time, or timestamp value does not correspond to a real date or time, Java adjusts the value to a real date or time value.
Which, of course, explains everything!
The quick fix…
There is no quick fix!
1 – The customer must either change all SQL to use the CHAR function – Not good!
Or
2 – Check all of their important timestamp columns for the range 02.00.00.000000 -> 02.59.59.999999 data and then update them with plus one hour – Not good!
Faster and Faster : the best fix ?
This problem will get worse the faster the machines get and so my idea to solve it next year is simply issue a
SET LOG SUSPEND
at one second before 02:00 which flushes the log, issues a system checkpoint (non data-sharing), updates the BSDS and basically pauses the system. Then do the SET CLOCK command and then do a
SET LOG RESUME
It all takes about three seconds and so should not cause any timeouts.
I really hope that, one day, we simply get rid of daylight saving time…
As always, any questions or comments would be most welcome!
TTFN,
Roy Boxwell