首页 > 代码库 > ODI多库抽取到一个库操作
ODI多库抽取到一个库操作
需要将LIMS四套系统数据抽取到数据中心，最先方案如下，以REPORTS表为例，如下对应四个模型
分别建立了四个映射与四个包，但是在进行模拟测试时，发现数据抽取有问题
BEGIN UPDATE REPORTS SET FOLDERNO = FOLDERNO ; UPDATE CTI_REL_DATA.REPORTS SET FOLDERNO = FOLDERNO; UPDATE CTI_EDD_DATA.REPORTS SET FOLDERNO = FOLDERNO; UPDATE CTI_FDD_DATA.REPORTS SET FOLDERNO = FOLDERNO; COMMIT;END;
用上面语句模拟生产环境下，四套系统都有数据更新，提示错误如下：
原因是这样的，因为四套系统都要同步到ODI_TAG_DC下面，而在数据抽取时需要在DC生成临时对象（比如C$_临时表），我们用CREATE和DROP来表示创建和删除临时表，会话1和会话2分别表示两个不同会话，分别为不同模型传递数据，比如会话1为REL同步数据，会话2为SCL同步数据。会话1先执行CREATE,再加载数据，此时会话2也开始同步数据，先执行CREATE,发现临时表已经被会话1创建，此时在日志中会有提示信息，并不会影响后面步骤的执行。会话1同步完成，执行了DROP删除临时表，接下来，会话2要加载数据，所谓加载数据就是从源模型中抽取数据到临时表，这时的INSERT操作就会报错，因为临时表已经被会话1删除了，同步中止，错误提示如下截图。
为解决该问题，需要将所有数据汇总到一个模型下面。我们用CREATE USER创建了一个用户CTI_DATA。并为其创建了模型。下面以EDD环境FOLDERS表同步为例，说明如何将数据汇总到一个模型下面，并创建映射执行同步。
1、先创建视图FODLERS
CREATE OR REPLACE VIEW FOLDERS ASSELECT ORIGREC, LIMS_CODE, FOLDERNO, PONO, SUB_PONO, RASCLIENTID, DEPT, CHECKSTATUS, CREATER, SERVICE_TYPE, SERVICE_PERIOD, DUEDATE, LOGDATE, STARTDATE, FINISHDATE, STATUS, DISPSTATUS, QUOTATIONNO, SUBCONTRACT_FOLDERNO, JUDGE_STS, REPORT_TYPE, REPORT_FORMAT, REPORT_SEND_TYPE, RETURN_SAMPLE, BUSINESS_TYPE, NULL TESTTYPE, REPORTTITLE, REPORTTITLE_ENG, REPORTADDRESS, REPORTADDRESS_ENG FROM CTI_SCL_DATA.FOLDERSUNION ALLSELECT ORIGREC, LIMS_CODE, FOLDERNO, PONO, SUB_PONO, RASCLIENTID, DEPT, CHECKSTATUS, CREATER, SERVICE_TYPE, SERVICE_PERIOD, DUEDATE, LOGDATE, STARTDATE, FINISHDATE, STATUS, DISPSTATUS, QUOTATIONNO, SUBCONTRACT_FOLDERNO, JUDGE_STS, REPORT_TYPE, REPORT_FORMAT, REPORT_SEND_TYPE, RETURN_SAMPLE, BUSINESS_TYPE, TESTTYPE, REPORTTITLE, REPORTTITLE_ENG, REPORTADDRESS, REPORTADDRESS_ENG FROM CTI_REL_DATA.FOLDERSUNION ALLSELECT ORIGREC, LIMS_CODE, FOLDERNO, PONO, SUB_PONO, RASCLIENTID, DEPT, CHECKSTATUS, CREATER, SERVICE_TYPE, SERVICE_PERIOD, DUEDATE, LOGDATE, STARTDATE, FINISHDATE, STATUS, DISPSTATUS, QUOTATIONNO, SUBCONTRACT_FOLDERNO, JUDGE_STS, REPORT_TYPE, REPORT_FORMAT, REPORT_SEND_TYPE, RETURN_SAMPLE, BUSINESS_TYPE, TESTTYPE, REPORTTITLE, REPORTTITLE_ENG, REPORTADDRESS, REPORTADDRESS_ENG FROM CTI_EDD_DATA.FOLDERS;
上面的列为要同步的列，可以从DC中获取列信息。前面的SCL、REL之前已经创建好了的。
2、创建日志记录表并授权
create table J$FOLDERS( jrn_subscriber VARCHAR2(400 CHAR) not null, jrn_consumed VARCHAR2(1 CHAR), jrn_flag VARCHAR2(1 CHAR), jrn_date DATE, origrec NUMBER, lims_code NUMBER)grant insert, update, delete, references, alter, index on J$FOLDERS to CTI_REL_DATA;grant select on J$FOLDERS to CTI_REL_DATA with grant option;grant insert, update, delete, references, alter, index on J$FOLDERS to CTI_SCL_DATA;grant select on J$FOLDERS to CTI_SCL_DATA with grant option;
表事先已经创建好了的，我们只需要对CTI_EDD_DATA的授权
grant select on j$folders to cti_edd_data with grant option;grant insert,update,delete,references,alter,index on j$folders to cti_edd_data;
3、添加订阅用户
使用订阅用户名：FOLDERS
JRM_COUNT_CMD内容如下：
update CTI_DATA.SNP_SUBSCRIBERS SUBS set SUBS.JRN_ROW_COUNT=(select count(‘X‘) from CTI_DATA.J$FOLDERS JRN where JRN.JRN_SUBSCRIBER=‘$$SUBSCRIBER_NAME$$‘) where SUBS.JRN_SUBSCRIBER=‘$$SUBSCRIBER_NAME$$‘ and SUBS.JRN_TNAME = ‘CTI_DATA.FOLDERS‘
并为CTI_EDD_DATA授予权限
GRANT SELECT ON SNP_SUBSCRIBERS TO CTI_EDD_DATA;
4、修改CTI_EDD_DATA.FOLDERS的触发器
create or replace trigger T$FOLDERS after insert or update or delete on CTI_EDD_DATA.FOLDERS for each rowdeclare V_FLAG VARCHAR(1); V_ORIGREC NUMBER; V_LIMS_CODE NUMBER;begin if inserting then V_ORIGREC := :new.ORIGREC; V_LIMS_CODE := :new.LIMS_CODE; V_FLAG := ‘I‘; end if; if updating then V_ORIGREC := :new.ORIGREC; V_LIMS_CODE := :new.LIMS_CODE; V_FLAG := ‘I‘; end if; if deleting then V_ORIGREC := :old.ORIGREC; V_LIMS_CODE := :old.LIMS_CODE; V_FLAG := ‘D‘; end if;/* insert into CTI_EDD_DATA.J$FOLDERS (JRN_SUBSCRIBER, JRN_CONSUMED, JRN_FLAG, JRN_DATE, ORIGREC, LIMS_CODE) select JRN_SUBSCRIBER, ‘0‘, V_FLAG, sysdate, V_ORIGREC, V_LIMS_CODE from CTI_EDD_DATA.SNP_SUBSCRIBERS where JRN_TNAME = ‘CTI_EDD_DATA.FOLDERS‘;*/ insert into CTI_DATA.J$FOLDERS (JRN_SUBSCRIBER, JRN_CONSUMED, JRN_FLAG, JRN_DATE, ORIGREC, LIMS_CODE) select JRN_SUBSCRIBER, ‘0‘, V_FLAG, sysdate, V_ORIGREC, V_LIMS_CODE from CTI_DATA.SNP_SUBSCRIBERS where JRN_TNAME = ‘CTI_DATA.FOLDERS‘;end;
如上，注释掉原来部分，添加对CTI_DATA日志表的写入。
5、修改CTI_EDD_DATA.JV$DFOLDERS、CTI_EDD_DATA.JV$FOLDERS视图的定义
create or replace view jv$dfolders asselect decode(TARG.ROWID, null, ‘D‘, ‘I‘) AS JRN_FLAG, JRN.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, JRN.JRN_DATE AS JRN_DATE, JRN.ORIGREC AS ORIGREC, JRN.LIMS_CODE AS LIMS_CODE, TARG.FOLDERNO, TARG.PONO, TARG.SUB_PONO, TARG.RASCLIENTID, TARG.DEPT, TARG.CHECKSTATUS, TARG.CREATER, TARG.SERVICE_TYPE, TARG.SERVICE_PERIOD, TARG.DUEDATE, TARG.LOGDATE, TARG.STARTDATE, TARG.FINISHDATE, TARG.STATUS, TARG.DISPSTATUS, TARG.QUOTATIONNO, TARG.SUBCONTRACT_FOLDERNO, TARG.JUDGE_STS, TARG.REPORT_TYPE, TARG.REPORT_FORMAT, TARG.REPORT_SEND_TYPE, TARG.RETURN_SAMPLE, TARG.BUSINESS_TYPE, TARG.TESTTYPE, TARG.REPORTTITLE, TARG.REPORTTITLE_ENG, TARG.REPORTADDRESS, TARG.REPORTADDRESS_ENG from (select L.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, L.ORIGREC AS ORIGREC, L.LIMS_CODE AS LIMS_CODE, max(L.JRN_DATE) AS JRN_DATE /* from CTI_EDD_DATA.J$FOLDERS L*/ from CTI_DATA.J$FOLDERS L where LIMS_CODE = 3 group by L.JRN_SUBSCRIBER, L.ORIGREC, L.LIMS_CODE) JRN, CTI_EDD_DATA.FOLDERS TARG where JRN.ORIGREC = TARG.ORIGREC(+) and JRN.LIMS_CODE = TARG.LIMS_CODE(+);
如上，注释掉/* from CTI_EDD_DATA.J$FOLDERS L*/，添加from CTI_DATA.J$FOLDERS L where LIMS_CODE = 3，另外，列信息通过DC表结构获取，删去了原来FN_ODI函数生成其他不相干列信息，以减少源表表结构发生变化时，导致视图不可用的状况发生。
另一个脚本如下 ：
create or replace view jv$folders asselect decode(TARG.ROWID, null, ‘D‘, ‘I‘) AS JRN_FLAG, JRN.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, JRN.JRN_DATE AS JRN_DATE, JRN.ORIGREC AS ORIGREC, JRN.LIMS_CODE AS LIMS_CODE, TARG.FOLDERNO, TARG.PONO, TARG.SUB_PONO, TARG.RASCLIENTID, TARG.DEPT, TARG.CHECKSTATUS, TARG.CREATER, TARG.SERVICE_TYPE, TARG.SERVICE_PERIOD, TARG.DUEDATE, TARG.LOGDATE, TARG.STARTDATE, TARG.FINISHDATE, TARG.STATUS, TARG.DISPSTATUS, TARG.QUOTATIONNO, TARG.SUBCONTRACT_FOLDERNO, TARG.JUDGE_STS, TARG.REPORT_TYPE, TARG.REPORT_FORMAT, TARG.REPORT_SEND_TYPE, TARG.RETURN_SAMPLE, TARG.BUSINESS_TYPE, TARG.TESTTYPE, TARG.REPORTTITLE, TARG.REPORTTITLE_ENG, TARG.REPORTADDRESS, TARG.REPORTADDRESS_ENG from (select L.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, L.ORIGREC AS ORIGREC, L.LIMS_CODE AS LIMS_CODE, max(L.JRN_DATE) AS JRN_DATE /*from CTI_EDD_DATA.J$FOLDERS L*/ from CTI_DATA.J$FOLDERS L where L.JRN_CONSUMED = ‘1‘ and L.LIMS_CODE = 3 group by L.JRN_SUBSCRIBER, L.ORIGREC, L.LIMS_CODE) JRN, CTI_EDD_DATA.FOLDERS TARG where JRN.ORIGREC = TARG.ORIGREC(+) and JRN.LIMS_CODE = TARG.LIMS_CODE(+);
6、创建或修改CTI_DATA.JV$DFOLDERS和JV$FOLDERS两视图
CREATE OR REPLACE VIEW JV$DFOLDERS ASSELECT JRN_FLAG, JRN_SUBSCRIBER, JRN_DATE, ORIGREC, LIMS_CODE, FOLDERNO, PONO, SUB_PONO, RASCLIENTID, DEPT, CHECKSTATUS, CREATER, SERVICE_TYPE, SERVICE_PERIOD, DUEDATE, LOGDATE, STARTDATE, FINISHDATE, STATUS, DISPSTATUS, QUOTATIONNO, SUBCONTRACT_FOLDERNO, JUDGE_STS, REPORT_TYPE, REPORT_FORMAT, REPORT_SEND_TYPE, RETURN_SAMPLE, BUSINESS_TYPE, TESTTYPE, REPORTTITLE, REPORTTITLE_ENG, REPORTADDRESS, REPORTADDRESS_ENG FROM CTI_SCL_DATA.JV$DfoldersUNION ALLSELECT JRN_FLAG, JRN_SUBSCRIBER, JRN_DATE, ORIGREC, LIMS_CODE, FOLDERNO, PONO, SUB_PONO, RASCLIENTID, DEPT, CHECKSTATUS, CREATER, SERVICE_TYPE, SERVICE_PERIOD, DUEDATE, LOGDATE, STARTDATE, FINISHDATE, STATUS, DISPSTATUS, QUOTATIONNO, SUBCONTRACT_FOLDERNO, JUDGE_STS, REPORT_TYPE, REPORT_FORMAT, REPORT_SEND_TYPE, RETURN_SAMPLE, BUSINESS_TYPE, TESTTYPE, REPORTTITLE, REPORTTITLE_ENG, REPORTADDRESS, REPORTADDRESS_ENG FROM CTI_REL_DATA.JV$DFOLDERSUNION ALLSELECT JRN_FLAG, JRN_SUBSCRIBER, JRN_DATE, ORIGREC, LIMS_CODE, FOLDERNO, PONO, SUB_PONO, RASCLIENTID, DEPT, CHECKSTATUS, CREATER, SERVICE_TYPE, SERVICE_PERIOD, DUEDATE, LOGDATE, STARTDATE, FINISHDATE, STATUS, DISPSTATUS, QUOTATIONNO, SUBCONTRACT_FOLDERNO, JUDGE_STS, REPORT_TYPE, REPORT_FORMAT, REPORT_SEND_TYPE, RETURN_SAMPLE, BUSINESS_TYPE, TESTTYPE, REPORTTITLE, REPORTTITLE_ENG, REPORTADDRESS, REPORTADDRESS_ENG FROM CTI_EDD_DATA.JV$DFOLDERS;CREATE OR REPLACE VIEW JV$FOLDERS ASSELECT JRN_FLAG, JRN_SUBSCRIBER, JRN_DATE, ORIGREC, LIMS_CODE, FOLDERNO, PONO, SUB_PONO, RASCLIENTID, DEPT, CHECKSTATUS, CREATER, SERVICE_TYPE, SERVICE_PERIOD, DUEDATE, LOGDATE, STARTDATE, FINISHDATE, STATUS, DISPSTATUS, QUOTATIONNO, SUBCONTRACT_FOLDERNO, JUDGE_STS, REPORT_TYPE, REPORT_FORMAT, REPORT_SEND_TYPE, RETURN_SAMPLE, BUSINESS_TYPE, TESTTYPE, REPORTTITLE, REPORTTITLE_ENG, REPORTADDRESS, REPORTADDRESS_ENG FROM CTI_SCL_DATA.JV$foldersUNION ALLSELECT JRN_FLAG, JRN_SUBSCRIBER, JRN_DATE, ORIGREC, LIMS_CODE, FOLDERNO, PONO, SUB_PONO, RASCLIENTID, DEPT, CHECKSTATUS, CREATER, SERVICE_TYPE, SERVICE_PERIOD, DUEDATE, LOGDATE, STARTDATE, FINISHDATE, STATUS, DISPSTATUS, QUOTATIONNO, SUBCONTRACT_FOLDERNO, JUDGE_STS, REPORT_TYPE, REPORT_FORMAT, REPORT_SEND_TYPE, RETURN_SAMPLE, BUSINESS_TYPE, TESTTYPE, REPORTTITLE, REPORTTITLE_ENG, REPORTADDRESS, REPORTADDRESS_ENG FROM CTI_REL_DATA.JV$FOLDERSUNION ALLSELECT JRN_FLAG, JRN_SUBSCRIBER, JRN_DATE, ORIGREC, LIMS_CODE, FOLDERNO, PONO, SUB_PONO, RASCLIENTID, DEPT, CHECKSTATUS, CREATER, SERVICE_TYPE, SERVICE_PERIOD, DUEDATE, LOGDATE, STARTDATE, FINISHDATE, STATUS, DISPSTATUS, QUOTATIONNO, SUBCONTRACT_FOLDERNO, JUDGE_STS, REPORT_TYPE, REPORT_FORMAT, REPORT_SEND_TYPE, RETURN_SAMPLE, BUSINESS_TYPE, TESTTYPE, REPORTTITLE, REPORTTITLE_ENG, REPORTADDRESS, REPORTADDRESS_ENG FROM CTI_EDD_DATA.JV$FOLDERS;
注意：上面创建两对象时，应该指定列名（而不使用*），以减少为数据抽取添加列时（对源表视图添加列时），造成CTI_DATA两视图不可访问的情况。
接下来，需要创建映射及包。下面，进行两步的测试
原数据如下：
SELECT LIMS_CODE, FOLDERNO, CHECKSTATUS, DISPSTATUS FROM FOLDERS WHERE FOLDERNO IN (‘A1605210020117‘, ‘MAT1000002‘, ‘HLNBE00003401‘);
更新数据
UPDATE CTI_REL_DATA.FOLDERS SET DISPSTATUS = ‘指派中‘ WHERE FOLDERNO = ‘MAT1000002‘;UPDATE CTI_SCL_DATA.FOLDERS SET CHECKSTATUS = ‘受理中‘ WHERE FOLDERNO = ‘A1605210020117‘;UPDATE CTI_EDD_DATA.FOLDERS SET CHECKSTATUS = ‘受理中‘ WHERE FOLDERNO = ‘HLNBE00003401‘;COMMIT;
查看日志数据
执行同步操作
数据刷新成功。执行修改+删除操作
UPDATE CTI_REL_DATA.FOLDERS SET DISPSTATUS = ‘指派中1‘ WHERE FOLDERNO = ‘MAT1000002‘;UPDATE CTI_SCL_DATA.FOLDERS SET CHECKSTATUS = ‘受理中1‘,FOLDERNO = ‘A0000000000‘ WHERE FOLDERNO = ‘A1605210020117‘;DELETE FROM CTI_EDD_DATA.FOLDERS WHERE FOLDERNO = ‘HLNBE00003401‘;COMMIT;
日志数据
执行同步，查询数据
SELECT LIMS_CODE, FOLDERNO, CHECKSTATUS, DISPSTATUS FROM FOLDERS WHERE FOLDERNO IN (‘A1605210020117‘, ‘MAT1000002‘, ‘HLNBE00003401‘,‘A0000000000‘);
数据同步成功。
注意：建立CTI_SRC_DATA模型时，逆向工程要选择视图
另外，中间表的命名，可以从物理结构看到
ODI多库抽取到一个库操作
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。