Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Location
    Bangalore, India
    Posts
    35

    Unanswered: DB2 does not use index but after reorg

    AIX 6.1
    DB2 9.7 FP 5

    Recently we had performance issue with a query. We analysed the query and found that it going for tbscan as index was not present on predicates of query.

    We created an index on those predicates.
    index creation was followed by runstats on table as well indexes.

    after rerunning the query we observe that db2 still did not use the index.

    We were confused as this was a simple query .

    we checked the defragmentation of table in question using reorgchk however it found it okay.

    Left with no choice and to improve the disk i/o performance in case of tbscan we decided to do the reorg. After reorg got completed, we ran the query and it started using the index!

    Question

    1) DB2 might have reasons to not use index before reorg however my question is how to identify this.

    2) is reorgchk always accurate in what it tells. if not is there any other way to check defragmentaion of table.

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    Do you specify the index name when you reorg the table ?
    plz provide the following info:
    1,the query and the execution plan ( gererated by db2exfmt )
    2, the reorg command you just used.
    3, the ddl for index and table.

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by divakargoel View Post
    AIX 6.1
    [...]
    2) is reorgchk always accurate in what it tells. if not is there any other way to check defragmentaion of table.
    Check the percentage of overflows. Something like:

    Code:
    select tabschema, tabname, cast(100.0*OVERFLOW_ACCESSES / nullif(rows_read,0) as decimal(4,2))
    from sysibmadm.snaptab 
    order by 3 desc
    will tell you if you are doing additional I/O due to fragmented tables.
    Last edited by lelle12; 12-18-12 at 14:12.
    --
    Lennart

Posting Permissions

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