Find the Statements Using an Index Without Event Monitors

You may also like...

3 Responses

  1. Isaac Munoz says:

    Hi Amber, I liked your blog as I tried the same thing before and feels nice somebody else is trying to get the same info DB2 does not make easy to get.
    My experiences are:
    A) I’ve found SQLs where DB2 uses indexes which have nothing to do at all with the WHERE predicate. Like this particular query below uses the PK index (which is SINGLETRANSID column) but you don’t see it in the where predicate, even though there’s an index on ARCHFLAG+ARCHDATE+TMSTAMP columns.

    “SELECT Min(singletransID) FROM isaac.table WHERE ARCHFLAG = ? and Tmstamp < timestamp(current_date) – ? DAY"

    Only if you add 'and ARCHDATE=? and' in the predicate, DB2 uses the right index. So I think if we know indexes with fullkeycard=1 are not useful we should drop them anyway.

  2. Prasun says:

    Hi ,
    Thanks for sharing the information . It was great to see the way we can pull the metadata statistics and use the same to improve our application and database performance . I was also wondering if there is a way to do the below :

    I need to know which columns of a certain index does a SELECT query use if at all the query uses that index . As for eg let’s say I have an index ix1 based on columns c1 , c2 , c3 and c4 . Now my select query is in the form of select * from table where c1 = …. and c3 = …. . If I see the explain plan I will be able to see that DB2 has indeed used index ix1 but how can I know which columns out of that index has DB2 used viz. c1 only or c1 and c3 both . Do we have any third party tool that pretty much serves the above purpose ?

    • Ember Crooks says:

      DB2 has to read all of the columns from the index, so it doesn’t really matter which columns it “uses” – unless we’re talking index-only access, or unless the table has to then be scanned/accessed to get more information that’s not in the index. If the data is in the index and there isn’t a function in the query that prevents its use, then the data in the index will generally be preferred over the table. By reading the explain, you can generally tell which predicates are being applied at which step, but you have to dig down into the details to do so.

      The bigger question to ask with indexes is usually how they are being accessed. There are two ways to access an index – following the b-tree structure to retrieve certain leaf pages, or scanning through all the leaf pages. The latter is obviously more resource-intensive and worse for performance.

Leave a Reply

Your email address will not be published. Required fields are marked *