Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    chennai
    Posts
    30

    Question Unanswered: Tuning DB2 query

    Hi all,

    Can any one tell how to tune queries in DB2 UDB 6.1. Are there any third party tools to do that. If yes suggest me.

    Thanks,
    sekhar
    Thanks,
    sekhar rao k

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    Here are some of them:

    Quest Central For DB2
    DBArtisan by Embcardeo

    Cheers,
    Prashant

  3. #3
    Join Date
    Apr 2002
    Posts
    18
    Have you tried using db2's explain to see the access plan.

    You can also look at other things such as index overcrowding, Index only searches etc. Obviously look for anything which is SARGABLE and try wherever possible to eliminate tablescans and sorts

    Greg

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605

    Re: Tuning DB2 query

    Hi,

    On Windows:
    1. Open the Command Center and connect to database. Then write query and execute Interactive | Create Access plan.

    You will get the access plan. The total cost (timerons) should be the parameter you must try to degresse.

    2. run statistics
    RUNSTATS ON TABLE schema.table_name WITH DISTRIBUTION AND DETAILED INDEXES ALL;

    3. create primary keys on tables (uniq indexes are automaticaly generated)

    4. create indexes on foreign keys

    5. create additional indexes


    After each step you will make you have to run statistics.
    For each step verify the acess plan (first step). The "total cost" parameter must fall down.

    For detailed discription you can read the "Visual Explain Tutorial" from web page http://www-3.ibm.com/cgi-bin/db2www/...bs.d2w/en_main

    This tutorial is for DB2 version 8, but it can be used for earlyer versions.

    Hope this helps,
    Grofaty

    Originally posted by k_sekhar_rao
    Hi all,

    Can any one tell how to tune queries in DB2 UDB 6.1. Are there any third party tools to do that. If yes suggest me.

    Thanks,
    sekhar

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605

    Re: Tuning DB2 query

    Hi,

    You can aso look at:
    http://dbforums.com/t695977.html

    Hope this helps,
    Grofaty

Posting Permissions

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