1.
系统环境准备
1.1
用户和组
创建如下用户,用户的属组如下所示:
A机:源数据库
10.68.22.231
uid=301(db2inst1) gid=301(db2ig
rp1) groups=1(staff),501(mqm)
uid=303(db2fusr1) gid=30
3
(db2fgrp1) groups=1(staff),501(mqm)
uid=501(mqm) gid=501(mqm) groups=1(staff)
uid=305(dasusr1) gid=305(dasgrp1) groups=1(staff),501(mqm)
B机:目标数据库
10.68.22.23
2
uid=302(db2inst2) gid=30
2
(db2igrp
2
) groups=1(staff),50
2
(mqm)
uid=304(db2fusr2) gid=30
4
(db2fgrp
2
) groups=1(staff),50
2
(mqm)
uid=50
2
(mqm) gid=50
2
(mqm) groups=1(staff)
uid=306(dasusr2) gid=306(dasgrp2) groups=1(staff),502(mqm)
1.2
文件系统
在A机创建如下文件系统:
文件系统名 |
大小 |
裸设备名 |
描述 |
/caplog |
10G |
/dev/caploglv |
|
/var/mqm/QMCAP |
3G |
/dev/qmcaplv |
|
/var/mqm/QMCAP/log |
7G |
/dev/qmcaploglv |
|
在B机创建如下文件系统:
文件系统名 |
大小 |
裸设备名 |
描述 |
/applog |
10G |
/dev/apploglv |
|
/var/mqm/QMAPP |
3G |
/dev/qmapplv |
|
/var/mqm/QMAPP/log |
7G |
/dev/qmapploglv |
|
2.
软件安装
2.1
DB2UDB安装
DB2 UDB版本情况:
$db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09013" with level identifier "01040107".
Informational tokens are "DB2 v9.
5
.0.
7
", "s070719", "U811590", and Fix Pack "
7
". Product is installed at "/db2/V9.
5
".
2.2
WebSphere MQ安装
$dspmqver
Name: WebSphere MQ
Version: 6.0.
0
.
0
CMVC level: p600-101-060504
BuildType: IKAP - (Production)
2.3
注册Q复制服务器license
在A机注册Q复制服务器license
$su
–
db2inst1
$db2licm -a wsrs.lic
在B机注册Q复制服务器license
$su
–
db2inst1
$db2licm -a wsrs.lic
3.
DB2 UDB配置
3.1
介绍数据环境
在A机和B机上创建数据库,数据库名都为
BPFDB,apply
3.2
配置编目信息
在A机的db2inst1用户进行如下编目:
$su
–
db2inst1
$db2 catalog tcpip node
CCZFDB1
remote
10.68.22.231
server
50010
$db2 catalog tcpip node
CCZFDB
2 remote
10.68.22.232
server
500
2
0
$db2 catalog db
APPLY
at node
CCZFDB
2
$db2 terminate
在B机的db2inst2用户进行如下编目:
$su
–
db2inst2
$db2 catalog tcpip node
CCZFDB1
remote
10.68.22.231
server
50010
$db2 catalog tcpip node
CCZFDB
2 remote
10.68.22.232
server
500
2
0
$db2 catalog db
BPFDB
at node
CCZFDB1
$db2 terminate
4.
WebSphere MQ配置
4.1
创建MQ对象
4.1.1.
A机MQ配置
$su - mqm
$env MQSPREFIX=
/var/mqm/QMCAP
$crtmqm -ld
/var/mqm/QMCAP/log
-lc -lf 15000 -lp 30 -ls 10 QMCAP
$strmqm QMCAP
$ runmqsc QMCAP <crt_cap_obj.tst >crt_cap_obj.log
创建QMCAP队列和通道的脚本crt_cap_obj.tst内容:
DEFINE QLOCAL('DEADQ') MAXMSGL(4194403) MAXDEPTH(5000) ALTER QMGR DEADQ('DEADQ') DEFINE QLOCAL('ASN.QMCAP.RESTARTQ') DEFPSIST(YES) PUT(ENABLED) GET(ENABLED) MAXMSGL(4194403) MAXDEPTH(5000) DEFINE QLOCAL('ASN.QMCAP.ADMINQ') DEFPSIST(YES) PUT(ENABLED) GET(ENABLED) MAXMSGL(4194403) MAXDEPTH(5000) DEFINE QLOCAL('ASN.QMAPP_TO_QMCAP.DATAQ') DEFPSIST(YES) SHARE MSGDLVSQ(PRIORITY) MAXMSGL(4194403) MAXDEPTH(5000) DEFINE QREMOTE('ASN.QMAPP.ADMINQ') RNAME('ASN.QMAPP.ADMINQ') RQMNAME('QMAPP') XMITQ('QMAPP') DEFPSIST(YES) DEFINE QLOCAL('QMAPP') USAGE(XMITQ) DEFPSIST(YES) TRIGGER TRIGTYPE(FIRST) TRIGDATA(QMCAP_TO_QMAPP) INITQ(SYSTEM.CHANNEL.INITQ) MAXMSGL(4194403) MAXDEPTH(5000) DEFINE QREMOTE('ASN.QMCAP_TO_QMAPP.DATAQ') RNAME('ASN.QMCAP_TO_QMAPP.DATAQ') RQMNAME('QMAPP') XMITQ ('QMAPP') DEFPSIST(YES) DEFINE CHL ('QMCAP_TO_QMAPP') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME(' DEFINE CHL ('QMAPP_TO_QMCAP') CHLTYPE(RCVR) TRPTYPE(TCP) HBINT(300) DEFINE QMODEL('IBMQREP.SPILL.MODELQ') DEFSOPT(SHARED) MAXDEPTH(500000) MSGDLVSQ(FIFO) DEFTYPE(PERMDYN) MAXMSGL( ALTER QMGR MAXUMSGS(10000) MAXMSGL(4194403) define listener(listener1) trptype(TCP) control(MANUAL) IPADDR(' start listener(listener1) START CHL ('QMCAP_TO_QMAPP')
|
4.1.2.
B机MQ配置
创建MQ队列管理器(QMAPP):
$su - mqm
$env MQSPREFIX=
/var/mqm/QMAPP
$crtmqm -ld
/var/mqm/QMAPP/log
-lc
-
lf 15000 -lp 30 -ls 10 QMAPP
$strmqm QMAPP
$ runmqsc QMAPP < crt_app_obj.tst>crt_app_obj.log
创建QMAPP队列和通道的脚本crt_app_obj.tst内容:
DEFINE QLOCAL('DEADQ') MAXMSGL(4194403) MAXDEPTH(5000) ALTER QMGR DEADQ('DEADQ') DEFINE QLOCAL('ASN.QMAPP.RESTARTQ') DEFPSIST(YES) PUT(ENABLED) GET(ENABLED) MAXMSGL(4194403) MAXDEPTH(5000) DEFINE QLOCAL('ASN.QMAPP.ADMINQ') DEFPSIST(YES) PUT(ENABLED) GET(ENABLED) MAXMSGL(4194403) MAXDEPTH(5000) DEFINE QLOCAL('ASN.QMCAP_TO_QMAPP.DATAQ') DEFPSIST(YES) SHARE MSGDLVSQ(PRIORITY) MAXMSGL(4194403) MAXDEPTH(5000) DEFINE QREMOTE('ASN.QMCAP.ADMINQ') RNAME('ASN.QMCAP.ADMINQ') RQMNAME('QMCAP') XMITQ('QMCAP') DEFPSIST(YES) DEFINE QLOCAL('QMCAP') USAGE(XMITQ) DEFPSIST(YES) TRIGGER TRIGTYPE(FIRST) TRIGDATA(QMAPP_TO_Q1) INITQ(SYSTEM.CHANNEL.INITQ) MAXMSGL(4194403) MAXDEPTH(5000) DEFINE QREMOTE('ASN.QMAPP_TO_QMCAP.DATAQ') RNAME('ASN.QMAPP_TO_QMCAP.DATAQ') RQMNAME('QMCAP') XMITQ('QMCAP') DEFPSIST(YES) DEFINE CHL ('QMAPP_TO_QMCAP') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME(' DEFINE CHL ('QMCAP_TO_QMAPP') CHLTYPE(RCVR) TRPTYPE(TCP) HBINT(300) DEFINE QMODEL('IBMQREP.SPILL.MODELQ') DEFSOPT(SHARED) MAXDEPTH(500000) MSGDLVSQ(FIFO) DEFTYPE(PERMDYN) MAXMSGL( ALTER QMGR MAXUMSGS(10000) MAXMSGL(4194403) define listener(listener1) trptype(TCP) control(MANUAL) IPADDR(' start listener(listener1) START CHL ('QMAPP_TO_QMCAP')
|
4.2
刷新用户安全信息
在A机刷新实例用户的MQ安全信息:
$su - mqm
$runmqsc QMCAP
refresh security
end
在B机刷新实例用户的MQ安全信息:
$su - mqm
$runmqsc QMAPP
refresh security
end
5.
实施DB2 Q复制
以下内容建议使用windows
客户端编目后的
db2rc
复制中心实施。
在DB2RC
的所在客户端惊醒
DB2
编目:
db2 => catalog tcpip node CCZFDB1 remote 10.68.22.231 server 50010
DB20000I CATALOG TCPIP NODE 命令成功完成。
DB21056W 直到刷新目录高速缓存之后,目录更改才生效。
db2 => catalog tcpip node CCZFDB2 remote 10.68.22.232 server 50020
DB20000I CATALOG TCPIP NODE 命令成功完成。
DB21056W 直到刷新目录高速缓存之后,目录更改才生效。
db2 => catalog db APPLY at node CCZFDB2
DB20000I CATALOG DATABASE 命令成功完成。
DB21056W 直到刷新目录高速缓存之后,目录更改才生效。
db2 => db2 catalog db BPFDB at node CCZFDB1
SQL0104N 在
"
语句开始
"
后面找到异常标记
"db2"
。预期标记可能包括:
"SELECT"
。
SQLSTATE=42601
db2 => catalog db BPFDB at node CCZFDB1
DB20000I CATALOG DATABASE 命令成功完成。
DB21056W 直到刷新目录高速缓存之后,目录更改才生效。
db2 => terminate
DB20000I TERMINATE 命令成功完成。
C:/IBM/SQLLIB/BIN>
5.1
创建Q capture和Q apply控制表
使用initii.txt脚本产生capture控制表脚本qcapctrl.sql和apply控制表脚本qappctrl.sql。
在源数据库中执行capture控制表脚本qcapctrl.sql,在目标数据库中执行apply控制表脚本qappctrl.sql。
操作步骤如下:
$su
–
db2inst1
$cd /caplog
$asnclp
-
f initii.txt
$db2 connect to
BPFDB
user db2inst1 using
123456
$db2
–
tvf qcapctrl.sql
$db2 connect to
APPLY
user db2inst2 using
123456
$db2
–
tvf qappctrl.sql
Initii.txt脚本内容:
#set environment ASNCLP SESSION SET TO Q REPLICATION; SET LOG "qcontrol.err"; SET SERVER CAPTURE TO DB SET QMANAGER "QMCAP" FOR CAPTURE SCHEMA; SET CAPTURE SCHEMA SOURCE ASN; SET SERVER TARGET TO DB SET QMANAGER "QMAPP" FOR APPLY SCHEMA; SET APPLY SCHEMA ASN; SET OUTPUT CAPTURE SCRIPT "qcapctrl.sql" TARGET SCRIPT "qappctrl.sql"; SET RUN SCRIPT LATER;
# 2 Creating Q Capture control tables. CREATE CONTROL TABLES FOR CAPTURE SERVER USING RESTARTQ "ASN.QMCAP.RESTARTQ" ADMINQ "ASN.QMCAP.ADMINQ" MEMORY LIMIT 64 MONITOR INTERVAL 600000
# 3 Creating Q Apply control tables. # This command specifies a password file, asnpwd.aut. The Q Apply progam uses this # file to connect to the Q Capture server when it loads the target table. CREATE CONTROL TABLES FOR APPLY SERVER USING PWDFILE "asnpwd.aut" IN UW TBSPACE TBSAPP;
# 4 Ending the ASNCLP session. QUIT;
|
5.2
创建Q复制队列映射
使用crt_map.txt脚本产生Q capture队列映射脚本
qcapmap
.sql和Q apply队列映射脚本
qappmap
.sql。
在源数据库中执行Q capture复制队列脚本
qcapmap
.sql,在目标数据库中执行apply复制队列脚本
qappmap
.sql。
操作步骤如下:
$su
–
db2inst1
$cd /caplog
$asnclp
–
f crt_map.txt
$db2 connect to
BPFDB
user db2inst1 using
123456
$db2
–
tvf
qcapmap
.sql
$db2 connect to
APPLY
user db2inst2 using
123456
$db2
–
tvf
qappmap
.sql
crt_map.txt脚本内容如下:
# 1 Setting the environment. ASNCLP SESSION SET TO Q REPLICATION; SET LOG "rqmap.err"; SET SERVER CAPTURE TO DB SET CAPTURE SCHEMA SOURCE ASN; SET SERVER TARGET TO DB SET APPLY SCHEMA ASN; SET OUTPUT CAPTURE SCRIPT "qcapmap.sql" TARGET SCRIPT "qappmap.sql"; SET RUN SCRIPT LATER;
# 2 Creating a replication queue map. CREATE REPLQMAP S_ASN_TO_T_ASN USING ADMINQ "ASN.QMCAP.ADMINQ" RECVQ "ASN.QMCAP_TO_QMAPP.DATAQ" SENDQ "ASN.QMCAP_TO_QMAPP.DATAQ" NUM APPLY AGENTS 8 HEARTBEAT INTERVAL 5; |