Hi... And thanks... I have a iSeries 570 with DB2 5.4... and this qry not work ok, indeed, i'm lost...

This query, run in 2 seconds... (running after o before not matter the order)

select *
from pd812dta.f4211

where sdIVD = 111161

and sdKCOO in ( '00001', '00003' )

and substr(sdVR02,1,2) in ( '01','02', '03', '06', '07', '08', '19', '20', '21' )

and sdDCT not
in ( 'VA', 'VC', 'VD', 'VF', 'VP', 'VK', 'TA', 'TF', 'TI', 'TN', 'TQ', 'TV', 'XA' )
with ur



but this.... taken one minute a 11 seconds!!!

select *
from pd812dta.f4211

where sdIVD =
( ((year( '2011-06-10' )-1900)*1000) + ( dayofyear( '2011-06-10')))

and sdKCOO in ( '00001', '00003' )

and substr(sdVR02,1,2) in ( '01','02', '03', '06', '07', '08', '19', '20', '21' )

and sdDCT not
in ( 'VA', 'VC', 'VD', 'VF', 'VP', 'VK', 'TA', 'TF', 'TI', 'TN', 'TQ', 'TV', 'XA' )
with ur




why ?
any idea?
TIA!!

Try matching data types in where clause using INTEGER function in second query. Older versions of DB2 optimizer have problems with data type mis-match in where clause

INTEGER(( ((year( '2011-06-10' )-1900)*1000) + ( dayofyear( '2011-06-10'))))

Satya...

you could also try a JOIN

That wouldn't help for the performance: also a JOIN condition must use matching data types!