DB2 HADR 8.2.2 for REORG maintenance

[derekbarrett]

Hi all,

Our business is in manufacturing, and we are running SAP on DB2 8.1,
planning on upgrading to 8.2.2 within a couple of months. We are
running operations 24/7, so are looking for ways to reduce our
maintenance impact to our users. Our users hit some specific SAP tables
very heavily, and concurrently, so even doing an online REORG causes
some system slowdown. We are looking for ways to reduce these slowdown
times, because even though we have an outage once per month to catch up
REORGs, we would like to be able to do them during normal business
hours, without impacting system performance.

The idea of using HADR came to mind, along with the Q Replication, as
mentioned in this article, where businesses were using Q Replication to
minimize downtime:

http://www.db2mag.com/story/showArti...cleID=23903558

Basically, what we would do is, run our maintenance, such as table
REORGs, on the secondary/standby machine, and once the maintenance has
finished, we would then make the machine available to the end users. In
turn, we would then do the same maintenance on the other machine.
Therefore all performance intensive maintenance activities would take
place on a machine that users would not be hitting.

I initially spoke with our sales rep and his engineer about using Q
Replicator, but they thought HADR has a better choice for what we
wanted.

Has anyone had any experience so far with this scenario? Or is there a
better way to approach this?

Also, if anyone from IBM reads these groups, are there any plans to
make HADR load balance?

[Ian]

de**********@gm ail.com wrote:

Hi all,

Our business is in manufacturing, and we are running SAP on DB2 8.1,
planning on upgrading to 8.2.2 within a couple of months. We are
running operations 24/7, so are looking for ways to reduce our
maintenance impact to our users. Our users hit some specific SAP tables
very heavily, and concurrently, so even doing an online REORG causes
some system slowdown. We are looking for ways to reduce these slowdown
times, because even though we have an outage once per month to catch up
REORGs, we would like to be able to do them during normal business
hours, without impacting system performance.

The idea of using HADR came to mind, along with the Q Replication, as
mentioned in this article, where businesses were using Q Replication to
minimize downtime:

HADR will not support what you are trying to do. The HADR standby is
not usable until it takes over from the primary. Doing this via
replication might be physically possible, but I don't think that you'll
get much support from SAP on this (and replicating thousands of tables
is a nightmare I'd rather not have).

Why don't you look at the ONLINE REORG functionality that was introduced
in V8? Combined with the ability to throttle utilities (like REORG) to
minimize their impact on system performance, you may be able to meet
your goal without introducing extra complexity.
Good luck,

[Steve Pearson (news only)]

> Also, if anyone from IBM reads these groups, are there any plans to

make HADR load balance?

Could you elaborate on what you're looking for? Do you mean to have
the primary and standby servers both active and balance application
workload across them? If so, then there is no plan to support this.

Unlike with traditional replication, where applications have some
support for updating either/any copy of the database, the HADR
architecture is fundamentally a uni-directional replication, and
updates can only occur at the primary.

That said, it is possible to implement read-only access to the standby
in such an architecture, and that is something that's been on our radar
since day one, but is not available in the current releases. The
closest thing to "load balancing" I can think of is the possibility
that readers could be biased to connecting to the standby, and updaters
to the primary. One could also consider some kind of transparent
redirection of updaters from standby to primary in case one does
attempt to connect to an otherwise read-capable standby.

Please note that all of this is purely speculative; at this time IBM
has no announced plans to support any of it.

Regards,
-Steve P.
----------------------
Steve Pearson
DB2 UDB Development
Portland, OR

[derekbarrett]

Steve Pearson (news only) wrote:

Also, if anyone from IBM reads these groups, are there any plans to
make HADR load balance?

Could you elaborate on what you're looking for? Do you mean to have
the primary and standby servers both active and balance application
workload across them? If so, then there is no plan to support this.

Yes, that was what I was curious about.

I don't think it would really add to our situation anyway, it would
probably introduce more complexity and no return on investment.
Thanks for taking the time to answer my question, much appreciated!

[derekbarrett]

Hi Ian,

We are currently doing ONLINE REORG, but there are a couple of specific
tables that it doesn't work so well, it brings certain parts of SAP to
a crawl. However, I was unaware that REORG could be throttled. I knew
that RUNSTATS could do it, but did not know that REORG would. I will
investigate this further, as we are using default settings on REORG.

Very much appreciated!