DB2 does a tablescan even with an index present

[cherukuc@gmail.com]

We have a unique issue where a query does a tablescan even though an
index is present. Basically we created a new DB by loading existing
data from a source DB.
The index names and everything else are same in both the DBs. The
query does an index scan in the main source DB but not in the one
newly created. Ihave verified the index, and it is exactly the same in
both the DBs. Any insights on what could be the issue ?

Thanks
Ranjan

[Arun Srinivasan]

Re: DB2 does a tablescan even with an index present

On Feb 21, 4:30 pm, cheru...@gmail. com wrote:

We have a unique issue where a query does a tablescan even though an
index is present. Basically we created a new DB by loading existing
data from a source DB.
The index names and everything else are same in both the DBs. The
query does an index scan in the main source DB but not in the one
newly created. Ihave verified the index, and it is exactly the same in
both the DBs. Any insights on what could be the issue ?
>
Thanks
Ranjan

Can you please try out the following?
* for the issue you just reported, try getting the cardinality ...
* run a runstats with distribution on column that you use extensively
during selection (in my prod table, for client - 501 we had 600 K
rows, and for 502 we had 1k rows, and running distribution greately
reduced the time, also read about 'reopt always' clause for binding SP
if you use procedures)
* For your scenario, if you think about it, you have newly created the
table, you don't have fragments in pages, and if the cardinality is
not high, and if db2 thinks it can get the rows by directly scanning
table, instead of going to index, fetch rids, then go to data page and
fetch data rows, then it will do the table scan.
To test this, have a selection that will return some 10000 rows (in
the column that was indexed) and then do the explain, you'll see index
scans...

[Mark A]

Re: DB2 does a tablescan even with an index present

<cherukuc@gmail .comwrote in message
news:c391c22e-79ee-47c0-a2a2-6da25d366ab2@k2 g2000hse.google groups.com...

We have a unique issue where a query does a tablescan even though an
index is present. Basically we created a new DB by loading existing
data from a source DB.
The index names and everything else are same in both the DBs. The
query does an index scan in the main source DB but not in the one
newly created. Ihave verified the index, and it is exactly the same in
both the DBs. Any insights on what could be the issue ?
>
Thanks
Ranjan

db2 runstats on table <table-namewith distribution on key columns and
indexes all
db2 flush package cache dynamic

If that does not work, try this:

db2 alter table <table-namevolatile
db2 flush package cache dynamic