Re: DB2 HADB

From: Mark Townsend <markbtownsend_at_comcast.net>
Date: Thu, 24 Nov 2005 20:16:02 -0800
Message-ID: <43869002.4040400@comcast.net>

Mark A wrote:
> "Mark Townsend" <markbtownsend_at_comcast.net> wrote in message
> news:43868262.3090901_at_comcast.net...
>

>>A quick analysis of HADR from IBM's own doc shows that

>
>

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

" * 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 ?

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

>>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 ?

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

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

>>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 ? Received on Thu Nov 24 2005 - 22:16:02 CST