DB2 stored procedure Dynamic SQL is truncated

Source: Internet
Author: User
Tags db2 string format 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.