db2 mysql复制_DB2 Q复制实施方案

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(db2igrp1) 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机创建如下文件系统：

文件系统名

大小

裸设备名

描述

/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.1DB2UDB安装

DB2 UDB版本情况：

$db2level

DB21085IInstance "db2inst1" uses "64" bits and DB2 code release "SQL09013" with 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.2WebSphere 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 -awsrs.lic

在B机注册Q复制服务器license

$su–db2inst1

$db2licm -awsrs.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 CCZFDB2 remote 192.168.0.202 server 50020

$db2 catalog db vhfsres at node CCZFDB1

$db2 catalog db vhfsdst at node CCZFDB2

$db2 terminate

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

$su–db2inst2

$db2 catalog tcpip node CCZFDB1 remote 192.168.0.201 server 50010

$db2 catalog tcpip node CCZFDB2 remote 192.168.0.202 server 50020

$db2 catalog db vhfsres at node CCZFDB1

$db2 catalog db vhfsdst at node CCZFDB2

$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.log

创建QMCAP队列和通道的脚本crt_cap_obj.tst内容：

DEFINEQLOCAL('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) SHAREMSGDLVSQ(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) 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(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内容：

DEFINEQLOCAL('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) SHAREMSGDLVSQ(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('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')

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。

在源数据库中执行capture控制表脚本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;

#2Creating 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 IN UW TBSPACE TBSCAP;

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

#4Ending 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 vhfsres user db2inst1 using db2inst1

$db2–tvfqcapmap.sql

$db2 connect to vhfsdst user db2inst2 using db2inst2

$db2–tvfqappmap.sql

crt_map.txt脚本内容如下：

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

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

#3Ending 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，在目标数据库中执行applyQ预订脚本qappsub.sql。

操作步骤如下：

$su-db2inst1

$cd /caplog

$asnclp-f crt_q_order.txt

$db2 connect to vhfsres user db2inst1 using db2inst1

$db2-tvf qcapsub.sql

$db2 connect to vhfsdst user db2inst2 using db2inst2

$db2–tvf qappsub.sql

crt_q_order.txt内容：

#1Setting the environment.

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

# definitions for the Q subscription 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 "db2inst1";

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

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

E 2);

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

#3Ending 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 vhfsdst 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 statement is required. The commands run immediately and send

# results to the command window and log.

ASNCLP SESSION SET TO Q REPLICATION;

SET LOG "qchecks.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;

# 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 receive 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 REPLQMAP 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

$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–tvfq_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 "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 "q_start.sql" TARGET SCRIPT "q_start1.sql";

START QSUB for SUBNAME like "SUB%";

7.常见问题

问题一：

ASN2270EThe 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组。