[SPAM] Online reorg of large tablespace with many indexes

Daniel Luksetich

[SPAM] Online reorg of large tablespace with many indexes
"is anyone out there doing this?" Yup


"find a suitable window where activity is low enough to perform the
switch" Yup


"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."

yes we wait for low activity. Whether or not you get the switch depends on
your ability to drain. Worse case senario is that you may need a thread
killer. We had one process set up where we were able to switch the thread
reusability by using RDO in CICS.

Retry logic is always good to have. If you are expected to be 24X7 then
you have to have it.

That's about all I got.

Cheers,
Dan

On Wed, 8 Dec 2010 16:19:06 -0500, Peter Brown <[login to unmask email]>
wrote:
> Hello
>
> 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, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* 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, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

David Simpson

Re: Online reorg of large tablespace with many indexes
(in response to Daniel Luksetich)
Here's a link to a presentation I did that discusses (among other things) a way to time the switch so that it falls in a window that you desire. Look for "Reorg Plan A"...

http://www.hoadb2ug.org/Docs/Simpson0903.pdf

In the shop where I worked we had a short scheduled outage at 2am on Sunday morning. The trick was to get a reorg that runs for 6 hours to switch in that window.

Feel free to reply with questions or comments on the technique.

______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
For more information about Themis, visit www.themisinc.com


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Peter Brown
Sent: Wednesday, December 08, 2010 3:19 PM
To: [login to unmask email]
Subject: [DB2-L] Online reorg of large tablespace with many indexes

Hello

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, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* 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, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv