If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > collecting statistics after db migration

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-24-12, 09:34
db2girl db2girl is offline
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
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?
Reply With Quote
  #2 (permalink)  
Old 01-24-12, 09:45
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
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
Reply With Quote
  #3 (permalink)  
Old 01-24-12, 14:16
db2girl db2girl is offline
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
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.
Reply With Quote
  #4 (permalink)  
Old 01-27-12, 09:09
fengsun2 fengsun2 is offline
Registered User
 
Join Date: Nov 2011
Posts: 124
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...
Reply With Quote
  #5 (permalink)  
Old 01-27-12, 16:14
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
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
Reply With Quote
  #6 (permalink)  
Old 01-28-12, 09:00
fengsun2 fengsun2 is offline
Registered User
 
Join Date: Nov 2011
Posts: 124
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..
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On