You have setup your databases in HADR operation and you are wondering how my DB2 LOAD will transfer from one machine to another since DB2 LOAD operation is not logged. Well, the DB2 LOAD operation is of course logged but not the actual contents. So, how do I get my LOAD transferred to the standby machine.
DB2 LOAD differs from Oracle SQL*Loader direct path in this way since DB2 LOAD is also recoverable. Oracle’s SQL*Loader direct path (equivalent to DB2 LOAD) is not recoverable. So, if you are using Data Guard in Oracle 10g, you are out of luck if you are using SQL*Loader in direct path on your primary Oracle instance.
DB2 LOAD operates in 2 modes.
- Recoverable
- Non-recoverable
When you use DB2 in RECOVERABLE mode, DB2 LOAD creates a copy of the LOAD image at a location that you specify and this LOAD operation is logged. When HADR transfers this LOAD operation from one database to another, the HADR on standby database picks up the DB2 LOAD copy from the location that you specified on your primary machine. The key here is that the location of the LOAD copy should be accessible to both primary and standby databases. You can create a common SAN mount point and use same location name at both the machine.
Sample LOAD Command in Recoverable mode
LOAD FROM “/data/incoming/orders.dat” OF DEL
MODIFIED BY COLDEL|
MESSAGES “/data/incoming/msg/orders.dat”
REPLACE INTO orders STATISTICS USE PROFILE
COPY YES TO “/hadrpair/data”
INDEXING MODE AUTOSELECT;
The key is to use COPY YES command to let DB2 LOAD create an image of the data that it is loading in orders table on primary database. If /hadrpair/data is also available to the standby machine, you will get the LOAD transferred to the standby machine.
The recoverable LOAD is not only meant for HADR but they are useful for your regular databases if you use LOAD very frequently to load the data in your tables.
For DB2 database to recover completely, you have to take care of followings:
- DB2 Backup
- DB2 Active Log files
- DB2 Archive Log files since last backup
- DB2 LOAD copies if using LOAD in recoverable mode by using COPY YES option in LOAD