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.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Comment