DB2 for Linux, UNIX and Windows Availability Topics

HADR Automation with dual NIC cards

Dale_McInnis ‎ 935 Views

It has been quite a while since I posted, going forward you will see this blob becoming much more active.

I am now in a new role in the Tech Sales organization for IBM Analytics, supporting all North American sales teams. I continue to focus primarily on DB2, in all of its new forms - on-prem, in the cloud, or in an appliance form factor.

I will now use this blog to document solutions to issues I encounter while deploying systems at customer sites or deploying in the cloud.

The first such entry involves enabling automation of HADR Takeovers, yes I still focus on resiliency.

While setting up a system for one of my clients I came across an interesting limitation that appears to be poorly documented.

If you are setting up TSA to automate HADR takeovers on an environment that is using dual NICs, one for public access and one for private HADR log transmission, you can not use a HOSTNAME for the private network. You must use the actually IP address, which should not be an issue as your apps will never use this IP address to connect to the DB.

If you are having challenges setting up HADR automation please let me know, I am working on an updated DB2HAICU white paper. We also have an excellent HADR PoT that focuses on TSA automation that can be provided at your site.

Thanks

Dale

HADR Monitoring tips

Dale_McInnis ‎ 2,327 Views

When monitoring a HADR system I would recommend you start with the following as a minimum set.

HADR_PEER_STATE
- Anything but PEER = Sev 1
HADR_LOG_GAP
- current_value / MAX log gap x 90% = 90,000,000
- (Max Log Gap: 5min recover, 20M/Min )
HADR_CONNECT_STATUS
- Anything but CONNECTED = Sev 1
STANDBY_RECV_BUF_PERCENT
- > 90% = Sev1
STANDBY_SPOOL_PERCENT
- > 10% = Sev 3
- > 50% = Sev 2
- > 90% = Sev 1

Modified on by Dale_McInnis

Data Deduplication and Encryption

Dale_McInnis ‎ 2 Comments ‎ 2,781 Views

It has been determined that the release of DB2's native encryption feature on the backup utility is incompatible with the data deduplication option on backup utility.

The reason for this is that when encryption is enabled on the backup, the utility will read the data from disk, un-encrypt the data, re-encrypt the data utilizing a random key as part of the algorithm and then send the data to the backup target device. The result is that each backup of the same data will not be binary identical, which is what the data deduplication device requires to indentify data that is had already processed.

Backup and Restore Statistics

Dale_McInnis ‎ 1 Comment ‎ 6,164 Views

Want to determine why your backup is running slowly - Check out the backup and restore statistics what is now on be default as of V 10.2 FP2. This feature was available as of V 9.7 by using the DB2_BAR_STATS registry variable

Backup and restore statistics

Each successful backup and restore operation generates a single record in the db2diag.log file, which provides information on the performance of that operation. The log record is informational and is dumped at diaglevel 3 (the default) and 4.

Example

The log records for backup and restore statistics consist of a row for each backup and restore buffer manipulator (db2bm) EDU and a row for each backup and restore media controller (db2med) EDU:

2012-07-30-15.41.30.012922-240 E15775E1464           LEVEL: Info
PID     : 15882                TID : 46913126656320  KTID : 16001
PROC    : db2sysc
INSTANCE: krodger              NODE : 000            DB   : SAMPLE
APPHDL  : 0-18                 APPID: *LOCAL.krodger.120730194119
AUTHID  : KRODGER              HOSTNAME: hotel74
EDUID   : 49                   EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, database utilities, sqluxLogDataStats, probe:377
MESSAGE : Performance statistics
DATA #1 : String, 951 bytes

Number of buffers = 4
Buffer size       = 16781312 (4097 4kB pages)
                                                                
BM#     Total      I/O      MsgQ      WaitQ     Buffers    kBytes 
---  --------  --------  --------  --------    --------  --------
000      6.30      0.02      0.00      6.18           4       640
001      5.88      4.48      0.00      1.33           9    139536
---  --------  --------  --------  --------    --------  --------
TOT     12.18      4.51      0.00      7.51          13    140176

MC#     Total      I/O      MsgQ      WaitQ     Buffers    kBytes
---  --------  --------  --------  --------    --------  --------
000      6.36      0.34      5.94      0.00           9    114748
001      6.29      0.18      5.60      0.10           6     81944
---  --------  --------  --------  --------    --------  --------
TOT     12.66      0.53     11.55      0.10          15    196692

The meanings of the various columns are as follows:

BM: The db2bm EDU ID
Total: Length of time that each EDU existed
I/O: Time that was spent performing read or write I/O, to or from stable storage
MsgQ: Time that was spent waiting for an I/O buffer
WaitQ: Time that was spent waiting for a state machine control message.
Buffers: Number of I/O buffers that were processed
KBytes: Quantity of data that was processed
MC: the db2med EDU ID

Example

For compressed backups, the log record contains two additional columns for performance information about the compression operation:

2012-07-30-15.41.47.228766-240 E38419E1913           LEVEL: Info
PID     : 15882                TID : 46913126656320  KTID : 16081
PROC    : db2sysc
INSTANCE: krodger              NODE : 000            DB   : SAMPLE
APPHDL  : 0-29                 APPID: *LOCAL.krodger.120730194132
AUTHID  : KRODGER              HOSTNAME: hotel74
EDUID   : 80                   EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, database utilities, sqluxLogDataStats, probe:377
MESSAGE : Performance statistics
DATA #1 : String, 1399 bytes

Number of buffers = 4
Buffer size       = 16781312 (4097 4K pages)
                                                                       Compr          
BM#    Total     I/O   Compr     MsgQ     WaitQ   Buffers    kBytes    kBytes 
---  -------  ------  ------  -------  --------  --------  --------  --------
000    12.08    4.36    7.18     0.00      0.37         5    139536    144941
001    11.87    0.01    0.01     0.00     11.79         1       640       640
---  -------  ------  ------  -------  --------  --------  --------  --------
TOT    23.96    4.38    7.19     0.00     12.17         6    140176    145581

MC#    Total     I/O             MsgQ     WaitQ   Buffers   kBytes
---  -------  ------  ------  -------  --------  --------  --------  --------
000    12.07    0.11            11.76      0.00         4     49168
001    12.10    0.07            11.84      0.15         4     32808
---  -------  ------          -------  --------  --------  --------
TOT    24.18    0.19            23.61      0.15         8     81976

Compr: Time that was spent performing the compression operation
Compr Bytes: Quantity of uncompressed data that was compressed

DB2 Dedup paper published on developerworks

Dale_McInnis ‎ 3,136 Views

I'm proud to announce the publication of my article on developerWorks: "Integrated support for data deduplication devices in DB2 for Linux, UNIX, and Windows".

Summary: Learn how data deduplication offers benefits such as reducing storage requirements, improving backup performance, and reducing traffic across your network. This article explains the basic technology of deduplication, discusses the various methodology's that you can use, and shows how DB2® for Linux®, UNIX®, and Windows® has been changed to support various deduplication devices. Finally, the article includes the results of benchmark testing to illustrate how effective the Tivoli® Storage Manager server data deduplication feature can be with DB2. http://www.ibm.com/developerworks/data/library/techarticle/dm-1302db2deduplication/index.html

I hope you like it and find it useful. If you have feedback, I'd love to hear it.

TSM deduplication test results

Dale_McInnis Tags: tsm db2 backup dedup ‎ 4 Comments ‎ 6,298 Views

I have been running some experiments with TSM 6.2's native deduplication capabilities. Here are some results from my first run, they are quite impressive.

Dedup test results
			6-Dec-12
DB2 BACKUP DB PERF USE TSM OPEN 8 SESSION DEDUP_DEVICE				Utilizing both CLIENT and SERVER side deduplication
Start	End	Elapsed Time	Actual DB Size in bytes	Dedup Size sent to server in bytes	% Savings in bandwidth
12:22:57 AM	2:32:54 AM	2:09	1.35891E+11	1.3574E+11	0.11%
2:33:02 AM	3:04:13 AM	0:31	1.35891E+11	3501526595	97.42%
3:04:19 AM	3:34:07 AM	0:29	1.35891E+11	1439490051	98.94%
3:34:13 AM	4:03:14 AM	0:29	1.35891E+11	875452538	99.36%
4:03:20 AM	4:32:45 AM	0:29	1.35891E+11	1015678830	99.25%

DB2 BACKUP DB PERF USE TSM OPEN 8 SESSION DEDUP_DEVICE				Utilizing both CLIENT and SERVER side deduplication
Start	End	Elapsed Time	Actual DB Size in bytes	Dedup Size sent to server in bytes	% Savings in bandwidth
10:29:22 PM	2:05:51 AM	3:36	1.35891E+11	1.3574E+11	0.11%
2:05:59 AM	2:37:17 AM	0:31	1.35891E+11	3258729375	97.60%
2:37:23 AM	3:05:53 AM	0:28	1.35891E+11	1602671422	98.82%
3:06:00 AM	3:35:27 AM	0:29	1.35891E+11	1337909163	99.02%
3:35:33 AM	4:04:32 AM	0:28	1.35891E+11	967988939	99.29%

DB2 BACKUP DB PERF USE TSM OPEN 8 SESSION				Utilizing both CLIENT and SERVER side deduplication
Start	End	Elapsed Time	Actual DB Size in bytes	Dedup Size sent to server in bytes	% Savings in bandwidth
11:27:16 AM	4:40:14 PM	5:12	1.35901E+11	1.35762E+11	0.10%
4:40:14 PM	5:10:12 PM	0:29	1.35901E+11	5735212149	95.78%
5:10:12 PM	5:39:45 PM	0:29	1.35901E+11	4192184764	96.92%
5:39:45 PM	6:08:26 PM	0:28	1.35901E+11	3486134412	97.43%
6:08:26 PM	6:37:29 PM	0:29	1.35901E+11	3136679820	97.69%

Using the DEDUP_DEVICE option on the backup invocation will make a difference, particularly when setting a new baseline backup.

Multiple Standby White Paper

Dale_McInnis Tags: hadr multiple standby ‎ 1 Comment ‎ 5,856 Views

Here is a recently published white paper on HADR Multiple Standby supported added to db2 v10

http://www.ibm.com/developerworks/data/library/long/dm-1206hadrmultiplestandby/index.html

Enjoy

Quick Business Continuity Survey

Dale_McInnis ‎ 2,770 Views

DB2 Business Continuity Survey

We would ask that you consider participating in a very quick survey which will provide you the ability to directly influence our future product feature list. This survey will focus on two main areas which affect your systems’ availability, planned and unplanned outages. Please provide sufficient detail so that we can understand the business scenario.
Please list your top 5 most disruptive planned outages – in priority order
1.
2.
3.
4.
5.
Please list your top 5 most disruptive unplanned outages - in priority order
1.
2.
3.
4.
5.
Please provide the top 3 disruptive planned or unplanned outages (from above) in priority order
1.
2.
3.
Please list the most recent 5 issues that caused an interruption to the business you support
1.
2.
3.
4.
5.
What availability features would you like to see in the product that could help expand the use of DB2 in your company?
1.
2.
3.
4.
5.
Please send your completed survey to dmcinnis@ca.ibm.com. This information will not be shared with other customers; it is solely for the purpose of product planning. All company specific information will be removed when the results are rolled up.

HADR and TSA information pointers

Dale_McInnis Tags: hadr db2haicu tsa ‎ 6,250 Views

Here is a collection of TSA and HADR white papers

1) Here is a white paper that discusses cross site HADR automation:
https://www.ibm.com/developerworks/data/library/long/dm-1002automateddb2hadr/

2) Here is a white paper that discusses TSA shared disk automation
http://www.ibm.com/developerworks/data/library/long/dm-0909hasharedstorage/index.html

3) HADR Best Practices White paper:
http://public.dhe.ibm.com/software/dw/dm/db2/bestpractices/DB2BP_HADR_0911.pdf

4)Using DB2HAICU
http://public.dhe.ibm.com/software/dw/data/dm-0908hadrdb2haicu/HADR_db2haicu.pdf
https://www.ibm.com/developerworks/mydeveloperworks/blogs/nsharma/resource/HADR-db2haicu_v5.pdf?lang=en

IDUG NA - HADR configuration change propogation

Dale_McInnis Tags: hadr configuration ‎ 3,461 Views

Question:

Does HADR propagate the database configuration changes made on the primary to the standby(s)

Response:

HADR will propagate any operation that is logged. Unfortunately changes to both the database manager and database configuration parameters are not logged, thus they are not propagated from the primary to the standby

Question from IDUG NA - HADR Load Support

Dale_McInnis Tags: load hadr ‎ 3,990 Views

question:

how does load work in an HADR environment

Response:

there are 3 types of load
1) load non recoverable - in this case the table on the standby will be taken offline
2) load copy no - also in this case the table on the standby will be taken offline

3) load copy yes - when the load commit log record gets processed on the standby the load copy image MUST be accessible on the standby. this can be done by using a shared device - e.g NFS mount or TSM.

Our recommendation is to the new V10 INGEST option rather than load - INGEST is a fully logged operation and as such is completely compatible with HADR. Ingest does not require a V 10 server, only a V 10 client.

Questions from IDUG NA

Dale_McInnis Tags: lobs hadr ros ‎ 3,218 Views

This week is the IDUG North American 2012 conference in Denver CO.

Over the next week I will be posting questions and the associate answers from questions from customers that occur either during my sessions or from hallway conversations.

Question:
Has an change been made to HADR RoS for access the LOB data

Response:

As of DB2 v 9.7 FP5 we now supporting returning any INLINE LOB on queries run on the standby. This feature also applies to INLINE XML data as well.

This is also available in DB2 10

Pointers to DB2 HADR V 10 documentation

Dale_McInnis ‎ 2,953 Views

Log Archiving in an HADR environment

Dale_McInnis Tags: db2 archiving hadr log ‎ 1 Comment ‎ 10,334 Views

Log archiving in an HADR environment
It is recommended that both the primary database and the standby databases be configured for log archiving, as any of these systems could be promoted to become the primary at which point it will be required to archive its log files.

Only the current primary database can perform log archiving. If the primary and standby databases are set up with separate archiving locations, logs are archived only to the primary database's archiving location. In the event of a takeover, the standby database becomes the new primary database and any logs archived from that point on are saved to the original standby database's archiving location. In such a configuration, logs are archived to one location or the other, but not both; with the exception that following a takeover, the new primary database might archive a few logs that the original primary database had already archived. In a multiple standby system, the archived log files can be scattered among all databases' (primary and standbys) archive devices. A shared archive is preferred because all files are stored in a single location.

Many operations need to retrieve archived log files. These operations include: database roll forward, the HADR primary database retrieving log files to send to the standby database in remote catch up, and replication programs (such as Q Replication) reading logs. As a result, a shared archive for an HADR system is preferred, otherwise, the needed files can be distributed on multiple archive devices, and user intervention is needed to locate the needed files and copy them to the requesting database. The recommended copy destination is an archive device. If copying into an archive is not feasible, copy the logs into the overflow log path. As a last resort, copy them into the log path (but one should be aware that there is a risk of damaging the active log files). DB2® does not auto delete user copied files in the overflow and active log path, so one should manually remove the files when they are no longer needed by any HADR standby or any application.

Log file management on standby
The standby database automatically manages log files in its log path. The standby database does not delete a log file from its local log path unless it has been notified by the primary database that the primary database has archived it. This behavior provides added protection against the loss of log files. If the primary database fails before the log file is safely stored in the archive, the standby database would ensure the log file is archived. If both the logarchmeth1 and logarchmeth2 configuration parameters are in use, the standby database does not recycle a log file until the primary database has archived it using both methods.

Dale McInnis

DB2 V 10 HADR Time Delayed Apply on Standby

Dale_McInnis Tags: v hadr delay db2 10 time ‎ 5,934 Views

HADR delayed replay helps prevent data loss due to errant transactions. To implement HADR delayed replay, set the hadr_replay_delay database configuration parameter on the HADR standby database.

Delayed replay intentionally keeps the standby database at a point in time that is earlier than that of the primary database by delaying replay of logs on that standby. If an errant transaction is executed on the primary, one has until the configured time delay has elapsed to take action to prevent the errant transaction from being replayed on the standby. To recover the lost data, one can either copy this data back to the primary, or one can have the standby take over as the new primary database.

Delayed replay works by comparing timestamps in the log stream, which is generated on the primary, and the current time of the standby. As a result, it is important to synchronize the clocks of the primary and standby databases. Transaction commit is replayed on the standby according to the following equation:

(current time on the standby - value of the hadr_replay_delay configuration parameter) >=

timestamp of the committed log record

One should set the hadr_replay_delay database configuration parameter to a large enough value to allow time to detect and react to errant transactions on the primary.

One can use this feature in either single standby mode or multiple standby mode. In multiple standby mode, typically one or more standbys stays current with the primary for high availability or disaster recovery purposes, and one standby is configured with delayed replay for protection against errant transactions.

There are several important restrictions for delayed replay:

One can set the hadr_replay_delay configuration parameter only on a standby database.
A TAKEOVER command on a standby with replay delay enabled will fail. One must first set the hadr_replay_delay configuration parameter to 0 and then deactivate and reactivate the standby to pick up the new value, and then issue the TAKEOVER command.
The delayed replay feature is supported only in SUPERASYNC mode. Because log replay is delayed, a lot of unreplayed log data might accumulate on the standby, filling up receive buffer and spool (if configured). In other synchronization modes, this would cause the primary to be blocked.

If one enables delayed replay, it is recommended that log spooling is also enabled by setting the hadr_spool_limit database configuration parameter. Because of the intentional delay, replay position can be far behind log receive position on standby. Without spooling, log receive can only go beyond replay by the amount of the receive buffer. With spooling enabled the standby can receive much more logs beyond the replay position, providing greater protection against data loss in case of primary failure. Note that in either case, because of the mandatory SUPERASYNC mode, the primary will not be blocked by enabling delayed replay.

Feed for Blog Entries Feed for Blog Comments

Blogs