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

I guess this would have been better stated as: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.

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

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.