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
LVL 2
pr_rAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi pr_r,

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
0
pr_rAuthor Commented:
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
0
Kent OlsenData Warehouse Architect / DBACommented:
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
0
pr_rAuthor Commented:
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
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi pr_r,

I'm not sure what you want from that code.

IBM has a "decent" write up of FENCE / NOT FENCED here:

  http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp?topic=/com.ibm.db2v7.doc/db2a0/db2a050.htm


One thing that the article doesn't mention is that at DB2 versions 8 and 9, all SQL procedures run in trusted mode.  For SQL procedures, there is no such thing as FENCED or NOT FENCED.  Since you tagged the original question with DB2 9.1 I'm assuming that your question refers to that version of DB2.  


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.