数据库之间数同步

有些业务需要不同数据库的数据进行同步。

下面的存储过程实现数据同步

SET SCHEMA TMISUSR ;


SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";


CREATE PROCEDURE "TMISUSR"."PRC_BATCH_PAYACTOR_MOVE"
 (OUT "PO_I_RETCODE" INTEGER, 
  OUT "PO_S_RETTEXT" VARCHAR(2048), 
  OUT "PO_S_STMT" VARCHAR(2048)
 ) 
  SPECIFIC "TMISUSR"."SQL130802221333200"
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  P1: BEGIN
-- 声明变量
DECLARE V_s_Stmt Varchar(3000) DEFAULT ' ';
DECLARE V_s_return Varchar(3000) DEFAULT ' ';
DECLARE v_s_ErrMsg              Varchar(1024)   DEFAULT ' '; --错误原因描述
DECLARE SQLCODE INT;
DECLARE SQLSTATE CHAR(5);
------------------------------------------------------------------------
   -- SQL 存储过程 
-- po_i_RetCode （0－成功，其他－失败）

--------------------------------------------------------------------
-----****************    功能说明    ****************** -------------
--------------------------------------------------------------------

-- 同步支付系统行号，从TMISPTDB同步到TMISRPDB
------------------------------------------------------------------------

--声明异常信息
declare exit handler for SQLEXCEPTION
begin
GET DIAGNOSTICS EXCEPTION 1 v_s_ErrMsg = MESSAGE_TEXT;
   values(SQLCODE,'同步支付系统行号发生异常，错误状态：'||SQLSTATE||'说明:'||v_s_ErrMsg,V_s_Stmt) into po_i_RetCode,po_s_RetText,po_s_Stmt;
end;

set V_s_Stmt=' delete from tcbs_td_payactor ';
prepare s1 from V_s_Stmt;
execute s1;

set V_s_return = 'load from (database tmisptdb select * from tcbs_td_payactor) of cursor  insert into tcbs_td_payactor COPY YES TO /tmisdatas/etlloadtmp DATA BUFFER 32768 SORT BUFFER 32768 CPU_PARALLELISM 2 DISK_PARALLELISM 1';
call SYSPROC.admin_cmd(V_s_return);
commit;
set po_s_Stmt = V_s_return;
values(0,'同步支付系统行号成功!') into po_i_RetCode,po_s_RetText;
END P1;