Re: DB2 HADB

"Mark Townsend" <markbtownsend_at_comcast.net> wrote in message news:43869002.4040400_at_comcast.net...
>>>1) True no loss log shipping requires a remote mirrored disk sub system.
>>>If you have a remote mirrored disk sub system then you really don't need
>>>HADR anyhow
>>
>> That is not accurate. With synch mode in HADR, the commit on the primary
>> will not complete until the logs are written to disk on the standby, but
>> this is not shared or mirrored disk. With near synch mode, the commit
>> will guarantee that the replay is written to the log buffer on the
>> standby.
>
> Hmm - I just picked this up from the HADR information center at
> http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0011267.htm
>

The above link is a "suggestion" for improved performance in extreme situations so that transaction information does not need to be sent via the network, and it is not a requirement to use shared disk or disk mirroring. It is not the recommended configuration.

> " * Use a synchronous mirror for no loss support. You can do this using
> modern disk subsystems such as ESS and EMC, or another remote mirroring
> technology. NVRAM cache (both local and remote) is also recommended to
> minimize the performance impact of a disaster recovery situation. "
>
> This is in the section on Log Shipping -
> http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/c0007190.htm
>
> Perhaps this is referring to the pre HADR capabilities ?
>

The above refers to log shipping only, which is not the same as HADR (it was avaialble prior to HADR).

>
>>>2) Index rebuilds on the primary are not done on the standby at the point
>>>they occur. The are done when first accessed after the standby becomes a
>>>primary. So presumably you cannot failover quickly, as immediately after
>>>failover, index rebuilds may be required. Not sure if the index rebuild
>>>in DB2 is an online or offline op.
>>
>>
>> Not true. If you set LOGINDEXBUILD to ON, then index builds (such as from
>> a reorg) are replayed on the standby. You have choice if you want this.
>
> Similarly from the same site
>
> "# When the standby database processes a log record indicating that an
> index rebuild took place on the primary database, the indexes on the
> standby server are not automatically rebuilt. The index will be rebuilt on
> the standby server either at the first connection to the database, or at
> the first attempt to access the index after the standby server is taken
> out of rollforward pending state. It is recommended that the standby
> server be resynchronized with the primary server if any indexes on the
> primary server are rebuilt. "
>

The above refers to log shipping only, which is not the same as HADR (it was avaialble prior to HADR).

>>>3) Any non-logged operation on the primary is not replayed on the
>>>standby. IBM recommends that the standby be rebuilt after all non-logged
>>>ops
>>
>>
>> All SQL (DDL and DML) can be logged except for LOB's that are over 1 GB
>> in size.
>
> Well - except if the table is not logged initially - presumably done for
> large data loads ?
>

Yes, not logged initially can be used for large data loads within a single unit of work (single commit scope) where you don't have a enough log space.

>>>4) Resync/Rebuild requires either a new backup from the primary, or a
>>>copy of the primary, typically using split mirrors.
>>
>>
>> Or a snaphot can be used if one has that technology (such is available
>> from NetApps and others).
>>
>>
>>>5) If the two databases aren't in communication during a fialover - (i.e
>>>perhaps the network has gone down), then you have to rebuild the primary
>>>as a secondary in order to bring it back
>>
>>
>> That depends on the timeout setting you configure. You can configure a
>> very long timeout period if you want to.
>>
>>
>>>5) It doesn't look like you can take backups of the standby and recover
>>>them to the primary
>>
>>
>> Once the standby becomes the primary (when takeover occurs) then you can
>> make a backup. You can acutally make a backup of the standby if you take
>> it out of HADR mode, but synchronization with the primary will cease
>> during that time.
>
> One of the things customers like about Data Guard physical standby is that
> they can offload their production backup cycles to the standby, and use
> any backups from the standby to recover the primary, if required. IBM say
> this is not supported with HADR
> "Backup operations are not supported on the standby database."
>

You cannot backup the standby while it is in peer state as the standby. You can do a DB2 back up the standby if HADR is stopped on the standby. I am not sure why one would want to backup the standby. You can probably do a snaphot of the standby.

>>>6) It doesn't work if the DB2 database is partitioned
>>
>>
>> It will work with range partitioning (in DB2 V9, which is not available
>> yet) but as you state it is not available for partitions that are on
>> mutliple nodes.
>>
>>
>>>7) There is no equivalent to Data Guard logical standby in HADR
>>
>>
>> If you mean read access on the standby, then you are correct, but IBM has
>> said that it will probably be available next year (no guarantees of
>> course).
>
> Logical is more than just read access. A logical standby can be physically
> different from the production (data file layout, for example). IBM
> presumably get close to Logical with some of their supported replication
> architectures, not sure if they have the failover support provided for
> replicated sites, however.
>
>>>8) You need to tell DB2 to log index build operations on the primary in
>>>order for them to be replayed on the secondary. This "means that index
>>>builds might take longer on the primary sytem and that more log space is
>>>required"
>>
>>
>> In number 2 above you said it was not even possible, now you say it takes
>> longer.
>
> Is an index build in DB2 the same operation as an index rebuild ? They are
> slightly different operations in Oracle (in that you build an index when
> data has been loaded, you rebuild an index when it has been invalidated,
> perhaps by a partition switch). The IBM text also seemed to suggest that
> they where different ops as well. When do you rebuild indexes in DB2 (as
> opposed to build ?)
>
> Obviously it takes longer, even on Oracle it takes longer to replay
>> index build operations as they occur. Disk space is ridiculously cheap
>> these days.
>
> Yes, but you don't have to remember to collect extra logging information
> on the primary - it seems that DB2 has a 'fastpath' index build operation
> ?
>
>>>9) New (or changed) Stored procedures and UDFs are not replayed to the
>>>secondary
>>
>>
>> You can't change a SP or UDF in DB2 (drop and recreate). I was not aware
>> that a new SP would not replay, but I will check this out. I don't recall
>> reading that in the doc.
>
> "HADR does not replicate stored procedure and UDF object and library
> files. You must create the files on identical paths on both the primary
> and standby databases. If the standby database cannot find the referenced
> object or library file, the stored procedure or UDF invocation will fail
> on the standby database."
>

I just now tested this. If you create a SP on the primary, and do a takeover (primary and standby switch roles) the SP is accessible on the new primary (the old standby). I don't know what they mean by "libraries" or "object" but I have several problems with the HADR docs, which are sometimes confusing. This is a good example.

>>>10) Online re-orgs on the primary generate lots of log files, which slow
>>>down the primary. Offline re-orgs on the primary generate less log files
>>>(and in some cases only one), with the result that the standby may fall
>>>behind the primary - while the re-org is happening on the primary,
>>>nothing is happening on the standby, once the re-org finishes on the
>>>primary, and transaction work starts again, the secondary needs to start
>>>and finish the reorg befor eit can continue normal ops. If the re-org
>>>takes a long time, then the standby is this amount of time behind the
>>>primary
>>
>>
>> This is a repeat of number 8. You make it sound like Dataguard can do
>> these things with no performance lag, which is ridiculous.
>
> Here's the IBM text
>
> "While offline reorganizations are not logged as extensively as online
> reorganizations, operations are typically logged per hundreds or thousands
> of affected rows. This means that the standby database could fall behind
> because it waits for each log record and then replays many updates at
> once. If the offline reorganization is non-clustered, a single log record
> is generated after the entire reorganization operation. This mode has the
> greatest impact on the ability of the standby database to keep up with the
> primary database. The standby database will perform the entire
> reorganization after it receives the log record from the primary
> database."
>
> I guess the difference is in the term offline re-org - what does in mean
> in DB2, and when would you do ? What is an online re-org, and when would
> you do it ?

An on-line reorg is done while the table is fully accessible in read/write mode to applications.

The bottom line is that index reorgs or rebuilds are replayed on the standby. Received on Thu Nov 24 2005 - 22:33:54 CST