db2預存程序動態sql被截斷

標籤：back   成功   cee   0.00   err   int   主題   tab   val   

編寫預存程序，使用動態sql時，調試時發現變數賦值後被截斷。

關鍵代碼如下：

實現的效果是先把上下遊做對比的sql語句和相關參數存入RKDM_DATA_VOID_RULE，

執行預存程序後把兩個sql語句得出的結果插入另一張結果表RKDM_DATA_VOID_CHK_REST。

 

建表語句：

CREATE TABLE RKDM_DATA_VOID_CHK_REST (
DATA_DTDATE,
ORDR_NUMINTEGER,
CHK_BIG_CLSVARCHAR(256),
DATA_PRTNVARCHAR(80),
SBJVARCHAR(256),
ENT_ENVARCHAR(256),
ENT_CNVARCHAR(200),
FLD_ENVARCHAR(100),
FLD_CNVARCHAR(256),
FWD_CHK_SQLVARCHAR(500),
REV_CHK_SQLVARCHAR(500),
CD_TABVARCHAR(100),
CD_FLDVARCHAR(100),
CHK_AIMVARCHAR(50),
CHK_COMNTVARCHAR(50),
FWD_CHK_RSLTVARCHAR(500),
REV_CHK_RSLTVARCHAR(500),
NULL_CNTINTEGER,
CD_VALVARCHAR(50),
ERR_CNTINTEGER
)
;

COMMENT ON TABLE RKDM_DATA_VOID_CHK_REST IS ‘資料品質檢查結果‘;

COMMENT ON RKDM_DATA_VOID_CHK_REST (
DATA_DT IS ‘資料日期‘,
ORDR_NUM IS ‘序號‘,
CHK_BIG_CLS IS ‘檢查類型‘,
DATA_PRTN IS ‘資料分區‘,
SBJ IS ‘主題‘,
ENT_EN IS ‘實體英文名‘,
ENT_CN IS ‘實體中文名‘,
FLD_EN IS ‘欄位英文名‘,
FLD_CN IS ‘欄位中文名‘,
FWD_CHK_SQL IS ‘上遊/正向檢查SQL‘,
REV_CHK_SQL IS ‘下遊/反向檢查SQL‘,
CD_TAB IS ‘代碼錶‘,
CD_FLD IS ‘代碼欄位‘,
CHK_AIM IS ‘檢查目的‘,
CHK_COMNT IS ‘檢查說明‘,
FWD_CHK_RSLT IS ‘上遊/正向檢查結果‘,
REV_CHK_RSLT IS ‘下遊/反向檢查結果‘,
NULL_CNT IS ‘空值數‘,
CD_VAL IS ‘代碼值‘,
ERR_CNT IS ‘異常條數‘ );

CREATE TABLE RKDM_DATA_VOID_RULE (
ORDR_NUMINTEGER,
CHK_BIG_CLSVARCHAR(256),
DATA_PRTNVARCHAR(80),
SBJVARCHAR(256),
ENT_ENVARCHAR(256),
ENT_CNVARCHAR(200),
FLD_ENVARCHAR(100),
FLD_CNVARCHAR(256),
FWD_CHK_SQLVARCHAR(500),
REV_CHK_SQLVARCHAR(500),
CD_TABVARCHAR(100),
CD_FLDVARCHAR(100),
CHK_AIMVARCHAR(500),
CHK_COMNTVARCHAR(500)
)
;

COMMENT ON TABLE RKDM_DATA_VOID_RULE IS ‘資料品質檢查規則‘;

COMMENT ON RKDM_DATA_VOID_RULE (
ORDR_NUM IS ‘序號‘,
CHK_BIG_CLS IS ‘檢查類型‘,
DATA_PRTN IS ‘資料分區‘,
SBJ IS ‘主題‘,
ENT_EN IS ‘實體英文名‘,
ENT_CN IS ‘實體中文名‘,
FLD_EN IS ‘欄位英文名‘,
FLD_CN IS ‘欄位中文名‘,
FWD_CHK_SQL IS ‘上遊/正向檢查SQL‘,
REV_CHK_SQL IS ‘下遊/反向檢查SQL‘,
CD_TAB IS ‘代碼錶‘,
CD_FLD IS ‘代碼欄位‘,
CHK_AIM IS ‘檢查目的‘,
CHK_COMNT IS ‘檢查說明‘ );

資料：

INSERT INTO RKDM_DATA_VOID_RULE
(Ordr_Num,Chk_Big_Cls,Data_Prtn,Sbj,Ent_EN,Ent_CN,FLD_EN,FLD_CN,Fwd_Chk_SQL,Rev_Chk_SQL,Chk_Aim)
VALUES (‘1‘,
‘關鍵計量檢核_上下遊比對‘,
‘零售‘,
‘參與主體‘,
‘TB_RZT_CUST_ACCT_STATS‘,
‘客戶賬戶統計‘,
‘Dmnd_Dpst_Acct_Cnt‘,
‘活期存款賬戶數‘,
‘SELECT COUNT(1) FROM TEST_T_APP_2 WHERE B=‘‘2‘‘‘,
‘SELECT COUNT(1) FROM TEST_T_APP_3‘,
‘通過對客戶賬戶統計表的活期存款賬戶數欄位源和目標的檢核，確認處理邏輯是否存在問題‘);

INSERT INTO RKDM_DATA_VOID_RULE
(Ordr_Num,Chk_Big_Cls,Data_Prtn,Sbj,Ent_EN,Ent_CN,FLD_EN,FLD_CN,Fwd_Chk_SQL,Rev_Chk_SQL,Chk_Aim)
VALUES (‘2‘,
‘關鍵計量檢核_上下遊比對‘,
‘零售‘,
‘參與主體‘,
‘TB_RZT_CUST_ACCT_STATS‘,
‘客戶賬戶統計‘,
‘Mtg_Loan_Acct_Cnt‘,
‘按揭貸款賬戶數‘,
‘SELECT COUNT(1) FROM TEST_T_APP_2 WHERE B=‘‘1‘‘‘,
‘SELECT COUNT(1) FROM TEST_T_APP_3 WHERE B IS NOT NULL‘,
‘通過對客戶賬戶統計表的按揭貸款賬戶數欄位源和目標的檢核，確認處理邏輯是否存在問題‘);

預存程序代碼：

CREATE OR REPLACE PROCEDURE RKDM_KEY_INDX_CHK(
IN in_data_dt VARCHAR(10),
OUT out_succeed INTEGER
)
DYNAMIC RESULT SETS 1
/******************************************************************************
程式名稱：RKDM_KEY_INDX_CHK
功能描述：關鍵計量測試_上下遊比對
輸入參數：in_data_dt 資料日期
輸出參數：out_succeed 是否成功標誌。1-失敗 0-成功

版本號碼：V1.0.0.0
修改曆史：
版本 更改日期 更新人 更新說明

******************************************************************************/
P1:BEGIN
/*************標準定義變數**************************************************/
DECLARE v_job_name VARCHAR(60) DEFAULT ‘CLEAN_DATA‘; --作業名稱
DECLARE v_point VARCHAR(10); --記錄點
DECLARE v_start_tm TIMESTAMP; --開始執行時間
DECLARE v_end_tm TIMESTAMP; --結束執行時間
DECLARE v_sql VARCHAR(20000); --執行SQL
DECLARE v_ex_sql_log VARCHAR(20000); --執行SQL
DECLARE v_run_result VARCHAR(20); --執行結果
DECLARE v_date VARCHAR(10); --資料日期
DECLARE v_msg VARCHAR(10); --錯誤資訊
DECLARE SQLCODE INT DEFAULT 0; --顯示定義資料庫變數SQLCODE
DECLARE SQLSTATE CHAR(5) DEFAULT ‘0000‘; --顯示定義資料庫變數SQLSTATE
DECLARE v_etl_owner VARCHAR(20) DEFAULT ‘ETL‘; --本SP操作的使用者

/**************定義常用日期變數**********************************************/
DECLARE V_T_YEAR VARCHAR(4); --本年
DECLARE V_T_MONTH VARCHAR(8); --本月
DECLARE V_T_DAY VARCHAR(8); --本日
DECLARE V_L_YEAR VARCHAR(4); --去年
DECLARE V_F_TX_DATE DATE; --標準日期
DECLARE V_F_C_DATE VARCHAR(10); --十位標準日期文字格式
DECLARE V_LAST_DAY VARCHAR(8); --上一日
DECLARE V_NEXT_MON_START VARCHAR(8); --下月初
DECLARE V_MON_START VARCHAR(8); --本月初
DECLARE V_MON_END VARCHAR(8); --本月末
DECLARE V_LAST_MON_END VARCHAR(8); --上月末
DECLARE V_BEGIN_YEAR VARCHAR(8); --年初
DECLARE V_LAST_YEAR_END VARCHAR(8); --上年末
DECLARE V_LAST_YEAR_PERIOD VARCHAR(8); --去年同期
DECLARE V_QUARTER VARCHAR(8); --所在季度數 V_QUERTER
DECLARE V_BEGIN_QUARTER VARCHAR(8); --季初
DECLARE V_TH_LAST_MON_END VARCHAR(8); --上上上月末
DECLARE V_TH_LAST_YEAR_END VARCHAR(8); --上上上年末

/**************自訂變數***************************************************/
DECLARE V_DATA_COUNT_PRE INTEGER;
DECLARE DATA_DT VARCHAR(8); --資料日期
DECLARE ETL_DT VARCHAR(8); --ETL處理日期（當前日期）
DECLARE ADD_DT VARCHAR(8); --增量日期
DECLARE MAXDATE VARCHAR(8); --最大日期
DECLARE ILLDATE VARCHAR(8); --錯誤日期
DECLARE NULLDATE VARCHAR(8); --空日期
DECLARE NULLSTRING VARCHAR(1); --Null 字元串
DECLARE NULLNUMBER VARCHAR(1); --空數值
DECLARE NULLTIME TIME; --空時間
DECLARE NULLTIMESTAMP TIMESTAMP; --空時間戳記

DECLARE v_sql_del VARCHAR(20000); --執行SQL
DECLARE v_sql_fwd VARCHAR(20000); --執行SQL
DECLARE v_sql_rev VARCHAR(20000); --執行SQL
DECLARE v_sql_insert VARCHAR(30000); --執行SQL
DECLARE VAL_FWD VARCHAR(10);
DECLARE VAL_REV VARCHAR(10);
DECLARE RS_STMT_FWD STATEMENT;
DECLARE RS_STMT_REV STATEMENT;
DECLARE RS_C_FWD CURSOR FOR RS_STMT_FWD;
DECLARE RS_C_REV CURSOR FOR RS_STMT_REV;

/**************異常處理******************************************************/
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 v_msg = MESSAGE_TEXT;
SET out_succeed = 1;
SET v_run_result = ‘執行失敗‘;
SET v_msg = ‘SQLCODE:‘||rtrim(CHAR(SQLCODE))||‘.SQLSTATE:‘||SQLSTATE||‘‘||v_msg;
SET v_end_tm = current timestamp;
ROLLBACK;
END;

/**************標準變數處理**************************************************/
SET v_start_tm = current timestamp;
SET v_date = in_data_dt;
SET out_succeed = 0;
SET v_run_result = ‘執行成功‘;
SET v_job_name = ‘CLEAN_DATA‘;

--自訂參數賦值
VALUES in_data_dt INTO DATA_DT;
VALUES to_char(current date,‘YYYYMMDD‘) INTO ETL_DT;
VALUES in_data_dt INTO ADD_DT;
VALUES ‘89991231‘ INTO MAXDATE;
VALUES ‘00010102‘ INTO ILLDATE;
VALUES ‘00010101‘ INTO NULLDATE;
VALUES ‘‘ INTO NULLSTRING;
VALUES 0 INTO NULLNUMBER;
VALUES ‘00:00:00‘ INTO NULLTIME;
VALUES ‘0001-01-01 00:00:00.000000‘ INTO NULLTIMESTAMP;

--自訂日期參數賦值
VALUES substr(in_data_dt,1,4) INTO V_T_YEAR; --本年
VALUES substr(in_data_dt,5,2) INTO V_T_MONTH; --本月
VALUES substr(in_data_dt,7,2) INTO V_T_DAY; --本日
VALUES substr(in_data_dt,1,4)-1 INTO V_L_YEAR; --去年
VALUES to_date(in_data_dt,‘yyyy-mm-dd‘) INTO V_F_TX_DATE; --標準日期
VALUES to_char(to_date(in_data_dt,‘yyyy-mm-dd‘),‘yyyy-mm-dd‘) INTO V_F_C_DATE; --十位標準日期文字格式
VALUES to_char(to_date(in_data_dt,‘yyyymmdd‘)-1 day,‘yyyymmdd‘) INTO V_LAST_DAY; --上一日
--VALUES to_char(last_day(to_date(in_data_dt,‘yyyymmdd‘))+1 day,‘yyyymmdd‘) INTO V_NEXT_MON_START; --下月初
VALUES V_T_YEAR||V_T_MONTH||‘01‘ INTO V_MON_START; --本月初
--VALUES to_char(last_day(to_date(in_data_dt,‘yyyymmdd‘),‘yyyymmdd‘),‘yyyymmdd‘) INTO V_MON_END; --本月末
VALUES to_char(to_date(V_MON_START,‘yyyymmdd‘)-1 day,‘yyyymmdd‘) INTO V_LAST_MON_END; --上月末
VALUES to_char(to_date(V_T_YEAR||‘-01-01‘,‘yyyymmdd‘),‘yyyymmdd‘) INTO V_BEGIN_YEAR; --年初
VALUES to_char(to_date(V_BEGIN_YEAR,‘yyyymmdd‘)-1 day,‘yyyymmdd‘) INTO V_LAST_YEAR_END; --上年末
VALUES to_char(to_date(in_data_dt,‘yyyymmdd‘)-12 month,‘yyyymmdd‘) INTO V_LAST_YEAR_PERIOD; --去年同期
VALUES CASE WHEN V_T_MONTH IN (‘01‘,‘02‘,‘03‘) THEN ‘1‘
WHEN V_T_MONTH IN (‘04‘,‘05‘,‘06‘) THEN ‘2‘
WHEN V_T_MONTH IN (‘07‘,‘08‘,‘09‘) THEN ‘3‘
WHEN V_T_MONTH IN (‘10‘,‘11‘,‘12‘) THEN ‘4‘
END INTO V_QUARTER; --所在季度
VALUES CASE V_QUARTER
WHEN ‘1‘ THEN V_T_YEAR||‘0101‘
WHEN ‘2‘ THEN V_T_YEAR||‘0401‘
WHEN ‘3‘ THEN V_T_YEAR||‘0701‘
WHEN ‘4‘ THEN V_T_YEAR||‘1001‘
END INTO V_BEGIN_QUARTER; --季初
VALUES to_char(last_day(to_date(in_data_dt,‘yyyymmdd‘)-3 month),‘yyyymmdd‘) INTO V_TH_LAST_MON_END; --上上上月末
VALUES to_char(year(to_date(in_data_dt,‘yyyymmdd‘)-3 year)||‘-01-01‘,‘yyyymmdd‘) INTO V_TH_LAST_YEAR_END; --上上上年末

/**************指令碼主要邏輯**************************************************/

--防重跑，先刪除資料
SET v_sql_del=‘DELETE FROM RKDM_DATA_VOID_CHK_REST WHERE DATA_DT=‘‘‘||v_date||‘‘‘ AND Chk_Big_Cls=‘‘關鍵計量檢核_上下遊比對‘‘‘;
PREPARE DEL_STMT FROM v_sql_del;
EXECUTE DEL_STMT;

--迴圈檢查規則
FOR RS_LOOP AS
SELECT Ordr_Num as Ordr_Num,
Chk_Big_Cls as Chk_Big_Cls,
Data_Prtn as Data_Prtn,
Sbj as Sbj,
Ent_EN as Ent_EN,
Ent_CN as Ent_CN,
FLD_EN as FLD_EN,
FLD_CN as FLD_CN,
Fwd_Chk_SQL as Fwd_Chk_SQL,
Rev_Chk_SQL as Rev_Chk_SQL,
Chk_Aim as Chk_Aim
FROM RKDM_DATA_VOID_RULE WHERE Chk_Big_Cls=‘關鍵計量檢核_上下遊比對‘
DO
SET v_sql_fwd=RS_LOOP.Fwd_Chk_SQL;
SET v_sql_rev=RS_LOOP.Rev_Chk_SQL;
PREPARE RS_STMT_FWD FROM v_sql_fwd;
OPEN RS_C_FWD;
PREPARE RS_STMT_REV FROM v_sql_rev;
OPEN RS_C_REV;
FETCH RS_C_FWD INTO VAL_FWD;
FETCH RS_C_REV INTO VAL_REV;
CLOSE RS_C_FWD;
CLOSE RS_C_REV;

SET v_sql_insert=‘INSERT INTO RKDM_DATA_VOID_CHK_REST(DATA_DT,Chk_Big_Cls,Ordr_Num,Data_Prtn,Sbj,Ent_EN,Ent_CN,FLD_EN,FLD_CN,Fwd_Chk_SQL,Rev_Chk_SQL,Fwd_Chk_Rslt,Rev_Chk_Rslt,Chk_Aim)VALUES(‘‘‘||v_date||‘‘‘,‘‘‘||RS_LOOP.Chk_Big_Cls||‘‘‘,‘‘‘||RS_LOOP.Ordr_Num||‘‘‘,‘‘‘||RS_LOOP.Data_Prtn||‘‘‘,‘‘‘||RS_LOOP.Sbj||‘‘‘,‘‘‘||RS_LOOP.Ent_EN||‘‘‘,‘‘‘||RS_LOOP.Ent_CN||‘‘‘,‘‘‘||RS_LOOP.FLD_EN||‘‘‘,‘‘‘||RS_LOOP.FLD_CN||‘‘‘,‘‘‘||RS_LOOP.Fwd_Chk_SQL||‘‘‘,‘‘‘||RS_LOOP.Rev_Chk_SQL||‘‘‘,‘‘‘||VAL_FWD||‘‘‘,‘‘‘||VAL_REV||‘‘‘,‘‘‘||RS_LOOP.Chk_Aim||‘‘‘)‘;
PREPARE RS_STMT_INST FROM v_sql_insert;
EXECUTE RS_STMT_INST;
END FOR;

END P1

 問題：使用ibm data studio 調試發現所有的變數都能正常取出來但是整合到v_sql_insert變數中時就會被截斷，v_sql_insert這個變數值出來的不是完整的語句。

嘗試的方法：1.最後不使用v_sql_insert這個動態sql ，直接這麼寫：INSERT INTO RKDM_DATA_VOID_CHK_REST(DATA_DT,Chk_Big_Cls,Ordr_Num,Data_Prtn,Sbj,Ent_EN,Ent_CN,FLD_EN,FLD_CN,Fwd_Chk_SQL,Rev_Chk_SQL,Fwd_Chk_Rslt,Rev_Chk_Rslt,Chk_Aim)VALUES(v_date,RS_LOOP.Chk_Big_Cls,RS_LOOP.Ordr_Num,RS_LOOP.Data_Prtn,RS_LOOP.Sbj,RS_LOOP.Ent_EN,RS_LOOP.Ent_CN,RS_LOOP.FLD_EN,RS_LOOP.FLD_CN,RS_LOOP.Fwd_Chk_SQL,RS_LOOP.Rev_Chk_SQL,VAL_FWD,VAL_REV,RS_LOOP.Chk_Aim)這樣做是沒有問題。

2.建立頁比較大的系統暫存資料表空間。

3.修改v_sql_insert這個變數聲明的資料類型大小。

 

 

db2預存程序動態sql被截斷