Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Unanswered: Question about Performance in a Qry

    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!!

  2. #2
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    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...

  3. #3
    Join Date
    Aug 2008
    Posts
    147
    you could also try a JOIN
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by JackVamvas View Post
    you could also try a JOIN
    That wouldn't help for the performance: also a JOIN condition must use matching data types!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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