TIMESTAMPDIFF scalar function fails with SQL0433N error when the DB2_COMPATIBILITY_VECTOR registry variable is enabled.

Problem(Abstract)

An 'SQL0433N Value "XXXX" is too long' error is returned when the TIMESTAMDIFF scalar function is run against a database with Oracle compatibility.

Symptom

SQL0433N Value "XXXX" is too long. SQLSTATE=22001

Cause

Subtraction of timestamp values returns DECFLOAT(34), representing the difference as a number of days. As a result, you can not use the TIMESTAMPDIFF scalar function because it depends on the default behavior of timestamp subtraction. Similarly, subtraction of date values returns DECFLOAT(34), which also represents a number of days, because date values are really TIMESTAMP(0). For more details on the functions that are changed under date_compat mode, please go through the 'DATE data type based on TIMESTAMP(0)' in the Related URL below.

Environment

Environments where databases are created with Oracle compatibility mode enabled.

Diagnosing the problem

The following test case can help you determine if you are encountering the problem:

1. Set the DB2_COMPATIBILITY_VECTOR registry variable as below to enable all of the supported Oracle compatibility features:
   
   db2set DB2_COMPATIBILITY_VECTOR=ORA
   db2stop
   db2start
    
2. Created the oratest database for testing purpose: 
   
   db2 create db oratest
   DB20000I  The CREATE DATABASE command completed successfully. 
   
   
3. Running the TIMESTAMPDIFF scalar function will return SQL0433N error as below:
   
   db2 "values(TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-11.25.42.483219')-TIMESTAMP('2001-09-26-12.07.58.065497'))))"
   
   1
   -----------
   SQL0433N  Value "2.970652982893518518518518518518519" is too long. SQLSTATE=22001

Resolving the problem

This is a restriction on the Oracle compatibility mode. A comparison of the tradeoffs needs to be done to determine if Oracle compatibility mode is more necessary than the use of these functions. one of the solutions below can be used as a work around when the database has been created with Oracle compatibility mode enabled:

o  Reducing the length of the timestamp

db2 "values(TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2010-04-01-09.43.05')-TIMESTAMP('2010-05-01-09.43.05'))))"

1
----------- 
          0
  1 record(s) selected.

o  Multiply the DECFLOAT result to get the units you are interested in.  

db2 "values ( TIMESTAMP('2001-09-29-11.25.42.483219')-TIMESTAMP('2001-09-26-12.07.58.065497') ) /* days */ * 24 /* hours per day */ * 60 /* minutes per hour */"

1                                         
------------------------------------------
       4277.740295366666666666666666666668

  1 record(s) selected.

http://www-01.ibm.com/support/docview.wss?uid=swg21567168