存储过程执行中报sqlcode -601的错怎么解决
各位存储过程执行中报601的错怎么解决啊?大51放假的,系统也不消停啊,我们这个系统里面晚上定时任务调用存储过程批量生成转账凭证,是按照分区信息频繁的掉用这个存储过程,存储过程中定义了临时表,请大家帮忙定位下问题出现在哪里了?
一般情况下还不会出错,但这个问题也出现了几回了,每次重新手工在开一次定时任务,调用这个存储过程又过去了。。。。。。
com.cfcc.jaf.persistence.dao.exception.JAFDB2Exception: 存储过程调用完成但未能成功执行,返回码-1返回信息:分区号:33 生成转账凭证失败!SQLSTATE:42710, SQLCODE:-601
存储过程如下
SET SCHEMA TCBSUSRA;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TCBSUSRA";
CREATE PROCEDURE TCBSUSRA.PRC_BPS_GEN_ACCT_BOOKVOU
(IN PI_S_BOOKORGCODE CHARACTER(12),
IN PI_D_ACCT DATE,
IN PI_I_PARTNO INTEGER,
IN PI_I_TASKNO INTEGER,
OUT PO_I_RETCODE INTEGER,
OUT PO_S_RETTEXT CHARACTER(200)
)
SPECIFIC TCBSUSRA.PRC_BPS_GEN_ACCT_BOOKVOU
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
------------------------------------------------------------------------
-- SQL 存储过程
-- 功能:生成转账凭证
-- 输入参数:
-- pi_d_Acct 日终批处理的账务日期
-- pi_i_PartNo 分区号
-- pi_i_TaskNo 任务序号
-- 输出参数:
-- po_i_RetCode 返回结果代码 0:成功
-- po_s_RetText 返回结果说明
------------------------------------------------------------------------
P1: BEGIN ATOMIC
DECLARE cTaskState char(1);--
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';--
DECLARE SQLCODE integer default 0;--
--增加异常处理
DECLARE UNDO HANDLER FOR SQLWARNING, SQLEXCEPTION
P2:BEGIN
values('分区号:'||char(pi_i_PartNo)||'生成转账凭证失败!'||'SQLSTATE:'||SQLSTATE ||', SQLCODE:'||CHAR(SQLCODE),-1)
into po_s_RetText,po_i_RetCode;--
END P2; --
--双讫转账临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.tv_book_tmp(
S_BOOKORGCODE CHAR(12),
I_TRANSFERVOUNO INTEGER,
F_AMT DECIMAL(18,2)
)
NOT LOGGED on rollback delete rows on commit delete rows; --
--create unique index session.ix_v_booktwo_tmp on session.tv_book_tmp(s_bookorgcode,i_transfervouno);--
--
--DECLARE GLOBAL TEMPORARY TABLE SESSION.tb_bookserial_one_tmp(
-- S_BOOKORGCODE CHAR(12),
-- I_TRANSFERVOUNO BIGINT,
-- I_CRPSRLNO BIGINT
--)
--in TS_USRTMP_32K with replace NOT LOGGED on rollback delete rows on commit delete rows;--
--校验当前和前置存储过程是否已经执行完成. 小于1表示前置存储过程未执行, 大于等于2表示已执行完成.
SELECT c_TaskState INTO cTaskState
FROM tm_task_trace
WHERE d_Acct = pi_d_Acct AND i_PartNo = pi_i_PartNo AND s_TaskClass='20302'
FETCH FIRST 1 ROWS ONLY;--
IF( cTaskState >= '2' ) THEN
values(0,'分区号:'||char(pi_i_PartNo)||'已经生成转账凭证!')
into po_i_RetCode,po_s_RetText; --
RETURN;--
ELSEIF( cTaskState < '1' ) THEN
values(-1,'分区号:'||char(pi_i_PartNo)||'前置任务未执行!')
into po_i_RetCode,po_s_RetText; --
RETURN;--
END IF;--
--如果当前模式为‘TCBSUSRA’那么取模式‘TCBSUSRB’的双讫表来处理,否则相反;--
IF CURRENT SCHEMA = 'TCBSUSRA' THEN
--生成双讫转账凭证(汇总部分)
--生成临时表
INSERT INTO SESSION.tv_book_tmp
( s_BookOrgCode,i_TransferVouNo, f_Amt)
SELECT s_BookOrgCode,i_TransferVouNo, SUM(f_Amt)
FROM TCBSUSRB.tb_bookserial_two
WHERE i_PartNo = pi_i_PartNo
AND c_SumFlag = '1'
GROUP BY i_TransferVouNo,s_BookOrgCode WITH UR;--
--生成双讫转账凭证
--s_DebitAcct, s_LoanAcct, s_Brief 要从参数表中join得到
INSERT INTO TCBSUSRB.tv_book_two (s_BookOrgCode, i_TransferVouNo, s_DebitAcct, s_LoanAcct, s_debitsbtcode, s_loansbtcode,
s_Brief, d_Acct, f_Amt, c_SumFlag, i_SumNum, ts_SysUpdate)
SELECT DISTINCT a.s_BookOrgCode, a.i_TransferVouNo, b.s_DebitAcct, b.s_LoanAcct, b.s_debitsbtcode, b.s_loansbtcode,
b.s_TransVouBrief, pi_d_Acct, a.f_Amt, '1', 0, CURRENT TIMESTAMP
FROM SESSION.tv_book_tmp a, tpy_dayend_totaltrans_merge b
WHERE a.s_BookOrgCode = b.s_BookOrgCode
AND a.i_TransferVouNo = b.i_TransTotalVouSeqNo
WITH UR;--
--生成双讫转账凭证(不汇总部分)
INSERT INTO TCBSUSRB.tv_book_two (s_BookOrgCode, d_Acct, i_TransferVouNo, s_DebitAcct, s_LoanAcct, s_DebitSbtCode, s_LoanSbtCode,
f_Amt, s_Brief, c_SumFlag, i_SumNum, ts_SysUpdate)
SELECT a.s_BookOrgCode, pi_d_Acct, a.I_ENROLSRLNO, a.s_DebitAcct, a.s_LoanAcct, a.s_DebitSbtCode, a.s_LoanSbtCode,
a.f_Amt,b.s_SrlAcctBriefName, '0', 0, CURRENT TIMESTAMP
FROM TCBSUSRB.tb_bookserial_two a, tpy_srlacctbrief b
WHERE a.i_PartNo = pi_i_PartNo
AND a.s_SrlAcctBriefCode = b.s_SrlAcctBriefCode
AND a.c_SumFlag = '0' WITH UR;--
--生成核算主体单迄转账凭证
INSERT INTO TCBSUSRB.tv_book_one
( s_BookOrgCode, d_Acct, i_TransferVouNo, s_BookAcct, c_AmtOrt,
f_Amt, s_Brief, ts_SysUpdate)
SELECT a.s_BookOrgCode, pi_d_Acct, nextval FOR TCBSUSRA.TCBS_ACCT_ENROL_SRLNO_SEQ, a.s_BookAcct, a.c_AmtOrt,
a.f_Amt, a.S_BRIEF, CURRENT TIMESTAMP
FROM TCBSUSRB.tb_bookserial_one a,tdy_bookorg b
WHERE a.S_BOOKORGCODE = b.s_BookOrgCode and b.i_PartNo=pi_i_PartNo WITH UR;--
--
--为了解决互锁
--回填流水账转账凭证号码
--INSERT INTO SESSION.tb_bookserial_one_tmp
-- (S_BOOKORGCODE, I_TRANSFERVOUNO, I_CRPSRLNO)
-- SELECT a.s_BookOrgCode, a.i_TransferVouNo, a.I_CRPSRLNO
-- FROM TCBSUSRB.tv_book_one a,tdy_bookorg b
-- WHERE a.s_BookOrgCode = b.s_BookOrgCode and b.i_PartNo=pi_i_PartNo WITH UR;--
--
--MERGE INTO TCBSUSRB.tb_bookserial_one a
--USING SESSION.tb_bookserial_one_tmp b
--ON (a.I_ENROLSRLNO = b.I_CRPSRLNO AND a.s_BookOrgCode = b.s_BookOrgCode)
--WHEN MATCHED THEN
-- UPDATE SET a.i_TransferVouNo = b.i_TransferVouNo;--
ELSE
--生成双讫转账凭证(汇总部分)
--生成临时表
INSERT INTO SESSION.tv_book_tmp
( s_BookOrgCode,i_TransferVouNo, f_Amt)
SELECT s_BookOrgCode,i_TransferVouNo, SUM(f_Amt)
FROM TCBSUSRA.tb_bookserial_two
WHERE i_PartNo = pi_i_PartNo
AND c_SumFlag = '1'
GROUP BY i_TransferVouNo,s_BookOrgCode WITH UR;--
--生成双讫转账凭证
--s_DebitAcct, s_LoanAcct, s_Brief 要从参数表中join得到
INSERT INTO TCBSUSRA.tv_book_two (s_BookOrgCode, i_TransferVouNo, s_DebitAcct, s_LoanAcct, s_debitsbtcode, s_loansbtcode,
s_Brief, d_Acct, f_Amt, c_SumFlag, i_SumNum, ts_SysUpdate)
SELECT DISTINCT a.s_BookOrgCode, a.i_TransferVouNo, b.s_DebitAcct, b.s_LoanAcct, b.s_debitsbtcode, b.s_loansbtcode,
b.s_TransVouBrief, pi_d_Acct, a.f_Amt, '1', 0, CURRENT TIMESTAMP
FROM SESSION.tv_book_tmp a, tpy_dayend_totaltrans_merge b
WHERE a.s_BookOrgCode = b.s_BookOrgCode
AND a.i_TransferVouNo = b.i_TransTotalVouSeqNo WITH UR; --
--生成双讫转账凭证(不汇总部分)
INSERT INTO TCBSUSRA.tv_book_two (s_BookOrgCode, d_Acct, i_TransferVouNo, s_DebitAcct, s_LoanAcct, s_DebitSbtCode, s_LoanSbtCode,
f_Amt, s_Brief, c_SumFlag, i_SumNum, ts_SysUpdate)
SELECT a.s_BookOrgCode, pi_d_Acct, a.I_ENROLSRLNO, a.s_DebitAcct, a.s_LoanAcct, a.s_DebitSbtCode, a.s_LoanSbtCode,
a.f_Amt,b.s_SrlAcctBriefName, '0', 0, CURRENT TIMESTAMP
FROM TCBSUSRA.tb_bookserial_two a, tpy_srlacctbrief b
WHERE a.i_PartNo = pi_i_PartNo
AND a.s_SrlAcctBriefCode = b.s_SrlAcctBriefCode
AND a.c_SumFlag = '0' WITH UR;--
--生成核算主体单迄转账凭证
INSERT INTO TCBSUSRA.tv_book_one
( s_BookOrgCode, d_Acct, i_TransferVouNo, s_BookAcct, c_AmtOrt,
f_Amt, s_Brief, ts_SysUpdate)
SELECT a.s_BookOrgCode, pi_d_Acct, NEXT VALUE FOR TCBSUSRA.TCBS_ACCT_ENROL_SRLNO_SEQ, a.s_BookAcct, a.c_AmtOrt,
a.f_Amt, s_Brief, CURRENT TIMESTAMP
FROM TCBSUSRA.tb_bookserial_one a,tdy_bookorg b
WHERE a.s_BookOrgCode = b.s_BookOrgCode and b.i_PartNo=pi_i_PartNo ;--
--
--为了解决互锁
--回填流水账转账凭证号码
--INSERT INTO SESSION.tb_bookserial_one_tmp
-- (S_BOOKORGCODE, I_TRANSFERVOUNO, I_CRPSRLNO)
-- SELECT a.s_BookOrgCode, i_TransferVouNo, I_CRPSRLNO
-- FROM TCBSUSRA.tv_book_one a,tdy_bookorg b
-- WHERE a.s_BookOrgCode = b.s_BookOrgCode and b.i_PartNo=pi_i_PartNo WITH UR ;--
--
--MERGE INTO TCBSUSRA.tb_bookserial_one a
--USING SESSION.tb_bookserial_one_tmp b
--ON (a.I_ENROLSRLNO = b.I_CRPSRLNO AND a.s_BookOrgCode = b.s_BookOrgCode)
--WHEN MATCHED THEN
-- UPDATE SET a.i_TransferVouNo = b.i_TransferVouNo;--
END IF;--
--修改批处理执行跟踪表,更新状态为2;--
UPDATE tm_task_trace SET c_TaskState = '2'
WHERE d_Acct = pi_d_Acct
and i_PartNo=pi_i_PartNo;--
values(0,'分区号:'||char(pi_i_PartNo)||'生成转账凭证成功!')
into po_i_RetCode,po_s_RetText;--
END P1;