469,628 Members | 1,091 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,628 developers. It's quick & easy.

System Catalog Table Maintenance

Do db2 system catalog table require maintenance (reorg/runstat) ?

Thanks,
Sam

Mar 7 '07 #1
5 5224
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
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
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
"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
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.

Similar topics

236 posts views Thread by Andrew Rawnsley | last post: by
3 posts views Thread by reachsamdurai | last post: by
4 posts views Thread by Laurence Breeze | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.