Professional Web Applications Themes

Runstats issue - IBM DB2

Is there a reason why runstats doesn't refresh the access plans. After creating an index and doing runstats, the query still has the same access plan. using distribution and detailed indexes all -- Posted via [url]http://dbforums.com[/url]...

  1. #1

    Default Runstats issue


    Is there a reason why runstats doesn't refresh the access plans. After
    creating an index and doing runstats, the query still has the same
    access plan. using distribution and detailed indexes all

    --
    Posted via [url]http://dbforums.com[/url]
    Kota Guest

  2. #2

    Default Re: Runstats issue


    Actually this is dynamic SQL.

    --
    Posted via [url]http://dbforums.com[/url]
    Kota Guest

  3. #3

    Default Re: Runstats issue

    "Kota" <member31270dbforums.com> wrote in message
    news:3068773.1057179189dbforums.com...
    >
    > Actually this is dynamic SQL.
    >
    Then DB2 optimizer would make use of the new statistics. The only exception
    would be if the package was cached (same SQL reused by the same thread).

    If you don't think you are getting the right access path, the problem lies
    elsewhere. I would suggest a reorg of table and indexes, and then runstats
    again after the reorg.


    Mark A Guest

  4. #4

    Default Re: Runstats issue

    You can verify that your query is being planned with the new statistics by
    EXPLAINing the query and using db2exfmt to generate a formatted explain.
    Right at the bottom of the db2exfmt output will be a summary of table and
    index information, including when RUNSTATS was last run on each.
    -kt

    "Mark A" <maswitchboard.net> wrote in message
    news:0dIMa.87$wc.38976news.uswest.net...
    > "Kota" <member31270dbforums.com> wrote in message
    > news:3068773.1057179189dbforums.com...
    > >
    > > Actually this is dynamic SQL.
    > >
    > Then DB2 optimizer would make use of the new statistics. The only
    exception
    > would be if the package was cached (same SQL reused by the same thread).
    >
    > If you don't think you are getting the right access path, the problem lies
    > elsewhere. I would suggest a reorg of table and indexes, and then runstats
    > again after the reorg.
    >
    >

    Kaarel Truuvert Guest

Similar Threads

  1. runstats - oracle
    By janagana in forum IBM DB2
    Replies: 5
    Last Post: August 23rd, 05:31 PM
  2. runstats and error code 930
    By Fan Ruo Xin in forum IBM DB2
    Replies: 3
    Last Post: August 15th, 12:31 AM
  3. runstats use all the CPUs?
    By Fan Ruo Xin in forum IBM DB2
    Replies: 2
    Last Post: July 26th, 12:22 AM
  4. runstats and embedded sql
    By Larry Menard in forum IBM DB2
    Replies: 2
    Last Post: July 9th, 11:32 PM
  5. runstats
    By xixi in forum IBM DB2
    Replies: 10
    Last Post: July 3rd, 12:51 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139