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.

5 Comments

  1. Ember Crooks

    Nice blog, and nice level of detail. I like the innovation of the second method for rare scenarios.

    I opened an RFE with IBM to try to force the load copy location for the third scenario. Vote for it here:
    http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=83961

    If I ever have the third scenario in a database environment, I run a script or monitor to look at the load copy locations and find any that are not correct on an ongoing basis. They slip in if you’re not careful.

    I also wrote a blog article on how to identify tables on the standby that were inoperative due to an incorrect load copy location: http://db2commerce.com/2017/10/19/avoiding-inoperative-tables-on-the-hadr-standby/

    Reply
    • Raghu

      Hi Ember, thank you for the nice words.
      I voted yes for your RFE and infact I read your blog post.

      Also if you don’t mind would you be able to read and test the following tool and let me know what you think should be improved ?

      https://www.raghu-on-tech.com/2017/12/19/how-to-deep-compare-two-db2-environments/

      I read multiple blog posts of yours on db2commerce.com and they are very informative. Now that I started to blog I can appreciate you even more, as I understand how much time and effort it takes on your part to blog.

      Regards,
      Raghu

      Reply
  2. James

    Hi Raghu,

    This is very informative . We had recently enabled HADR for one our environments and using the third process you mentioned. I had a question here : As LOAD utility generates copy image of an loaded data, how long this image will retain on the shared file system? Do we need to have this LOAD copy image forever ? or is there a way in DB2 /LOAD utitliy to delete it after the data has been loaded to secondary database ?

    We are using DB2LUW – v 10.5 fp8

    Thank you in advance.

    J

    Reply
    • Raghu

      James,

      LOAD copy images should be part of your database recovery strategy. When ever you restore a database and rollforward through transaction logs DB2 looks for these LOAD copy files. If it can’t find them your table will go into drop pending state. So you should keep these files as long as you keep your transaction log files.

      You should manually clean them up along with your transaction log files.
      You can even automate this via DB2… take a look at below two database configuration parameters.

      AUTO_DEL_REC_OBJ
      REC_HIS_RETENTN

      Reply
  3. Andrew Hilden

    Great Post Raghu. DB2 on Cloud which is the IBM managed version of Db2 on the IBM Cloud currently use a deviation of Option 3 where we leverage Cloud Object Storage for LOAD COPY files as well as transaction logs and backups.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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