IBM Support

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

Troubleshooting


Problem

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.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Env variables\/reg variables\/db2greg","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;9.5;10.1;10.5","Edition":"Advanced Enterprise Server;Enterprise Server;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21567168

Page Feedback