DB2 SQL复制指南(asnclp命令配置)

大梦

参考内容:
http://publib.boulder.ibm.com/in ... iiyrsclpoprsys.html


一、在Linux、Unix、Windows环境下设置asnclp的Java环境：
The ASNCLP program runs in a Java environment, and during installation the path to certain jar files are added to your CLASSPATH environment variable.

About this task

Use the following procedure if the ASNCLP entries are removed from the CLASSPATH variable and you need to restore them.

Procedure

To set up a Java environment to run the ASNCLP program, specify the following jar files in your CLASSPATH environment variable:
INSTDIR\java\Common.jar
INSTDIR\tools\db2cmn.jar
INSTDIR\tools\db2replapis.jar
INSTDIR\tools\db2qreplapis.jar
INSTDIR\tools\jt400.jar
Where INSTDIR is the DB2 instance directory. On Linux and UNIX, the instance directory is the INSTDIR/sqllib directory, where INSTDIR is the home directory of the instance owner. On Windows, the instance directory is the \sqllib directory where DB2 was installed.

Examples
Linux, UNIX: To set the CLASSPATH environment variable from a UNIX command prompt:
export
CLASSPATH=$CLASSPATH
:/u/myinst/sqllib/java/Common.jar;
:/u/myinst/sqllib/tools/db2cmn.jar;
:/u/myinst/sqllib/tools/db2replapis.jar;
:/u/myinst/sqllib/tools/db2qreplapis.jar;
:/u/myinst/sqllib/tools/jt400.jar;

Windows: To set the CLASSPATH environment variable from a Windows command prompt:
set CLASSPATH=%CLASSPATH%;%c:\Program File\sqllib\java\Common.jar;
%c:\Program File\sqllib\tools\db2cmn.jar;
%c:\Program File\sqllib\tools\db2replapis.jar;
%c:\Program File\sqllib\tools\db2qreplapis.jar;
%c:\Program File\sqllib\tools\jt400.jar;

To run the ASNCLP commands in interactive mode:

Open an operating system command prompt and issue the following command:
ASNCLP
The ASNCLP command starts the ASNCLP program and changes the command prompt to Repl >.
Issue any of the ASNCLP commands. For example: To set the Q Capture server to the database aliasname, issue the following command:
SET SERVER CAPTURE TO DBALIAS aliasname
To exit the ASNCLP program, issue the following command:
quit
To get help for the ASNCLP program, issue the following command from an operating system command prompt:
ASNCLP ?


To run the ASNCLP commands in batch mode using an input file:

Create an input file that contains the ASNCLP commands that you want to run. Commands in the input file must be delimited by the semicolon ( and can span multiple lines. You can also add comments to the input file by beginning the comment line with a number (#) sign.
Open an operating system command prompt and issue the following command:
ASNCLP -f myfile.in
In the example the input-file name is myfile.in and can consist of any valid file name plus an extension. You can also specify a full file path and file name. For example:
ASNCLP -f c:\temp\myfile.in
The ASNCLP command starts the ASNCLP program, which processes all of the commands in the input file until it encounters an error or the end of the file.
If your input file does not contain the quit command, you can exit the ASNCLP program by issuing the following command:
quit


二、运行DB2复制命令行接口命令asnclp，具体操作如下所示

asnclp

Repl >

1、生成Capture control tables脚本(最后手工运行capctrl.sql)
ASNCLP script 1 (Capture control tables)
========
(1)设置为SQL复制模式
asnclp session set to sql replication

(2)设置capture server
set server capture to db dwedemo id jianghua password "********"

(3)设置capture控制表的sql脚本名称
set output capture script "capctrl.sql"

(4)设置错误日志文件
set log "capctrl.err"

(5)设置稍后手工运行capture控制表脚本
set run script later

(6)生成控制表的sql脚表，uow与其他的控制表都放在userspace1表空间，也可以指定不同的表空间，但需要事先建好
create control tables for capture server in uw uow userspace1 others userspace1

(7)退出asnclp
quit

2、生成源表注册脚本(最后手工运行register.sql)
ASNCLP script 2 (registration)
========
(1)设置为SQL复制模式
asnclp session set to sql replication

(2)设置capture server
set server capture to db dwedemo id jianghua password "********"

(3)设置注册源表的sql脚本名称
set output capture script "register.sql"

(4)设置错误日志文件
set log "register.err"

(5)设置稍后手工运行注册源表的sql脚本
set run script later

(6)注册指定源表
create registration (jianghua.tab01) differential refresh stage cdtab01

(7)退出asnclp
quit

3、生成Apply control tables脚本(最后手工运行appctrl.sql)
ASNCLP script 3 (Apply control tables)
========
(1)设置为SQL复制模式
asnclp session set to sql replication

(2)设置apply control server
set server control to db dwesamp id jianghua password "********"

(3)设置apply控制表的sql脚本名称
set output control script "appctrl.sql"

(4)设置错误日志文件
set log "appctrl.err"

(5)设置稍后手工运行apply控制表脚本
set run script later

(6)生成控制表的sql脚表，uow与其他的控制表都放在userspace1表空间，也可以指定不同的表空间，但需要事先建好
create control tables for apply control server in uw others userspace1

(7)退出asnclp
quit

4、生成生成预定义集SQL脚本(最后手工运行appsubset.sql)
ASNCLP script 4 (subscription set)
========
(1)设置为SQL复制模式
asnclp session set to sql replication

(2)设置环境(capture、apply、target数据库的名称)
set server capture to db dwedemo id jianghua password "********"         -- Capture服务器
set server control to db dwesamp id jianghua password "********"         -- Apply服务器
set server target  to db dwesamp id jianghua password "********"         -- 复制的目标服务器

(3)设置控制表的sql脚本名称
set output capture script "capsubset.sql" control script "appsubset.sql"

(4)设置错误日志文件
set log "subset.err"

(5)设置稍后手工运行脚本
set run script later

(6)创建预定义集名set00，限定符为aq00，并进行激活，且复制间隔为1分钟
create subscription set setname set00 applyqual aq00 activate yes timing interval 1

(7)退出asnclp
quit

5、生成预定义集添加成员的SQL脚本(最后手工运行capmember.sql,appsubset.sql,repltrg.sql)
ASNCLP script 5 (subscription-set member)
========
(1)设置为SQL复制模式
asnclp session set to sql replication

(2)设置环境(capture、apply、target数据库的名称)
set server capture to db dwedemo id jianghua password "********"         -- Capture服务器
set server control to db dwesamp id jianghua password "********"         -- Apply服务器
set server target  to db dwesamp id jianghua password "********"         -- 复制的目标服务器

(3)设置控制表的sql脚本名称
set output capture script "capmember.sql" control script "appmember.sql"

(4)设置错误日志文件
set log "member.err"

(5)设置稍后手工运行脚本
set run script later

(6)增加预定义集的成员，设定目标表的表空间为tstrg00，默认为所有字段
set profile tbsprofile for object target tablespace options uw using file "e:\temp\repl\tstrg.ts" size 2000 pages
create member in setname set00 applyqual aq00 activate yes source tab01 target name tab01 definition in tstrg00 create using profile tbsprofile type usercopy cols all registered

(7)退出asnclp
quit


三、通过命令行启动capture与apply进行测试
如果源与目标不在同一物理主机上，则需要分别在每台物理主机上进行远程的catalog操作，具体操作省。

1、在每台物理主机上生成密码文件：
asnpwd init using "e:\temp\repl\asnpwd.aut"
asnpwd add alias dwedemo id jianghua password q1w2e3r4 using "e:\temp\repl\asnpwd.aut"
asnpwd add alias dwesamp id jianghua password q1w2e3r4 using "e:\temp\repl\asnpwd.aut"

2、在capture上运行冷启动，进行初始数据装入，仅在第一次运行冷启动：
asncap CAPTURE_SERVER=dwedemo CAPTURE_SCHEMA=ASN CAPTURE_PATH=e:\temp\repl startmode=COLD
以后启动时用以下方式：
asncap CAPTURE_SERVER=dwedemo CAPTURE_SCHEMA=ASN CAPTURE_PATH=e:\temp\repl startmode=WARMSI
停止capture
asnccmd CAPTURE_SERVER=dwedemo CAPTURE_SCHEMA=ASN STOP

3、在apply服务器上运行apply进程:
asnapply CONTROL_SERVER=dwesamp APPLY_QUAL=aq00 APPLY_PATH=e:\temp\repl
停止apply
asnacmd CONTROL_SERVER=dwesamp APPLY_QUAL=aq00 STOP

4、通过asntdiff可以查询源表与目标表上的差别:
asntdiff db=dwesamp where="target_table = 'TAB01' and apply_qual = 'AQ00' and set_name = 'SET00'"


export PATH=.:/db2home2/db2inst2/sqllib/java/jdk64/binPATH
export CLASSPATH=$CLASSPATH:/db2home2/db2inst2/sqllib/java/Common.jar:/db2home2/db2inst2/sqllib/tools/db2cmn.jar:/db2home2/db2inst2/sqllib/tools/db2replapis.jar:/db2home2/db2inst2/sqllib/tools/db2qreplapis.jar:/db2home2/db2inst2/sqllib/tools/jt400.jar

asnpwd init using "/db2home2/db2inst2/repl/asnpwd.aut"
asnpwd add alias s_db id "db2inst2" password "db<lihai!0102" using "/db2home2/db2inst2/repl/asnpwd.aut"
asnpwd add alias t_db id "db2inst2" password "db<lihai!0102" using "/db2home2/db2inst2/repl/asnpwd.aut"
rm *.err
rm *.sql
asnclp -f capctrl.in
db2 connect to s_db
db2 -tvf capctrl.sql
db2 terminate
asnclp -f register.in
db2 connect to s_db
db2 -tvf register.sql
db2 terminate
asnclp -f appctrl.in
db2 connect to t_db
db2 -tvf appctrl.sql
db2 terminate
asnclp -f appsubset.in
db2 connect to t_db
db2 -tvf appsubset.sql
db2 terminate
asnclp -f capmember.in
db2 connect to s_db
db2 -tvf capmember.sql
db2 terminate
db2 connect to t_db
db2 -tvf appmember.sql
db2 -tvf repltrg.sql
db2 terminate

nohup asncap CAPTURE_SERVER=s_db CAPTURE_SCHEMA=ASN CAPTURE_PATH=/db2home2/db2inst2/repl/logs startmode=COLD &
nohup asnapply CONTROL_SERVER=t_db APPLY_QUAL=aq00 APPLY_PATH=/db2home2/db2inst2/repl/logs &

nohup asncap CAPTURE_SERVER=s_db CAPTURE_SCHEMA=ASN CAPTURE_PATH=/db2home2/db2inst2/repl/logs startmode=WARMSI &
nohup asnapply CONTROL_SERVER=t_db APPLY_QUAL=aq00 APPLY_PATH=/db2home2/db2inst2/repl/logs &

asnccmd CAPTURE_SERVER=s_db CAPTURE_SCHEMA=ASN STOP
asnacmd CONTROL_SERVER=t_db APPLY_QUAL=aq00 STOP

kll99

lz写的是很详细，可是没有环境的介绍，看的让人一头雾水，不知道从哪个库复制到哪个ku，哪两张表互相对应等等。