orcleAndqad数据互同步方法.doc

方法一
利用DB2联合数据库
Oracle联合表
WPS_EXEC_SEQUENCE_TAB，WPS_ERPBOM，WPS_ERPPART，WPS_ERPSUPPLIER
QAD联合表
首先安装QAD数据库驱动,运行ERP系统客户端程序clienthelper.exe,安装QAD测试库驱动.
通过ODBC连接,建立数据源
数据源名
服务地址
服务端端口
数据库名
用户名/密码
erpdb
192.168.1.4
20201
test
Root/1
erptestsuzuki
192.168.1.4
20203
test
Root/1

xydcseq_mstr，xydcbom_mstr，xydcvend_mstr，xydcpt_mstr
存储过程
过程P_READ_QADTOORACLE
依次利用游标读取QAD联合数据库中的表,检查是否有记录更新,有则同步到oracle数据库中对应表对应记录
CREATE PROCEDURE DB2ADMIN.P_READ_QADTOORACLE ( )
------------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
BEGIN
------------------------------------------------------------------------
-- 主同步表变量
------------------------------------------------------------------------
DECLARE dgno varchar(50);
DECLARE execstate INTEGER;
DECLARE executes INTEGER;

------------------------------------------------------------------------
-- BOM表变量
------------------------------------------------------------------------
DECLARE bomerpno varchar(50);
DECLARE bompdmno varchar(50);
DECLARE bomerppartno varchar(50);
DECLARE bompdmpartno varchar(50);
DECLARE bomstate INTEGER;
DECLARE bomrmks varchar(80);

------------------------------------------------------------------------
-- 零件表变量
------------------------------------------------------------------------
DECLARE parterppartno varchar(50);
DECLARE partstate INTEGER;
DECLARE partrmks varchar(80);

------------------------------------------------------------------------
-- 供应商表变量
------------------------------------------------------------------------
DECLARE supperppartno varchar(50);
DECLARE suppcode varchar(50);
DECLARE suppstate INTEGER;
DECLARE supprmks varchar(80);

DECLARE tmpSQL varchar(1000);

DECLARE count1 INTEGER;
DECLARE count2 INTEGER;
DECLARE count3 INTEGER;
DECLARE count4 INTEGER;

DECLARE errsql varchar(2000);
DECLARE v_Message VARCHAR(2000);
----------------------------------------------------------------------------
-- 游标定义,主表只读取最新的10条设变进行处理 ,其它表通过设变号读取
----------------------------------------------------------------------------

declare cur1 cursor for SELECT designNO, Execute FROM DB2ADMIN.WPS_EXEC_SEQUENCE_TAB where Execute<1 order by GTime;
declare curtemp cursor WITH RETURN TO CALLER FOR S1;

DECLARE exit handler FOR sqlexception
begin
--开始异常处理
VALUES ('设变号：'||dgno||' 错误描述:'||errsql )
INTO v_Message;
--设置存储过程返回值
SIGNAL SQLSTATE '99999'
SET MESSAGE_TEXT =v_Message;
end;

set count1= (select count(*) from (SELECT designNO, Execute FROM DB2ADMIN.WPS_EXEC_SEQUENCE_TAB where Execute<1 order by GTime) as a);
open cur1;
while count1>0 do
fetch cur1 into dgno,executes;
if(executes<0) then goto A;end if;
set execstate=(select xydcseq_exec from DB2ADMIN.xydcseq_mstr where xydcseq_no=dgno);
if (execstate>0) then
-- set dgno=''''||dgno||'''';
set errsql='更新[WPS_EXEC_SEQUENCE_TAB]出错！';
update WPS_EXEC_SEQUENCE_TAB set Execute =execstate where designno=dgno;
----------------bom表
set count2=(select count(*) from xydcbom_mstr where xydcbom_nbr=dgno);
set tmpSQL='select xydcbom_fg, xydcbom_pdmfg, xydcbom_pdm_part, xydcbom_erp_part , xydcbom_status, xydcbom_rmks from xydcbom_mstr where
xydcbom_nbr='''||dgno||'''';
PREPARE S1 from tmpSQL;
Open curtemp;
While count2>0 do
fetch curtemp into bomerpno, bompdmno, bompdmpartno, bomerppartno, bomstate,bomrmks;
if(bomstate>0) then
set errsql='更新[wps_erpbom]出错！';
update wps_erpbom set execueState=char(bomstate),remark=bomrmks where designNO=dgno and erpProductNO=bomerpno and pdmProductNO=bompdmno and pdmPartNO=
bompdmpartno and eprPartNO= bomerppartno;
end if;
set count2=count2-1;
End while;
Close curtemp;
----------------零件表
set count3=(select count(*) from xydcpt_mstr where xydcpt_nbr =dgno);
set tmpSQL='select xydcpt_part, xydcpt_status, xydcpt_rmks from xydcpt_mstr where xydcpt_nbr='''||dgno||'''';
PREPARE S1 from tmpSQL;
Open curtemp;
While count3>0 do
fetch curtemp into parterppartno, partstate,partrmks;
if(partstate>0) then
set errsql='更新[wps_erppart]出错！';
update wps_erppart set execueState=char(partstate),remark=partrmks where designno=dgno and erpPartNO= parterppartno;
end if;
set count3=count3-1;
End while;
Close curtemp;
----------------供应商表
set count4=(select count(*) from xydcvend_mstr where xydcvend_nbr =dgno);
set tmpSQL='select xydcvend_part, xydcvend_code, xydcvend_status, xydcvend_rmks from xydcvend_mstr where xydcvend_nbr='''||dgno||'''';
PREPARE S1 from tmpSQL;
Open curtemp;
While count4>0 do
fetch curtemp into supperppartno, suppcode,suppstate,supprmks;
if(suppstate>0) then
set errsql='更新[WPS_ERPSupplier]出错！';
update WPS_ERPSupplier set execueState=char(suppstate),remark=supprmks where designno=dgno and erpPartNO= supperppartno and supplierCode= suppcode;
end if;
set count4=count4-1;
End while;
Close curtemp;

end if;
set count1=count1-1;
end while;
A:
Close cur1;
end

过程P_SYN_QAD ( )
CREATE PROCEDURE DB2ADMIN.P_SYN_QAD ( )
------------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
BEGIN
DECLARE tmpSQL varchar(1000);
DECLARE errsql varchar(2000);
DECLARE tmp varchar(500);

DECLARE dgno varchar(50);
DECLARE gtime varchar(50);
DECLARE executes INTEGER;

DECLARE count1 INTEGER;
DECLARE count2 INTEGER;
DECLARE count3 INTEGER;

DECLARE state1 INTEGER;
------------------------------------------------------------------------
-- BOM表变量
------------------------------------------------------------------------
DECLARE bomerpno varchar(50);
DECLARE bompdmno varchar(50);
DECLARE bomerppartno varchar(50);
DECLARE bompdmpartno varchar(50);
DECLARE bomstate INTEGER;
DECLARE bomrmks varchar(80);

------------------------------------------------------------------------
-- 零件表变量
------------------------------------------------------------------------
DECLARE parterppartno varchar(50);
DECLARE partstate INTEGER;
DECLARE partrmks varchar(80);

------------------------------------------------------------------------
-- 供应商表变量
------------------------------------------------------------------------
DECLARE supperppartno varchar(50);
DECLARE suppcode varchar(50);
DECLARE suppstate INTEGER;
DECLARE supprmks varchar(80);
DECLARE v_Message VARCHAR(2000);

----------------------------------------------------------------------------
-- 游标定义,主表只读取最新的10条设变进行处理 ,其它表通过设变号读取
----------------------------------------------------------------------------

--declare cur1 cursor for select designNO, GTime, Execute from (SELECT rownumber() over(order by GTime) as m, designNO, GTime, Execute FROM DB2ADMIN.WPS_EXEC_SEQUENCE_TAB where Execute<1) as a;
declare cur1 cursor for SELECT designNO, GTime, Execute FROM DB2ADMIN.WPS_EXEC_SEQUENCE_TAB where Execute<1 order by GTime;

declare curtemp cursor WITH RETURN TO CALLER FOR S1;
DECLARE exit handler FOR sqlexception
begin
--开始异常处理
VALUES ('设变号：'||dgno||' 错误描述:'||errsql )
INTO v_Message;
--设置存储过程返回值
SIGNAL SQLSTATE '99999'
SET MESSAGE_TEXT =v_Message;
end;
set count1= (select count(*) from (SELECT designNO, GTime, Execute FROM DB2ADMIN.WPS_EXEC_SEQUENCE_TAB where Execute<1 order by GTime) as a);

open cur1;
while count1>0 do
fetch cur1 into dgno,gtime, executes;
-----------------主表
if(executes<0) then goto A;end if;
set count2=(select count(*) from DB2ADMIN.xydcseq_mstr where xydcseq_no=dgno);
if(count2<1) then
set errsql='插入[xydcseq_mstr]出错！';
insert into DB2ADMIN.xydcseq_mstr(xydcseq_no, xydcseq_date, xydcseq_exec) values(dgno,date(gtime), executes);
else
/*set state1=(select xydcseq_exec from DB2ADMIN.xydcseq_mstr where xydcseq_no=dgno);
if(state1>0) then
set errsql='更新[WPS_EXEC_SEQUENCE_TAB]出错！';
update WPS_EXEC_SEQUENCE_TAB set Execute =state1 where designno=dgno;commit;
end if;*/
end if;
------------------BOM表
set count2=(select count(*) from DB2ADMIN. xydcbom_mstr where xydcbom_nbr=dgno);
set count3=(select count(*) from DB2ADMIN. wps_erpbom where designno=dgno);
if(count2<1 and count3>0) then
set errsql='插入[Xydcbom_mstr]出错！';
--错误sql=insert into DB2ADMIN. Xydcbom_mstr(select designNO, erpProductNO, pdmProductNO, Area, pdmPartNO, colorDivision, colorCode, eprPartNO, cast(char(numBefore) as decimal(10,2)), useArea, useAreaRate, workshop, cast(char(workShopNum) as decimal(10,2)), date(effectiveDate),date(expireDate) ,structureType , cast(char(wasteRate) as decimal(10,2)) ,cast(char(orderAheadDate) as INTEGER), cast(char(execueState) as INTEGER), remark from wps_erpbom where designno='||dgno||')';
insert into DB2ADMIN. Xydcbom_mstr(select designNO, erpProductNO, pdmProductNO, Area, pdmPartNO, colorDivision, colorCode, eprPartNO, cast
(char(numBefore) as decimal(10,2)), useArea, useAreaRate, workshop, cast(char(workShopNum) as decimal(10,2)), date(effectiveDate),date(expireDate)
,structureType , cast(char(wasteRate) as decimal(10,2)) ,cast(char(orderAheadDate) as INTEGER), cast(char(execueState) as INTEGER), remark from wps_erpbom
where designno=dgno);
else
/*set tmpSQL='select xydcbom_fg, xydcbom_pdmfg, xydcbom_pdm_part, xydcbom_erp_part , xydcbom_status, xydcbom_rmks from xydcbom_mstr where
xydcbom_nbr='''||dgno||'''';
PREPARE S1 from tmpSQL;
Open curtemp;
While count2>0 do
fetch curtemp into bomerpno, bompdmno, bompdmpartno, bomerppartno, bomstate,bomrmks;
if(bomstate>0) then
set errsql='更新[wps_erpbom]出错！';
update wps_erpbom set execueState=char(bomstate),remark=bomrmks where designNO=dgno and erpProductNO=bomerpno and
pdmProductNO=bompdmno and pdmPartNO= bompdmpartno and eprPartNO= bomerppartno;
commit;
end if;
set count2=count2-1;
End while;
Close curtemp;*/

end if;
---------------零件表
set count2=(select count(*) from DB2ADMIN. xydcpt_mstr where xydcpt_nbr=dgno);
set count3=(select count(*) from DB2ADMIN. wps_erppart where designno=dgno);
if(count2<1 and count3>0) then
set errsql='插入[xydcpt_mstr]出错！';
--错误sql=insert into DB2ADMIN. xydcpt_mstr(select designNO, erpPartNO, erpPartName, unit, productType, itemType, site, location, cast(char(mainDate) as smallint), cast(char(planOrder) as smallint), orderPrinciple, cast(char(orderLifecycle) as INTEGER), cast(char(safeStockLevel) as decimal(10,2)), supplier, purchaseOrProduce , cast(char(isUseOldPart) as smallint), cast(char(jit) as smallint) , pt_chr07, cast(char(execueState) as INTEGER), remark from wps_erppart where designno='||dgno||')';
insert into DB2ADMIN. xydcpt_mstr(select designNO, erpPartNO, erpPartName, unit, productType, itemType, site, location, cast(char(mainDate)
as smallint), cast(char(planOrder) as smallint), orderPrinciple, cast(char(orderLifecycle) as INTEGER), cast(char(safeStockLevel) as decimal(10,2)),
supplier, purchaseOrProduce , cast(char(isUseOldPart) as smallint), cast(char(jit) as smallint) , pt_chr07, cast(char(execueState) as INTEGER), remark from
wps_erppart where designno=dgno);
else
/*set tmpSQL='select xydcpt_part, xydcpt_status, xydcpt_rmks from xydcpt_mstr where xydcpt_nbr='''||dgno||'''';
PREPARE S1 from tmpSQL;
Open curtemp;
While count2>0 do
fetch curtemp into parterppartno, partstate,partrmks;
if(partstate>0) then
set errsql='更新[wps_erppart]出错！';
update wps_erppart set execueState=char(partstate),remark=partrmks where designno=dgno and erpPartNO= parterppartno;
commit;
end if;
set count2=count2-1;
End while;
Close curtemp;*/

end if;
--------------供应商表
set count2=(select count(*) from DB2ADMIN. xydcvend_mstr where xydcvend_nbr=dgno);
set count3=(select count(*) from DB2ADMIN. WPS_ERPSupplier where designno=dgno);
if(count2<1 and count3>0) then
set errsql='插入[xydcvend_mstr]出错！';
--错误sql=insert into DB2ADMIN. xydcvend_mstr(select designNO, erpPartNO, supplierCode, supplierName, supplierChgType, cast(char(packNum) as decimal(10,2)), cast(char(numOfDays) as INTEGER), cast(char(lifecycle) as INTEGER) , sdt , cast(char(supply) as decimal(10,2)) , date(supplyStartDate), assmbleNO,cast(char(execueState) as INTEGER), remark from WPS_ERPSupplier where designno='||dgno||')';
insert into DB2ADMIN. xydcvend_mstr(select designNO, erpPartNO, supplierCode, supplierName, supplierChgType, cast(char(packNum) as decimal
(10,2)), cast(char(numOfDays) as INTEGER), cast(char(lifecycle) as INTEGER) , sdt , cast(char(supply) as decimal(10,2)) , date(supplyStartDate),
assmbleNO,cast(char(execueState) as INTEGER), remark from WPS_ERPSupplier where designno=dgno);
else
/*
set tmpSQL='select xydcvend_part, xydcvend_code, xydcvend_status, xydcvend_rmks from xydcvend_mstr where xydcvend_nbr='''||dgno||'''';
PREPARE S1 from tmpSQL;
Open curtemp;
While count2>0 do
fetch curtemp into supperppartno, suppcode,suppstate,supprmks;
if(suppstate>0) then
set errsql='更新[WPS_ERPSupplier]出错！';
update WPS_ERPSupplier set execueState=char(suppstate),remark=supprmks where designno=dgno and erpPartNO= supperppartno and
supplierCode= suppcode;
commit;
end if;
set count2=count2-1;
End while;
Close curtemp;
*/

end if;
set count1=count1-1;--commit;
end while;
A:
Close cur1;
end
利用DB2复制功能
建立两个Capture 控制服务器
DB2(中转服务器)
建立一个DB2的capture服务器

Oracle(非DB2服务器)
注册昵称WPS_EXEC_SEQUENCE_TAB，WPS_ERPBOM，WPS_ERPPART，WPS_ERPSUPPLIER
建立一个Apply 控制服务器
建立虚拟预定集(可以正常运行的,具体源和目标无所谓.执行频率暂设为5分钟一次.该预定集功能主要为定时执行自定义的存储过程P_READ_ORACLETOQAD将数据由oracle同步到QAD)
建立虚拟预定集(可以正常运行的,具体源和目标无所谓.执行频率暂设为5分钟一次.该预定集功能主要为定时执行自定义的存储过程P_READ_QADTOORACLE将数据由QAD同步到oracle)
搭建部署图
联合oracle
联合progress
DB2中转数据库
复制数据
利用虚拟的复制服务器监控progress数据库变化,同步到oracle数据库中
基于DB2联合数据库
oracle
progress
利用虚拟的复制服务器监控DB2数据库变化,同步到progress数据库中