By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,651 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

System Catalog Table Maintenance

P: n/a
Do db2 system catalog table require maintenance (reorg/runstat) ?

Thanks,
Sam

Mar 7 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Mar 7, 9:26 am, "Sam Durai" <reachsamdu...@gmail.comwrote:
Do db2 system catalog table require maintenance (reorg/runstat) ?

Thanks,
Sam
Yes.

Mar 7 '07 #2

P: n/a
On Mar 7, 11:51 am, "Mark A" <m00...@yahoo.comwrote:
On Mar 7, 9:26 am, "Sam Durai" <reachsamdu...@gmail.comwrote:
Do db2 system catalog table require maintenance (reorg/runstat) ?
Thanks,
Sam

Yes.
Thanks for the response. Is it required only if there is frequent
change in DDL or do we need to run it on stable systems(No change in
DDL only data gets added to table) as well ?

Please advice

Mar 7 '07 #3

P: n/a
Since change activity is not very high on the catalog tables in a stable
production environment, we use:
db2 reorgchk on table system, every quarter. The command runs with UPDATE
STATISTICS as a default. We'll then REORG whatever the output recommends if
we think it necessary. This may happen to three or four tables generally.
We then re-run stats on those.
You can polish this up also easily after that with: dr2rbind <dbname-l
rbindout.txt ALL command which will cause all packages in the
SYSCAT.pPACKAGES table to be rebound with their options already specified.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Sam Durai" <re***********@gmail.coma écrit dans le message de news:
11*********************@n33g2000cwc.googlegroups.c om...
Do db2 system catalog table require maintenance (reorg/runstat) ?

Thanks,
Sam
Mar 7 '07 #4

P: n/a
"Sam Durai" <re***********@gmail.comwrote in message
news:11**********************@h3g2000cwc.googlegro ups.com...
Thanks for the response. Is it required only if there is frequent
change in DDL or do we need to run it on stable systems(No change in
DDL only data gets added to table) as well ?

Please advice
Reorgs are never "required." Once the schema is defined, and runstats has
been run, then you don't need to keep reorging the catalog. But after a big
schema change, you should probably run the reorgs/runstats twice, because
the first time you run runstats, additional rows may be added to the
catalog.

If you have a scheduled maintenance window, then an offline (classic)
catalog reorg is not a problem and costs little (very few DB2 LUW systems
use job accounting to chargeback the costs to a user). Some catalog tables
cannot be reorged online, and you will just get an error message when you
try it.

The real trick is to figure out what index to use when reorging the catalog
(or any table). For any reorg, you should usually select an index to use in
this order:

1. Clustering index

2. Primary Key index

3. Unique Index

4. First index on the table

5. No index clause (if you got this far down the list, then no index exists)
Mar 8 '07 #5

P: n/a
Sam,

the best time for a catalog table reorg is after you install a fipack.
In the maintenance window you can reorg all catalog tables. Only a few of
minutes.

Best regards,
Joachim Müller

"Sam Durai" <re***********@gmail.comschrieb im Newsbeitrag
news:11*********************@n33g2000cwc.googlegro ups.com...
Do db2 system catalog table require maintenance (reorg/runstat) ?

Thanks,
Sam
Mar 8 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.