Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2009
    Posts
    11

    Unanswered: Execution time of a SQL

    With the help of db2advis I found out that on one query we should have 2 indexes:


    I would like to create those indexes and would like to show to the application team that after making the indexes the query will take less time.
    I would like to get the execution time before creating indexes and after creating indexes.

    How should I do it?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which DB2 version and OS?

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use the db2batch utility. It allows you to run a sample workload, possibly with varying parameters, and reports execution time summary. You will find more details in the manual.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    explain shows a number of timerons and if indexes were used. you can compare the timeron values before and after to prove that there is a performance improvement and indexes were used.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MarkhamDBA View Post
    the timeron values before and after to prove
    Unfortunately, this is a common misconception. Cost estimate does not prove anything; it indicates that there might be a possible performance improvement, from the optimizer point of view. It may or may not reflect the actual change in query performance.

    Only by running the actual workload on the actual system you can prove to yourself and to others that query performance has improved.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    and if you don't current statistics, you may not get any benefits from additional indexes because DB2 doesn't know how selective they may be
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2009
    Posts
    11
    I am using DB2 9.1 FP4 on Solaris.

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    How many indexes do you already have? Do you need all of them? Are your stats upto date? How big is the table and is it partitioned or not? Just because you were told to build indexes , does not mean you have to.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Apr 2009
    Posts
    11
    We have one indexes already existing which db2advis shows that we should drop it as it is unused. Table has approximately 1250000 rows. The table is not partitioned. Statistics are updated every week.

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    set up db2pd to capture information on index usage to verify that that index is not used for sure before you drop it.

    Is this SQL a one time deal or it is going to be run on the regular basis? If later, then you might want to create it.

    How many tables are joined in your SQL? Are they joined correctly?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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