Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: collecting statistics after db migration

    Do you collect statistics on user tables/indexes (I see statistics are automatically collected on most system catalog tables) after db migration?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You should always run your own runstats. The automated runstats do not collect the proper information and apparently it is not possible to customize the options. You should run it after data migration (not use old stats from db2look). Good idea to run weekly on LUW (more complicated on z/OS since they often employ chargeback for each job you run).

    Here is what I use:

    runstats on table <tabname> with distribution on key columns and detailed indexes all;

    Some people do distribution on all columns, but it seems to me that unless a column is indexed, then DB2 cannot really benefit by knowing the column distribution, since this information is used in deciding whether DB2 uses an index or not.
    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 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I think you can customize the options using statistic profile. I used RESTORE DB (the manual mentions UPGRADE DB) to migrate the databases and db2 collected stats on most catalog tables, but I can redo it with the appropriate runstats options (for catalog tables/indexes):
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    I'm not sure whether to collect stats on user tables/indexes as part of db2 migration. I checked the databases and many user tables/indexes don't have any stats collected at all or stats are very old. One of the environments has 25 db's, so not sure if it's worth to spend time collecting stats during the migration weekend or do it during some other maintanence window. Their system seems to performing *ok* with no stats / old stats up to now.

  4. #4
    Join Date
    Nov 2011
    Posts
    334

    hi

    Quote Originally Posted by Marcus_A View Post
    You should always run your own runstats. The automated runstats do not collect the proper information and apparently it is not possible to customize the options. You should run it after data migration (not use old stats from db2look). Good idea to run weekly on LUW (more complicated on z/OS since they often employ chargeback for each job you run).

    Here is what I use:

    runstats on table <tabname> with distribution on key columns and detailed indexes all;

    Some people do distribution on all columns, but it seems to me that unless a column is indexed, then DB2 cannot really benefit by knowing the column distribution, since this information is used in deciding whether DB2 uses an index or not.
    column distribution information is not only used in deciding whether DB2 uses an index or not, but also used for estimating the filter factor after join (maybe more often ), so sometimes only collecting distribution information on key columns is not enough...

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by fengsun2 View Post
    column distribution information is not only used in deciding whether DB2 uses an index or not, but also used for estimating the filter factor after join (maybe more often ), so sometimes only collecting distribution information on key columns is not enough...
    Maybe I don't understand this, but if there is join on a non-indexed column, then the query is probably already in the toilet.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    Non-indexed column join is very common in the datawarehouse environment,
    we can't create index for each join column because there are too many ad-hoc querys..

Posting Permissions

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