IBM Support

IT34960: DATE PRECISION IS LOST WHEN REMOTE SQL SHIPPED TO FEDERATED DB AND RESULTS IN 0 ROWS.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error 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.
    

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)
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.5.5.1 or higher                            *
    ****************************************************************
    

Problem conclusion

  • Upgrade to Db2 11.5.5.1 or higher
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT34960

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2020-11-17

  • Closed date

    2021-03-31

  • Last modified date

    2021-06-15

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB50 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.5"}]

Document Information

Modified date:
31 August 2021