Raghu On Tech

Pro tips on managing data at scale

How To Perform DB2 Loads in HADR Environment ?

by | Jan 24, 2018 | 5 comments

Problem Statement:

You need to run DB2 LOAD utility in a HADR environment, however DB2 LOAD utility only logs certain kinds of transaction log records and its not enough to replicate the loaded data via transaction logs to the HADR Standby(s). If you would like to learn more about what kind of log records that LOAD utility marks in transaction logs, take a look at the following link from IBM Knowledge Center.

DB2 LOAD Utility Log Records

Since LOAD operation is not completely logged, one need to understand its implications when running the utility in HADR environments. So how do you handle LOAD’s in a HADR environment ?

I can think of a few solutions to get past this situation. Lets discuss them in detail in the following sections.

Solution 1:

I will start with the easy one first. You simply reinitialize the HADR standby(s) with the backup image of the primary database post completion of LOAD. I am not going to show exact commands for this operation since this is a straight forward solution. Following are the sequence of steps one might perform to reinitialize HADR

  • STOP HADR on primary and standby(s).
  • Perform LOAD operation on HADR primary database table(s).
  • Take an online backup of primary database.
  • Restore the backup image on Standby database server(s).
  • Start HADR on standby database(s) and primary database in that sequence.

Solution 2:

Here assuming you have the IBM recommended identical primary and standby servers with respect to hardware and software, we will work towards a solution with out making use of a shared or Network File System. In my case, I created a filesystem dedicated to database backups on my primary and standby virtual machines.

df -H | grep -i db2backup
/dev/mapper/db2vg-db2backup 74G 23G 48G 32% /db2backup

Following are the sequence of steps that one might perform to successfully replicate a LOAD operation on primary to Standby servers without the use of shared file system. All the IP addresses and hostnames you see below are fictional or modified.

Primary:

  • Created a test table for this exercise, this table structure is identical to an existing table.
  • Stop HADR on primary database server. so that you can perform the LOAD on primary. This is needed to ensure that standby won’t keep looking for the LOAD copy file.

Standby:

  • Deactivate the Standby database.
  • Stop HADR on standby database, so that you can perform the LOAD on primary. This is needed to ensure that standby won’t keep looking for the LOAD copy file.

Primary:

  • Declare a cursor to select all from an existing table which is identical to the newly created table in step 1.
  • Load the data from cursor with load copy image saved to /db2backup/db2inst1.
  • SCP/SFTP the LOAD copy image from primary to standby to exact same location on Standby server.

Standby:

  • Start HADR on standby database.

Primary:

  • Start HADR on primary database.
  • Verify both primary and standby are operating in PEER state.

Standby:

  • Ensure that LOAD copy image was restored successfully on HADR standby database. You should see something similar to below in the db2diag.log.
  • Takeover HADR on standby database server.
  • Verify that Takeover command completed successfully and both primary and standby are in PEER state.
  • Finally verify that the number of rows loaded on primary is equal to the count on the new primary.
Now you have seen two different ways to replicate a RECOVERABLE LOAD from HADR primary database to the standby database.

Solution 3:

Now lets take a look at the final and most commonly used solution to replicate RECOVERABLE LOAD’s between primary and standby servers. For this solution you need to have a shared file system or NFS (Network file system) between primary and standby servers. Following are the preparation steps that I performed.

  • On my iMac I used VMWare Fusion pro and built two virtual machines.
  • These two virtual machines act as a primary and standby servers and are built using thunderbolt SSD storage for fast boot time etc.
  •         I also shared my iMac’s drive “/mnt/hgfs” between two virtual machines, which will be visible from both VM’s.

Lets verify that the shared drive is accessible via both primary and standby, a simple df command should help us determine that

Primary:

Standby:

Create a directory on the shared filesystem from either primary or standby servers. In my case since I am already on primary I chose to do it from primary VM.

Primary:

Primary:

Now verify that load copy file is accessible from both primary and standby VM’s.

Primary:

Standby:

Now verify that LOAD copy got replicated to the standby. You can look for the following entry in the db2diag.log on standby.

Standby:

This solution is really simple. However you need to remember that all the projects or ETL teams or whoever that run db2 LOAD utility in a HADR environment must utilize the shared or NFS file system as destination for their LOAD COPY files. If not, you are in for a lot of surprises during failover scenarios.

Shares
Share This

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close