[SymmetricDS/CrystalDB CRS] SMR - DB2 to CrystalDB(PostgreSQL)

1.環境:
db2_server
--------------------------------------
ip : 192.168.38.22
帳號 : db2inst1 / 1qaz2wsx, tmp / tmp , crs /crs
ibm db2 udb (9.5)
共有兩個要做replication的schema: db2inst1, tmp
CRS使用的schema: crs
共有五個table要複製 :
DB2INST1.item
DB2INST1.item_selling_price
DB2INST1.sale_return_line_item
DB2INST1.sale_transaction
TMP.TB1
--------------------------------------

CrystalDB
--------------------------------------
ip : 192.168.38.121
帳號 : db2inst1 / 1qaz2wsx, tmp / tmp , crs /crs
CrystalDB (9.3 base on PostgreSQL 9.3)
共有三個schema對應db2上的三個, db2inst1, tmp, crs
--------------------------------------



2.帳號與權限設定 :
db2_server
--------------------------------------
#db2
db2=> connect to master user db2inst1
db2=> grant select, alter on xxx to user crs
db2=> connect to master user tmp
db2=> grant select, alter on xxx to user crs
--------------------------------------

CrystalDB
--------------------------------------
#/opt/CrystalDB/9.3/bin/psql -U postgres
// 建立所需的Userrole

postgres=# create user crs with password 'crs' superuser;

postgres=# create user db2inst1 with password '1qaz2wsx' ;

postgres=# create user tmp with password 'tmp';

// 建立要複製的database

postgres=# create database monitor owner crs;

postgres=# CREATE SCHEMA crs AUTHORIZATION crs;

postgres=# GRANT ALL ON SCHEMA crs TO public;

postgres=# CREATE SCHEMA db2inst1 AUTHORIZATION db2inst1;

postgres=# GRANT ALL ON SCHEMA db2inst1 TO crs;

postgres=# CREATE SCHEMA tmp AUTHORIZATION tmp;

postgres=# GRANT ALL ON SCHEMA tmp TO crs;

--------------------------------------

3.CrystalDB CRS組態
------------------------------------------------------------------------------------------------------
Master ($CrystalDB_CRS_HOME/engine/master-000.properties):
------------------------------------------------------------------------------------------------------
engine.name=master-000
db.driver=com.ibm.db2.jcc.DB2Driver
db.url=jdbc:db2://192.168.38.22:50000/master
db.user=crs
db.password=crs
registration.url=
sync.url=http://192.168.38.1:8080/sync/master-000
group.id=master
external.id=000
job.purge.period.time.ms=7200000
job.routing.period.time.ms=5000
job.push.period.time.ms=10000
job.pull.period.time.ms=10000
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
Slave ($CrystalDB_CRS_HOME/engine/replica-001.properties):
------------------------------------------------------------------------------------------------------
engine.name=replica-001
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.38.121:5432/monitor?stringtype=unspecified
db.user=crs
db.password=crs
registration.url=http://192.168.38.1:8080/sync/master-000
group.id=replica
external.id=001
job.routing.period.time.ms=5000
job.push.period.time.ms=10000
job.pull.period.time.ms=10000
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
properties for db schema sync :
for sync db2inst1, we need a properties for cmt_export / cmt_import
 ($CrystalDB_CRS_HOME/conf/DB2INST1.properties):
------------------------------------------------------------------------------------------------------
engine.name=replica-001
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.38.121:5432/monitor?stringtype=unspecified
db.user=db2inst1
db.password=1qaz2wsx
registration.url=http://192.168.38.1:8080/sync/master-000
group.id=replica
external.id=001
job.routing.period.time.ms=5000
job.push.period.time.ms=10000
job.pull.period.time.ms=10000
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
for sync tmp, we need a properties for cmt_export / cmt_import
 ($CrystalDB_CRS_HOME/conf/TMP.properties):
------------------------------------------------------------------------------------------------------
engine.name=replica-001
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.38.121:5432/monitor?stringtype=unspecified
db.user=tmp
db.password=tmp
registration.url=http://192.168.38.1:8080/sync/master-000
group.id=replica
external.id=001
job.routing.period.time.ms=5000
job.push.period.time.ms=10000
job.pull.period.time.ms=10000
------------------------------------------------------------------------------------------------------

4.EXP/IMPort Schema from DB2 UDB to CrystalDB
# cd /Volumes/TimeMachine/PG_pack_src/CRS/src/CRS/bin
// 取得DB2上DB2INST1下的所有table schema.
# ./cmt_export -p ../engines/master-000.properties --schema DB2INST1 --no-data --compatible postgres > /tmp/db2inst1_schema.sql
// 取得DB2上TMP下的所有table schema.
# ./cmt_export -p ../engines/master-000.properties --schema TMP --no-data --compatible postgres > /tmp/tmp_schema.sql

// 匯入schema
# ./cmt_import -p ../conf/DB2INST1.properties /tmp/db2inst1_schema.sql

# ./cmt_import -p ../conf/TMP.properties /tmp/tmp_schema.sql 

5.建立CRS所需的資料庫及schema
# cd /Volumes/TimeMachine/PG_pack_src/CRS/src/CRS/bin
// 建立CRS並建立db2inst1的複製環境(這裡的db2inst1指的是target資料庫所使用的schema，注意大小寫)
# ./smrsetup db2inst1
// 建立tmp的複製環境
# ./addschema tmp
// 加入tables (addtables後面帶的必需是shcema.table，注意大小寫)
# ./addtable DB2INST1.item,DB2INST1.item_selling_price,DB2INST1.sale_return_line_item,DB2INST1.sale_transaction,TMP.TB1