DB2 FOR LUW / DB2FORLUW / A10 - DB2

DB2 might produce incorrect result when executing a query with 
datetime predicates. 
The incorrect result will only happen if the table has a 
timestamp of the form 'YYYY-DD-MM:HH:MM:SS:24.00.00.000000' 
 
 
db2 "create table tablexyz (ts TIMESTAMP DEFAULT 
'2999-12-31-24.00.00.000000')" 
 
db2 "insert into tablexyz values ('2999-12-31-24.00.00.000000')" 
 
 
db2 "select * from tablexyz" 
 
TS 
-------------------------- 
2999-12-31-24.00.00.000000 
 
  1 record(s) selected. 
 
Example 1: 
db2 "select * from tablexyz where date(ts) = '2999-12-31'" 
 
ts 
-------------------------- 
 
  0 record(s) selected. 
 
Example 2: 
db2 "select * from tablexyz where year(ts) = 2999" 
 
TS 
-------------------------- 
 
  0 record(s) selected. 
 
Example 3: 
db2 "select * from tablexyz where year(ts) = 2999 and month(ts) 
= 12" 
 
TS 
-------------------------- 
 
  0 record(s) selected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 and Fix Pack 3                   * 
****************************************************************

Workaround: Manually rewrite the predicate from date/year to 
timestamp format. 
Example1. 
select * from tablexyz where ts >= 
'2999-12-31-00.00.00.000000' and ts <= 
'2999-12-31-24.00.00.000000' 
 
example 2. 
select * from tablexyz where ts >= 
'2999-01-01-00.00.00.000000' and ts <= 
'2999-12-31-24.00.00.000000' 
 
select * from tablexyz where ts >= 
'2999-12-01-00.00.00.000000' and ts <= 
'2999-12-31-24.00.00.000000'

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 Version 10.1 and Fix Pack 3

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC95380 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

31.05.2013
27.09.2013
27.09.2013