Results 1 to 6 of 6

Thread: Runstat

  1. #1
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197

    Question Unanswered: Runstat

    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...

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    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 ...

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by alwayssathya View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    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,

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by alwayssathya View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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