IBM Support

LI73191: 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.5 FP2 onwards.
    

Local fix

  • change the datatype to TIME or DATE.
    

Problem summary

  • Users affected
    

Problem conclusion

  • First fixed in DB2 UDB Version 9.5, FixPak 2
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI73191

  • Reported component name

    DB2 UDB QP LINU

  • Reported component ID

    5724E2504

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-02-25

  • Closed date

    2008-08-26

  • Last modified date

    2008-08-26

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

    LI72585

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

Fix information

  • Fixed component name

    DB2 UDB QP LINU

  • Fixed component ID

    5724E2504

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGA","label":"DB2 Query Patroller"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
26 August 2008