ogg(字段转换)

ADD RMTTRAIL /ggs/dirdat/tr000003

ADD RMTTRAIL ./mt, EXTRACT extcdc1




ADD RMTTRAIL ./dirdat/tr, EXTRACT extcdc1

ADD RMTTRAIL ./dirdat/dt, EXTRACT extcdc1

----------------------------------------------
DELETE RMTTRAIL ./dirdat/dt

DELETE replicat reptrsf

ADD EXTTRAIL dirdat/aa, EXTRACT extcdc1

ADD EXTRACT datapump, EXTTRAILSOURCE dirdat/aa , begin now
ADD RMTTRAIL ./dirdat/dt, EXTRACT DATAPUMP

extract extcdc1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg@orcl,password ogg
rmthost 192.168.133.129,mgrport 7809
rmttrail ./dirdat/dt
table yxuser1.test1;



ADD EXTTRAIL ./dirdat/aa, EXTRACT EXTCDC1



-----------------------------zhuanhuan 源端-------------------------------
---抽取进程
add extract Datatrsf,tranlog,begin now,threads 1
ADD EXTTRAIL ./dirdat/df, EXTRACT Datatrsf
edit params datatrsf

extract datatrsf
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg@orcl,password ogg
--rmthost 192.168.133.129,mgrport 7809
EXTTRAIL ./dirdat/df
table yxuser2.test2;

---投递进程

ADD EXTRACT trsfpump, EXTTRAILSOURCE ./dirdat/df , begin now
ADD RMTTRAIL ./dirdat/df, EXTRACT trsfpump

edit params trsfpump


extract trsfpump
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
PASSTHRU
--userid ogg@orcl,password ogg
rmthost 192.168.133.129,mgrport 7809
rmttrail ./dirdat/df
table yxuser2.test2;

---生成表定义文件
edit params defgen

DEFSFILE ./dirdef/oltp.def
userid ogg@orcl,password ogg
table yxuser2.test2;

./defgen paramfile dirprm/defgen.prm


----------目标端mobiaoduan-------------------------------

dblogin userid ogg,password oracle
add checkpointtable ogg.ggschkpttrsf

add replicat reptrsf,exttrail ./dirdat/df,checkpointtable ogg.ggschkpttrsf

edit params reptrsf

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 = 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) );