Tuesday, June 12, 2012

Issue with DATE and TIMESTAMP data type in DB2 Oracle Compatibility Mode

While working with a client , I came across this strange behavior of DB2 in Oracle compatibility mode.
This issue only comes into picture when we are using the regular DB2 date and timestamp semantics in the Oracle compatibility mode.
I'll explain it through examples :
db2 "values current timestamp"

1
--------------------------
2012-06-12-11.26.42.445000

  1 record(s) selected.

As I am in GMT + 5:30 , so the timezone that DB2 shows is :
db2 "values current timezone"

1
--------
  53000.

  1 record(s) selected.
Now , If we want to use these details to get the value of GMT , we would obviously subtract the timezone value from current timestamp.
When we try it :

db2 "values current timestamp - current timezone"

1
--------------------------
1867-05-04-11.26.43.753000

  1 record(s) selected.

Here it gives year as 1867 ? Strange .
Reason : When Oracle Compatibility mode is set , the DATE data type is changed to support applications that use the Oracle DATE data type expecting that the values include time information.
So when the DATE data type is explicitly encountered in SQL statements, it is implicitly mapped to TIMESTAMP(0). As a result of this implicit mapping, messages refer to the TIMESTAMP data type instead of DATE, and any operations that describe data types for columns or routines return TIMESTAMP instead of DATE.
Adding a numeric value to a TIMESTAMP value or subtracting a numeric value from a TIMESTAMP value assumes that the numeric value represents a number of days. The numeric value can have any numeric data type, and any fractional value is considered to be a fractional portion of a day. For example,TIMESTAMP '2008-03-28 12:00:00' + 1.3adds 1 day, 7 hours, and 12 minutes to the TIMESTAMP value, resulting in '2008-03-29 19:12:00'.
Solution : The same thing happened in our case where we subtracted CURRENT TIMEZONE from CURRENT TIMESTAMP.
CURRENT TIMEZONE is giving us result in hours and minutes , but due to ORA mode implicit mapping , DB2 is treating as 53000 days . That's why we are getting year as 1867 in the result set.
So the solution would be to cast CURRENT TIME in hours/minutes :
1) Let's try to cast CURRENT TIMEZONE to hours :

db2 "values current timestamp - (current timezone/10000)hours"

1
--------------------------
2012-06-12-06.26.46.417000

  1 record(s) selected.

Here the result would be improper , as 53000/10000 would give 5.3 , which would be rounded up in 5 hours . So the result would not be proper.

2) Now , let's cast CURRENT TIMEZONE to minutes :
db2 "values current timestamp - ((current timezone/9630)*60)minutes

1
--------------------------
2012-06-12-05.56.49.405000

  1 record(s) selected.

Here we get the correct output.

2 comments:

  1. Nice blog!
    BTW [ I am guessing you know this already].
    To get GMT , we can execute this command instead
    db2 => values current timestamp - 5 HOURS - 30 MINUTES

    1
    --------------------------
    2012-06-14-15.51.03.905000

    1 record(s) selected.

    ReplyDelete
    Replies
    1. Thanks for your comment .
      Yes , you are correct that by using the hard coded values instead of variable CURRENT TIMEZONE would help here in this example.
      But if we think about customers who use this parameter CURRENT TIMEZONE in their stored procedures/triggers where they require the run time value of timezone irrespective of the user's geography , than the value of this parameter will change.
      So to make it generic we can for a generic conversion.

      Actually I hit that issue while working on Emptoris's migration from Oracle to DB2 .
      Their requirement was to use a generic solution to get value of GMT in run time , irrespective of user's geography.
      So this was the solution that I proposed :)

      Delete