DB2 Q复制实施手册

sunfloweremryologistData Management

Oct 31, 2013 (3 years and 8 months ago)

749 views

中联软件产品技术支持

ganquan




1




11/1/2013



DB2 Q
复制实施方案

1.

系统环境准备

1.1

用户和组

创建如下用户,用户的属组如下所示:

A
机:源数据库


uid=301(db2inst1) gid=301(db2igrp1) groups=1(staff),501
(mqm)


uid=303(db2fusr1) gid=302(db2fgrp1) groups=1(staff),501
(mqm)


uid=501(mqm) gid=501(mqm) groups=1(staff)


B
机:目标数据库


uid=302(db2inst2) gid=301(db
2igrp1) groups=1(staff),501
(mqm)


uid=304(db2fusr2) gid=302(db2fgrp1) groups=1(staff),501
(mqm)


uid=501(mqm) gid=501(mqm) groups=1(staff)

1.2

文件系统


A
机创建如下文件系统:

文件系统名

大小

裸设备名

描述

⽣慰汯/

㄰1

/
摥瘯捡灬潧汶


⽶慲⽭煭⽑䵃䅐



⽤敶⽱浣慰汶


⽶慲⽭煭⽑䵃䅐⽬潧



⽤敶⽱浣慰汯杬/




B
机创建如下文件系统:

文件系统名

大小

裸设备名

描述

⽡灰汯/

㄰1

⽤敶⽡灰汯杬/


⽶慲⽭煭⽑䵁偐



⽤敶⽱浡灰汶


⽶慲⽭煭⽑䵁偐⽬潧



⽤敶⽱浡灰汯杬/



2.

软件安装

2.1

DB2UDB
安装

DB2 UDB
版本情况:

$db2level

DB21085I Instance "db2inst1" uses "64" bits and DB2 code release
"SQL09013" with
中联软件产品技术支持

ganquan




2




11/1/2013



level identifier "01040107".

Informational tokens are "DB2 v9.1.0.3", "s070719", "U811590", and Fix Pack "3".
Product is installed at "/opt/IBM/db2/V9.1".

2.2

WebSphere MQ
安装

$dspmqver

Name: WebSphere MQ

Version: 6.0.1.1

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
机上创建数据库,目前数据库名都为
vhfs



3.2

配置编目信息


A
机的
db2inst1
用户进行如下编目
:

$su


db2inst1

$db2 catalog tcpip node
CCZFDB1

remote
192.168.0.201

server
50010

$db2 catalog tcpip node
CCZFDB
2 remote
192.168.0.20
2 server
500
2
0

$
db2 catalog db vhfsres at node
CCZFDB1

$db2 catalog db vhfsdst at node
CCZFDB
2

$db2 terminate



B
机的
db2inst2
用户进行如
下编目
:

$su


db2in
st2

中联软件产品技术支持

ganquan




3




11/1/2013



$db2 catalog tcpip node
CCZFDB1

remote
192.168.0.201

server
50010

$db2 catalog tcpip node
CCZFDB
2 remote
192.168.0.20
2 server
500
2
0

$db2 catalog db vhfsres at node
CCZFDB1

$db2 catalog db vhfsdst at node
CCZFDB
2

$db2 terminate


4.

WebSpher
e 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
'
)

MAXM
SGL(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) M
AXDEPTH(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('172.16.10.105(1415)') XMITQ ('QMAPP') DISCINT (0) HBINT(300)

DEFINE CHL ('QMAPP_TO_QMCAP') CHLTYPE(RCVR) TRPTYPE(TCP) HBINT(300)

DEFINE QMODEL('IBMQREP.SPILL.MODELQ') DEFSOPT(SHARED) MA
XDEPTH(500000)
MSGDLVSQ(FIFO) DEFTYPE(PERMDYN) MAXMSGL(100000)

ALTER QMGR MAXUMSGS(10000) MAXMSGL(4194403)

define listener(listener1) trptype(TCP) control(MANUAL) IPADDR('172.16.10.105')
中联软件产品技术支持

ganquan




4




11/1/2013



PORT(1414)

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
'
)

MA
XMSGL(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(419440
3) 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('QM
CAP') 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') DEFPSIS
T(YES)

DEFINE CHL ('QMAPP_TO_QMCAP') CHLTYPE(SDR) TRPTYPE(TCP)
CONNAME('172.16.10.105(1414)') XMITQ ('QMCAP') DISCINT (0) HBINT(300)

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(100000)

ALTER QMGR MAXUMSGS(10000) MAXMSGL(4194403)

define listener(listener1) trptype(TCP) control(MANUAL) IPADDR('172.16.10.105')
PORT(1415)

start listener(listener1)

START CHL ('QMAPP_TO_QMCAP')



中联软件产品技术支持

ganquan




5




11/1/2013



4.2

刷新用户安全信息


A
机刷新实例用户的
MQ
安全信息:

$su
-

mqm

$runmqsc QMCAP



refresh security


end



B
机刷新实例用户的
MQ
安全信息:

$su
-

mqm

$runmqsc QMAPP



refresh security


end

5.

实施
DB2 Q
复制

5.1

创建
Q
capture

Q
apply
控制表

使用
initii.txt
脚本产生
capture
控制表脚本
qcapctrl.sql

apply
控制表脚本
qappctrl.sql


在源数据库中执行
captur
e
控制表脚本
qcapctrl.sql
,在目标数据库中执行
apply
控制表
脚本
qappctrl.sql


操作步骤如下:

$su


db2inst1

$cd /caplog

$asnclp

f initii.txt

$db2 connect to vhfsres user db2inst1 using db2inst1

$db2

tvf qcapctrl.sql

$db2 connect to vhfsdst user db2inst2 using db2inst2

$db2

tvf qappctrl
.sql

Initii.txt
脚本内容:



#set environment

ASNCLP SESSION SET TO Q REPLICATION;

SET LOG "qcontrol.err";

SET SERVER CAPTURE TO DB vhfsres ID db2inst1 PASSWORD "db2inst1";

SET QMANAGER "QMCAP" FOR CAPTURE SCHEMA;

SET CAPTURE SCHEMA SOURCE ASN;

SET SERVER TARGET

TO DB vhfsdst ID db2inst2 PASSWORD "db2inst2";

SET QMANAGER "QMAPP" FOR APPLY SCHEMA;

SET APPLY SCHEMA ASN;

SET OUTPUT CAPTURE SCRIPT "qcapctrl.sql" TARGET SCRIPT "qappctrl.sql";

SET RUN SCRIPT LATER;

中联软件产品技术支持

ganquan




6




11/1/2013




# 2 Creating Q Capture control tables.

CREATE CONTR
OL TABLES FOR CAPTURE SERVER USING

RESTARTQ "ASN.QMCAP.RESTARTQ" ADMINQ "ASN.QMCAP.ADMINQ"

MEMORY LIMIT 64 MONITOR INTERVAL 600000 IN UW TBSPACE TBSCAP;


# 3 Creating Q Apply control tables.

# This command specifies a password file, asnpwd.aut. The Q App
ly 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
ca
pture
队列映射脚本
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 vhfsres user db2inst1 using db2inst1

$db2

tvf
qcapmap
.sql

$db2 c
onnect to vhfsdst user db2inst2 using db2inst2

$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 vhfsres ID db2inst1 PASSWORD "db2inst1";

SET CAPTURE
SCHEMA SOURCE ASN;

SET SERVER TARGET TO DB vhfsdst ID db2inst2 PASSWORD "db2inst2";

SET APPLY SCHEMA ASN;

SET OUTPUT CAPTURE SCRIPT "qcapmap.sql" TARGET SCRIPT "qappmap.sql";

SET RUN SCRIPT LATER;


中联软件产品技术支持

ganquan




7




11/1/2013



# 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;


# 3 Ending the ASNCLP session.


QUIT;



5.3

创建
Q
预订

使用
crt_q_order.txt
脚本产生
Q capture

Q
预订
脚本
qcapsub.sql

Q apply

Q
预订
脚本
qappsub.sql


在源数据库中执行
Q capture

Q
预订
脚本
qcapsub.sql
,在目标数据库中执行
apply
Q
预订
脚本
qappsub.sql


操作步骤如下:

$su
-

db2inst1

$cd /caplog

$asnclp
-
f crt_q_order
.txt

$db2 connect to vhfsres user db2inst1 using db2
inst1

$db2
-
tvf
qcapsub
.sql

$db2 connect to vhfsdst user db2inst2 using db2inst2

$db2

tvf
qappsub.sql


crt_q_order.txt
内容:

# 1 Setting the environment.

# The SET OUTPUT command creates two SQL scripts: qcapsub.sql, which adds

# definitions for the Q subs
cription to the Q Capture control tables, and

# qappsub.sql, which adds definitions for the Q subscription to the Q Apply

# control tables.


ASNCLP SESSION SET TO Q REPLICATION;

SET LOG "qsub.err";

SET SERVER CAPTURE TO DB vhfsres ID db2inst1 PASSWORD "db2
inst1";

SET CAPTURE SCHEMA SOURCE ASN;

SET SERVER TARGET TO DB vhfsdst ID db2inst2 PASSWORD "db2inst2";

SET APPLY SCHEMA ASN;

SET OUTPUT CAPTURE SCRIPT "qcapsub.sql" TARGET SCRIPT "qappsub.sql";

SET RUN SCRIPT LATER;


CREATE QSUB USING REPLQMAP S_ASN_TO_T_
ASN(SUBNAME SUB_cisunitbasinfo VHFS.cisuni

中联软件产品技术支持

ganquan




8




11/1/2013



tbasinfo OPTIONS HAS LOAD PHASE I exist TARGET NAME VHFS.cisunitbasinfo LOAD TYP

E 2);



#you can add a lot of
Q sub of table that you want


# 3 Ending the ASNCLP session.


QUIT;

5.4

口令配置


A
机配置口令:

$su


db2inst1

$
cd /caplog

$asnpwd init

$asnpwd add alias vhfsres id db2inst1 password db2inst1

$asnpwd add alias vhfsdst id db2inst2 password db2inst2



B


$su


db2inst2

$cd /applog

$asnpwd init

$asnpwd add alias vhfsres id db2inst1 password db2inst1

$asnpwd add alias v
hfsdst id db2inst2 password db2inst2


6.

常用
DB2 Q
复制命令

6.1

检查
WebsphereMQ
环境

使用
check_mq
.txt
脚本
检查
WebSphere MQ
环境


操作步骤如下:

$su


db2inst1

$cd /caplog

$asnclp

f

check_mq.txt

确保
命令执行后没有错误输出。


WebSphere MQ
环境检查脚本
check_mq.txt
内容:

# 1 Setting the environment.

# No SET RUN stat
ement is required. The commands run immediately and send

# results to the command window and log.

中联软件产品技术支持

ganquan




9




11/1/2013



ASNCLP SESSION SET TO Q REPLICATION;

SET LOG "qchecks.err";

SET SERVER CAPTURE TO DB vhfsres ID db2inst1 PASSWORD "db2inst1";

SET QMANAGER "QMCAP" FOR CA
PTURE SCHEMA;

SET CAPTURE SCHEMA SOURCE ASN;

SET SERVER TARGET TO DB vhfsdst ID db2inst2 PASSWORD "db2inst2";

SET QMANAGER "QMAPP" FOR APPLY SCHEMA;

SET APPLY SCHEMA ASN;


# 2 Checking the queue managers and queues.

# These commands check whether the
queue managers and queues exist, and validate

# their settings against the requirements for Q replication. If errors are detected,

# you must correct them before you start the Q Capture and Q Apply programs.

VALIDATE WSMQ ENVIRONMENT FOR CAPTURE SCHEMA;


VALIDATE WSMQ ENVIRONMENT FOR APPLY SCHEMA;

VALIDATE WSMQ ENVIRONMENT FOR REPLQMAP S_ASN_TO_T_ASN;


# 3 Sending test messages.

# This command puts a test message on the send queue, ASN1.QM1_TO_QM2.DATAQ,

# and tries to get the message from the receiv
e queue, ASN1.QM1_TO_QM2.DATAQ.

# The command also puts a test message on the Q Apply administration queue,

# ASN1.QM1.ADMINQ, and tries to get the message from the Q Capture administration

# queue, ASN1.QM1.ADMINQ.


VALIDATE WSMQ MESSAGE FLOW FOR REPL
QMAP S_ASN_TO_T_ASN;


# 4 Ending the ASNCLP session.

QUIT;

6.2

启动
caputer

apply


A

启动
caputer

$su
-

db2inst1

$ nohup asnqcap capture_server=VHFS startmode=cold capture_path="/caplog"
logreuse=y logstdout=y memory_limit=512 &



B

启动
apply

$su
-

db2inst2

$ nohup

asnqapp apply_server=VHFS apply_path="/applog" logreuse=y &

6.3

停止
caputer

apply


A
机停止
capture

中联软件产品技术支持

ganquan




10




11/1/2013



$su
-

db2inst1

$asnqccmd CAPTURE_SERVER=vhfs STOP



B
机停止
apply

$su
-

db2inst2

$asnqacmd apply_server=vhfs STOP

6.4

启动
Q
预订

$su


db2inst1

$cd /caplog

$asnclp

f

q_start
.txt

$db2 connect to vhfsres user db2inst1 using db2inst1

$db2

tvf
q
_start
.sql


WebSphere MQ
环境检查脚本
q_start
.txt
内容:

ASNCLP SESSION SET TO Q REPLICATION;

SET LOG "start_q_sub.err";

SET SERVER CAPTURE TO DB vhfsres ID db2inst1 PASSWORD "db2inst1";

SET QMANAGER "QM
CAP" FOR CAPTURE SCHEMA;

SET CAPTURE SCHEMA SOURCE ASN;

SET SERVER TARGET TO DB vhfsdst ID db2inst2 PASSWORD "db2inst2";

SET QMANAGER "QMAPP" FOR APPLY SCHEMA;

SET APPLY SCHEMA ASN;

SET OUTPUT CAPTURE SCRIPT "q_start.sql" TARGET SCRIPT "q_start1.sql";

STAR
T QSUB for SUBNAME like "SUB%";


7.

常见问题

问题一:


ASN2270E The stored procedure "ASN.ADMINIF" in database "VHFSDST" is not author

ized to access the WebSphere MQ queue manager "CCZFDB2_SVC
-

QMAPP"because the o

perating system user ID "db2inst2", which is the
DB2 fenced user of the instance


that contains this database, is not a member of the operating system group for

WebSphere MQ applications (usually mqm) at the host "CCZFDB2_SVC".


分析:


需要把
db2 fence
用户属于
mqm
组。