DB2 HADR LOAD操作

原创

wx62e28ac222a20 2022-07-30 00:04:17 ©著作权

文章标签 python sql webgl nodejs es 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任

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. https://www.cndba.cn/hbhe0316/article/4787

If it is not possible to establish a shared file system, the standby server can be DEACTIVATED (“db2 deactivate database “) before the load runs and ACTIVATEd (“db2 activate database “) 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. This will be shown in our example. https://www.cndba.cn/hbhe0316/article/4787

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.https://www.cndba.cn/hbhe0316/article/4787

一、如果有共享存储

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.

二、如果没有共享存储
如果没有设置A、B、C、D机的共享存储

[db2inst1@db01 backup]$ db2 "select * from t1"

ID          NAME      
----------- ----------
          1 a         
          2 b         
          3 c         
          4 d         
          5 e         
          6 f         
          7 g         
          8 h         
          9 i         
         10 k         

  10 record(s) selected.

db2 "create table t3 like t1"
db2 "export to t1.ixf of ixf select * from t1"

则在B、C、D执行命令，

db2 deactivate db <dbname>

在A机执行

db2 "load from t1.ixf of ixf insert into t3 copy yes to /db2data/backup"

将A机的/db2data/backup下生成的TESTDB.4.db2inst1.DBPART000.20200501232805.001文件拷贝至B、C、D机

scp TESTDB.4.db2inst1.DBPART000.202005012328* db02:/db2data/backup/
scp TESTDB.4.db2inst1.DBPART000.202005012328* db03:/db2data/backup/
scp TESTDB.4.db2inst1.DBPART000.202005012328* db04:/db2data/backup/

启动B、C、D机
db2 activate db

三、模拟在A机执行了LOAD操作

[db2inst1@db02 backup]$ db2 "create table t4 like t1"
[db2inst1@db02 backup]$ db2 "load from t1.ixf of ixf insert into t4"

这个时候切换至B、C、D访问t4表，发现t4表已经失效
在B上执行：

[db2inst1@db02 backup]$ db2 takeover hadr on db testdb
[db2inst1@db02 backup]$ db2 connect to testdb
[db2inst1@db02 backup]$ db2 "select * from t4"
ID          NAME      
----------- ----------
SQL1477N  For table "DB2INST1.T4" an object "7" in table space "2" cannot be 
accessed.  SQLSTATE=55019

[db2inst1@db02 db2share]$ db2 load query table t4
Tablestate:
  Unavailable
[db2inst1@db02 db2share]$ db2 "select distinct varchar(tabname,30) from sysibmadm.admintabinfo where available =  'N'"

1                             
------------------------------
T5                            

  1 record(s) selected.

在A上执行命令：

[db2inst1@db01 backup]$ db2 takeover hadr on db testdb
[db2inst1@db01 backup]$ db2 connect to testdb

在B、C、D执行命令：

db2 deactivate db <dbname>


[db2inst1@db02 backup]$ db2 "load from t1.ixf of ixf replace into t4 copy yes to /db2data/backup"

将A机下/db2data/bacup下的TESTDB.4.db2inst1.DBPART000.20200501234512.001文件拷贝至B、C、D机

启动B、C、D机

db2 activate db <dbname>

再次在B机上切换HADR，发现t4表已经可用

[db2inst1@db02 backup]$ db2 takeover hadr on db testdb
[db2inst1@db02 backup]$ db2 connect to testdb
[db2inst1@db02 backup]$ db2 "select * from t4"

ID          NAME      
----------- ----------
          1 a         
          2 b         
          3 c         
          4 d         
          5 e         
          6 f         
          7 g         
          8 h         
          9 i         
         10 k

Linux,oracle