XXX工商局DB Q复制配置维护手册

1、1、 Q复制规划1.1主机和DB2的相关设置信息descriptionsourcetargetHost nameDbsvr01Dbsvr04Host IP AddressDb2 instance ownerDb1inst1Db1inst1Db2 tcp port5000250002Database server /aliaspronodedssnodeDb2 codepage13861386LogretainYesYesDB ALIAStopicidssdb注意: 在使用复制功能之前，源数据库都应该将日志模式设置为archive logging模式（归档日志模式）1.2Websphere MQ

2、的相关配置信息descriptionsourcetargetQueue ManagerQMSYSAQMSYSBListener port14531454Transmit QSYSA.XMITQSYSB.XMITQSDR channelSYSAtoSYSBSYSBtoSYSASCV channelSYSBtoSYSASYSAtoSYSBRestart QSYSA.RESTARTQAdminQSYSA.ADMINQSYSA.ADMINQSend Q(remote)SYSA.SENDQReceive Q(local)SYSB.RECVQAdminQ(remote)SYSA.ADMINQSpill Q

3、1.3Q复制的配置信息descriptionsourcetargetQ capture schemaASN Q apply schemaASN Q capture path/home/db2inst2/captureQ apply path/home/db2inst2/applyReplication queue map nameSYSAtoSYSBSYSAtoSYSB1.4复制队列映射属性descriptionQCaptureQApply复制队列映射名称SYSAtoSYSBQ Capture服务器topicisQ Apply服务器dssdb发送队列SYSA.SENDQ接收队列SYSB.REC

4、VQ管理队列SYSA.ADMINQSYSA.ADMINQ.1.5通道测试cd /usr/mqm/samp/bin./amqsput SYSA.SENDQ QMSYSA./amqsget SYSB.RECVQ QMSYSB2、mq用户创建创建用户mqm和组mqm,并把组mqm加入到用户db2inst1和db2fenc1中。3、mq software install 解压缩mq软件，用smitty installp安装,创建大小50G的mqmvg 、mqmlv和文件系统mqm,挂载点/var/mqm，/var/mqm/log 4、mq 队列和通道创建在dbsvr04上执行setclock dbsv

5、r01进行与dbsvr01的时间同步，需要在/etc/hosts中添加dbsvr01和IP。由于MQ的需要在dbsvr01和dbsvr04中/etc/hosts添加相互的IP和name 信息。Service ip 和 service name 也要添加。Dbsvr04:/etc/hosts:1 dbsvr04 dbsvr045 dbserver /hacmp 中service IP MQ测试：Dbsvr01:WebSphere MQ queue manager created.Creating or replacing default obje

6、cts for venus.queue.manager.Default objects statistics : 58 created. 0 replaced. 0 failed.Completing setup.Setup completed.$ strmqmWebSphere MQ queue manager 'venus.queue.manager' starting.5 log records accessed on queue manager 'venus.queue.manager' during the log replay phase.Log r

7、eplay for queue manager 'venus.queue.manager' complete.Transaction manager state recovered for queue manager 'venus.queue.manager'.WebSphere MQ queue manager 'venus.queue.manager' started.$ runmqsc5724-H72 (C) Copyright IBM Corp. 1994, 2008. ALL RIGHTS RESERVED.Starting MQSC

8、for queue manager venus.queue.manager.define qlocal (orange.queue) 1 : define qlocal (orange.queue)AMQ8006: WebSphere MQ queue created.end 2 : endOne MQSC command read.No commands have a syntax error.All valid MQSC commands were processed.$ cd /usr/mqm/samp/bin/$ ./amqsput ORANGE.QUEUESample AMQSPUT

9、0 starttarget queue is ORANGE.QUEUEabcddsfsdsdfdsenddbsvr04:WebSphere MQ queue manager created.Creating or replacing default objects for venus.queue.manager.Default objects statistics : 58 created. 0 replaced. 0 failed.Completing setup.Setup completed.$ $ strmqmWebSphere MQ queue manager 'venus.

10、queue.manager' starting.5 log records accessed on queue manager 'venus.queue.manager' during the log replay phase.Log replay for queue manager 'venus.queue.manager' complete.Transaction manager state recovered for queue manager 'venus.queue.manager'.WebSphere MQ queue man

11、ager 'venus.queue.manager' started.$ runmqsc5724-H72 (C) Copyright IBM Corp. 1994, 2008. ALL RIGHTS RESERVED.Starting MQSC for queue manager venus.queue.manager.define qlocal (orange.queue) 1 : define qlocal (orange.queue)AMQ8006: WebSphere MQ queue created. : : : end 2 : endOne MQSC command

12、 read.No commands have a syntax error.All valid MQSC commands were processed.$ cd /usr/mqm/samp/bin$ ./amqsput ORANGE.QUEUESample AMQSPUT0 starttarget queue is ORANGE.QUEUEadsfsdfdsfsdfsdfsSample AMQSPUT0 end$ $ ./amqsget ORANGE.QUEUESample AMQSGET0 startmessage <adsfsdfdsfsdfsdfs>dbsvr01:crtm

13、qm -lc -d SYSA.XMITQ -u DEADLETTER -lp 20 -ls 10 -lf 10240 QMSYSA /创建MQ队列strmqm QMSYSA /起队列管理器endmqlsr -m QMSYSA /停队列管理器ps -ef | grep mq /查看队列管理器nohup runmqlsr -t tcp -p 1453 -m QMSYSA & /起监听ps ef|grep lsr / 查看监听进程endmqm QMSYSA /停队列endmqlsr /停监听dltmqm QMSYSA /删除管理队列runmqsc QMSYSA /起MQ资源DEFINE QR

14、EMOTE('SYSA.SENDQ') RNAME('SYSB.RECVQ') RQMNAME('QMSYSB') XMITQ('SYSA.XMITQ')DEFINE QLOCAL('SYSA.XMITQ') USAGE(XMITQ) MAXDEPTH(1000000) DEFPSIST(YES)DEFINE QLOCAL('DEADLETTER') USAGE(NORMAL) MAXDEPTH(500000)ALTER QMGR DEADQ('DEADLETTER')DEFINE

15、CHL ('SYSAtoSYSB') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('4(1454)') XMITQ('SYSA.XMITQ') DISCINT (0) BATCHSZ (200)START CHANNEL ('SYSAtoSYSB')DEFINE CHL ('SYSBtoSYSA') CHLTYPE(RCVR) TRPTYPE(TCP) BATCHSZ(200)DEFINE QLOCAL('SYSA.ADMINQ') MAXDEPTH(5000

16、00) DEFPSIST(YES)DEFINE QLOCAL('SYSA.RESTARTQ') MAXDEPTH(500000) DEFPSIST(YES)End*runmqsc QMSYSA dis chstatus('SYSAtoSYSB') /显示running 通道状态正常dis chstatus('SYSAtoSYSB') 1 : dis chstatus('SYSBtoSYSA')AMQ8417: Display Channel Status details. CHANNEL(SYSBtoSYSA) CHLTYPE(R

17、CVR) CONNAME(4) CURRENT RQMNAME(QMSYSB) STATUS(RUNNING) SUBSTATE(RECEIVE) dis chstatus('SYSBtoSYSA') 2 : dis chstatus('SYSBtoSYSA') AMQ8417: Display Channel Status details. CHANNEL(SYSBtoSYSA) CHLTYPE(RCVR) CONNAME(4) CURRENT RQMNAME(QMSYSB) STATUS(RUNNING) SUBSTATE(R

18、ECEIVE) *runmqsc QMSYSB dis chstatus('SYSBtoSYSA') runmqchl -c SYSAtoSYSB -m QMSYSA /如果没有错误信息显示，表明该channel成功运行runmqchl -c SYSBtoSYSA -m QMSYSA /如果没有错误信息显示，表明该channel成功运行*修改queue manager的CCSID:strmqmrunmqscdisplay qmgr / 检查当前queue manager的CCSID值alter qmgr ccsid(437)end*dbsvr04:crtmqm -lc -d S

19、YSB.XMITQ -u DEADLETTER -lp 20 -ls 10 -lf 10240 QMSYSBstrmqm QMSYSBnohup runmqlsr -t tcp -p 1454 -m QMSYSB &runmqsc QMSYSBDEFINE QLOCAL('SYSB.RECVQ') MAXDEPTH(1000000) DEFPSIST(YES)DEFINE QREMOTE('SYSA.ADMINQ') RNAME('SYSA.ADMINQ') RQMNAME('QMSYSA') XMITQ('SYS

20、B.XMITQ')DEFINE QLOCAL('SYSB.XMITQ') MAXDEPTH(1000000) USAGE(XMITQ) DEFPSIST(YES)DEFINE QLOCAL('DEADLETTER') USAGE(NORMAL) MAXDEPTH(500000)ALTER QMGR DEADQ('DEADLETTER')DEFINE CHL ('SYSBtoSYSA') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('5(1453)') XMITQ(&#

21、39;SYSB.XMITQ') DISCINT (0) BATCHSZ (200)START CHANNEL ('SYSBtoSYSA') DEFINE CHL ('SYSAtoSYSB') CHLTYPE(RCVR) TRPTYPE(TCP) BATCHSZ(200)DEFINE QMODEL('IBMQREP.SPILL.MODELQ') DEFSOPT(SHARED) MAXDEPTH(1000000) MSGDLVSQ(FIFO) DEFTYPE(PERMDYN)end*nohup runmqlsr -t tcp -p 1454

22、-m QMSYSB & /起监听runmqsc QMSYSB dis chstatus('SYSBtoSYSA') /显示running 通道状态正常 dis chstatus('SYSBtoSYSA') dis chstatus('SYSAtoSYSB') 1 : dis chstatus('SYSAtoSYSB')AMQ8417: Display Channel Status details. CHANNEL(SYSAtoSYSB) CHLTYPE(RCVR) CONNAME(5) CURRENT RQ

23、MNAME(QMSYSA) STATUS(RUNNING) SUBSTATE(RECEIVE) dis chstatus('SYSBtoSYSA') 2 : dis chstatus('SYSBtoSYSA')AMQ8417: Display Channel Status details. CHANNEL(SYSBtoSYSA) CHLTYPE(SDR) CONNAME(5(1453) CURRENT RQMNAME(QMSYSA) STATUS(RUNNING) SUBSTATE(MQGET) XMITQ(SYSB.XMITQ)EN)*修改队列

24、管理器中的字符集命令：首先打开命令行窗口。 在打开后的命令窗口中输入：runmqsc （队列管理器名称），然后按回车。 其后再输入：ALTER QMGR CCSID (字符集代码)，回车，修改成功。 最后输入：DISPLAY QMGR CCSID,查看已经更改过的字符编码集。 DISPLAY QMSTATUS STATUS（查看qmgr的运行状态） 注:1208是UTF-8格式，1381是中文格式。在字符集代码处选择输入，如果这两都不是你需要的，你可以查看MB帮助或MQ帮助，选择合适你的字符集代码。5、创建catalog 库本地复制中心pc、dbsvr01、dbsvr02上创建catalog库

25、。本地复制中心pc上:C:Documents and Settingsadmin>cd C:>db2 list db directorySQL1031N 在指示的文件系统中找不到数据库目录。 SQLSTATE=58031C:>db2 catalog tcpip node pri5 server 50002 / prodnode对应dbsvr01主机DB20000I CATALOG TCPIP NODE 命令成功完成。DB21056W 直到刷新目录高速缓存之后，目录更改才生效。C:>db2 catalog db topicis at node pridnodeDB2000

26、0I CATALOG DATABASE 命令成功完成。DB21056W 直到刷新目录高速缓存之后，目录更改才生效。C:>db2 terminateDB20000I TERMINATE 命令成功完成。C:>db2 connect to topicis user db2inst1 using db2inst1 数据库连接信息 数据库服务器 SQL 授权标识 = DB2INST1 本地数据库别名 = TOPICISC:>db2 catalog tcpip node dssnode remote 4 server 50002 /dssnode对应dbsvr04主机DB

27、20000I CATALOG TCPIP NODE 命令成功完成。DB21056W 直到刷新目录高速缓存之后，目录更改才生效。C:>db2 catalog db topicis as dssdb at node dssnode /创建topicis别名为dssdbDB20000I CATALOG DATABASE 命令成功完成。DB21056W 直到刷新目录高速缓存之后，目录更改才生效。C:>db2 terminateDB20000I TERMINATE 命令成功完成。C:>db2 connect to dssdb user db2inst1 using db2inst1 数

28、据库连接信息 数据库服务器 SQL 授权标识 = DB2INST1 本地数据库别名 = DSSDB删除用 ：db2 uncatalog db dssdb Db2 uncatalog node dssnodedbsvr01上catalog:dbsvr01:$ db2 catalog tcpip node dssnode remote 4 server 50002 / dssnode对应为dbsvr04DB20000I The CATALOG TCPIP NODE command completed successfully.DB21056W Directory changes m

29、ay not be effective until the directory cache is refreshed.$ db2 catalog db topicis as dssdb at node dssnode /创建topicis别名为dssdbDB20000I The CATALOG DATABASE command completed successfully.DB21056W Directory changes may not be effective until the directory cache is refreshed.$ db2 terminateDB20000I T

30、he TERMINATE command completed successfully.$ dbsvr04上catalog:dbsvr04:$ db2 catalog db topicis as dssdb on /home/db2inst1 /创建topicis别名为dssdbDB20000I The CATALOG DATABASE command completed successfully.DB21056W Directory changes may not be effective until the directory cache is refreshed.$ db2 termin

31、ateDB20000I The TERMINATE command completed successfully.$ db2 list db directory System Database Directory Number of entries in the directory = 2Database 1 entry: Database alias = TOPICIS Database name = TOPICIS Local database directory = /home/db2inst1 Database release level = d.00 Comment = Direct

32、ory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =Database 2 entry: Database alias = DSSDB Database name = TOPICIS Local database directory = /home/db2inst1 Database release level = d.00 Comment = Directory entry type = Indirect

33、 Catalog database partition number = 0 Alternate server hostname = Alternate server port number =$ db2 connect to dssdb Database Connection Information SQL authorization ID = DB2INST1 Local database alias = DSSDB5、用户口令配置Dbsvr01:asnpwd initasnpwd add alias topicis id db2inst1 password db2inst1asnpwd

34、add alias dssdb id db2inst1 password db2inst1Dbsvr04:asnpwd initasnpwd add alias topicis id db2inst1 password db2inst1asnpwd add alias dssdb id db2inst1 password db2inst16、db2复制中心：db2rc7.1 capture模式创建 7.2、Q预定创建 7.3创建复制队列映射 7.4、APPLY模式创建 7.5、Q预定单个启动与停止 8、MQ相关脚本8.1 Capture主机中的相关脚本。启动脚本：capture.sh:date

35、#export LD_LIBRARY_PATH=/usr/opt/db2_08_01/libexport LIBPATH=/usr/mqm/lib64:$LIBPATHexport LD_LIBRARY_PATH=$LIBPATHecho $LIBPATHecho $LD_LIBRARY_PATHasnqcap capture_server=topicis capture_schema=ASN capture_path="/home/db2inst1/capture" PWDFILE=asnpwd.aut &#/usr/opt/db2_08_01/bin/asnqc

36、ap startmode=COLD #capture_server=BTPDBS capture_schema=ASN1 #capture_path="/home/db2inst1/capture_data" PWDFILE=asnpwd.aut停止脚本stop.sh:asnqccmd CAPTURE_SERVER=topicis CAPTURE_SCHEMA=ASN stop查看状态脚本status.sh:asnqccmd CAPTURE_SERVER=topicis CAPTURE_SCHEMA=ASN status启mqm脚本：Mqmstart.sh:strmqm Q

37、MSYSArunmqsc QMSYSA < ./startcomponent.mqscnohup runmqlsr -t tcp -p 1453 -m QMSYSA &startcomponent.mqsc:START CHANNEL('SYSAtoSYSB')START CHANNEL('SYSBtoSYSA')END停mqm脚本：Mqmstop.sh:runmqsc QMSYSA < ./stopcomponent.mqscendmqlsr -w -m QMSYSAendmqm -i QMSYSAendmqm -p QMSYSAstopc

38、omponent.mqsc:STOP CHANNEL('SYSBtoSYSA') MODE(FORCE)STOP CHANNEL('SYSBtoSYSA') MODE(FORCE)END8.2 Apply主机中的相关脚本。启动脚本：apply.sh:date#export LD_LIBRARY_PATH=/usr/opt/db2_08_01/lib#export LIBPATH=/usr/lib:/lib:/usr/opt/db2_08_01/lib:/usr/mqm/libexport LIBPATH=/usr/mqm/lib64:$LIBPATHexport

39、 LD_LIBRARY_PATH=$LIBPATHecho $LIBPATHecho $LD_LIBRARY_PATHasnqapp apply_server=dssdb apply_schema=ASN apply_path="/home/db2inst1/apply" PWDFILE=asnpwd.aut &停止脚本stop.sh:asnqacmd APPLY_SERVER=dssdb APPLY_SCHEMA=ASN stop查看状态脚本status.sh:asnqacmd APPLY_SERVER=dssdb APPLY_SCHEMA=ASN status启

40、mqm脚本：Mqmstart.sh:strmqm QMSYSBrunmqsc QMSYSB < ./startcomponent.mqscnohup runmqlsr -t tcp -p 1454 -m QMSYSB &startcomponent.mqsc:START CHANNEL('SYSAtoSYSB')START CHANNEL('SYSBtoSYSA')END停mqm脚本：Mqmstop.sh:runmqsc QMSYSB < ./stopcomponent.mqscendmqlsr -w -m QMSYSBendmqm -i Q

41、MSYSBendmqm -p QMSYSBstopcomponent.mqsc:STOP CHANNEL('SYSBtoSYSA') MODE(FORCE)STOP CHANNEL('SYSBtoSYSA') MODE(FORCE)END9、启动顺序DB2与MQM启动顺序 Db2数据库启动顺序为，先启动hacmp 集群中db2，启动MQ主机上的db2，启动MQ主机上的mq 队列与通道、监听等。启动hacmp 集群中db2主机上的mq 队列与通道、监听等。启动MQ主机上的mq的apply.sh启动hacmp 集群中db2主机上的mq的capture.sh10、相关

42、信息总结ASN7137W MAX_MESSAGE_SIZE偏小程序名：程序标识：发布或 Q 预订 名称 的指定 MAX_MESSAGE_SIZE 对于发送队列 队列名 和发布队列映射或复制队列映射 queue_map_name 可能太小。可能需要 大小 字节的最大值。说明发布队列映射或复制队列映射的 MAX_MESSAGE_SIZE 值（用于指定此发送队列）可能太小，因此不能包括 WebSphere MQ 消息中的一行。如果 Q Capture 程序在运行时发生此问题，那么它将停止。用户响应增大队列映射的 MAX_MESSAGE_SIZE 值。使用 asnqacmd reinitq 命令重新初

43、始化队列映射。alter qmgr MAXMSGL(104857600)db2 "update ASN.IBMQREP_SENDQUEUES set max_message_size = 8192"asnqacmd reinitqSendMQ:db2 "update ASN.IBMQREP_SENDQUEUES set max_message_size = 8192"alter qmgr MAXMSGL(104857600)alter ql(SYSA.XMITQ) MAXMSGL(104857600)dis ql(SYSA.XMITQ) endRecei

44、veMQ:db2 "update ASN.IBMQREP_SENDQUEUES set max_message_size = 8192"dis ql(SYSB.RECVQ)alter qmgr MAXMSGL(104857600) alter ql(SYSB.RECVQ) MAXMSGL(104857600)endQ复制源表增加字段是不能传输到目标表的。复制源表增加字段是不能传输到目标表的，需要在目标库中也添加相同的字段，然后重新做Q预定即可。查看 Q复制ASN系统表状态信息：$ db2 list tables for schema asnTable/View Schema

45、 Type Creation time - - - -$ db2 decribe table ASN.IBMQREP_ADMINMSG$ db2 “select * from ASN. IBMQREP_ADMINMSG”查看schema asn表内容：db2 list tables for schema asn11、关于Q复制故障排查步骤总结：11.1启动11.2查看工作状态查看Q复制当前状态：asnqccmd APPLY_SERVER=dssdb APPLY_SCHEMA=ASN status show detailsasnqccmd CAPTURE_SERVER=topicis CAPTU

46、RE_SCHEMA=ASN status show detailsMQ CAPTURE:$ status.sh2010-02-26-10.12.08.181205 ASN0600I "AsnQCcmd" : "" : "Initial" : Program "mqpubcmd 9.7.1" is starting.2010-02-26-20162 ASN0520I "AsnQCcmd" : "ASN" : "Initial" : The

47、 STATUS command response: "HoldLThread" thread is in the "is waiting" state.2010-02-26-20806 ASN0520I "AsnQCcmd" : "ASN" : "Initial" : The STATUS command response: "AdminThread" thread is in the "is resting" state.2010-02-26-21105 ASN0520I "AsnQCcmd" : "ASN" : "Initial" : The STATUS command response: "P