Q

Is running a reorg on indexes required?

If we run reorg on the table space weekly, is running a reorg on the index space ever required? With the required options a table space reorg by itself rebuilds indexes, correct?

Yes, if you are running a weekly table space reorg you may not need to reorg the indexes on that table space since

those indexes are rebuilt every time the table space is reorganized.

However, for a very active application in a transaction processing environment you might want to see how disorganized the indexes become during the week. You might be able to get better performance if the indexes become disorganized quickly by reorganizing the index once (or perhaps more) during the period between table space reorgs. For example, if you reorg the table space every Saturday, you might want to examine the statistics for the indexes on Wednesday (or even Tuesday) to see how disorganized the indexes are. Look at the LEAFDIST, LEAFFAR, LEAFNEAR, and NLEVELS. As these stats get larger the index becomes less efficient. Maybe NLEVELS starts out at 3 on Saturday and is at 4 by Wednesday, then back to 3 after the reorg on Saturday. Reorganizing just the index on Wednesday night might help performance.

This was first published in January 2003

Dig deeper on IBM DB2 management

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close