Solved

Fenced procedure

Posted on 2008-10-21
5
1,335 Views
Last Modified: 2008-11-11
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
0
Comment
Question by:pr_r
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22766525
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
 
LVL 2

Author Comment

by:pr_r
ID: 22819697
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22821224
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
 
LVL 2

Author Comment

by:pr_r
ID: 22828703
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
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 180 total points
ID: 22830228
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
I need a like query for db2 3 86
DB2 iSeries Combine Results of 2 Selects (nested Join?) ? 2 78
How to create a stored procedure in AS400 1 160
filter only by one character 4 47
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question