Results 1 to 5 of 5

Thread: Date conditions

  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: Date conditions

    Shown below is a portion of a query that I've written. Would using date conditions cause DB2 to skip an index that is comprised of the
    W02_POS_BEG_xx date fields? The table is fairly large. Despite a number of indexes, DB2 chooses to perform a tablescan instead of using the indexes.



    (DATE(DIGITS(CAST(W02_POS_BEG_YY AS DECIMAL(4, 0))) || '-' || DIGITS(CAST(W02_POS_BEG_mm AS DECIMAL(2, 0))) ||'-'|| DIGITS(CAST(W02_POS_BEG_dd AS DECIMAL(2, 0)))))as W02POSDT,
    (DIGITS(W02_POS_BEG_MM)||'/'||DIGITS(W02_POS_BEG_DD)||'/'||DIGITS(W02_POS_BEG_YY))AS POSBEG,
    ('6'||'/'||'30'||'/'||DIGITS(W02_POS_END_YY))AS POSEND,

    Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by citi
    Shown below is a portion of a query that I've written. Would using date conditions cause DB2 to skip an index that is comprised of the
    W02_POS_BEG_xx date fields? The table is fairly large. Despite a number of indexes, DB2 chooses to perform a tablescan instead of using the indexes.
    I guess this would have been better stated as:
    Our data model is seriously flawed. We store dates as numeric fields for year, month, and day values, while many queries have values of the DATE datatype in their selection criteria. Performance is unacceptable. How can we improve it?
    You have three options:
    - change the way you store dates;
    - create (and keep updated) a computed DATE column, calculated from your year, month, and day values; build an index on that column;
    - parse the search criteria into the year, month, and day values and make your queries search by those values.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS?

    What determines the access plan more than anything is the WHERE clause. Returning calculations on the columns will not have much of an impact.

    Andy

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Maybe it is a question for Lenny77 as he also works at citi
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Cougar8000
    Maybe it is a question for Lenny77 as he also works at citi
    He's busy calculating prime numbers. Digits of pi will be next.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •