工商局复制配置维护基础手册.doc

1、 Q复制规划
1.1主机和DB2有关设立信息
description
source
target
Host name
Dbsvr01
Dbsvr04
Host IP Address
10.0.1.45
10.0.1.44
Db2 instance owner
Db1inst1
Db1inst1
Db2 tcp port
50002
50002
Database server /alias
pronode
dssnode
Db2 codepage
1386
1386
Logretain
Yes
Yes
DB ALIAS
topici
dssdb
注意：在使用复制功能之前，源数据库都应当将日记模式设立为archive logging模式（归档日记模式）
1.2Websphere MQ有关配备信息
description
source
target
Queue Manager
QMSYSA
QMSYSB
Listener port
1453
1454
Transmit Q
SYSA.XMITQ
SYSB.XMITQ
SDR channel
SYSAtoSYSB
SYSBtoSYSA
SCV channel
SYSBtoSYSA
SYSAtoSYSB
Restart Q
SYSA.RESTARTQ

AdminQ
SYSA.ADMINQ
SYSA.ADMINQ
Send Q(remote)
SYSA.SENDQ

Receive Q(local)

SYSB.RECVQ
AdminQ(remote)

SYSA.ADMINQ
Spill Q

IBMQREP.SPILL.MODELQ
1.3Q复制配备信息
description
source
target
Q capture schema
ASN

Q apply schema

ASN
Q capture path
/home/db2inst2/capture

Q apply path

/home/db2inst2/apply
Replication queue map name
SYSAtoSYSB
SYSAtoSYSB
1.4复制队列映射属性
description
QCapture
QApply
复制队列映射名称
SYSAtoSYSB
Q Capture服务器
topicis

Q Apply服务器

dssdb
发送队列
SYSA.SENDQ

接受队列

SYSB.RECVQ
管理队列
SYSA.ADMINQ
SYSA.ADMINQ.
1.5通道测试
cd /usr/mqm/samp/bin
./amqsput SYSA.SENDQ QMSYSA
./amqsget SYSB.RECVQ QMSYSB

2、mq顾客创立
创立顾客mqm和组mqm,并把组mqm加入到顾客db2inst1和db2fenc1中。
3、mq software install
解压缩mq软件，用smitty installp安装,创立大小50Gmqmvg 、mqmlv和文献系统mqm,挂载点/var/mqm，/var/mqm/log
4、mq 队列和通道创立
在dbsvr04上执行setclock dbsvr01进行与dbsvr01时间同步
，需要在/etc/hosts中添加dbsvr01和IP。
由于MQ需要在dbsvr01和dbsvr04中/etc/hosts添加互相IP和name 信息。Service ip 和 service name 也要添加。
Dbsvr04:
/etc/hosts:
10.0.1.41 dbsvr01
10.0.1.44 dbsvr04
10.0.1.45 dbserver //hacmp 中service IP
MQ测试：
Dbsvr01:
$ crtmqm -q venus.queue.manager
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.
$ strmqm
WebSphere MQ queue manager 'venus.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 manager 'venus.queue.manager' started.
$ runmqsc
5724-H72 (C) Copyright IBM Corp. 1994，. 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 ：end
One MQSC command read.
No commands have a syntax error.
All valid MQSC commands were processed.
$ cd /usr/mqm/samp/bin/
$ ./amqsput ORANGE.QUEUE
Sample AMQSPUT0 start
target queue is ORANGE.QUEUE
abcddsfsdsdfds
end
dbsvr04:
$ crtmqm -q venus.queue.manager
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.
$ $ strmqm
WebSphere MQ queue manager 'venus.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 manager 'venus.queue.manager' started.
$ runmqsc
5724-H72 (C) Copyright IBM Corp. 1994，. 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 ：end
One MQSC command read.
No commands have a syntax error.
All valid MQSC commands were processed.
$ cd /usr/mqm/samp/bin
$ ./amqsput ORANGE.QUEUE
Sample AMQSPUT0 start
target queue is ORANGE.QUEUE
adsfsdfdsfsdfsdfs

Sample AMQSPUT0 end
$
$ ./amqsget ORANGE.QUEUE
Sample AMQSGET0 start
message <adsfsdfdsfsdfsdfs>

dbsvr01:
crtmqm -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 QREMOTE('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 CHL ('SYSAtoSYSB') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('10.0.1.44(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(500000) 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(RCVR)
CONNAME(10.0.1.44) CURRENT
RQMNAME(QMSYSB) STATUS(RUNNING)
SUBSTATE(RECEIVE)
dis chstatus('SYSBtoSYSA')
2 ： dis chstatus('SYSBtoSYSA')
AMQ8417：Display Channel Status details.
CHANNEL(SYSBtoSYSA) CHLTYPE(RCVR)
CONNAME(10.0.1.44) CURRENT
RQMNAME(QMSYSB) STATUS(RUNNING)
SUBSTATE(RECEIVE)
*************************
runmqsc QMSYSB
dis chstatus('SYSBtoSYSA')
runmqchl -c SYSAtoSYSB -m QMSYSA //如果没有错误信息显示，表白该channel成功运营
runmqchl -c SYSBtoSYSA -m QMSYSA //如果没有错误信息显示，表白该channel成功运营
********************************
修改queue managerCCSID:
strmqm
runmqsc
display qmgr // 检查当前queue managerCCSID值
alter qmgr ccsid(437)
end

*************************
dbsvr04:
crtmqm -lc -d SYSB.XMITQ -u DEADLETTER -lp 20 -ls 10 -lf 10240 QMSYSB
strmqm QMSYSB
nohup runmqlsr -t tcp -p 1454 -m QMSYSB &
runmqsc QMSYSB

DEFINE QLOCAL('SYSB.RECVQ') MAXDEPTH(1000000) DEFPSIST(YES)

DEFINE QREMOTE('SYSA.ADMINQ') RNAME('SYSA.ADMINQ') RQMNAME('QMSYSA') XMITQ('SYSB.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('10.0.1.45(1453)') XMITQ('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 -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(10.0.1.45) CURRENT
RQMNAME(QMSYSA) STATUS(RUNNING)
SUBSTATE(RECEIVE)
dis chstatus('SYSBtoSYSA')
2 ：dis chstatus('SYSBtoSYSA')
AMQ8417：Display Channel Status details.
CHANNEL(SYSBtoSYSA) CHLTYPE(SDR)
CONNAME(10.0.1.45(1453)) CURRENT
RQMNAME(QMSYSA) STATUS(RUNNING)
SUBSTATE(MQGET) XMITQ(SYSB.XMITQ)
EN)
*********************************************
修改队列管理器中字符集命令：
一方面打开命令行窗口。 在打开后命令窗口中输入：runmqsc （队列管理器名称），然后按回车。 其后再输入：ALTER QMGR CCSID (字符集代码)，回车，修改成功。 最后输入：DISPLAY QMGR CCSID,查看已经更改过字符编码集。
DISPLAY QMSTATUS STATUS（查看qmgr运营状态）
注:1208是UTF-8格式，1381是中文格式。在字符集代码处选取输入，如果这两都不是你需要，你可以查看MB协助或MQ协助，选取适当你字符集代码。
5、创立catalog 库
本地复制中心pc、dbsvr01、dbsvr02上创立catalog库。
本地复制中心pc上:
C:\Documents and Settings\admin>cd \
C:\>db2 list db directory
SQL1031N 在批示文献系统中找不到数据库目录。 SQLSTATE=58031

C:\>db2 catalog tcpip node pridnode remote 10.0.1.45 server 50002 // prodnode相应dbsvr01主机
DB0I CATALOG TCPIP NODE 命令成功完毕。
DB21056W 直到刷新目录高速缓存之后，目录更改才生效。

C:\>db2 catalog db topicis at node pridnode
DB0I CATALOG DATABASE 命令成功完毕。
DB21056W 直到刷新目录高速缓存之后，目录更改才生效。

C:\>db2 terminate
DB0I TERMINATE 命令成功完毕。

C:\>db2 connect to topicis user db2inst1 using db2inst1

数据库连接信息

数据库服务器 = DB2/AIX64 9.7.1
SQL 授权标记 = DB2INST1
本地数据库别名 = TOPICIS

C:\>db2 catalog tcpip node dssnode remote 10.0.1.44 server 50002 //dssnode相应dbsvr04主机
DB0I CATALOG TCPIP NODE 命令成功完毕。
DB21056W 直到刷新目录高速缓存之后，目录更改才生效。

C:\>db2 catalog db topicis as dssdb at node dssnode //创立topicis别名为dssdb
DB0I CATALOG DATABASE 命令成功完毕。
DB21056W 直到刷新目录高速缓存之后，目录更改才生效。

C:\>db2 terminate
DB0I TERMINATE 命令成功完毕。

C:\>db2 connect to dssdb user db2inst1 using db2inst1

数据库连接信息

数据库服务器 = DB2/AIX64 9.7.1
SQL 授权标记 = DB2INST1
本地数据库别名 = DSSDB

删除用 ：db2 uncatalog db dssdb
Db2 uncatalog node dssnode

dbsvr01上catalog:

dbsvr01:
$ db2 catalog tcpip node dssnode remote 10.0.1.44 server 50002 // dssnode相应为dbsvr04
DB0I The CATALOG TCPIP NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
$ db2 catalog db topicis as dssdb at node dssnode //创立topicis别名为dssdb

DB0I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
$ db2 terminate
DB0I The TERMINATE command completed successfully.
$

dbsvr04上catalog:
dbsvr04:
$ db2 catalog db topicis as dssdb on /home/db2inst1 //创立topicis别名为dssdb

DB0I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
$ db2 terminate
DB0I The TERMINATE command completed successfully.
$ db2 list db directory

System Database Directory

Number of entries in the directory = 2

Database 1 entry:

Database alias = TOPICIS
Database name = TOPICIS
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory 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
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

$ db2 connect to dssdb

Database Connection Information

Database server = DB2/AIX64 9.7.1
SQL authorization ID = DB2INST1
Local database alias = DSSDB

5、顾客口令配备

Dbsvr01:
asnpwd init
asnpwd add alias topicis id db2inst1 password db2inst1
asnpwd add alias dssdb id db2inst1 password db2inst1

Dbsvr04:
asnpwd init
asnpwd add alias topicis id db2inst1 password db2inst1
asnpwd add alias dssdb id db2inst1 password db2inst1

6、db2复制中心：db2rc
7.1 capture模式创立

7.2、Q预定创立

7.3创立复制队列映射

7.4、APPLY模式创立

7.5、Q预定单个启动与停止

8、MQ有关脚本
8.1 Capture主机中有关脚本。
启动脚本：capture.sh:
date
#export LD_LIBRARY_PATH=/usr/opt/db2_08_01/lib
export LIBPATH=/usr/mqm/lib64:$LIBPATH
export LD_LIBRARY_PATH=$LIBPATH
echo $LIBPATH
echo $LD_LIBRARY_PATH
asnqcap capture_server=topicis capture_schema=ASN capture_path="/home/db2inst1/capture" PWDFILE=asnpwd.aut &
#/usr/opt/db2_08_01/bin/asnqcap 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 QMSYSA

runmqsc QMSYSA < ./startcomponent.mqsc

nohup runmqlsr -t tcp -p 1453 -m QMSYSA &

startcomponent.mqsc:
START CHANNEL('SYSAtoSYSB')
START CHANNEL('SYSBtoSYSA')

END

停mqm脚本：
Mqmstop.sh:
runmqsc QMSYSA < ./stopcomponent.mqsc

endmqlsr -w -m QMSYSA

endmqm -i QMSYSA

endmqm -p QMSYSA

stopcomponent.mqsc:

STOP CHANNEL('SYSBtoSYSA') MODE(FORCE)

STOP CHANNEL('SYSBtoSYSA') MODE(FORCE)

END

8.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/lib
export LIBPATH=/usr/mqm/lib64:$LIBPATH
export LD_LIBRARY_PATH=$LIBPATH
echo $LIBPATH
echo $LD_LIBRARY_PATH

asnqapp 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

启mqm脚本：
Mqmstart.sh:

strmqm QMSYSB

runmqsc QMSYSB < ./startcomponent.mqsc

nohup runmqlsr -t tcp -p 1454 -m QMSYSB &
startcomponent.mqsc:
START CHANNEL('SYSAtoSYSB')
START CHANNEL('SYSBtoSYSA')

END

停mqm脚本：
Mqmstop.sh:
runmqsc QMSYSB < ./stopcomponent.mqsc

endmqlsr -w -m QMSYSB

endmqm -i QMSYSB

endmqm -p QMSYSB

stopcomponent.mqsc:

STOP CHANNEL('SYSBtoSYSA') MODE(FORCE)

STOP CHANNEL('SYSBtoSYSA') MODE(FORCE)

END

9、启动顺序
DB2与MQM启动顺序
Db2数据库启动顺序为，
先启动hacmp 集群中db2，
启动MQ主机上db2，
启动MQ主机上mq 队列与通道、监听等。
启动hacmp 集群中db2主机上mq 队列与通道、监听等。
启动MQ主机上mqapply.sh
启动hacmp 集群中db2主机上mqcapture.sh
10、有关信息总结
ASN7137W MAX_MESSAGE_SIZE偏小
程序名：程序标记：发布或 Q 预订 名称 指定 MAX_MESSAGE_SIZE 对于发送队列 队列名 和发布队列映射或复制队列映射 queue_map_name 也许太小。也许需要 大小 字节最

大值。

阐明
发布队列映射或复制队列映射 MAX_MESSAGE_SIZE 值（用于指定此发送队列）也许太小，因而不能涉及 WebSphere MQ 消息中一行。如果 Q Capture 程序在运营时发生此问题

，那么它将停止。

顾客响应
增大队列映射 MAX_MESSAGE_SIZE 值。使用 asnqacmd reinitq 命令重新初始化队列映射。
alter qmgr MAXMSGL()
db2 "update ASN.IBMQREP_SENDQUEUES set max_message_size = 8192"
asnqacmd reinitq

SendMQ:
db2 "update ASN.IBMQREP_SENDQUEUES set max_message_size = 8192"
alter qmgr MAXMSGL()
alter ql(SYSA.XMITQ) MAXMSGL()
dis ql(SYSA.XMITQ)
end

ReceiveMQ:
db2 "update ASN.IBMQREP_SENDQUEUES set max_message_size = 8192"
dis ql(SYSB.RECVQ)
alter qmgr MAXMSGL()
alter ql(SYSB.RECVQ) MAXMSGL()
end

Q复制源表增长字段是不能传播到目的表。
复制源表增长字段是不能传播到目的表，需要在目的库中也添加相似字段，然后重新做Q预定即可。

查看 Q复制ASN系统表状态信息：
$ db2 list tables for schema asn

Table/View Schema Type Creation tim