Problem with the global temp table in a SP.

Problem with the global temp table in a SP.

 

  

Hi All,

At the end of this SP there is a global temp table is declared.
We have opened a cursor to fetch the records from the temp table.
When we call this SP from the command prompt , it will be executed very
first time.
When we call the same SP second time, there will be a problem with the
temp table.

We cannot drop the temp table it says the temp table is in use because
we are fetching the records by opening the cursor.

We cannot close the cursor also.

Any suggestion on this is appreciated.

Thanks,
Jayaprakash.


CREATE PROCEDURE prc_sel_fundcompallocrep(
IN v_rundate CHAR(8),
IN v_report_key CHAR(10))
DYNAMIC RESULT SETS 1=20
LANGUAGE SQL=20
BEGIN=20
-- Declare variables
DECLARE v_ls_this VARCHAR(30);
DECLARE v_ls_step VARCHAR(128);
DECLARE v_li_retstat INT;
DECLARE v_li_error INT;
=20
DECLARE v_sqlstate_test CHAR(5);=20
DECLARE v_sqlcode_test INT;=20
=20
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
=20
DECLARE not_found CONDITION FOR SQLSTATE '02000';
=20
DECLARE EXIT HANDLER FOR SQLEXCEPTION=20
SELECT SQLSTATE,SQLCODE INTO v_sqlstate_test,v_sqlcode_test FROM
sysibm.sysdummy1;
-- Create temporary table to hold components changed on run date
DECLARE GLOBAL TEMPORARY TABLE SESSION."#temp"
(
parent_key CHAR(10) NOT NULL,
fc_key CHAR(10) NOT NULL,
target_pct_c DOUBLE NOT NULL,
target_pct_p DOUBLE NOT NULL,
buy_tol_pct_c DOUBLE NOT NULL,
buy_tol_pct_p DOUBLE NOT NULL,
sell_tol_pct_c DOUBLE NOT NULL,
sell_tol_pct_p DOUBLE NOT NULL,
buy_pct_c DOUBLE NOT NULL,
buy_pct_p DOUBLE NOT NULL,
sell_pct_c DOUBLE NOT NULL,
sell_pct_p DOUBLE NOT NULL
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
BEGIN=20
=20
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR=20
SELECT f.fund_type, ft.fund_type_desc, f.name, f.ta_sys_id AS ul_ataac,
c.name, c.cusip, coalesce(ad1.buy_pct, 0) AS buy_pct,
coalesce(ad1.sell_pct, 0) AS sell_pct,
CASE
WHEN ((v_rundate IS NULL) OR
CAST(SYB.date_to_char12(fc1.last_update_time, 1) AS
CHAR(8)) <>
v_rundate) AND
((v_rundate IS NULL) OR
CAST(SYB.date_to_char12(rd1.last_update_time, 1) AS
CHAR(8)) <>
v_rundate) AND
((v_rundate IS NULL) OR
CAST(SYB.date_to_char12(ad1.last_update_time, 1) AS
CHAR(8)) <>
v_rundate)
THEN CAST(NULL AS CHAR(1))
WHEN fc1.last_update_time >
CASE
WHEN coalesce(rd1.last_update_time,
CAST('2000-01-01-00.00.00' AS TIMESTAMP))
>
coalesce(ad1.last_update_time,
CAST('2000-01-01-00.00.00' AS TIMESTAMP))
THEN coalesce(rd1.last_update_time,
CAST('2000-01-01-00.00.00' AS TIMESTAMP))
ELSE coalesce(ad1.last_update_time,
CAST('2000-01-01-00.00.00' AS TIMESTAMP))
=20
END=20
THEN CAST(SYB.date_to_char8(fc1.last_update_time, 1) AS
CHAR(30))
ELSE CASE
WHEN coalesce(rd1.last_update_time,
CAST('2000-01-01-00.00.00' AS TIMESTAMP))
>
coalesce(ad1.last_update_time,
CAST('2000-01-01-00.00.00' AS TIMESTAMP))
THEN CAST(SYB.date_to_char8(rd1.last_update_time, 1) AS
CHAR(30))
ELSE CAST(SYB.date_to_char8(ad1.last_update_time, 1) AS
CHAR(30))
=20
END=20
=20
END AS update_time, fc1.target_pct,
coalesce(rd1.buy_tolerance_pct, 0) AS buy_tolerance_pct,
coalesce(rd1.sell_tolerance_pct, 0) AS sell_tolerance_pct,
(SELECT COUNT (9)=20
FROM fund_component
WHERE parent_key =3D f.fund_key) AS ul_ataac_count
=20
FROM fund AS f INNER JOIN fund_type AS ft ON f.fund_type =3D =
ft.fund_type
INNER JOIN fund_component AS fc1 ON=20
f.fund_key =3D fc1.parent_key INNER JOIN component AS c ON
fc1.comp_comp_key =3D
c.comp_key
INNER JOIN rebalance_detail AS rd1 ON=20
fc1.fc_key =3D rd1.fc_key AND 'DEFAULT' =3D rd1.model_key INNER JOIN
allocation_detail_stage AS ad1 ON=20
fc1.fc_key =3D ad1.fc_key AND ' ' =3D ad1.activity_type
WHERE f.fund_key IN
(SELECT parent_key=20
FROM SESSION."#temp"
WHERE target_pct_c <> target_pct_p OR buy_tol_pct_c <>
buy_tol_pct_p
OR sell_tol_pct_c <> sell_tol_pct_p OR buy_pct_c <>
buy_pct_p OR
sell_pct_c <> sell_pct_p)
ORDER BY f.fund_type, ul_ataac, c.cusip ;
-- Validate parameters
IF v_rundate IS NULL THEN=20
=20
RETURN -1;
END IF;
IF v_report_key IS NULL THEN=20
=20
RETURN -1;
END IF;
SET v_ls_step =3D 'create #temp';
IF SUBSTR(SQLSTATE,2) NOT IN ('00','01','02') THEN=20
SIGNAL not_found SET MESSAGE_TEXT =3D 'NOT CREATED !!';
END IF;
=09
SET v_ls_step =3D 'insert into #temp';
=20
INSERT INTO SESSION."#temp"
SELECT fc.parent_key, fc.fc_key, fc.target_pct, -1,
rd.buy_tolerance_pct,
-1, rd.sell_tolerance_pct, -1, ad.buy_pct, -1, ad.sell_pct,
-1=20
FROM fund_component AS fc INNER JOIN rebalance_detail AS rd ON
fc.fc_key
=3D
=20
rd.fc_key
AND
=20
'DEFAULT'
=3D
=20
rd.model_key INNER JOIN allocation_detail_stage AS ad ON=20
fc.fc_key =3D ad.fc_key AND ' ' =3D ad.activity_type
WHERE (CAST(SYB.date_to_char12(fc.last_update_time, 1) AS CHAR(8)) =
=3D
v_rundate OR
CAST(SYB.date_to_char12(rd.last_update_time, 1) AS CHAR(8)) =
=3D
v_rundate
OR
CAST(SYB.date_to_char12(ad.last_update_time, 1) AS CHAR(8)) =
=3D
v_rundate) AND
EXISTS
(SELECT 9=20
FROM fund_report_destination AS frd
WHERE frd.report_key =3D v_report_key AND
frd.fund_key =3D fc.parent_key AND frd.include_in_rpt =
=3D
'Y');
COMMIT;
IF SUBSTR(SQLSTATE,2) NOT IN ('00','01','02') THEN=20
SIGNAL not_found SET MESSAGE_TEXT =3D 'NO Record Found !!';
END IF;
-- update temporary table with previous values
SET v_ls_step =3D 'update #temp';
=20
UPDATE SESSION."#temp"
SET (target_pct_p,buy_tol_pct_p,sell_tol_pct_p,buy_pct_p,sell_pct_p)
=3D (
SELECT DISTINCT fch.target_pct, rdh.buy_tolerance_pct,
rdh.sell_tolerance_pct, adh.buy_pct, adh.sell_pct

FROM fund_component_his AS fch INNER JOIN rebalance_detail_his AS
rdh ON=20
fch.fc_key =3D rdh.fc_key AND 'DEFAULT' =3D rdh.model_key INNER JOIN
allocation_detail_his AS adh ON=20
fch.fc_key =3D adh.fc_key AND ' ' =3D adh.activity_type
WHERE SESSION."#temp".fc_key =3D fch.fc_key AND
fch.last_update_time =3D
(SELECT MAX (last_update_time)=20
FROM fund_component_his
WHERE fc_key =3D fch.fc_key AND
CAST(SYB.date_to_char12(last_update_time, 1) AS
CHAR(8)) <
v_rundate)
AND
rdh.last_update_time =3D
(SELECT MAX (last_update_time)=20
FROM rebalance_detail_his
WHERE fc_key =3D fch.fc_key AND
CAST(SYB.date_to_char12(last_update_time, 1) AS
CHAR(8)) <
v_rundate)
AND
adh.last_update_time =3D
(SELECT MAX (last_update_time)=20
FROM allocation_detail_his
WHERE fc_key =3D fch.fc_key AND
CAST(SYB.date_to_char12(last_update_time, 1) AS
CHAR(8)) <
v_rundate))
WHERE EXISTS
(SELECT * =20
FROM fund_component_his AS fch INNER JOIN
rebalance_detail_his AS rdh ON=20
fch.fc_key =3D rdh.fc_key AND 'DEFAULT' =3D rdh.model_key =
INNER
JOIN allocation_detail_his AS adh ON=20
fch.fc_key =3D adh.fc_key AND ' ' =3D adh.activity_type
WHERE SESSION."#temp".fc_key =3D fch.fc_key AND
fch.last_update_time =3D
(SELECT MAX (last_update_time)=20
FROM fund_component_his
WHERE fc_key =3D fch.fc_key AND
CAST(SYB.date_to_char12(last_update_time, 1) AS
CHAR(8))
< v_rundate)
AND
rdh.last_update_time =3D
(SELECT MAX (last_update_time)=20
FROM rebalance_detail_his
WHERE fc_key =3D fch.fc_key AND
CAST(SYB.date_to_char12(last_update_time, 1) AS
CHAR(8))
< v_rundate)
AND
adh.last_update_time =3D
(SELECT MAX (last_update_time)=20
FROM allocation_detail_his
WHERE fc_key =3D fch.fc_key AND
CAST(SYB.date_to_char12(last_update_time, 1) AS
CHAR(8))
< v_rundate));
IF SUBSTR(SQLSTATE,2) NOT IN ('00','01','02') THEN=20
SIGNAL not_found SET MESSAGE_TEXT =3D 'NOT UPDATED !!';
END IF;

-- select ALL fund components where fund exists in temp table with
ANY changed component percentage(s)=20
SET v_ls_step =3D 'select from #temp';
=20
OPEN temp_cursor;
=09
IF SUBSTR(SQLSTATE,2) NOT IN ('00','01','02') THEN=20
SIGNAL not_found SET MESSAGE_TEXT =3D 'NOT FOUND !!';
END IF;

-- Delete temporary table
SET v_ls_step =3D 'drop #temp';
=09
DROP TABLE SESSION."#temp";
IF (SQLCODE<>0) THEN
--IF SUBSTR(SQLSTATE,2) NOT IN ('00','01','02') THEN=20
SIGNAL not_found SET MESSAGE_TEXT =3D 'NOT FOUND !!';
END IF;
=09
END;
END!


DB2 & UDB email list listserv db2-l LazyDBA home page