pr_r
asked on
Fenced procedure
How do I create a fenced stored procedure. I tried adding FEnced just below create procedure. But it is giving me in valid syntax error. I am using SQL Stored procedure
ASKER
HI Kent,
I have a procedure which is used extensively. So when the DBA monitored the system , this procedure takes lot of memory. What should I do? one option I thought was fenced. But you are saying that is phaased out. Note. That procedure returns a resultset to the calling program
I have a procedure which is used extensively. So when the DBA monitored the system , this procedure takes lot of memory. What should I do? one option I thought was fenced. But you are saying that is phaased out. Note. That procedure returns a resultset to the calling program
Making the procedure Fenced will actually hurt performance even more. In fenced mode, the procedure runs as it's own task and exchanges input and output with DB2. In non-fenced mode, the procedure runs as part of DB2 and the data is exchanged between the procedure and DB2 by memory address.
It might be that you try to improve the procedure. Can you post it here?
Kent
It might be that you try to improve the procedure. Can you post it here?
Kent
ASKER
REATE PROCEDURE proc
(IN wher VARCHAR(5000),OUT msgtxt CHAR(100))
DYNAMIC RESULT SETS 1
BEGIN
DECLARE stmt VARCHAR(5000);
DECLARE cur1 CURSOR with return FOR PreparedStatement;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
msgtxt=MESSAGE_TEXT;
SET stmt = ' SELECT * FROM tab ' || wher;
PREPARE PreparedStatement FROM stmt;
--cursor open for Client application
open cur1;
END
(IN wher VARCHAR(5000),OUT msgtxt CHAR(100))
DYNAMIC RESULT SETS 1
BEGIN
DECLARE stmt VARCHAR(5000);
DECLARE cur1 CURSOR with return FOR PreparedStatement;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
msgtxt=MESSAGE_TEXT;
SET stmt = ' SELECT * FROM tab ' || wher;
PREPARE PreparedStatement FROM stmt;
--cursor open for Client application
open cur1;
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Fenced SQL procedures were phased out a while back. (Other fenced functions still exist though.)
With version 8 (and 9), when an SQL procedure is compiled it generates thread-safe code as to not be dangerous to DB2. Fenced procedures are intended to keep DB2 safe from bad or malicious code. Since SQL can not generate malicious code, running in fenced mode is an unnecessary performance degradation.
Fenced mode procedures for objects written in other languages, like C still exist.
Good Luck,
Kent