|
Problem IT34960
|
Status: Closed |
DATE PRECISION IS LOST WHEN REMOTE SQL SHIPPED TO FEDERATED DB AND RESULTS IN 0 ROWS.
|
| product: |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 |
| Problem description: |
It is the same issue as v11.1 APAR IT35236.
DB2level:
Informational tokens are "DB2 v11.5.4.0", "special_4066",
"DYN2010151020AMD64_4066", and Fix Pack "0".
Federated DB:
Oracle
db2set:
DB2_COMPATIBILITY_VECTOR=ORA
problem:
When the below types of where predicate is sent by DB2 to
federated Oracle DB , there is no results returned by db2.
in the below example the 'remote statement' prepared & sent by
DB2 to Oracle is:
example:
select * from tab1 t where TRUNC(CURRENT_DATE) - t.run_time >
2/(24*60)
In the explain we see that Remote statement:
RMTQTXT : (Remote statement):
Where (0.001388888888888888888888888888888889 < EXTRACT(DAY FROM
(TRUNC(TO_TIMESTAMP(:H1 )) - A0."RUN_TIME")))
the root cause for the wrong result (no date return) of bad
query is we tried to convert DATE INTERVAL type result of
TRUNC(CURRENT_DATE) - t.RUN_TIME to the number of days using "
EXTRACT(DAY FROM...)" in Oracle, but EXTRACT could only get the
DAY part of the date interval, not the number of days for the
whole interval in Oracle. For example DATA INTERVAL +000000000
19:58:59.000, EXTRACT(DAY FROM +000000000 19:58:59.000) will get
0, not 0.832627315.
This cause the wrong result and thus 0 rows returned. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* all *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Db2 11.5.5.1 or higher *
**************************************************************** |
| Local Fix: |
Request DB2 support for special build fix.
This SB fix will make the where predicate " TRUNC(CURRENT_DATE)
- t.RUN_TIME > 2/(24*60) " workable, and translates the remote
stmt that is prepared by DB2 and shipped to Oracle
as:
EXTRACT(DAY FROM (TRUNC(CURRENT_DATE) - t.RUN_TIME) DAY(9) TO
SECOND(9)) + EXTRACT(HOUR FROM (TRUNC(CURRENT_DATE) -
t.RUN_TIME) DAY(9) TO SECOND(9)) / 24 + EXTRACT(MINUTE FROM
(TRUNC(CURRENT_DATE) - t.RUN_TIME) DAY(9) TO SECOND(9)) /
(24*60) + EXTRACT(SECOND FROM (TRUNC(CURRENT_DATE) - t.RUN_TIME)
DAY(9) TO SECOND(9)) / (24*60*60) |
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* all *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Db2 11.5.5.1 or higher *
**************************************************************** |
| Comment |
Upgrade to Db2 11.5.5.1 or higher |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 17.11.2020
31.03.2021
15.06.2021 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
