Hi experts,

i have a doubt, what and how exactly RUNSTAT works in DB2,

if suppose i have a table which has a primary key, when i look into the access plan it shows the full table scan,

Question here is since it contains a index key, why it is taking the full table scan ?


if i run the RUNSTAT utility, then if i check the access plan it show the index-key plan...

Runstats updates the DB2 catalog tables with the latest statistics concerning the number of rows, number of unique values for index columns, etc (depending on which runstats options you choose). The DB2 optimizer uses these statistics to determine the access plan for the SQL statement. This is called cost-based optimization.

If DB2 thinks there are a relatively small number of rows in the table (from a previous runstats that is no longer accurate) then DB2 may decide it is faster to do a table scan than use the index.

marcus,

so if the table is huge and contains more data, which is created with a primary key,

will it choose the index access or table scan access before running the runstat ...

What happens before you run runstats depends on what the previous statistics were in the catalog. The DB2 optimizer uses the statistics in the catalog, which are not real-time, and only updated after you run runstats.

thank you ...

let me make it more clear.

one more time ....

if i create a table A with primary key. which is loaded with 50,000 rows.
if i look at the access plan ..... it showing full-table scan,

But , if i run the run-statistics then if check the access plan it shows the index-scan.


why it is not taking the primary key before running the run-stat,

Two of the possible reasons include:

1. Until you run runstats, DB2 does not know that there are 50,000 rows.
2. Until you run runstats, DB2 does not have enough information about the index statistics to know that it will be faster to use the index.