Online reorg of large tablespace with many indexes

Peter Brown

Online reorg of large tablespace with many indexes

We have a 3 billion row tablespace with NPIs on it. We are getting increased pressure to go 24/7. We already perform online schema changes to reduce outages. Of course online schema changes without a reorg can impact CPU consumption during access so I am looking for real world advice on online reorgs using the MAXRO DEFER DRAIN ALL LONGLOG CONTINUE parms.
The idea of course is to run the reorg for as long as it takes and then find a suitable window where activity is low enough to perform the switch.
My question is, is anyone out there doing this? If so, do you wait for a low activity window to perform the switch or do you set the tablespace to RO and rely on program retry logic to allow you to guarantee you get the tablespace unavailable for a short period of time.
Any general gotchas with this approach? I know one is that no other utility can run against the tablespace during the reorg so we have to be aware of moving nightly image copies. Any more?

Secondly I understand there may be a WTO in syslog when the logging phase starts. Does anyone have an example of this that we could use to automate some of our processes?

cheers Peter ...........

* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!

If you need to change settings, is the home of IDUG's Listserv