Can some one help me with online REORGs. I need to start on line reorgs on a very busy gaming DB.
As far as I know we need to have 70% free space in index tablespaces to reorg indexes online.
And steps as below.
1. reorgchk to decide which indexes and tables to do reorgs on.
2. Check tablespaces related to these tables and indexes.
3. Extend tablespaces if not enough free space ( tables 30% and indexes 70% free needed)
4. Start online reorgs in groups.
I have done reorgs online a few times, but this database is high activity database and I believe is sensetive too.
I have following questions regarding reorgs.
Q1 What happens if any reorg fails ? what steps might be needed to recover from failed reorgs. Is there any risk regarding database, tablespace or table availability in that situation ?
Q2 What should I do as a precaution to prevent any reorgs from failing ? How many concurent tables are reccomended for online reorgs ?
Please correct me if I miss anything or if I am wrong somewhere.
The trick in your scenario is to start the reorgs 1-by-1 and wait for 1 reorg to finish before you start the next one. The reorg-utility is something you must trust. You cannot anticipate a 'failure'. You can make sure that only 1 reorg at a time runs and schedule during low-activity, and do not forget to reorg the index as well after the data-reorg has finished.
Do not be afraid of offline reorgs. They are 20 times faster than online-reorgs and the indexes are also included. Try it on your test-system and see how long each tables takes. My guess that 80% will take less than a minute and most applications can handle such a lock-situation. So your problem will be the remaining 20%. That will be your big-tables and you should know that a MDC definition makes them less reorg-sensitive.