關於goldengate無主鍵表的update的同步(源端和目的端列數不一樣多)

msdnchina發表於2011-05-26
關於goldengate無主鍵表的update的同步

源端:windows 2003 sql2005 sp3
目的端:rhel5.3 x64bit, db2 v9.7 x64bit

源端表結構:(無主鍵無唯一索引)
CREATE TABLE [lc0019999].[KCCKD2](
[KCCKD2_PJLX] [char](1) NOT NULL,
[KCCKD2_LSBH] [varchar](10) NOT NULL,
[KCCKD2_FLBH] [varchar](10) NOT NULL,
[KCCKD2_WLBH] [varchar](30) NOT NULL,
[KCCKD2_TSKC] [char](1) NOT NULL DEFAULT (' '),
[KCCKD2_XGDX] [varchar](30) NOT NULL DEFAULT (' '),
[KCCKD2_DDLS] [varchar](20) NULL DEFAULT (' '),
[KCCKD2_DDFL] [varchar](10) NULL DEFAULT (' '),
[KCCKD2_TDLS] [varchar](10) NULL DEFAULT (' '),
[KCCKD2_WTLS] [varchar](10) NULL DEFAULT (' '),
[KCCKD2_WTFL] [varchar](10) NULL DEFAULT (' '),
[KCCKD2_QLLS] [varchar](10) NULL DEFAULT (' '),
[KCCKD2_QLFL] [varchar](10) NULL DEFAULT (' '),
[KCCKD2_HXSL] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_HXFSL1] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_HXFSL2] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_HXDJ] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_HXJE] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_GZZX] [varchar](30) NULL,
[KCCKD2_PCH] [varchar](20) NOT NULL DEFAULT (' '),
[KCCKD2_HWBH] [varchar](20) NOT NULL DEFAULT (' '),
[KCCKD2_ZYX1] [varchar](20) NOT NULL DEFAULT (' '),
[KCCKD2_ZYX2] [varchar](20) NOT NULL DEFAULT (' '),
[KCCKD2_ZYX3] [varchar](20) NOT NULL DEFAULT (' '),
[KCCKD2_ZYX4] [varchar](20) NOT NULL DEFAULT (' '),
[KCCKD2_ZYX5] [varchar](20) NOT NULL DEFAULT (' '),
[KCCKD2_WLZT] [varchar](4) NOT NULL DEFAULT (' '),
[KCCKD2_WLBZ] [varchar](8) NOT NULL DEFAULT (' '),
[KCCKD2_QLSL] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_SL] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_DJ] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_JE] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_FSL1] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_FSL2] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_TDFL] [varchar](10) NULL,
[KCCKD2_XEFL] [varchar](10) NULL,
[KCCKD2_BZBH] [varchar](20) NULL,
[KCCKD2_GXBH] [varchar](10) NULL,
[KCCKD2_ZJZT] [char](1) NULL,
[KCCKD2_ZJJL] [char](1) NULL,
[KCCKD2_CLFS] [char](1) NULL,
[KCCKD2_ZJWC] [char](1) NULL,
[KCCKD2_JSSL] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_BGDH] [varchar](20) NULL,
[KCCKD2_BZ] [varchar](200) NULL,
[KCCKD2_C1] [varchar](100) NULL,
[KCCKD2_C2] [varchar](100) NULL,
[KCCKD2_C3] [varchar](100) NULL,
[KCCKD2_C4] [varchar](100) NULL,
[KCCKD2_C5] [varchar](100) NULL,
[KCCKD2_U1] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_U2] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_U3] [dbo].[u001] NOT NULL DEFAULT ((0)),
[KCCKD2_FSL3] [dbo].[u001] NULL DEFAULT ((0)),
[KCCKD2_FQLSL] [dbo].[u001] NULL DEFAULT ((0)),
[KCCKD2_CXESL] [dbo].[u001] NULL DEFAULT ((0)),
[KCCKD2_CBBZ] [char](1) NULL,
[KCCKD2_THDZSL] [dbo].[u001] NULL DEFAULT ((0)),
[KCCKD2_THDJCSL] [dbo].[u001] NULL DEFAULT ((0)),
[KCCKD2_SCSQLS] [varchar](10) NULL,
[KCCKD2_SCSQFL] [varchar](10) NULL,
[KCCKD2_SCDDLS] [varchar](20) NULL
) ON [PRIMARY]


目的端表結構:(無主鍵無唯一索引)

除了上述列之外,比源端多了5列,這5列是用於bi目的的標識列.


需要在源端的ext和datapump程式的引數檔案中,定義keycols,keycols包含源端所有的列。
需要在目的端的rep程式的引數檔案中,定義keycols,keycols包含源端所有的列,keycols用作update的"偽主鍵".

若是不定義keycols,目的端的rep程式,在啟動時會報錯:

***********************************************************************
** Run Time Messages **
***********************************************************************

Opened trail file /db2/ggv11db2/dirdat/bb000008 at 2011-05-25 18:22:02

MAP resolved (entry LC0019999.KCCKD2):
map LC0019999.KCCKD2, target GGSCHEMA.KCCKD2, COLMAP (USEDEFAULTS, ETL_FL_NM =1, BUSI_DATE = @COLSTAT (NULL), PPN_TMSTAMP = @COLSTAT (NULL), OPERTYPE = @GETENV ("GGHEADER", "OPTYPE") , MODIFY_DATATIME = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
Using the following default columns with matching names:
KCCKD2_PJLX=KCCKD2_PJLX, KCCKD2_LSBH=KCCKD2_LSBH, KCCKD2_FLBH=KCCKD2_FLBH, KCCKD2_WLBH=KCCKD2_WLBH, KCCKD2_TSKC=KCCKD2_TSKC, KCCKD2_XGDX=KCCKD2_XGDX, KCCKD2_DDLS=KCCKD2_DDLS, KCCKD2_DDFL=KCCKD2_DDFL, KCCKD2_TDLS=KCCKD2_TDLS, KCCKD2_WTLS=KCCKD2_WTLS, KCCKD2_WTFL=KCCKD2_WTFL, KCCKD2_QLLS=KCCKD2_QLLS, KCCKD2_QLFL=KCCKD2_QLFL, KCCKD2_HXSL=KCCKD2_HXSL, KCCKD2_HXFSL1=KCCKD2_HXFSL1, KCCKD2_HXFSL2=KCCKD2_HXFSL2, KCCKD2_HXDJ=KCCKD2_HXDJ, KCCKD2_HXJE=KCCKD2_HXJE, KCCKD2_GZZX=KCCKD2_GZZX, KCCKD2_PCH=KCCKD2_PCH,
KCCKD2_HWBH=KCCKD2_HWBH, KCCKD2_ZYX1=KCCKD2_ZYX1, KCCKD2_ZYX2=KCCKD2_ZYX2, KCCKD2_ZYX3=KCCKD2_ZYX3, KCCKD2_ZYX4=KCCKD2_ZYX4, KCCKD2_ZYX5=KCCKD2_ZYX5, KCCKD2_WLZT=KCCKD2_WLZT, KCCKD2_WLBZ=KCCKD2_WLBZ, KCCKD2_QLSL=KCCKD2_QLSL, KCCKD2_SL=KCCKD2_SL, KCCKD2_DJ=KCCKD2_DJ, KCCKD2_JE=KCCKD2_JE, KCCKD2_FSL1=KCCKD2_FSL1, KCCKD2_FSL2=KCCKD2_FSL2, KCCKD2_TDFL=KCCKD2_TDFL, KCCKD2_XEFL=KCCKD2_XEFL, KCCKD2_BZBH=KCCKD2_BZBH, KCCKD2_GXBH=KCCKD2_GXBH, KCCKD2_ZJZT=KCCKD2_ZJZT, KCCKD2_ZJJL=KCCKD2_ZJJL,
KCCKD2_CLFS=KCCKD2_CLFS, KCCKD2_ZJWC=KCCKD2_ZJWC, KCCKD2_JSSL=KCCKD2_JSSL, KCCKD2_BGDH=KCCKD2_BGDH, KCCKD2_BZ=KCCKD2_BZ, KCCKD2_C1=KCCKD2_C1, KCCKD2_C2=KCCKD2_C2, KCCKD2_C3=KCCKD2_C3, KCCKD2_C4=KCCKD2_C4, KCCKD2_C5=KCCKD2_C5, KCCKD2_U1=KCCKD2_U1, KCCKD2_U2=KCCKD2_U2, KCCKD2_U3=KCCKD2_U3, KCCKD2_FSL3=KCCKD2_FSL3, KCCKD2_FQLSL=KCCKD2_FQLSL, KCCKD2_CXESL=KCCKD2_CXESL, KCCKD2_CBBZ=KCCKD2_CBBZ, KCCKD2_THDZSL=KCCKD2_THDZSL, KCCKD2_THDJCSL=KCCKD2_THDJCSL, KCCKD2_SCSQLS=KCCKD2_SCSQLS, KCCKD2_SCSQFL=KCCKD2_SCSQFL,
KCCKD2_SCDDLS=KCCKD2_SCDDLS

Using the following key columns for target table GGSCHEMA.KCCKD2: KCCKD2_PJLX, KCCKD2_LSBH, KCCKD2_FLBH, KCCKD2_WLBH, KCCKD2_TSKC, KCCKD2_XGDX, KCCKD2_DDLS, KCCKD2_DDFL, KCCKD2_TDLS, KCCKD2_WTLS, KCCKD2_WTFL, KCCKD2_QLLS, KCCKD2_QLFL, KCCKD2_HXSL, KCCKD2_HXFSL1, KCCKD2_HXFSL2, KCCKD2_HXDJ, KCCKD2_HXJE, KCCKD2_GZZX, KCCKD2_PCH, KCCKD2_HWBH, KCCKD2_ZYX1, KCCKD2_ZYX2, KCCKD2_ZYX3, KCCKD2_ZYX4, KCCKD2_ZYX5, KCCKD2_WLZT, KCCKD2_WLBZ, KCCKD2_QLSL, KCCKD2_SL, KCCKD2_DJ, KCCKD2_JE, KCCKD2_FSL1, KCCKD2_FSL2, KCCKD2_TDFL, KCCKD2_XEFL, KCCKD2_BZBH, KCCKD2_GXBH, KCCKD2_ZJZT, KCCKD2_ZJJL, KCCKD2_CLFS, KCCKD2_ZJWC, KCCKD2_JSSL, KCCKD2_BGDH, KCCKD2_BZ, KCCKD2_C1, KCCKD2_C2, KCCKD2_C3, KCCKD2_C4, KCCKD2_C5, KCCKD2_U1, KCCKD2_U2, KCCKD2_U3, KCCKD2_FSL3, KCCKD2_FQLSL, KCCKD2_CXESL, KCCKD2_CBBZ, KCCKD2_THDZSL, KCCKD2_THDJCSL, KCCKD2_SCSQLS, KCCKD2_SCSQFL, KCCKD2_SCDDLS, ETL_FL_NM, BUSI_DATE, PPN_TMSTAMP, OPERTYPE, MODIFY_DATATIME.


2011-05-25 18:22:17 WARNING OGG-01004 Aborted grouped transaction on 'GGSCHEMA.KCCKD2', Database error 100 ([IBM][CLI Driver][DB2/LINUXX8664] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

).

2011-05-25 18:22:17 WARNING OGG-01003 Repositioning to rba 9546 in seqno 8.

2011-05-25 18:22:17 WARNING OGG-01154 SQL error 100 mapping LC0019999.KCCKD2 to GGSCHEMA.KCCKD2 [SQL error 100 (0x64)][IBM][CLI Driver][DB2/LINUXX8664] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000.

2011-05-25 18:22:17 WARNING OGG-01003 Repositioning to rba 9546 in seqno 8.

Source Context :
SourceModule : [er.main]
SourceID : [/mnt/ecloud/workspace/Build_OpenSys_r11.1.1.0.0_078_[37283]/perforce/src/app/er/rep.c]
SourceFunction : [take_rep_err_action]
SourceLine : [15780]
ThreadBacktrace : [8] elements
: [/db2/ggv11db2/replicat(CMessageContext::AddThreadContext()+0x26) [0x70a036]]
: [/db2/ggv11db2/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x700ad2]]
: [/db2/ggv11db2/replicat(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, DBString<777> const&, DBString<777> const&, CMessageFactory::MessageDisposition)+0x9b) [0x6ac89b]]
: [/db2/ggv11db2/replicat [0x550e93]]
: [/db2/ggv11db2/replicat [0x55a20a]]
: [/db2/ggv11db2/replicat(main+0xe9c) [0x55bedc]]
: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x389281d974]]
: [/db2/ggv11db2/replicat(__gxx_personality_v0+0x19a) [0x4ce81a]]

2011-05-25 18:22:17 ERROR OGG-01296 Error mapping from LC0019999.KCCKD2 to GGSCHEMA.KCCKD2.[@more@]

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/161195/viewspace-1050398/,如需轉載,請註明出處,否則將追究法律責任。

相關文章