Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2008
    Posts
    5

    Unanswered: optimizing Db2 query

    Hi,
    I using db2 udb v 9.I m using "coalesce(ucase(varchar" funcyion in the left side.can anybody give a suggestion to optimize the following code.

    " select b.customer_number into customer_num_temp
    from etw_rms_reg_cust_account_mst a ,
    etw_rms_reg_customer_mst b ,
    etw_rms_reg_cust_id_dtl c,
    etw_rms_reg_cust_address_dtl d,
    etw_rms_com_implicit_hdr_vw e
    where a.cust_acc_number = e.cust_Acct_no
    and a.customer_number = b.customer_number
    and a.customer_number = c.customer_number
    and a.customer_number = d.customer_number
    and b.customer_number = d.customer_number
    and c.customer_number = d.customer_number
    and a.org_unit = b.org_unit
    and b.org_unit = c.org_unit
    and b.org_unit = c.org_unit
    and d.org_unit = e.org_unit
    and e.org_unit = a.org_unit
    and e.status_mcd = etwrmscode5('ACT')
    --and c.primary_id = etwrmscheck(1) --B2C1PT_ETWRMSCRN_00036
    and coalesce(varchar(d.address_type),'%') like varchar(addresstype_hdn)
    and coalesce(varchar(d.address_sub_type),'%') like varchar(address_subtype_hdn)
    and coalesce(varchar(d.unit_number),'%') like varchar(unitnumber)
    and coalesce(ucase(varchar(d.block_name)),'%') like varchar(blockname)
    and coalesce(ucase(varchar(d.complex_name)),'%') like varchar(complexname)
    and coalesce(ucase(varchar(d.street_number)),'%') like varchar(streetnumber)
    and coalesce(ucase(varchar(d.street_name)),'%') like varchar(streetname)
    and coalesce(ucase(varchar(d.street_type)),'%') like varchar(streettype_hdn)
    and coalesce(ucase(varchar(d.suburb)),'%') like varchar(suburb_tmp)
    and coalesce(ucase(varchar(d.postal_area)),'%') like varchar(postalarea_tmp)
    and coalesce(ucase(varchar(d.city_name)),'%') like varchar (city)
    and coalesce(ucase(varchar(d.postal_code)),'%') like varchar(postalcode)
    fetch first 1 rows only with ur;
    if customer_num_temp is null then
    begin
    error message
    end;
    end if;
    open rc3;
    end;"

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    I can see you are using too many expressions in the WHERE Clause. Using expressions reduces the chance of using the right INDEX. Moreover, you are using multiple CASTings as well. The number of tables used inside the JOINs are too many. Are they using Table-Collocation or you are putting CUSTOMER_NUMBER for all the tables as Partitioning-Key.? You can also refer to the best-practice guidelines from IBM for writing efficient SELECT statement which mentions the DO's and DONTs.
    Thanks,
    Jayanta, New Delhi, India

  3. #3
    Join Date
    Apr 2008
    Posts
    39
    As Jayanta mentioned, joining too many tables, too many castings, too many expressions in WHERE clause will impact query performance.


    Here is the best practices document for tuning your query:
    http://download.boulder.ibm.com/ibmd...ning_0508I.pdf

  4. #4
    Join Date
    Nov 2008
    Posts
    5
    Hi ,
    is theere any other way to avoid the following casting.bcos the coalesce function will work only on the NDDs. so i must convert it into varchar from both the side.

    coalesce(varchar(d.address_type),'%') like varchar(b.d.address_type)



    Quote Originally Posted by JAYANTA_DATTA
    I can see you are using too many expressions in the WHERE Clause. Using expressions reduces the chance of using the right INDEX. Moreover, you are using multiple CASTings as well. The number of tables used inside the JOINs are too many. Are they using Table-Collocation or you are putting CUSTOMER_NUMBER for all the tables as Partitioning-Key.? You can also refer to the best-practice guidelines from IBM for writing efficient SELECT statement which mentions the DO's and DONTs.
    Thanks,
    Jayanta, New Delhi, India

  5. #5
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Not sure, how frequenly you will access the Query. But if it is frequently used and you are not getting better performance, you can think of having an MQT which will have those CASTED fields used in the WHERE Clause as additional fields. You just need to use those fields in WHERE clause without any additional Casting and you will be using the MQT only in your SELECT clause. If required, you can put additional Indexes on the MQT fields.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ravi255
    coalesce(varchar(d.address_type),'%') like varchar(b.d.address_type)
    Is this *really* what you want to do? I mean, is b.d.address_type really containing "%" and "_" wildcards against which you want to do a "LIKE" ?
    This is imho a very uncommon kind of situation!

    If all you need is either (1) d.address_type = b.d.address_type, when both are not-NULL, or (2) verifying that both are NULL, you could consider using
    Code:
    WHERE d.address_type IS NOT DISTINCT FROM b.d.address_type
    If you want a match when the left hand side is NULL and the right hand side is '%', consider using
    Code:
    WHERE d.address_type IS NOT DISTINCT FROM NULLIF(b.d.address_type, '%')
    If the "IS NOT DISTRINCT FROM" predicate is not (yet) available in your version of DB2, you'll have to replace it with either
    Code:
    WHERE (d.address_type = b.d.address_type OR
           (d.address_type IS NULL AND b.d.address_type IS NULL))
    or with a "union all" (that latter probably being more performant).
    Last edited by Peter.Vanroose; 11-11-08 at 09:45.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Nov 2008
    Posts
    5
    Thankyou verymuch for ur informative reply,

    "and coalesce(varchar(d.address_type),'%') like varchar(addresstype_hdn)"

    From the following line "addresstype_hdn" is a input parameter for a SP.actully its a search SP.if the input value is passed as NULL then we are replace the NULL value to '%'.the column value in the left hand side is NULL i m making it as '%' by using coalesce.so it wil fetch all the records.value for the "addresstype_hdn" may come like "indivi%".that is y i m using the keyword "like".so this type of query is taking long time.

    ur suggesstion:
    WHERE d.address_type IS NOT DISTINCT FROM NULLIF(b.d.address_type, '%')
    pls tel me whether ur query will work like a "like" keyword.

  8. #8
    Join Date
    Nov 2008
    Posts
    5
    i m executing a query from control center with access plan.

    but it gives me the following error:

    [IBM][CLI Driver][DB2/LINUXZ64] SQL0220N The Explain table "DB2INST1.EXPLAIN_OBJECT", column "PAGES" does not have the proper definition or is missing. SQLSTATE=55002


    pls any body tell me wht i have to do.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ravi255


    pls any body tell me wht i have to do.
    I thnk u shld drp expln tbls n cr8 nu tbls by xecutng explain.ddl, whch can b found in sqllib/misc.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    And buy a keyboard with all 26 and a shift key on it.

    p.s: Personally, I consider those abbreviations as very impolite because apparently, the writer doesn't even want to spent some time on the writing itself. So I don't bother with answering such posts in the first place (in case I would have an answer, that is).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stolze
    the writer doesn't even want to spent some time on the writing itself.
    I suspect that it may actually take more effort to write in that strange "language", because you would have to continuously translate what you're writing from proper English. Unless, of course, there are places in this world where they teach lolspeak in schools...
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Nov 2008
    Posts
    5
    Hi ,

    Can anybody tell me the cost of the following query

    if exists( select 'X'
    from table1 a ,
    table2 b
    where a.ref_no = b.ref_no
    and a.inst_unit = b.inst_unit
    and b.special_status = cast('CASH' as etwrmscode5)
    and cust_number = customeraccountnumber
    begin
    {
    }
    end
    else
    begin
    end;
    end if;

    i just want to know whether IF EXIST Cost more than the following query.

    set exist_tmp = 'N';

    select 'Y'
    into exist_tmp
    from table1 a ,
    table2 b
    where a.ref_no = b.ref_no
    and a.inst_unit = b.inst_unit
    and b.special_status = cast('CASH' as etwrmscode5)
    and cust_number = customeraccountnumber
    fetch first 1 rows only

    if exist_tmp = 'Y' then
    begin
    {
    }
    end;
    else
    begin
    {
    }
    end;
    emd if;

    which one will be better in performance aspect for existence checking.
    Last edited by ravi255; 12-03-08 at 04:01.

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the access plans and what DB2 estimates.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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