OGG-COLMAP-列映射

OGG-COLMAP-列映射

COLMAP官方语法定义：
COLMAP availability
The COLMAP option is available with the following parameters:
Syntax TABLE <table spec>, TARGET <table spec>,
COLMAP ([USEDEFAULTS, ] <target column> = <source expression>);
Or...
MAP <table spec>, TARGET <table spec>,
COLMAP ([USEDEFAULTS, ] <target column> = <source expression>);
我这里在目标端使用COLMAP
说明：
USEDEFAULTS：源端的列名与目标端的列名相同，使用USEDEFAULTS
target column:目标列
source expression：源端表达式或列
COLMAP官方使用定义：
When using COLMAP for source and target tables that are not identical in structure, you must:
● generate data definitions for the source tables, the target tables, or both, depending on
the Oracle GoldenGate configuration and the databases that are being used.
● transfer the definitions file to the system where they will be used.
● use the SOURCEDEFS parameter to identify the definitions file for Replicat on a target
system or use the TARGETDEFS parameter to identify the definitions file for Extract or a
data pump on a source system or intermediary system.


当使用COLMAP为了源端与目标端的表的结构不相同的时候，必须使用defgen生成定义文件.
When using COLMAP for source and target tables that are identical in structure, and you are
only using COLMAP for other functions such as conversion, a source definitions file is not
needed. When a definitions file is not being used, you must use the ASSUMETARGETDEFS
parameter instead. See the Oracle GoldenGate Windows and UNIX Reference Guide


当使用COLMAP为了源端与目标端的表的结构相同的时候，仅使用COLMAP进行其他的功能，例如转换，不需要定义文件，但是必须使用 ASSUMETARGETDEFS 参数。
Using table-level column mapping
Use the COLMAP option of the MAP and TABLE parameters to:
● explicitly map source columns to target columns that have different names.
● specify default column mapping when an explicit column mapping is not needed.
COLMAP只能是完成表级别的映射功能，也能全局映射


复制进程配置：
REPLICAT rep_li
USERID ggs, PASSWORD ggs
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND
SOURCEDEFS /u01/app/oracle/ggs/dirdef/baobao14.def

--MAP gis_test.test1, TARGET gis_test.test1 ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 9116587);

-- MAP gis_test.baobao14, TARGET gis_test.baobao14, colmap (pxid=pxid, fang=fang);

MAP gis_test.baobao14, TARGET gis_test.baobao14,colmap (USEDEFAULTS, dname = tname);



MAP fin.account, TARGET fin.acctab,COLMAP (account = acct,balance = bal,branch = branch);


MAP fin.teller, TARGET fin.telltab,WHERE (branch = “NY”);
IGNOREINSERTS
MAP fin.teller, TARGET fin.telltab,WHERE (branch = “LA”);


MAP TEST.T54 TARGET TEST.T55, COLMAP (col1=c1, col3=c2, col4=c3, col2=c4);
MAP TEST.T54 TARGET TEST.T55, COLMAP (col1=c1, col3=c2, col4=c3, col2=@CASE(c4,"CAR","a car"));

http://www.traveldba.com/archives/360

map comm.SICK_INFO ,target cdinfo.patient_info, & 
SQLEXEC (ID lookup, & 
QUERY "select cdinfo.SEQUENCE_NO.NEXTVAL from dual",& , 
NOPARAMS) ,& 
COLMAP(PATIENT_NO = @GETVAL(lookup.cdinfo.SEQUENCE_NO.NEXTVAL), 
ORG_CODE = @COLSTAT(NULL), 
PATIENT_ID = SICK_ID, 
PATIENT_CLASS = STANDING);


MAP yxuser2.test2, TARGET ssyxuser.sstable2, &
SQLEXEC (SPNAME ssyxuser.LOOKUP_ACCOUNT, &
ID lookup1, PARAMS (dqcode_in = name3)), &
COLMAP (USEDEFAULTS, &
ssid = ID, &
sname1 = @STREXT(name1,0,12), &
sname2 =@GETVAL (lookup1.dqcode_out), &
sname3 = name4, &
saddcol = @COMPUTE (ID + 100) );


-------创建存储过程------
CREATE OR REPLACE PROCEDURE LOOKUP_ACCOUNT(dqcode_in IN VARCHAR2, dqcode_out OUT VARCHAR2)
as
BEGIN
SELECT dqcode2
INTO dqcode_out
FROM SSYXUSER.dqmapping
WHERE dqcode1 = dqcode_in;
END;


-----测试存储过程-----
  declare
        realname number;
  begin  
        lookup_account(101,realname); 
        DBMS_OUTPUT.PUT_LINE(REALNAME);
  END;  

--------------------测试----------------
REPLICAT reptrsf
sourcedefs ./dirdef/oltp.def
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ASSUMETARGETDEFS
USERID ogg@orcl,PASSWORD ogg
DISCARDFILE ./dirrpt/reptrsf_gg2.dsc,PURGE
MAP yxuser2.test2, TARGET ssyxuser.sstable2, &
SQLEXEC (id lookup, &
QUERY " SELECT dqcode2 into desc_param FROM ssyxuser.dqmapping " &
" WHERE dqcode1 = :dqcode_in " , &
PARAMS (dqcode_in = name3)), &
COLMAP (USEDEFAULTS, &
ssid = ID, &
sname1 = @STREXT(name1,0,12), &
sname2 = lookup.desc_param , &
sname3 = name4, &
saddcol = @COMPUTE (ID + 100) );



REPLICAT reptrsf
sourcedefs ./dirdef/oltp.def
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ASSUMETARGETDEFS
USERID ogg@orcl,PASSWORD ogg
DISCARDFILE ./dirrpt/reptrsf_gg2.dsc,PURGE
MAP yxuser2.test2, TARGET ssyxuser.sstable2, &

SQLEXEC (SPNAME ssyxuser.LOOKUP_ACCOUNT, ID lookup1, PARAMS (dqcode_in = ID)), &
COLMAP (USEDEFAULTS, &
ssid = ID, &
sname1 = @STREXT(name1,0,12), &
sname2 =@GETVAL (lookup1.dqcode_out), &
sname3 = name4, &
saddcol = @COMPUTE (ID + 100) );