IBM Support

LI72585: LACK OF CONSTANT FOLDING ON TIMESTAMP DATATYPE CAN RESULT IN TABLESCAN

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Problem description : The problem resides in lack of constant
    folding on a YEAR Function applied on TIMESTAMP data type.
    
    YEAR(TIMESTAMP('1994-01-01-00.00.00.00000')) is not working as
    constant like YEAR(DATE(' '1994-01-01-00.00.00.00000')) is.
    
    One of the problem is : If MDC is on generated column of data
    type TIMESTAMP , Query on source column will end up doing table
    scan rather than automatically add predicates using the
    generated column (thereby allowing the optimizer to utilize the
    block index).
    
    This can occur in any of the following functions:
    
    YEAR(TIMESTAMP) (as above),
    MONTH(TIMESTAMP),
    DAY(TIMESTAMP)
    HOUR(TIMESTAMP) (HOUR(TIME) is already simplified)
    MINUTE(TIMESTAMP) (MINUTE(TIME) is already simplified)
    SECOND(TIMESTAMP) (SECOND(TIME) is already simplified)
    
    The problem is fixed in version 9.1 Fixpak 5 onwards.
    

Local fix

  • change the datatype to TIME or DATE.
    

Problem summary

  • Users affected: all
    

Problem conclusion

  • First fixed in DB2 UDB Version 9.1, FixPak 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI72585

  • Reported component name

    DB2 UDB QP LINU

  • Reported component ID

    5724E2504

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-09-18

  • Closed date

    2008-06-24

  • Last modified date

    2008-06-24

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

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

    LI73191

Fix information

  • Fixed component name

    DB2 UDB QP LINU

  • Fixed component ID

    5724E2504

Applicable component levels

  • R820 PSY

       UP

  • R910 PSY

       UP

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Product":{"code":"SSEPGA","label":"DB2 Query Patroller"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]

Document Information

Modified date:
16 October 2021