hi all,
this is the procedure i have created
CREATE PROCEDURE XIREL2.proc_size_2 (p_dummy CHAR(1))
SPECIFIC XIREL2.proc_size_2
DYNAMIC RESULT SETS 3
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE v_query VARCHAR(6000);
DECLARE v_query2 VARCHAR(6000);
DECLARE v_query3 VARCHAR(6000);
DECLARE cur_size_query CURSOR WITH RETURN TO CLIENT FOR stmt;
-- DECLARE cur_size_query1 CURSOR WITH RETURN TO CLIENT FOR stmt1;
-- DECLARE cur_size_query2 CURSOR WITH RETURN TO CLIENT FOR stmt2;
IF p_dummy='A' THEN
SET v_query= ' select
sum(length(ticker_attach)+length(ticker_attach_id) +
length(company_id)+length(category)+length(attach_ name)+length(added_by)+
length(added_date)+(length(COALESCE(comments,'''') ))+(length(COALESCE(news_head_line,'''')))+(length (COALESCE(resource,''''))))
' ||
'/ (1024)' ||
'Ticker_attachment_in_Kb' ||
' from ticker_attachment ' ;
ELSEIF p_dummy='B' THEN
SET v_query='select sum(length(COALESCE(xi_research,'''')))' ||
'/ (1024)' ||
'xi_rates_hist_in_Kb' ||
' from xi_rates_hist ' ;
ELSEIF p_dummy='C' THEN
SET v_query='select sum(length(COALESCE(xi_research,'''')))' ||
'/ (1024)' ||
'company_ratings_in_Kb' ||
' from company_ratings ' ;
END IF;
PREPARE stmt FROM v_query;
--PREPARE stmt1 FROM v_query2;
-- PREPARE stmt2 FROM v_query3;
OPEN cur_size_query;
-- OPEN cur_size_query1;
-- OPEN cur_size_query2;
END P1
but it is giving me a error like below . it is working fine in local
server, but it is not at all building in test server what could be the
problem plz help me its urgent.
XIREL2.proc_size - Build started.
Create stored procedure returns -7032.
-- LOG FILE P8224385.log FOR PROCEDURE XIREL2 .PROC_SIZE
-- DB2_SQLROUTINE_PREPOPTS=
-- PREP/BIND MESSAGES FOR
C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc\SP ANDB\XIREL2\tmp\P8224385.sqc
-- PREP/BIND ERROR CODES: 0 -1
LINE MESSAGES FOR P8224385.sqc
------
--------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
448 SQL0010N The string constant beginning with " select
sum(length(ticker_attach)+length(ticker_attach_id) +
length(com" does not have an ending string delimiter.
SQL0095N No bind file was created because of previous
errors.
SQL0092N No package was created because of previous
errors.
SQL0091W Precompilation or binding was ended with "3"
errors and "0" warnings.
-- END OF LOG FILE (SQLCODE: -1)
XIREL2.proc_size - Build failed.
XIREL2.proc_size - Roll back completed successfully.
thanks and regards
sridhar
