Replicating Data Load from HADR Primary to Standby server

         In order for a DB2 LOAD to be replicated from an HADR primary server to a standby server, DB2 must be able to access the load data on the standby.  This is done using a shared file system and the COPY YES option on the LOAD command at the primary server. 

         If it is not possible to establish a shared file system, the standby server can be DEACTIVATED ("db2 deactivate database <db>") before the load runs and ACTIVATEd ("db2 activate database <db>") after the load has competed on the primary server and the load file has been manually copied to the standby (eg. via sftp or rcp).  It is important to note that it is NOT the load input file that is copied to the standby, but a specially formatted file which DB2 creates as part of the load process.  

Below is an example using the shared file system, which in this case is NFS.

     In this document, commands on the primary server will be issued at db2inst1@linux and shown in blue text.  Commands issued on the standby server will be issued at db2inst2@linux and shown in red text.

Step 1 – Setup Details

Test Setup:

Primary Server – db2hadr1

Primary Instance – db2inst1

Standby Server – db2hadr2

Standby Instance – db2inst2

NFS Details

db2inst1@linux:~> cat /etc/exports

/db2shared/     db2hadr2(rw,no_root_squash,sync)

db2inst2@linux:~> mount

<snip>

db2hadr1:/db2shared on /db2shared type nfs (rw,addr=192.168.217.151)

Step 2 – Create a test table that looks like the SAMPLE database EMPLOYEE table.  We will call it “EMPCOPY.”

db2inst1@linux:~> db2 create table empcopy like employee

DB20000I  The SQL command completed successfully.

Step 3 – Export some data from the EMPLOYEE table to load into our test table.

db2inst1@linux:~> db2 "export to /db2db/emp.del of del select * from employee"

SQL3104N  The Export utility is beginning to export data to file

"/db2db/emp.del".

SQL3105N  The Export utility has finished exporting "42" rows.

Number of rows exported: 42

Step 4 – LOAD the data utilizing the COPY YES option to copy the data to the shared file system.

db2inst1@linux:~> db2 "load from /db2db/emp.del of del replace into empcopy copy yes to /db2shared"

SQL3109N  The utility is beginning to load data from file "/db2db/emp.del".

SQL3500W  The utility is beginning the "LOAD" phase at time "01/08/2007

15:16:51.723262".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "42" rows were read from the input file.

SQL3519W  Begin Load Consistency Point. Input record count = "42".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time

"01/08/2007 15:16:51.792947".

Number of rows read         = 42

Number of rows skipped      = 0

Number of rows loaded       = 42

Number of rows rejected     = 0

Number of rows deleted      = 0

Number of rows committed    = 42

Step 5 – Check to see if our LOAD data was copied to the standby server.  Note that the timestamps from our load output and the copy file match and the tablespace ID (SAMPLE.x.db2inst1...) is for our tablespace.

Again, it is NOT the load input file that is copied to the standby, but a specially formatted file which DB2 creates as part of the load process. 

db2inst2@linux:~> ls -l /db2shared/

total 2137

-rw-r-----  1 db2inst1 db2grp1 274456 2007-01-08 15:16 SAMPLE.4.db2inst1.NODE0000.CATN0000.20070108151651.001

Step 6 – Look for messages in the db2diag.log on the standby server to verify that the load was executed.  The timestamps will match.

db2inst2@linux:~> less ~/sqllib/db2dump/db2diag.log

2007-01-08-15.26.38.393708-480 I862317G372        LEVEL: Warning

PID     : 3500                 TID  : 366115520   PROC : db2agnti (SAMPLE) 0

INSTANCE: db2inst2             NODE : 000         DB   : SAMPLE

APPHDL  : 0-97

FUNCTION: DB2 UDB, database utilities, sqludcpy, probe:544

DATA #1 : String, 64 bytes

Starting to restore a load copy.

DB2INST1.EMPCOPY.20070108151651

<snip>

2007-01-08-15.26.48.774310-480 I863391G333        LEVEL: Warning

PID     : 3500                 TID  : 366115520   PROC : db2agnti (SAMPLE) 0

INSTANCE: db2inst2             NODE : 000         DB   : SAMPLE

APPHDL  : 0-97

FUNCTION: DB2 UDB, database utilities, sqludcpy, probe:1134

MESSAGE : Load copy restore completed successfully.

Step 7 (Optional) – Just for fun, fail over, check the table and fail back.

db2inst2@linux:~> db2 takeover hadr on db sample

DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.

db2inst2@linux:~> db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUX 9.1.1

 SQL authorization ID   = DB2INST2

 Local database alias   = SAMPLE

db2inst2@linux:~> db2 "select count(*) from db2inst1.empcopy"

1

-----------

         42

  1 record(s) selected.

db2inst1@linux:/db2db> db2 takeover hadr on db sample

DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.

                                                                                                                                                             Written By Craig Maddux

Isolation Levels

Deadlock

Question: How can I trace and capture DB2 database deadlock details? I then want to  view the deadlock details.
Answer: Capturing DB2 deadlock details is straightforward. Use a similar process to below. Basically, you create an Event Monitor , capture some activity into a file, drop the event monitor.

#connect to db

#1-Create event monitor- make sure you've got write permissions

db2 "CREATE EVENT MONITOR dlmon FOR DEADLOCKS WRITE TO FILE '/tmp'"

#2 Turn on event monitordb2

SET EVENT MONITOR dlmon STATE = 1

#3. Turn off event monitordb2 

SET EVENT MONITOR dlmon STATE = 0

#4. Translate event monitor into readable stuff

db2evmon -path /tmp > /tmp/filtered.out

#5. Read the events

vi /tmp/filtered.out

#6. drop

db2 DROP EVENT MONITOR dlmon 










db2iset

db2iset displays, sets, or removes DB2 profile variables and profiles. 

db2iset [ [ [ variable[=[value]] ] [ -g | -i instance [ node-number ] ] ] 

[ -all ] [ -null ] 

[ -r [ instance [ node-number ] ] ] 

[ -d instance [ node-number ] ] 

[ -di instance ] 

[ -a instance [ node-number ] ] 

[ -c | -l | -lr | -dr | -cr ] [ -v ] [ -? | -h ] 

[ -gd aggregate-variable=value ] 

The command options are: 

-g Access the global profile variables. 

-i Specifies the instance profile to use instead of the 

default/current. 

-r Resets the profile registry for the given instance. 

The default/current instance will used if none provided. 

-l List all instance profiles. 

-lr List all supported registry variables. 

-a Add an instance profile to the registry. 

-d Delete an instance profile. 

-di Delete an instance record. 

-c Return the number of registered instance profiles. 

-dr Delete the profiles registry. 

-cr Create the profiles registry. 

-q Query instance existance. 

-v Verbose mode. 

-? Display the command help message. 

-h Same as -? option. 

-all Displays all occurrences of the local environment variables as 

defined in: 

The environment, denoted by [e] 

The node level registry, denoted by [n] 

The instance level registry, denoted by and 

The global level registry, denoted by [g]. 

-null Sets the variables value to null at the specified registry 

level to prevent looking up the value in the next registry 

level as defined in the variable value search order. 

-gd Query the configuration of an aggregate setting.