DB2大数据重一张表复制到另外一张表存储过程和直接在客户端中执行

--其中F_GET_PKGSTR('batchid', PI_PCKBED)是获取参数值的方法

--存储过程用法

CREATE OR REPLACE PROCEDURE P_MOVER_DATA (

  IN PI_PCKBED VARCHAR(32672),
IN PI_PCKEND VARCHAR(32672))
BEGIN


  DECLARE SQL1 VARCHAR(200);
  DECLARE SQL2 VARCHAR(200);
  DECLARE SQL3 VARCHAR(200);
  DECLARE SQL4 VARCHAR(200);
  DECLARE SQL5 VARCHAR(200);
  DECLARE SQL6 VARCHAR(200);
  DECLARE SQL7 VARCHAR(200);
  DECLARE SQL8 VARCHAR(200);
  DECLARE SQL9 VARCHAR(200);
  DECLARE SQL10 VARCHAR(200);
  DECLARE SQL11 VARCHAR(200);
  DECLARE SQL12 VARCHAR(200);
  
  --备份原始流水表 
  SET SQL1='SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,TRANBR, ACCTBR,'
  +'ITEMCD, CRCYCD, IOFLAG, CENTCD, PRSNCD, CUSTCD, PRDUCD,PRLNCD, ACCTNO,'
  +'TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD,SOURDT, SOURSQ,'
  +'SOURST, SRVCSQ, TOITEM, ASSIS0, ASSIS1, ASSIS2, ASSIS3,ASSIS4, ASSIS5,'
  +'ASSIS6, ASSIS7, ASSIS8, ASSIS9, DEALST, PRCSCD, ITEMNA,PRCSNA, STRKST,'
  +'STRKDT, STRKSQ, CRCYSD, TRANEQ, TAXBST, DEALMG, BATCHID FROM KL_GLI_VCHR'
  +'WHERE TRANDT<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND TRANDT>='+F_GET_PKGSTR('batchid', PI_PCKBED);
  SET SQL2='KL_GLI_VCHR_H';
  CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL1||') OF CURSOR INSERT INTO '||SQL2);
  
  --备份特殊处理后的流水表
  SET SQL3='SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,TRANBR, ACCTBR,'
  +'ITEMCD, CRCYCD, IOFLAG, CENTCD, PRSNCD, CUSTCD, PRDUCD,PRLNCD, ACCTNO,'
  +'TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD,SOURDT, SOURSQ,'
  +'SOURST, SRVCSQ, TOITEM, ASSIS0, ASSIS1, ASSIS2, ASSIS3,ASSIS4, ASSIS5,'
  +'ASSIS6, ASSIS7, ASSIS8, ASSIS9, DEALST, PRCSCD, ITEMNA,PRCSNA, STRKST,'
  +'STRKDT, STRKSQ, CRCYSD, TRANEQ, TAXBST, DEALMG, BATCHID FROM GLI_VCHR'
  +'WHERE TRANDT<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND TRANDT>='+F_GET_PKGSTR('batchid', PI_PCKBED);
  SET SQL4='GLI_VCHR_H';
  CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL3||') OF CURSOR INSERT INTO '||SQL4);
   
   --备份会计分录表
   SET SQL5='SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,TRANBR, ACCTBR,'
   +'ITEMCD, CRCYCD, CENTCD, PRSNCD, CUSTCD, PRDUCD, PRLNCD,ACCTNO, TRANTP,'
   +'AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD, TOITEM,ASSIS0, ASSIS1,'
   +'ASSIS2, ASSIS3, ASSIS4, ASSIS5, ASSIS6, ASSIS7, ASSIS8,ASSIS9, TRANST,'
   +'TRSDAM, CRCYSD FROM TXA_VCHR WHERE TRANDT<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND' 
   +'TRANDT>='+F_GET_PKGSTR('batchid', PI_PCKBED);
   SET SQL6='TXA_VCHR_H';
   CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL5||') OF CURSOR INSERT INTO '||SQL6);
  
   --备份非分期还款记录表
   SET SQL7='SELECT RD80DATE, RD80DPNOA, RD80DPNO, RD80CLITYP,RD80DUEBNO,'
   +'RD80CUR, RD80DATEO, RD80OWNTYP, RD80STANO, RD80PAYCHA, RD80PBTYPE,RD80PBMODE,'
   +'RD80ACIDP, RD80AMTP, RD80AMTPCA, RD80OPR, RD80STAN, RD80SEQ, BATCHID FROM '
   +'RDPF80 WHERE RD80DATE<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND RD80DATE>='+F_GET_PKGSTR('batchid', PI_PCKBED);
   SET SQL8='RDPF80_H';
   CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL7||') OF CURSOR INSERT INTO '||SQL8);


   --备份分期还款记录表
   SET SQL7='SELECT RD90DATE, RD90DPNOA, RD90DPNO, RD90CLITYP, RD90DUEBNO,'
   +'RD90CUR, RD90CACNT, RD90DATEO, RD90STANO, RD90PAYCHA, RD90ACIDP, RD90ACIDRP, RD90PRITYP,'
   +'RD90PPTYP, RD90AMTPP, RD90AMTPCA, RD90ACIDA, RD90ACIDRA, RD90ACRTYP, RD90PATYP, RD90AMTPA,'
   +'RD90AMTACA, RD90OPR, RD90STAN, RD90SEQ, BATCHID FROM RDPF90 WHERE RD90DATE<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND '
   +'RD90DATE>='+F_GET_PKGSTR('batchid', PI_PCKBED);
   SET SQL8='RDPF90_H';
   CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL9||') OF CURSOR INSERT INTO '||SQL10);
   
   --备份日志表
   SET SQL7='SELECT BSNSDT, USERCD, TRANTI, LOGMSG FROM COM_LOGM WHERE BSNSDT<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND '
   +'BSNSDT>='+F_GET_PKGSTR('batchid', PI_PCKBED);
   SET SQL8='COM_LOGM_H';
   CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL11||') OF CURSOR INSERT INTO '||SQL12);
   

END;

--直接在客户端中执行

COMMIT 
   ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
--备份原始流水表
   INSERT INTO KL_GLI_VCHR_H SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,
   TRANBR, ACCTBR, ITEMCD, CRCYCD, IOFLAG, CENTCD, PRSNCD, CUSTCD, PRDUCD,
   PRLNCD, ACCTNO, TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD,
   SOURDT, SOURSQ, SOURST, SRVCSQ, TOITEM, ASSIS0, ASSIS1, ASSIS2, ASSIS3,
   ASSIS4, ASSIS5, ASSIS6, ASSIS7, ASSIS8, ASSIS9, DEALST, PRCSCD, ITEMNA,
   PRCSNA, STRKST, STRKDT, STRKSQ, CRCYSD, TRANEQ, TAXBST, DEALMG, BATCHID
   FROM KL_GLI_VCHR WHERE TRANDT<=F_GET_PKGSTR('batchid', PI_PCKEND) AND 
   TRANDT>=F_GET_PKGSTR('batchid', PI_PCKBED);
   COMMIT;
   
   COMMIT 
   ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
   --备份特殊处理后的流水表
   INSERT INTO GLI_VCHR_H SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,
   TRANBR, ACCTBR, ITEMCD, CRCYCD, IOFLAG, CENTCD, PRSNCD, CUSTCD, PRDUCD,
   PRLNCD, ACCTNO, TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD,
   SOURDT, SOURSQ, SOURST, SRVCSQ, TOITEM, ASSIS0, ASSIS1, ASSIS2, ASSIS3,
   ASSIS4, ASSIS5, ASSIS6, ASSIS7, ASSIS8, ASSIS9, DEALST, PRCSCD, ITEMNA,
   PRCSNA, STRKST, STRKDT, STRKSQ, CRCYSD, TRANEQ, TAXBST, DEALMG FROM
   GLI_VCHR WHERE TRANDT<=F_GET_PKGSTR('batchid', PI_PCKEND) AND 
   TRANDT>=F_GET_PKGSTR('batchid', PI_PCKBED);
   COMMIT;
   
   COMMIT 
   ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
   --备份会计分录表
   INSERT INTO TXA_VCHR_H SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,
   TRANBR, ACCTBR, ITEMCD, CRCYCD, CENTCD, PRSNCD, CUSTCD, PRDUCD, PRLNCD,
   ACCTNO, TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD, TOITEM,
   ASSIS0, ASSIS1, ASSIS2, ASSIS3, ASSIS4, ASSIS5, ASSIS6, ASSIS7, ASSIS8,
   ASSIS9, TRANST, TRSDAM, CRCYSD FROM TXA_VCHR WHERE TRANDT<=F_GET_PKGSTR('batchid', PI_PCKEND)
   AND TRANDT>=F_GET_PKGSTR('batchid', PI_PCKBED);
   COMMIT;
   
   COMMIT 
   ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
   --备份非分期还款记录表
   INSERT INTO RDPF80_H SELECT RD80DATE, RD80DPNOA, RD80DPNO, RD80CLITYP,
   RD80DUEBNO, RD80CUR, RD80DATEO, RD80OWNTYP, RD80STANO, RD80PAYCHA, RD80PBTYPE,
   RD80PBMODE, RD80ACIDP, RD80AMTP, RD80AMTPCA, RD80OPR, RD80STAN, RD80SEQ, BATCHID
   FROM RDPF80 WHERE RD80DATE<=F_GET_PKGSTR('batchid', PI_PCKEND)
   AND RD80DATE>=F_GET_PKGSTR('batchid', PI_PCKBED);
   COMMIT;
   
   COMMIT 
   ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
   --备份分期还款记录表
   INSERT INTO RDPF90_H SELECT RD90DATE, RD90DPNOA, RD90DPNO, RD90CLITYP, RD90DUEBNO,
   RD90CUR, RD90CACNT, RD90DATEO, RD90STANO, RD90PAYCHA, RD90ACIDP, RD90ACIDRP, RD90PRITYP,
   RD90PPTYP, RD90AMTPP, RD90AMTPCA, RD90ACIDA, RD90ACIDRA, RD90ACRTYP, RD90PATYP, RD90AMTPA,
   RD90AMTACA, RD90OPR, RD90STAN, RD90SEQ, BATCHID FROM RDPF90 WHERE RD90DATE<=F_GET_PKGSTR('batchid', PI_PCKEND)
   AND RD90DATE>=F_GET_PKGSTR('batchid', PI_PCKBED);
   COMMIT;
   
   COMMIT 
   ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
   --备份日志表
   INSERT INTO COM_LOGM_H SELECT BSNSDT, USERCD, TRANTI, LOGMSG FROM COM_LOGM
   WHERE BSNSDT<=F_GET_PKGSTR('batchid', PI_PCKEND) AND BSNSDT>=F_GET_PKGSTR('batchid', PI_PCKBED);
   COMMIT;