var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: DB2 does not use index but after reorg
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!
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.
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.
Check the percentage of overflows. Something like:
Originally Posted by
will tell you if you are doing additional I/O due to fragmented tables.
select tabschema, tabname, cast(100.0*OVERFLOW_ACCESSES / nullif(rows_read,0) as decimal(4,2))
order by 3 desc
Last edited by lelle12; 12-18-12 at