Request More Information

We are hiring!

Xtivia, Inc.
2013 North America Liferay Partner of the Year

Join our top-notch team of Enterprise Java and Liferay Portal professionals in our Austin, TX office or work remotely! Please check out our current Dice listings.

Recent Bloggers

Vamshidhar Srikantapuram
Posts: 6
Stars: 0
Date: 7/18/14
Luke Smith
Posts: 14
Stars: 0
Date: 7/17/14
Bob Dietrich
Posts: 7
Stars: 0
Date: 7/1/14
Richard Ngo
Posts: 1
Stars: 0
Date: 6/19/14
Matt Wolinski
Posts: 1
Stars: 0
Date: 6/5/14
Kaushik Nagaraj
Posts: 8
Stars: 0
Date: 5/22/14
Nick Ortman
Posts: 2
Stars: 0
Date: 5/9/14
Ron Peacock
Posts: 4
Stars: 0
Date: 5/9/14
Chris Shaw
Posts: 4
Stars: 0
Date: 5/7/14
Paul Anderson
Posts: 1
Stars: 0
Date: 5/6/14
« Back

Loading data in a DB2 HADR environment

Importing data in DB2 HADR takes some extra considerations.  For one, non-logged activity is not replicated to the Standby database.  So if you use the LOAD command to import data into a table on the Primary database, the same table on the Standby database will go offline because it knows that is not in sync.

You can import using the INSERT command which is logged and then replayed on the standby.  But what if you have a HUGE table and the INSERT is very slow and will likely fill up your transaction logs?

There is a way to use the LOAD command with the COPY YES option.   The LOAD command is much faster than INSERT and it only logs the operation and not every row of data.   I've found the easiest way to use this is to have a NFS mounted filesystem between the Primary and Standby database.  (This is also very useful when moving backup images and other files between the Primary and Standby). 

Mount the NFS filesystem on the Standby with the exact same name as the mount point on the Primary.  Here is an example of the LOAD command with the COPY YES option.  My NFS mount is called /export/loads on both servers.

 db2 "LOAD from tabname.del of del messages loadmsg.out replace into schema.tabname COPY YES to /export/loads"

This command will put a file on the NFS mount.  As it is loaded on the Primary, the Standby will see that a table load is happening and will look to the same filesystem name for the file.  As long as that NFS mount point is named the same and the db2 instance owner has access to it, DB2 will read that file on the Standby and load the data on the Standby database.  You can verify it worked by reading from the diagnostic log on the Standby.  It should say something like this:

 

 2012-02-16-06.33.55.744401+060 I5856991E473        LEVEL: Warning
PID     : 16228                TID  : 47032572045632PROC : db2sysc
INSTANCE: instname            NODE : 000          DB   : DBNAME
APPHDL  : 0-24769              APPID: *LOCAL.DB2.120209225426
EDUID   : 121                  EDUNAME: db2agent (DBNAME)
FUNCTION: DB2 UDB, database utilities, sqludcpy, probe:553
DATA #1 : String, 68 bytes
Starting to restore a load copy.
SCHEMA.TABNAME.20120216063341

2012-02-16-06.35.47.671101+060 I5857465E430        LEVEL: Warning
PID     : 16228                TID  : 47032572045632PROC : db2sysc
INSTANCE: instname             NODE : 000          DB   : DBNAME
APPHDL  : 0-24769              APPID: *LOCAL.DB2.120209225426
EDUID   : 121                  EDUNAME: db2agent (DBNAME)
FUNCTION: DB2 UDB, database utilities, sqludcpy, probe:1136
MESSAGE : Load copy restore completed successfully. 

 

Comments
Trackback URL:

Anonymous User
HADR replicates load with certain restrictions. Because load data is not embedded in the log stream, the standby can only get the data from a load copy. Thus load is replicated only if there is a load copy. The copy must be available to the standby when it replays the load. The standby may attempt to access the copy any time after the primary completes the load. I recommend that a shared copy device such as NFS be used. Second, If you transfer the copy by means like physically transferring a tape, it is recommended that the standby be stopped ("db2 deactivated database") before the primary starts the load. Once the primary finishes the load and the copy is available to the standby, restart the standby ("db2 activate database"). The standby will then reconnect to the primary and replay the load.
When the primary does a load with COPY NO option, by default the load is automatically converted to NONRECOVERABLE.

When standby replays the load, the table is marked as invalid. COPY NO loads can be optionally converted to COPY YES via the "DB2_LOAD_COPY_NO_OVERRIDE" registry variable.

Since you have to restore on the standby; the fastest way would be to load on the primary - then backup online with logs and restore on the standby. Thereby the PRIMARY is never offline. You can check status of HADR using the following command:

#> db2pd -d <dbname> -hadr
Posted on 2/16/12 5:27 PM.

Request More Information