db2 Q复制的问题

db2里面采用Q复制来同步数据时，表里有自增长列的怎么处理啊....
总是无法启动Q预订

表结构如下:
CREATE TABLE DB2INST1.WD_SQL
(SQLID    BIGINT          NOT NULL  GENERATED ALWAYS
    AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE, MINVALUE 0, MAXVALUE 9223372036854775807, NO CYCLE, NO ORDER),
  SQLNAME  VARCHAR(200),
  SQLS     VARCHAR(4000)
)

在q apply启动Q预订时提示是这样的.


2009-07-18-15.21.58.471220 ASN7533E  "Q Apply" : "ASN" : "BR00000" : Column "SQL
ID" in the target table is not part of the Q subscription "WD_SQL0001" (receive
queue "ASN.QMCAP_TO_QMAPP.DATAQ", replication queue map "TDCAP_ASN_TO_TDAPP_ASN"
) and it is not nullable or it has no default values.
2009-07-18-15.21.58.471535 ASN7512E  "Q Apply" : "ASN" : "BR00000" : The Q Apply
program could not activate the Q subscription "WD_SQL0001" (receive queue "ASN.
QMCAP_TO_QMAPP.DATAQ", replication queue map "TDCAP_ASN_TO_TDAPP_ASN"). Reason c
ode: "15".
2009-07-18-15.22.04.009769 ASN7595W  "Q Apply" : "ASN" : "BR00000" : The Q Apply
program received a Q subscription deactivate message, but the SUB_ID "1009" (re
ceive queue "ASN.QMCAP_TO_QMAPP.DATAQ", replication queue map"TDCAP_ASN_TO_TDAPP
_ASN") cannot be found or the Q subscription is in the inactive state.


在q capture 上提示这样的

2009-07-18-15.11.14.832334 ASN7010I  "Q Capture" : "ASN" : "WorkerThread" : The
program successfully activated  XML publication or Q subscription "WD_SQL0001"
(send queue "ASN.QMCAP_TO_QMAPP.DATAQ", publishing or replication queue map "TDC
AP_ASN_TO_TDAPP_ASN") for source table "DB2INST1.WD_SQL".
2009-07-18-15.11.15.846751 ASN7013I  "Q Capture" : "ASN" : "WorkerThread" : The
XML publication or Q subscription "WD_SQL0001" was deactivated.

 

把这个 generated always换成generated by default在创建Q预订的时候就不报错了.顺利同步

 

method1:在quest里改的结果它是先export 再drop 再create --import

 

method2:  db2 => alter table test1_always alter column sqlid set generated by default

 

method1 模拟过程如下：

db2 => create table test1_default(sqlid bigint not null generated by default as
identity (start with 0,increment by 1,no cache,minvalue 0,maxvalue 9223372036854
775807,no cycle,no order),sqlname varchar(200))
DB20000I  SQL命令成功完成。

db2 => create table test1_always(sqlid bigint not null generated always as ident
ity (start with 0,increment by 1,no cache,minvalue 0,maxvalue 922337203685477580
7,no cycle,no order),sqlname varchar(200))
DB20000I  SQL命令成功完成。

db2 => insert into test1_always values(default,'fengjin')
DB20000I  SQL命令成功完成。

db2 => insert into test1_always values(default,'fengfeng')
DB20000I  SQL命令成功完成。

 

db2 => export to f:\DB2\export.del of del select * from test1_always
SQL3104N  EXPORT 实用程序 正在开始将数据导出至文件 "f:\DB2\export.del"。

SQL3105N  Export 实用程序已经完成导出 "2" 行。

导出的行数：2

db2 => load from f:\DB2\export.del of del modified by identityignores replace in
to test1_default nonrecoverable
SQL3501W  由于禁用数据库正向恢复，因此表所驻留的表空间将不被置于备份暂挂状态。

SQL3109N  实用程序正在开始从文件 "f:\DB2\export.del" 装入数据。

SQL3500W  在时间 "2009-07-19 21:05:30.281574"，实用程序在开始 "LOAD"。

SQL3519W  开始装入一致点。输入记录数 = "0"。

SQL3520W  “装入一致点”成功。

SQL3110N  实用程序已完成处理。从输入文件读了 "2" 行。

SQL3519W  开始装入一致点。输入记录数 = "2"。

SQL3520W  “装入一致点”成功。

SQL3515W  在时间 "2009-07-19 21:05:30.774059"，实用程序已经完成了 "LOAD"。

读取行数         = 2
跳过行数         = 0
装入行数         = 2
拒绝行数         = 0
删除行数         = 0
落实行数         = 2

db2 => select * from test1_default

SQLID                SQLNAME

-------------------- -----------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------------------
                   0 fengjin

                   1 fengfeng

 

  2 条记录已选择。

db2 =>

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13165828/viewspace-609638/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13165828/viewspace-609638/