DB2 stored procedure Dynamic SQL is truncated

When you write a stored procedure, when you use dynamic SQL, the variable assignment is truncated when you debug.

The key code is as follows:

The effect of the implementation is to first the upstream and downstream to make a comparison of SQL statements and related parameters deposited Rkdm_data_void_rule,

After executing the stored procedure, insert the results from the two SQL statements into another result table rkdm_data_void_chk_rest.

To build a table statement:

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 ' data quality check results ';

COMMENT on Rkdm_data_void_chk_rest (
DATA_DT is ' data date ',
Ordr_num is ' serial number ',
Chk_big_cls is ' Check type ',
DATA_PRTN is ' data partition ',
SBJ is ' theme ',
Ent_en is ' entity English name ',
ENT_CN is ' entity Chinese name ',
Fld_en is ' field English name ',
FLD_CN is ' field Chinese name ',
Fwd_chk_sql is ' upstream/forward checking SQL ',
Rev_chk_sql is ' downstream/reverse check SQL ',
Cd_tab is ' Code table ',
CD_FLD is ' Code field ',
Chk_aim is ' inspection purpose ',
CHK_COMNT is ' inspection instructions ',
FWD_CHK_RSLT is ' upstream/forward check result ',
REV_CHK_RSLT is ' downstream/reverse inspection results ',
NULL_CNT is ' null value ',
Cd_val is ' code value ',
ERR_CNT is ' exception bar number ');

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 ' data quality check rule ';

COMMENT on Rkdm_data_void_rule (
Ordr_num is ' serial number ',
Chk_big_cls is ' Check type ',
DATA_PRTN is ' data partition ',
SBJ is ' theme ',
Ent_en is ' entity English name ',
ENT_CN is ' entity Chinese name ',
Fld_en is ' field English name ',
FLD_CN is ' field Chinese name ',
Fwd_chk_sql is ' upstream/forward checking SQL ',
Rev_chk_sql is ' downstream/reverse check SQL ',
Cd_tab is ' Code table ',
CD_FLD is ' Code field ',
Chk_aim is ' inspection purpose ',
CHK_COMNT is ' inspection Instructions ');

Data:

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 ',
' Key indicators check the upstream and downstream ratios ',
' Retail ',
' Participation in the subject ',
' Tb_rzt_cust_acct_stats ',
' Customer account statistics ',
' Dmnd_dpst_acct_cnt ',
' Current account number ',
' SELECT COUNT (1) from test_t_app_2 WHERE b= ' 2 ",
' SELECT COUNT (1) from Test_t_app_3 ',
' Confirm the problem of the processing logic by checking the source and target of the current deposit account number in the customer account;

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 ',
' Key indicators check the upstream and downstream ratios ',
' Retail ',
' Participation in the subject ',
' Tb_rzt_cust_acct_stats ',
' Customer account statistics ',
' Mtg_loan_acct_cnt ',
' Number of mortgage loan accounts ',
' SELECT COUNT (1) from test_t_app_2 WHERE b= ' 1 ",
' SELECT COUNT (1) from Test_t_app_3 WHERE B was not NULL ',
' Verify that there is a problem with the processing logic ' by checking the source and target of the mortgage account number in the customer account.

Stored Procedure Code:

CREATE OR REPLACE PROCEDURE rkdm_key_indx_chk (
In In_data_dt VARCHAR (10),
Out Out_succeed INTEGER
)
DYNAMIC RESULT Sets 1
/******************************************************************************
Program Name: Rkdm_key_indx_chk
Function Description: Key indicator Test _ upstream and downstream ratio
Input parameters: In_data_dt Data Date
Output parameter: Out_succeed is the success flag. 1-Failure 0-Success

Version number: V1.0.0.0
Modify History:
Version Change date description for update person update

******************************************************************************/
P1:begin
/************* Standard definition variable **************************************************/
DECLARE v_job_name VARCHAR (DEFAULT ' clean_data '); --Job name
DECLARE v_point VARCHAR (10); --Record point
DECLARE V_start_tm TIMESTAMP; --Start Execution time
DECLARE V_end_tm TIMESTAMP; --End Execution time
DECLARE v_sql VARCHAR (20000); --Execute SQL
DECLARE V_ex_sql_log VARCHAR (20000); --Execute SQL
DECLARE V_run_result VARCHAR (20); --Execution results
DECLARE v_date VARCHAR (10); --Data date
DECLARE v_msg VARCHAR (10); --Error message
DECLARE SQLCODE INT DEFAULT 0; --Show define database variables Sqlcode
DECLARE SQLSTATE CHAR (5) DEFAULT ' 0000 '; --Show define database variables SQLState
DECLARE v_etl_owner VARCHAR (DEFAULT ' ETL '); --users of this SP operation

/************** defining common Date variables **********************************************/
DECLARE v_t_year VARCHAR (4); --this year
DECLARE V_t_month VARCHAR (8); --This month
DECLARE V_t_day VARCHAR (8); --This day
DECLARE v_l_year VARCHAR (4); --Last year
DECLARE v_f_tx_date DATE; --Standard Date
DECLARE v_f_c_date VARCHAR (10); --10-bit standard date string format
DECLARE V_last_day VARCHAR (8); --Last day
DECLARE V_next_mon_start VARCHAR (8); --Early next month
DECLARE V_mon_start VARCHAR (8); --Early this month
DECLARE v_mon_end VARCHAR (8); --Late this month
DECLARE v_last_mon_end VARCHAR (8); --Last month
DECLARE v_begin_year VARCHAR (8); --Early
DECLARE v_last_year_end VARCHAR (8); --Last year
DECLARE v_last_year_period VARCHAR (8); --the same period last year
DECLARE V_quarter VARCHAR (8); --Number of quarters V_querter
DECLARE V_begin_quarter VARCHAR (8); --Available
DECLARE v_th_last_mon_end VARCHAR (8); -Last month
DECLARE v_th_last_year_end VARCHAR (8); --The end of the year

/************** Custom Variable ***************************************************/
DECLARE V_data_count_pre INTEGER;
DECLARE Data_dt VARCHAR (8); --Data date
DECLARE Etl_dt VARCHAR (8); --etl Processing date (current date)
DECLARE Add_dt VARCHAR (8); --Incremental Date
DECLARE maxdate VARCHAR (8); --Maximum Date
DECLARE illdate VARCHAR (8); --Error Date
DECLARE nulldate VARCHAR (8); --Empty Date
DECLARE nullstring VARCHAR (1); --empty string
DECLARE nullnumber VARCHAR (1); --Null value
DECLARE nulltime time; --Empty time
DECLARE Nulltimestamp TIMESTAMP; --Empty time stamp

DECLARE V_sql_del VARCHAR (20000); --Execute SQL
DECLARE v_sql_fwd VARCHAR (20000); --Execute SQL
DECLARE V_sql_rev VARCHAR (20000); --Execute SQL
DECLARE V_sql_insert VARCHAR (30000); --Execute 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;

/************** Exception Handling ******************************************************/
DECLARE EXIT HANDLER for SQLEXCEPTION
BEGIN
GET Diagnostics EXCEPTION 1 v_msg = Message_text;
SET out_succeed = 1;
SET V_run_result = ' execution failed ';
SET v_msg = ' SQLCODE: ' | | RTrim (CHAR (SQLCODE)) | | '. SQLSTATE: ' | | sqlstate| | ' | | v_msg;
SET V_END_TM = current timestamp;
ROLLBACK;
END;

/************** standard variable handling **************************************************/
SET V_START_TM = current timestamp;
SET v_date = In_data_dt;
SET out_succeed = 0;
SET V_run_result = ' successful execution ';
SET v_job_name = ' clean_data ';

--Custom parameter assignment
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;

--Custom Date parameter assignment
VALUES substr (in_data_dt,1,4) into v_t_year; --this year
VALUES substr (in_data_dt,5,2) into V_t_month; --This month
VALUES substr (in_data_dt,7,2) into V_t_day; --This day
VALUES substr (in_data_dt,1,4)-1 into v_l_year; --Last year
VALUES to_date (In_data_dt, ' yyyy-mm-dd ') into v_f_tx_date; --Standard Date
VALUES To_char (to_date (In_data_dt, ' yyyy-mm-dd '), ' yyyy-mm-dd ') into v_f_c_date; --10-bit standard date string format
VALUES To_char (to_date (In_data_dt, ' YYYYMMDD ')-1 day, ' YYYYMMDD ') into V_last_day; --Last day
--values To_char (Last_day (To_date (In_data_dt, ' YYYYMMDD ')) +1 day, ' YYYYMMDD ') into V_next_mon_start; --Early next month
VALUES v_t_year| | v_t_month| | ' V_mon_start ' into; --Early this month
--values To_char (Last_day (To_date (In_data_dt, ' yyyymmdd '), ' YYYYMMDD '), ' YYYYMMDD ') into v_mon_end; --Late this month
VALUES To_char (to_date (V_mon_start, ' YYYYMMDD ')-1 day, ' YYYYMMDD ') into v_last_mon_end; --Last month
VALUES To_char (to_date (v_t_year| | ') -01-01 ', ' yyyymmdd '), ' YYYYMMDD ') into v_begin_year; --Early
VALUES To_char (to_date (v_begin_year, ' YYYYMMDD ')-1 day, ' YYYYMMDD ') into v_last_year_end; --Last year
VALUES To_char (to_date (In_data_dt, ' YYYYMMDD ') -12 month, ' YYYYMMDD ') into V_last_year_period; --the same period last year
VALUES case when v_t_month in (' "", ' "", ' ") Then ' 1 '
When v_t_month in (' ", '", ' ") Then ' 2 '
When v_t_month in (' ", '", ' ") Then ' 3 '
When v_t_month in (' Ten ', ' one ', ' one ') then ' 4 '
END into V_quarter; --Quarterly
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; --Available
VALUES To_char (Last_day (To_date (In_data_dt, ' YYYYMMDD ')-3 month), ' YYYYMMDD ') into v_th_last_mon_end; -Last month
VALUES To_char (Year (To_date (In_data_dt, ' YYYYMMDD ')-3 year) | | ' -01-01 ', ' YYYYMMDD ') into v_th_last_year_end; --The end of the year

/************** Script main Logic **************************************************/

--Anti-re-run, delete data first
SET v_sql_del= ' DELETE from rkdm_data_void_chk_rest WHERE data_dt= ' | | v_date| | " and chk_big_cls= "Key indicators check core _ upstream and downstream ratio of";
PREPARE del_stmt from V_sql_del;
EXECUTE del_stmt;

--Cyclic inspection rules
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= ' key indicators check for upstream and downstream ratios '
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

problem: Using IBM Data Studio debugging, it is found that all variables can be removed normally but are truncated when integrated into the V_sql_insert variable, v_sql_insert the value of this variable is not a complete statement.

The method to try:1. Finally do not use v_sql_insert this dynamic SQL, directly write: 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) Doing so is no problem.

2. Set up a larger system temp table space on the page.

3. Modify the data type size of the V_sql_insert variable declaration.

DB2 stored procedure Dynamic SQL is truncated